在开发中会用到好多excel表格,读取表格中的数据,然后进行处理,感觉还是不熟悉。在此使用POI最新的版本做一个记录。
maven项目依赖
<!--POI依赖-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
实现excel表格中的数据读取:
package com.example.demo.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class ExcelPoiReaderUtils {
/**
* 读取excel 第1张sheet (xls和xlsx)
*
* @param filePath excel路径
* @param columns 列名(表头)
* @return
*/
public static List<List<Object>> readExcel(String filePath, String columns[]) {
Sheet sheet = null;
Row row = null;
Row rowHeader = null;
List<List<Object>> excelData = null;
Object cellData = null;
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
if (wb != null) {
// 用来存放表中数据
excelData = new ArrayList<List<Object>>();
// 获取第一个sheet
sheet = wb.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
rowHeader = sheet.getRow(0);
// 获取最大列数
int colnum = rowHeader.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
List<Object> rowData = new ArrayList<>();
for (int j = 0; j < colnum; j++) {
if(columns[j].equals(getCellFormatValue(rowHeader.getCell(j)))){
cellData = getCellFormatValue(row
.getCell(j));
rowData.add(cellData);
}
}
excelData.add(rowData);
} else {
break;
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return excelData;
}
/** 获取单个单元格数据
* @param cell
* @return
*/
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case NUMERIC: {
cellValue = cell.getNumericCellValue();
break;
}
case FORMULA: {
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
// 数字
cellValue = cell.getNumericCellValue();
}
break;
}
case STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}
测试程序
package com.example.demo.excel;
import java.util.List;
public class TestPOI {
public static void main(String[] args) {
// String xlsFilePath = "D:\\test\\apitest.xls";
String xlsFilePath = "D:\\test\\apitest.xlsx";
String[] columns = {"id","name","address"};
List<List<Object>> excelData = ExcelPoiReaderUtils.readExcel(xlsFilePath, columns);
excelData.forEach(row -> System.out.println(row));
}
}
excel表个数据
运行结果
既然拿出数据来了,后续的操作就好了,比如说用到freemark模板引擎生成想要的东西。。。。