读取 exl - s9797456/MyCommonCodeSnippets GitHub Wiki
package com.xinle.car.admin.common.util;
import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile;
/**
- poi读取excel数据 工具类
- @author yx
/
public class ReadExcelUtil {
/*
* 读取Excel的数据，自动判断是2003(含2003)前的版本(xls)，还是2007(含2007)之后版本(xlsx)
* @param templetFile 文件
* @param startRow 开始行号 ，从0开始（如果第一行是标题，则从1开始）
* @param startCol 开始列号 ，从0开始
* @param sheetNum 开始标签号sheet，从0开始
* @return list<Map<String,String>>
*/
public static List<Map<String,String>> readExcel(MultipartFile templetFile, int startRow, int startCol, int sheetNum){
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
if(templetFile != null && templetFile.getSize() > 0){
String ofn = templetFile.getOriginalFilename().toLowerCase();// 文件名
/String extName = ""; // 扩展名格式
if(ofn.lastIndexOf(".") >= 0){
extName = ofn.substring(ofn.lastIndexOf("."));
}
if(".xls".equals(extName.toLowerCase())){
varList = readExcelByXls(templetFile, startRow, startCol, sheetNum);
}else if(".xlsx".equals(extName.toLowerCase())){
varList = readExcelByXlsx(templetFile, startRow, startCol, sheetNum);
}/
if(isExcel2003(ofn)){
list = readExcelByXls(templetFile, startRow, startCol, sheetNum);
}else if(isExcel2007(ofn)){
list = readExcelByXlsx(templetFile, startRow, startCol, sheetNum);
}
}
return list;
}
/**
* 判断是否是2003的excel，返回true是2003
* @param filePath
* @return
*/
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 判断是否是2007的excel，返回true是2007
* @param filePath
* @return
*/
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 读取Excel2003以前（包括2003）（扩展名是.xls）的数据
* @param templetFile 文件
* @param startRow 开始行号
* @param startCol 开始列号
* @param sheetNum 开始标签号sheet
* @return list
*/
public static List<Map<String,String>> readExcelByXls(MultipartFile templetFile, int startRow, int startCol, int sheetNum) {
List<Map<String,String>> varList = new ArrayList<Map<String,String>>();
try {
HSSFWorkbook wb = new HSSFWorkbook(templetFile.getInputStream());
HSSFSheet sheet = wb.getSheetAt(sheetNum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startRow; i < rowNum; i++) { //行循环开始
Map<String,String> varpd = new HashMap<String,String>();
HSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startCol; j < cellNum; j++) { //列循环开始
HSSFCell cell = row.getCell(Integer.parseInt(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
if(HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
} else {
cell.setCellType(1);
cellValue = cell.getStringCellValue();
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
//cell.setCellType(1);
//cellValue = cell.getStringCellValue();
//cellValue = cell.getNumericCellValue() + "";
cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
/**
* 读取Excel2007版本（扩展名是.xlsx）的数据
* @param templetFile 文件
* @param startRow 开始行号
* @param startCol 开始列号
* @param sheetNum 开始标签号sheet
* @return list
*/
public static List<Map<String,String>> readExcelByXlsx(MultipartFile templetFile, int startRow, int startCol, int sheetNum) {
List<Map<String,String>> varList = new ArrayList<Map<String,String>>();
try {
InputStream in = templetFile.getInputStream();
if(in == null) {
return null;
}
XSSFWorkbook wb = new XSSFWorkbook(in);
XSSFSheet sheet = wb.getSheetAt(sheetNum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startRow; i < rowNum; i++) { //行循环开始
Map<String,String> varpd = new HashMap<String,String>();
XSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startCol; j < cellNum; j++) { //列循环开始
XSSFCell cell = row.getCell(Integer.parseInt(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case 0:
if(HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
} else {
cell.setCellType(1);
cellValue = cell.getStringCellValue();
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getStringCellValue();
//cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var"+j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
System.out.println(e);
}
return varList;
}
}