POI、Easy Excel操作Excel

1.常用的场景

• 1. 将用户信息导出为Excel表格（导出数据）
• 2. 将Excel表中的信息录入到网站数据库中（习题上传）大大减轻网站录入量。开发中经常会用到设计处理Excel，如导入Excel和导出Excel
• 3. 目前操作Excel比较流行的就是Apach的POI和阿里巴巴的Easy Excel。
• 4. POI官网：https://poi.apache.org/
• 5. Easy Excel官网：https://github.com/alibaba/easyexcel

2.基本功能

• 1.POI的基本功能：（会比较麻烦，可能会出现OOM（Out Of Memory）异常）
  
  其中：第一个HSSF和XSSF的区别（前者用03版本的Excel，后者用07年的Excel）
  • 1.1 内存问题：
    POI 当数据为100w的时候，先将100w的数据先加载到内存当中（OOM），再写入文件
• 2. Easy Excel:
  • 2.1 简介，官方文档：(已经迁移到)https://easyexcel.opensource.alibaba.com/docs/current/
    
  • 2.2 内存问题：
    相对于POI来说，上图反映了POI会把所有内容加载到内存当中，而Easy Excel会一行一行通过磁盘返回。

3.Excel在Java中是一个对象

• 1. 工作簿
• 2. 工作表
• 3. 行
• 4. 列

4. 简单的写（07版本（.xlsx）Excel）

03版本代码中有标注，生成文件时候，需要改后缀为：(.xls)

package com.dapeng;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;import java.io.FileNotFoundException;
import java.io.FileOutputStream;/*** @Description* @Author zhaopeng* @Date 2023/10/13 9:49*/
public class ExcelWriteTest {@Testpublic void testWrite() throws Exception {String PATH = "D:\\ExcelTest\\";// 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)// Workbook workbook = new HSSFWorkbook();03版本Workbook workbook = new SXSSFWorkbook();// 2. 创建一个工作表Sheet sheet = workbook.createSheet("我的工作表");// 3. 创建一行(0代表从第一行开始创建)Row row1 = sheet.createRow(0);// 4. 创建一列(0代表从第一列开始创建),Cell代表一个单元格Cell cell11 = row1.createCell(0);Cell cell12 = row1.createCell(1);// 5. 给单元格赋值cell11.setCellValue("测试数据1-1");cell12.setCellValue("Cell12数据");// 来个第二行Row row2 = sheet.createRow(1);Cell cell21 = row2.createCell(0);Cell cell22 = row2.createCell(1);// 来个时间String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell21.setCellValue(time);cell22.setCellValue("第二行第二个数据");// 生成一张表FileOutputStream fileOutputStream = new FileOutputStream(PATH + "test.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();}
}

效果：

大文件写HSSF

• 优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
• 缺点：最多只能处理65535行，否则会抛出异常：

java.lang.IlleaglArgumentException:Invalid row number(65535) outside allowable rang (0..65535)

• 代码截图：(没有自己写–自己没有03版本的excel)
  
• 当超过65536 的时候会报错
  

大文件写XSSF

• 优点：可以写较大的数据量，如20万条。
• 缺点：写数据时非常慢，非常耗内存，也会发生内存溢出，如100万条。

