1、poi简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
2、maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
3、ExcelReaderUtil.java
public class ExcelReaderUtil {
// 默认单元格内容为数字时格式
private static DecimalFormat df = new DecimalFormat("0");
// 默认单元格格式化日期字符串
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static class ExcelRow {
//所在行标,从1开始
private int row;
//该行数据
private List<Object> datas = new ArrayList<>();
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
@Override
public String toString() {
return "[" + datas + "]";
}
}
/**
* 读取Excel文件,支持2007和2003两种版本
* 去掉空行,空单元格对应"",浮点数原样输出
* @param inputStream 输入流
* @param containTitle 是否包含表头
* @return
* @throws IOException
* List<List<Object>>
*/
@SuppressWarnings("deprecation")
public static List<ExcelRow> readExcel(InputStream inputStream, boolean isExcel2007, boolean containTitle) throws IOException {
List<ExcelRow> rowList = new ArrayList<>();
try {
//初始化Workbook对象
Workbook wb = null;
if (isExcel2007) {
wb = new XSSFWorkbook(inputStream);
} else {
wb = new HSSFWorkbook(inputStream);
}
//读取标题行,获取列数
Sheet sheet = wb.getSheetAt(0);
//第一列开始的行标
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(firstRowNum);
//表头列数
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
//遍历数据表,i是游标,要将有数据的读完,中间没数据的行也加入
int i = firstRowNum;
if (!containTitle) {
i = firstRowNum +1;
}
for (; i < lastRowNum + 1 ; i++) {
row = sheet.getRow(i);
//一行数据
List<Object> colList = new ArrayList<Object>();
if (row == null) {
continue;
}
boolean allBlank = true;
for (int j = firstCellNum; j < lastCellNum ; j++) {
Cell cell = row.getCell(j);
Object value = "";
if (cell == null) {
colList.add(value);
continue;
}
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
double num = cell.getNumericCellValue();
if(isInteger(num)) {
value = df.format(cell.getNumericCellValue());
} else {
value = num;
}
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
}// end switch
if(StringUtils.isNotBlank(value.toString())){
allBlank = false;
}
colList.add(value);
} // end for j
if (!allBlank) {
ExcelRow excelRow = new ExcelRow();
excelRow.setRow(i+1);
excelRow.setDatas(colList);
rowList.add(excelRow);
}
} // end for i
wb.close();
} catch (OfficeXmlFileException e){
throw new IOException("文件后缀与实际文件类型不符", e);
}
return rowList;
}
//判断整数(int)
private static boolean isInteger(double value) {
if(value % 1 == 0){// 是这个整数,小数点后面是0
return true;
}else{//不是整数,小数点后面不是0
return false;
}
}
}
4、调用
boolean isExcel2007 = false;
if (StringUtils.endsWithIgnoreCase(fileName, ".xlsx")) {
isExcel2007 = true;
}
List<ExcelRow> datas = ExcelReaderUtil.readExcel(file.getInputStream(), isExcel2007, true);
String col1 = (String) datas.get(i).getDatas().get(0);