POI及EasyExcel
常用场景
-
将用户信息导出为excel表格（导出数据…）
-
将Excel表中的数据录入到网站数据库（习题上传…）大大减轻网站录入量！
开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中！
操作Excel目前比较流行的就是 Apache POI 和阿里巴巴的 easyExcel！
Apache POI
官网：https://poi.apache.org/ 原生的，会比较麻烦！弊端：量大时，可能包OOM。
简介：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k686aXyN-1626757457887)(POI及EasyExcel.assets/image-20210716155844055.png)]
使用流程
1、导入依赖（创建一个普通maven项目）
<!--导入依赖-->
<dependencies>
<!--xls（03）-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!--xls（07）-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
注：03（xls）Excel 最多65536行
07（xlsx）理论上是无限的
2、POI-Excel 写
03|07
public class ExcelWriteTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
// 03版本
@Test
public void testWrite03() throws Exception {
//写03版本的Excel
// 1、创建一个工作薄
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("01统计表");
// 3、创建一个行 （1,1）
Row row1 = sheet.createRow(0);// Java从0开始，Excel从1开始
// 4、创建一个单元格
Cell cell1 = row1.createCell(0);// Java从0开始，Excel从1开始
cell1.setCellValue("员工名称");
//（1,2）
Cell cell2=row1.createCell(1);
cell2.setCellValue("张三");
//第二行（2,1）
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("创建时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//生成一张表（IO 流）03版本就是使用xls结尾！
FileOutputStream fileOutputStream = new FileOutputStream(path + "测试统计表03.xls");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("测试统计表03 生成完毕！");
}
// 07版本
@Test
public void testWrite07() throws Exception {
//写07版本的Excel
// 1、创建一个工作薄 07
Workbook workbook = new XSSFWorkbook();//SXSSFWorkbook比XSSFWorkbook效率高
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("02统计表");
// 3、创建一个行 （1,1）
Row row1 = sheet.createRow(0);// Java从0开始，Excel从1开始
// 4、创建一个单元格
Cell cell1 = row1.createCell(0);// Java从0开始，Excel从1开始
cell1.setCellValue("员工名称");
//（1,2）
Cell cell2=row1.createCell(1);
cell2.setCellValue("张三");
//第二行（2,1）
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("创建时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//生成一张表（IO 流）07版本就是使用xlsx结尾！
FileOutputStream fileOutputStream = new FileOutputStream(path + "测试统计表07.xlsx");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
System.out.println("测试统计表07 生成完毕！");
}
}
3、数据批量写入
大文件写HSSF
缺点：最多只能处理65536行，否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点：过程中写入缓存，不操作磁盘，最多一次性写入磁盘，速度快
public class ExcelWriteTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
@Test
public void testWrite03BigData() throws Exception {
// 时间
long begin = System.currentTimeMillis();
// 创建一个薄
Workbook workbook = new HSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite03BigData.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end=System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
}
大文件写XSSF
缺点：写数据是数独非常慢，非常耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量，如20万条
public class ExcelWriteTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
// 耗时较长！优化，缓存
@Test
public void testWrite07BigData() throws Exception 、、
// 时间
long begin = System.currentTimeMillis();
// 创建一个薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end=System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
}
大文件写SXSSF
优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存
注意：
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件
如果想自定义内存中数据的数量，可以使用 new SXSSFWorkbook（数量）
public class ExcelWriteTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
@Test
public void testWrite07BigDataS() throws Exception {
// 时间
long begin = System.currentTimeMillis();
// 创建一个薄
Workbook workbook = new SXSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigDataS.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
// 清除临时文件！
((SXSSFWorkbook) workbook).dispose();
long end=System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
}
SXSSFWorkbook 来自官方的解释：实现 “BigGridDemo”策略的流式XSSFWorkbook版本，这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。
请注意，仍然可能会消耗大量内存，这些内存基于您在使用的功能，例如合并区域，注释……仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。
再使用 POI 的时候！内存问题 Jprofile！
4、POI-Excel 读
03|07
03版本
public class ExcelReadTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "测试统计表03.xls");
// 1、创建一个工作簿。 使用 Excel 能操作的这边它都可以操作！
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
Row row = sheet.getRow(1);
// 4、得到列
Cell cell = row.getCell(2);
// 读取数值的时候，一定需要注意类型！
// getStringCellValue 字符串类型
// System.out.println(cell.getStringCellValue());
// getNumericCellValue 数字
System.out.println(cell.getNumericCellValue());
fileInputStream.close();
}
}
07版本
public class ExcelReadTest {
String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\";
@Test
public void testRead07() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "测试统计表07.xlsx");
// 1、创建一个工作簿。 使用 Excel 能操作的这边它都可以操作！
Workbook workbook = new XSSFWorkbook(fileInputStream);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
Row row = sheet.getRow(1);
// 4、得到列
Cell cell = row.getCell(2);
// 读取数值的时候，一定需要注意类型！
// getStringCellValue 字符串类型
// System.out.println(cell.getStringCellValue());
// getNumericCellValue 数字
System.out.println(cell.getNumericCellValue());
fileInputStream.close();
}
}
注意获取值的类型即可
读取不同的数据类型（最麻烦的就是这里了！）
public class ExcelReadTest { String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\"; @Test public void testCellType() throws Exception { // 获取文件流 FileInputStream fileInputStream = new FileInputStream(path + "多类型数据测试.xlsx"); // 创建一个工作簿，使用 Excel 能操作的这边它都可以操作！ Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); // 获取标题内容 Row row = sheet.getRow(0); if (row != null){ // 一定要掌握 int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum <cellCount ; cellNum++) { Cell cell = row.getCell(cellNum); if (cell != null){ CellType cellType = cell.getCellType(); String CellValue = cell.getStringCellValue(); System.out.print(CellValue+" | "); } } System.out.println(); } // 获取表中的内容 int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row rowData = sheet.getRow(rowNum); if (rowData != null){ // 读取列 int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]"); Cell cell = rowData.getCell(cellNum); // 匹配列的数据类型 if (cell != null){ CellType cellType = cell.getCellType(); String cellValue = ""; switch (cellType){ case STRING: // 字符串 System.out.print("【String】"); cellValue = cell.getStringCellValue(); break; case BOOLEAN: // 布尔 System.out.print("【BOOLEAN】"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: // 空 System.out.print("【BLANK】"); break; case NUMERIC: // 数字（日期、普通数字） System.out.print("【NUMERIC】"); if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期 System.out.print("【日期】"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); }else { // 不是日期格式，防止数字过长！ System.out.print("【转换为字符串输出】"); cell.setCellType(CellType.STRING); cellValue=cell.toString(); } break; case ERROR: // 错误 System.out.print("【数据类型错误】"); break; } System.out.println(cellValue); } } } } fileInputStream.close(); }}
注意，类型转换问题：
提取上面代码为工具类
public void testCellType(FileInputStream fileInputStream) throws Exception { // 获取文件流 // 创建一个工作簿，使用 Excel 能操作的这边它都可以操作！ Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); // 获取标题内容 Row row = sheet.getRow(0); if (row != null){ // 一定要掌握 int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum <cellCount ; cellNum++) { Cell cell = row.getCell(cellNum); if (cell != null){ CellType cellType = cell.getCellType(); String CellValue = cell.getStringCellValue(); System.out.print(CellValue+" | "); } } System.out.println(); } // 获取表中的内容 int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row rowData = sheet.getRow(rowNum); if (rowData != null){ // 读取列 int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]"); Cell cell = rowData.getCell(cellNum); // 匹配列的数据类型 if (cell != null){ CellType cellType = cell.getCellType(); String cellValue = ""; switch (cellType){ case STRING: // 字符串 System.out.print("【String】"); cellValue = cell.getStringCellValue(); break; case BOOLEAN: // 布尔 System.out.print("【BOOLEAN】"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: // 空 System.out.print("【BLANK】"); break; case NUMERIC: // 数字（日期、普通数字） System.out.print("【NUMERIC】"); if (HSSFDateUtil.isCellDateFormatted(cell)){ // 日期 System.out.print("【日期】"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); }else { // 不是日期格式，防止数字过长！ System.out.print("【转换为字符串输出】"); cell.setCellType(CellType.STRING); cellValue=cell.toString(); } break; case ERROR: // 错误 System.out.print("【数据类型错误】"); break; } System.out.println(cellValue); } } } } fileInputStream.close();}
计算公式（了解即可！）
public class ExcelReadTest { String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\"; @Test public void testFormula() throws IOException { FileInputStream fileInputStream = new FileInputStream(path + "公式表.xlsx"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(4); Cell cell = row.getCell(0); //拿到计算公式 eval FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); //输出单元格的内容 CellType cellType = cell.getCellType(); switch (cellType){ case FORMULA: // 公式 String formula = cell.getCellFormula(); System.out.println(formula); //计算 CellValue evaluate = formulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } }}
easyExcel
官网：https://alibaba-easyexcel.github.io/
EasyExcel 是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。
EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
内存问题：POI=100w先加载到内存OOM，在写入文件；easyExcel=1，一条条数据加载。
easyExcel 和 POI 解析Excel的对比图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x5xTsZb1-1626757457889)(POI及EasyExcel.assets/image-20210716161236598.png)]
使用流程
1、导入依赖
<!--导入依赖--> <dependencies> <!-- easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.20</version> </dependency> <!-- fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.76</version> </dependency> <!--日期格式化工具--> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.1</version> </dependency> <!--test--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
2、easy-Excel 写
官方网址参考：https://www.yuque.com/easyexcel/doc/write
格式类
@Datapublic class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; /** * 忽略这个字段 */ @ExcelIgnore private String ignore;}
模拟的数据和使用
public class EasyText { String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\"; private List<DemoData> data() { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } return list; } /** * 最简单的写 * <p>1. 创建excel对应的实体对象 参照{@link DemoData} * <p>2. 直接写即可 */ @Test public void simpleWrite() { // 写法1 String fileName = path+"EasyText.xlsx"; // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 // 如果这里想使用03 则 传入excelType参数即可 // write(名字，格式类) //sheet(表名) //doWrite(数据) EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); }}
3、easy-Excel 读
官方网址参考：https://www.yuque.com/easyexcel/doc/read
监听类
import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.fastjson.JSON;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.util.ArrayList;import java.util.List;// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去public class DemoDataListener extends AnalysisEventListener<DemoData> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } //读取数据会执行 invoke方法 // DemoData 类型 // AnalysisContext 分析上文 @Override public void invoke(DemoData data, AnalysisContext context) { System.out.println(JSON.toJSONString(data));// LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 持久化逻辑！ // 存储完成清理 list list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成！"); } private void saveData() { LOGGER.info("{}条数据，开始存储数据库！", list.size()); demoDAO.save(list); LOGGER.info("存储数据库成功！"); }}
Dao
/** * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。 **/public class DemoDAO { public void save(List<DemoData> list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 }}
使用
public class EasyText { String path="C:\\Users\\admin\\IdeaProjects\\POI-easyExcel\\poi\\"; /** * 最简单的读 * <p>1. 创建excel对应的实体对象 参照{@link DemoData} * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener} * <p>3. 直接读即可 */ @Test public void simpleRead() { // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 // 写法1： String fileName = path+"EasyText.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 // 重点注意读取的逻辑 DemoDataListener EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); }}
小结——学习方式
了解，面向对象的思想，学会面向接口编程！
理解使用测试API！
作业：把 EasyExcel 其它读和写等功能看看，练习测试一下
官网：https://www.yuque.com/easyexcel/doc/easyexcel
扫一扫
482
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
