Apache POI与Alibaba EasyExcel 的使用_综合

Apache POI 与 Alibaba EasyExcel 的使用

Apache POI

<dependencies><!-- xls(03) --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><!-- xls(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><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency>
</dependencies>

测试写（03版）

String Path = "D:\\data\\";@Test
public void testWrite03() throws Exception {
    // 创建工作簿对象 03Workbook workbook = new HSSFWorkbook();// 创建一个工作表Sheet sheet0 = workbook.createSheet("BLU测试表");// 创建一行Row row0 = sheet0.createRow(0);// 创建一个单元格Cell cell00 = row0.createCell(0);cell00.setCellValue("编号");Cell cell01 = row0.createCell(1);cell01.setCellValue("姓名");Row row1 = sheet0.createRow(1);Cell cell10 = row1.createCell(0);cell10.setCellValue(1);Cell cell11 = row1.createCell(1);cell11.setCellValue("BLU");Cell cell12 = row1.createCell(2);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell12.setCellValue(time);FileOutputStream fos = new FileOutputStream(Path + "test03.xls");workbook.write(fos);fos.close();System.out.println("文件生成完毕");
}

在这里插入图片描述

测试写（07版）

String Path = "D:\\data\\";@Test
public void testWrite07() throws Exception {
    Workbook workbook = new XSSFWorkbook();Sheet sheet0 = workbook.createSheet("BLU测试表");Row row0 = sheet0.createRow(0);Cell cell00 = row0.createCell(0);cell00.setCellValue("编号");Cell cell01 = row0.createCell(1);cell01.setCellValue("姓名");Row row1 = sheet0.createRow(1);Cell cell10 = row1.createCell(0);cell10.setCellValue(1);Cell cell11 = row1.createCell(1);cell11.setCellValue("BLU");Cell cell12 = row1.createCell(2);String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");cell12.setCellValue(time);FileOutputStream fos = new FileOutputStream(Path + "test07.xlsx");workbook.write(fos);fos.close();System.out.println("文件生成完毕");
}

在这里插入图片描述

测试大数据写入（03版）

String Path = "D:\\data\\";/*** 65536行数据耗时：2.815s * 最多只能创建65536行*/@Test
public void testWrite03BigData() throws Exception {
    long begin = System.currentTimeMillis();HSSFWorkbook 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 fos = new FileOutputStream(Path + "testWrite03BigData.xls");workbook.write(fos);fos.close();long end = System.currentTimeMillis();System.out.println((double) (end - begin) / 1000);
}

测试大数据写入（07版）

String Path = "D:\\data\\";/*** 65536行数据耗时6.843s* 10万行数据耗时13.028s* 内存占用大*/@Test
public void testWrite07BigData() throws Exception {
    long begin = System.currentTimeMillis();XSSFWorkbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet();for (int rowNum = 0; rowNum < 100000; 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 fos = new FileOutputStream(Path + "testWrite07BigData.xlsx");workbook.write(fos);fos.close();long end = System.currentTimeMillis();System.out.println((double) (end - begin) / 1000);}

使用 SXSSFWorkbook 测试大数据写入

String Path = "D:\\data\\";/*** 10万行数据耗时1.916s*/@Test
public void testWrite07BigDataS() throws Exception {
    long begin = System.currentTimeMillis();SXSSFWorkbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet();for (int rowNum = 0; rowNum < 100000; 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 fos = new FileOutputStream(Path + "testWrite07BigDataS.xlsx");workbook.write(fos);fos.close();// 清除临时文件workbook.dispose();long end = System.currentTimeMillis();System.out.println((double) (end - begin) / 1000);
}

测试读（03版）

String Path = "D:\\data\\";@Test
public void testRead03() throws Exception {
    FileInputStream fis = new FileInputStream(Path + "test03.xls");Workbook workbook = new HSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(1);Cell cell0 = row.getCell(0);double value0 = cell0.getNumericCellValue();System.out.println(value0);Cell cell1 = row.getCell(1);String value1 = cell1.getStringCellValue();System.out.println(value1);fis.close();
}

1.0
BLU

测试读（07版）

String Path = "D:\\data\\";@Test
public void testRead07() throws Exception {
    FileInputStream fis = new FileInputStream(Path + "test07.xlsx");Workbook workbook = new XSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(1);Cell cell0 = row.getCell(0);double value0 = cell0.getNumericCellValue();System.out.println(value0);Cell cell1 = row.getCell(1);String value1 = cell1.getStringCellValue();System.out.println(value1);fis.close();
}

1.0
BLU

根据数据类型读取的示例：

BLU.xls文件：
在这里插入图片描述

String Path = "D:\\data\\";@Test
public void testCellType() throws Exception {
    FileInputStream fis = new FileInputStream(Path + "BLU.xls");Workbook workbook = new HSSFWorkbook(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) {
    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 = rowData.getPhysicalNumberOfCells();for (int cellNum = 0; cellNum < cellCount; cellNum++) {
    Cell cell = rowData.getCell(cellNum);if (cell != null) {
    int cellType = cell.getCellType();switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:System.out.print(cell.getStringCellValue());break;case HSSFCell.CELL_TYPE_BOOLEAN:System.out.print(String.valueOf(cell.getBooleanCellValue()));break;case HSSFCell.CELL_TYPE_BLANK:break;case HSSFCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();String s = new DateTime(date).toString("yyyy-MM-dd");System.out.print(s);}else {
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);System.out.print(cell.toString());}break;case HSSFCell.CELL_TYPE_ERROR:break;}System.out.print(" | ");}}System.out.println();}}fis.close();
}