  @Testpublic void testWrite07BigData() throws Exception {long startTime = System.currentTimeMillis();// 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)Workbook workbook = new XSSFWorkbook();// 2. 创建一个工作表Sheet sheet = workbook.createSheet("我的工作表");// 3.循环写入大数据for (int i = 0; i < 100000; i++) {// 创建行Row row = sheet.createRow(i);for (int j = 0; j < 10; j++) {// 创建列jCell cell = row.createCell(j);// 给列赋值cell.setCellValue(j);}}System.out.println("完事了");// 生成一张表FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();long end = System.currentTimeMillis();System.out.println("花费了：" + (double)(end - startTime)/1000 + "秒");}

大文件写SXSSF

• 优点：可以写非常大的数据量，如100万条甚至更多条，写数据快，占用更少的内存。
  
• Demo（没有多大变化，只用了SXSSFWorkbook类）

 @Testpublic void testWrite07BigData() throws Exception {long startTime = System.currentTimeMillis();// 1.创建一个工作簿(SXSSF优化版的07的工作簿，HSSF是03年的)Workbook workbook = new SXSSFWorkbook();// 2. 创建一个工作表Sheet sheet = workbook.createSheet("我的工作表");// 3.循环写入大数据for (int i = 0; i < 1000000; i++) {// 创建行Row row = sheet.createRow(i);for (int j = 0; j < 10; j++) {// 创建列jCell cell = row.createCell(j);// 给列赋值cell.setCellValue(j);}}System.out.println("完事了");// 生成一张表FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");workbook.write(fileOutputStream);fileOutputStream.close();// 清除临时文件((SXSSFWorkbook)workbook).dispose();long end = System.currentTimeMillis();System.out.println("花费了：" + (double)(end - startTime)/1000 + "秒");}

5. Excel读

• Demo:

package com.dapeng;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;import java.io.FileInputStream;/*** @Description* @Author zhaopeng* @Date 2023/10/13 10:43*/
public class ExcelReadTest {String PATH = "D:\\ExcelTest\\";@Testpublic void testRead() throws Exception{// 1. 获取文件流读取文件FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");// 2. 创建一个工作簿Workbook workbook = new XSSFWorkbook(inputStream);// 3. 得到表(通过下标);Sheet sheet = workbook.getSheetAt(0);// 4. 得到行(第一行)Row row = sheet.getRow(0);// 5. 获取第一个数据Cell cell = row.getCell(0);// 6.输出数据System.out.println(cell.getStringCellValue());inputStream.close();}
}

5.1 读取遇到类型转化问题该怎么解决

@Testpublic void testReadTeyp() throws Exception {// 1. 获取文件流读取文件FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");// 2. 创建一个工作簿Workbook workbook = new XSSFWorkbook(inputStream);// 3. 得到表(通过下标);Sheet sheet = workbook.getSheetAt(0);// 4. 获取第一行标题Row rowTitle = sheet.getRow(0);//      4.1 获取标题的数量if (rowTitle != null) {// 必须掌握，拿到所有的列int cells = rowTitle.getPhysicalNumberOfCells();for (int i = 0; i < cells; i++) {Cell cell = rowTitle.getCell(i);if (cell != null) {int cellType = cell.getCellType();String cellValue = cell.getStringCellValue();System.out.print(cellValue + " | ");}}}// 获取行的总数int rowCounts = sheet.getPhysicalNumberOfRows();for (int rowNum = 1; rowNum < rowCounts; rowNum++) {// 获取每一行Row row = sheet.getRow(rowNum);if (row != null) {// 读取列int columns = rowTitle.getPhysicalNumberOfCells();for (int cellColumn = 0; cellColumn < columns; cellColumn++) {System.out.print("[" + rowNum + "-" + cellColumn + "]");// 获取每一个数据Cell cell = row.getCell(cellColumn);// 匹配列数据类型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 time = cell.getDateCellValue();// 使用工具类把日期进行转换cellValue = new DateTime(time).toString("yyyy-MM-dd HH:mm:ss");} 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);}}}}inputStream.close();}

5.2 遇到Excel公式怎么办

   @Testpublic void readMath() throws Exception{FileInputStream inputStream = new FileInputStream(PATH + "test.xlsx");// 读取Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);// 这个单元格前提有公式比如=SUM(A2:A4);Row row = sheet.getRow(4);Cell cell = row.getCell(0);// 1.拿到该工作簿的公式XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);// 输出单元格内容,先获取类型int cellType = cell.getCellType();switch (cellType){case HSSFCell.CELL_TYPE_FORMULA: //公式// 先获取公式String formula = cell.getCellFormula();System.out.println(formula); // 输出公式// 计算得到值CellValue evaluate = formulaEvaluator.evaluate(cell);System.out.println(evaluate);// 输出计算好的值String value = evaluate.formatAsString();// 把值转化为String输出System.out.println(value);break;}}

6. Easy Excel

6.1简单的写

参考官方文档：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write

1. 导入依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.0-beta2</version>
</dependency>

2. 数据来源：

  private List<Entity> data() {List<Entity> list = new ArrayList<Entity>();for (int i = 0; i < 10; i++) {Entity data = new Entity();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}

3. 一行代码导出Excel

 // 根据List写入值@Testpublic void simpleWrite(){// 1. 当前的位置String PATH = "D:\\ExcelTest\\";String fileName= PATH + "esayExcel.xlsx";EasyExcel.write(fileName,Entity.class).sheet("easyExcel").doWrite(data());}

6.2 简单的读

参考：

1. 先写一个类继承AnalysisEventListener并重写invoke方法，数据的处理逻辑全在这里。

package com.dapeng.DemoEntity;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.List;
import java.util.Map;/*** @Description* @Author zhaopeng* @Date 2023/10/13 15:31*/
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener extends AnalysisEventListener<DemoData> {/*** 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收*/private static final int BATCH_COUNT = 5;List<DemoData> list = new ArrayList<>();private DemoDAO demoDAO;public DemoDataListener() {// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数demoDAO = new DemoDAO();}/*** 这个每一条数据解析都会来调用** @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context*/@Overridepublic void invoke(DemoData data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));System.out.println("解析到一条数据:{}" +  JSON.toJSONString(data));list.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (list.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listlist.clear();}}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();log.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", list.size());demoDAO.save(list);log.info("存储数据库成功！");}
}

2. 测试：

    /*** 最简单的读* <p>* 1. 创建excel对应的实体对象 参照{@link DemoData}* <p>* 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}* <p>* 3. 直接读即可*/@Testpublic void simpleRead() {// 写法1：JDK8+ ,不用额外写一个DemoDataListener// since: 3.0.0-beta1String PATH = "D:\\ExcelTest\\";String fileName= PATH + "esayExcel.xlsx";// 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行// 具体需要返回多少行可以在`PageReadListener`的构造函数设置EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();}