狂神说_Java操作excel之POI和easyExcel
1.应用场景
将用户信息导出为excel表格
讲Excel表中的信息录入到网站数据库，大大减小网站数据的录入量！
开发中经常会涉及到excel的处理，如导出Excel到数据库中！
操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel
2.Apache POI
简介
Apache POI官网: https://poi.apache.org/
HSSF 对应 Excel 03 版本，最多支持65535行
XSSF对应 Excel 07 版本，行数无限制
缺点：
使用比较麻烦
数据量大的时候会可能报OOM异常
项目准备
创建maven项目，作为父项目，去掉src目录
创建module模块，也是maven项目poi-study
导入依赖
<dependencies> <!--xLs(03)--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <!--xLsx(07)--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</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>
创建两个版本的Excel文件
打开可以看到，03版最多支持到65536行，而07版不受限制，理论上无限
二者文件名后缀不同，对应操作的Java工具类也不同
明确几个概念，工作簿、工作表、行、单元格，分别对应了各自的对象
代码演示
写测试，创建类
public class ExcelWriteTest { // 构建路径 String PATH = "F:\\WorkSpace\\IDEA\\Test\\excel-study\\"; @Test public void testWrite03() throws Exception { // 创建工作簿 Workbook workbook = new HSSFWorkbook(); // 创建工作表 Sheet sheet = workbook.createSheet("考核成绩表"); // 创建第一行 Row row1 = sheet.createRow(0);// 第一行 // 创建单元格 Cell cell1 = row1.createCell(0);// 第一行的第一列 cell1.setCellValue("数学"); Cell cell2 = row1.createCell(1); cell2.setCellValue(100); // 第二行 Row row2 = sheet.createRow(1);// 第一行 Cell cell21 = row2.createCell(0);// 第一行的第一列 cell21.setCellValue("时间"); Cell cell22 = row2.createCell(1); cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss")); // 生成表,IO流,03版本使用xls后缀 FileOutputStream fileOutputStream = new FileOutputStream(PATH+"考核成绩表03.xls"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); System.out.println("考核成绩表03输出完毕"); } @Test public void testWrite07() throws Exception { // 创建工作簿 Workbook workbook = new XSSFWorkbook(); // 创建工作表 Sheet sheet = workbook.createSheet("考核成绩表"); // 创建第一行 Row row1 = sheet.createRow(0);// 第一行 // 创建单元格 Cell cell1 = row1.createCell(0);// 第一行的第一列 cell1.setCellValue("语文"); Cell cell2 = row1.createCell(1); cell2.setCellValue(100); // 第二行 Row row2 = sheet.createRow(1);// 第一行 Cell cell21 = row2.createCell(0);// 第一行的第一列 cell21.setCellValue("时间"); Cell cell22 = row2.createCell(1); cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss")); // 生成表,IO流,07版本使用xlsx后缀 FileOutputStream fileOutputStream = new FileOutputStream(PATH+"考核成绩表07.xlsx"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); System.out.println("考核成绩表07输出完毕");
testWrite07 运行报异常，为什么呢？？
大文件写HSSF-03
缺点：最多只能处理65536行，否则会报异常
java.lang.IllegalArgumentException:Invalid row number (65536) outside allowable range (0.. 65535)
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
@Test public void testwrite03BigData() throws IOException { //时间 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); } } FileOutputStream fos = new FileOutputStream(PATH + "03版本Excel大量数据测试.xls"); workbook.write(fos); fos.close(); System.out.println("over"); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000);
运行结果
第二次运行更快
大文件写XSSF-07
缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条数据
优点：可以写较大的数据量，如20万条数据
只做一下修改
Workbook workbook = new XSSFWorkbook();
Fileoutputstream ops = new Fileoutputstream(PATH +"07版本Excel大量数据测试.xlsx");
时间较长，但是可以写更多数据
大文件写SXSSF-07升级版
优点：可以写非常大量的数据库，如100万条甚至更多条，写数据速度快，占用更少的内存
注意：
过程中会产生临时文件，需要在程序运行结束后清理临时文件
默认由100条记录被保存在内存中，如果超出这数量，则最前面的数据被写入临时文件
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）
修改为
Workbook workbook = new SXSSFWorkbook(); Fileoutputstream ops = new Fileoutputstream(PATH +"07版本Excel大量数据测试.xlsx"); @Test public void testwrite07_S_BigData() throws IOException { //时间 long begin = System.currentTimeMillis(); //创建一个薄 Workbook workbook = new SXSSFWorkbook(100); //创建表 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); } } FileOutputStream fos = new FileOutputStream(PATH + "07_S_版本Excel大量数据测试.xlsx"); workbook.write(fos); fos.close(); //清除临时缓存 ((SXSSFWorkbook)workbook).dispose(); System.out.println("over"); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000);
SXSSWorkbook 来自官方解释：实现：BigGridDemo策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。
POI-Excel读
03类型
@Test public void testRead03() throws Exception { //获取文件流 FileInputStream fis = new FileInputStream(PATH + "03版本测试.xls"); //1、创建一个工作簿。使用 exceL能操作的这边他都可以操作！ Workbook workbook = new HSSFWorkbook(fis); //2、得到表 Sheet sheet = workbook.getSheetAt(0); //3、得到行 Row row = sheet.getRow(0); //4、得到列 Cell cell = row.getCell(1); //读取值的时候，一定要注意类型！ //getStringCellValue 字符串类型 System.out.println(cell.getNumericCellValue()); fis.close();
07类型
@Test public void testRead07() throws Exception { //获取文件流 FileInputStream fis = new FileInputStream(PATH + "07版本测试.xlsx"); //1、创建一个工作簿。使用 exceL能操作的这边他都可以操作！ Workbook workbook = new XSSFWorkbook(fis); //2、得到表 Sheet sheet = workbook.getSheetAt(0); //3、得到行 Row row = sheet.getRow(0); //4、得到列 Cell cell = row.getCell(0); //读取值的时候，一定要注意类型！ //getStringCellValue 字符串类型 System.out.println(cell.getStringCellValue()); fis.close();
注意获取值的类型即可
读取不同的数据类型，是工作上的重点，这段类型匹配代码工作时直接复制
判断不同的数据类型
@Test public void testCellType() throws Exception { //获取文件流 FileInputStream fis = new FileInputStream(PATH +"课题信息表20190701.xlsx"); //创建一个工作簿。使用 excel能操作的这边他都可以操作 Workbook workbook = new XSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); //获取标题内容 Row rowTitle = sheet.getRow(0); if (rowTitle != null) { //得到一行有多少列有数据 int cellCount = rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowTitle.getCell(cellNum); if (cell != null) { int 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 = rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { System.out.println("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]"); Cell cell = rowData.getCell(cellNum); //匹配列的数据类型 if (cell != null) { int cellType = cell.getCellType(); String cellValue = ""; switch (cellType) { case HSSFCell.CELL_TYPE_STRING://字符 System.out.print("【 String】"); cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN://布尔 System.out.print("【 BOOLEAN】"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_BLANK://空 System.out.print("【 BLANK】"); break; case HSSFCell.CELL_TYPE_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(HSSFCell.CELL_TYPE_STRING); cellValue = cell.toString(); } break; case HSSFCell.CELL_TYPE_ERROR://错误 System.out.print("【 数据类型错误】"); break; } System.out.println(cellValue); } } } } //关闭流 fis.close(); }
计算公式
@Test public void testFormula() throws Exception { FileInputStream fis = new FileInputStream(PATH+"公式.xls"); //创建一个工作簿。使用 excel能操作的这边他都可以操作 Workbook workbook = new HSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(4); Cell cell = row.getCell(0); //拿到计算公司 evaL FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); //输出单元格的内容 int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_FORMULA://公式 String formula = cell.getCellFormula(); System.out.println(formula); //计算 CellValue evaluate = FormulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } }
运行结果
SUM(A2:A4)
600.0
3.easyExcel
简介
easyExcel官网地址: https://github.com/alibaba/easyexcel
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节约内存著称。
EasyExcel能大量减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从一个磁盘上一行行读取数据，逐个解析。
下图是EasyExcel和POI在解析Excel时的对比图(时间与空间的相互取舍)。
官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
项目准备
添加依赖
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version >2.2.0-beta2</version>
</dependency>
由于easyexcel依赖中包含POI相关依赖,有可能发生冲突,所以注释掉
<!--导入依赖--> <dependencies> <!--fastjson--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version >1.2.62</version> </dependency> <!--easyexcel--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version >2.2.0-beta2</version> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version >1.18.12</version> </dependency> <!-- <!–xLs(03)–> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!–xLsx(07)–> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</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>
实体类
@Data public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; //忽略这个字段 @ExcelIgnore private String ignore; }
EasyExcel写入操作
package com.kuang.easy; import com.alibaba.excel.EasyExcel; import org.junit.Test; import java.util.ArrayList; import java.util.Date; import java.util.List; public class EasyTest { String PATH = "ExcelCreate\\"; //模拟写入数据 private List<DemoData> data() { java.util.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; }
//根据ist写 excel @Test public void simplewrite() { String fileName = PATH + "EasyTest.xlsx"; //这里需要指定写用哪个 class去写，然后写到第一个 sheet，名字为模板然后文件流会自动关闭 //write(fileName,格式类) //sheet（表名） //doWrite（数据） EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } }
EasyExcel读取操作
实体类
@Data public class DemoData { private String string; private Date date; private Double doubleData; }
监听器
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去 public class DemoDataListener extends AnalysisEventListener<DemoData> { private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int BATCH_COUNT = 5; List<DemoData> list = new ArrayList<DemoData>(); /** * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private DemoDAO demoDAO; public DemoDataListener() { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 demoDAO = new DemoDAO(); } /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param demoDAO */ public DemoDataListener(DemoDAO demoDAO) { this.demoDAO = demoDAO; } /** * 这个每一条数据解析都会来调用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(DemoData data, AnalysisContext context) { LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data)); list.add(data); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); LOGGER.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void saveData() { LOGGER.info("{}条数据，开始存储数据库！", list.size()); demoDAO.save(list); LOGGER.info("存储数据库成功！"); } }
持久层
/** * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。 **/ public class DemoDAO { public void save(List<DemoData> list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入 } }
读取代码
/** * 最简单的读 * <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 = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); // 写法2： fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx"; ExcelReader excelReader = null; try { excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build(); ReadSheet readSheet = EasyExcel.readSheet(0).build(); excelReader.read(readSheet); } finally { if (excelReader != null) { // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的 excelReader.finish(); } } }
固定套路
写入，固定类格式进行写入
读取，根据监听器设置的规则进行读取！
原文链接：https://blog.csdn.net/weixin_47257749/article/details/115981160
作者：你的雷哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文连接，否则保留追究法律责任的权利。