手机号 | 日期 | 订单号 | 商品编号 | 商品名称 | 价格 | 销售数量 | 销售金额 | 已发货 | 
15651776666 | 2020-09-30 | 000001 | 1 | 蒙牛 | 65.5 | 1 | 65.5 | true | 
15651776666 | 2020-10-01 | 000002 | 2 | 脑白金 | 100 | 10 | 1000 | false |

读取计算函数的示例：

func.xls文件：
在这里插入图片描述

String Path = "D:\\data\\";@Test
public void testFormula() throws Exception {
    FileInputStream fis = new FileInputStream(Path + "func.xls");Workbook workbook = new HSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(4);Cell cell = row.getCell(0);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

Alibaba EasyExcel

<dependencies><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.0-beta2</version></dependency><!-- 日期格式化工具 --><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.12</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.62</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency>	</dependencies>

实体类：

package com.blu.easy;import java.util.Date;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class DemoData {
    @ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")private Date date;@ExcelProperty("数字标题")private Double doubleData;/*** 忽略这个字段*/@ExcelIgnoreprivate String ignore;
}

监听器：

package com.blu.easy;import java.util.ArrayList;
import java.util.List;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;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() {
    demoDAO = new DemoDAO();}public DemoDataListener(DemoDAO demoDAO) {
    this.demoDAO = demoDAO;}@Overridepublic void invoke(DemoData data, AnalysisContext context) {
    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();LOGGER.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {
    LOGGER.info("{}条数据，开始存储数据库！", list.size());demoDAO.save(list);LOGGER.info("存储数据库成功！");}
}

DAO：

package com.blu.easy;import java.util.List;/*** 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。**/
public class DemoDAO {
    public void save(List<DemoData> list) {
    // 持久化操作// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入}
}

测试：

package com.blu.easy;import java.util.ArrayList;
import java.util.Date;
import java.util.List;import org.junit.Test;import com.alibaba.excel.EasyExcel;public class EasyTest {
    String Path = "D:\\data\\";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;}/*** 最简单的写*/@Testpublic void simpleWrite() {
    String fileName = Path + "easyTest.xlsx";EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());}/*** 最简单的读*/@Testpublic void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去String fileName = Path + "easyTest.xlsx";// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();}}

写入结果：
在这里插入图片描述
读取结果：

{"date":1601434567000,"doubleData":0.56,"string":"字符串0"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串1"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串2"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串3"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串4"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串5"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串6"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串7"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串8"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串9"}