Apache POI 及 alibaba EasyExcel

文章目录

java操作Excel数据使用场景excel 03 和 07的区别POIeasyExcel解析excel表中的对象POI使用步骤POI 写数据POI 读数据计算公式easyExcel读写数据·写数据·读数据

java操作Excel数据

在平时可以使用IO流对Excle进行操作
但是现在使用更加方便的第三方组件来实现

使用场景

1、将用户信息导出为Excel表格，导入数据
2、将Excel表中的数据录入到网站数据库（习题上传）减轻网站的录入量
3、开发中经常会设计到Excel的处理，导入Excel到数据库中

目前最流行的是 Apache POI以及阿里巴巴easyExcel

excel 03 和 07的区别

HSSF 对应excel中的03版本该版本要求excel中最多只能写65536行
后缀名为03.xls
XSSF 对应excel中的07版本该版本对于行数没有要求
后缀名为07.xlsx

POI

Apache提供的，会比较麻烦，比较原生

开放源码函式库，POI提供API给java程序对Office格式档案读和写的功能
但是存在内存问题 => POI将数据会先写入内存中，一旦写入的内容过多时会产生OOM，也叫做内存溢出

easyExcel

/alibaba/easyexcel

对POI进行了一些优化，可以使开发者更加简单，读和写代码只需要1行
存在时间的问题 => easyExcel在写数据时是一行一行往磁盘中写，所以解决了POI的内存问题，但是带来了时间问题

解析excel表中的对象

由于java中万物皆对象，所以需要先观察一张excel表中有哪些对象~

1、工作簿

2、工作表

3、行

4、列 => 单元格

POI使用步骤

第一步：创建Maven项目

第二步：导入依赖

<dependencies><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><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><scope>test</scope></dependency></dependencies>

POI 写数据

第一步：基本文件的写入

private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";

03版本测试

@Testpublic void test03() throws Exception {// 1 创建工作簿Workbook workbook = new HSSFWorkbook();// 2 创建工作表Sheet sheet1 = workbook.createSheet("表1");// 3 创建行下标从0开始第一行Row row1 = sheet1.createRow(0);// 4 创建单元格 (1,1)Cell cell1 = row1.createCell(0);// 5 往第一个单元格填入数据cell1.setCellValue("今日新加入");// 6 创建第二个单元格(1,2)Cell cell2 = row1.createCell(1);cell2.setCellValue("统计时间");// 创建第二行Row row2 = sheet1.createRow(1);// (2,1)Cell cell3 = row2.createCell(0);cell3.setCellValue("11000");// (2,2)Cell cell4 = row2.createCell(1);cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));// 生成表的IO流 03FileOutputStream fos = new FileOutputStream(PATH + "03版本excel.xls");// 将工作簿写入workbook.write(fos);// 释放流fos.close();System.out.println("创建成功");}

07版本测试

@Testpublic void test07() throws Exception {// 1 创建工作簿Workbook workbook = new SXSSFWorkbook(); //todo// 2 创建工作表Sheet sheet1 = workbook.createSheet("表1");// 3 创建行下标从0开始第一行Row row1 = sheet1.createRow(0);// 4 创建单元格 (1,1)Cell cell1 = row1.createCell(0);// 5 往第一个单元格填入数据cell1.setCellValue("今日新加入");// 6 创建第二个单元格(1,2)Cell cell2 = row1.createCell(1);cell2.setCellValue("统计时间");// 创建第二行Row row2 = sheet1.createRow(1);// (2,1)Cell cell3 = row2.createCell(0);cell3.setCellValue("11000");// (2,2)Cell cell4 = row2.createCell(1);cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));// 生成表的IO流 03FileOutputStream fos = new FileOutputStream(PATH + "07版本excel.xlsx");// 将工作簿写入workbook.write(fos);// 释放流fos.close();System.out.println("创建成功");}

第二步：大数据写入

HSSF 写入

缺点：最多只能处理65536行数据，否则会抛出异常java.lang.IllegalArgumentException

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

// 03 版本多数据写入@Testpublic void test03BigData() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new HSSFWorkbook();Sheet s1 = workbook.createSheet("表1");for (int row = 0; row < 65536; row++) {Row rows = s1.createRow(row);for (int cell = 0; cell < 10; cell++) {Cell cells = rows.createCell(cell);cells.setCellValue(cell);}}FileOutputStream fos = new FileOutputStream(PATH + "03BigData.xls");workbook.write(fos);fos.close();long end = System.currentTimeMillis();System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒");}

XSSF 写入

缺点：写数据使速度非常慢，非常耗费内存，也会发生内存溢出，如写100万条数据

优点：可以写比HSSF大的数据量，如20万条数据

// 07 版本低性能 XSSF@Testpublic void test07BigData() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new XSSFWorkbook();Sheet s1 = workbook.createSheet("表1");for (int row = 0; row < 65536; row++) {Row rows = s1.createRow(row);for (int cell = 0; cell < 10; cell++) {Cell cells = rows.createCell(cell);cells.setCellValue(cell);}}FileOutputStream fos = new FileOutputStream(PATH + "07BigData-XSSF.xlsx");workbook.write(fos);fos.close();long end = System.currentTimeMillis();System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒");}

SXSSF 写入

优点：可以写非常大的数据量，如100万条，写的速度也非常快，占用更少的内存

SXSSFWorkbook-来至官方的解释∶实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注.…….然只存储在内存中，因此如果广泛使用，可能需要大量内存。可以使用jprofile来监控

注意
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存到内存中，如果超过这个数量，则最前面的数据就被写入临时文件，如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）

// 07 版本高性能 SXSS 性能优化@Testpublic void test07BigData2() throws Exception {long start = System.currentTimeMillis();Workbook workbook = new SXSSFWorkbook();Sheet s1 = workbook.createSheet("表1");for (int row = 0; row < 65536; row++) {Row rows = s1.createRow(row);for (int cell = 0; cell < 10; cell++) {Cell cells = rows.createCell(cell);cells.setCellValue(cell);}}FileOutputStream fos = new FileOutputStream(PATH + "07BigData-SXSS.xlsx");workbook.write(fos);fos.close();// todo 清除临时文件需要强转类型((SXSSFWorkbook) workbook).dispose();long end = System.currentTimeMillis();System.out.println("总耗时:" + (double) (end - start) / 1000 + "秒");}

POI 读数据

在读取excel表格中单元格中的数据的时候，需要注意一点的是:单元格中的数据可以有String类型、Number类型、Date类型等，所以需要通过Switch-case来进行判断获取，否则会报错

private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";

不用清除临时文件

HSSF 读数据

@Testpublic void Read03() throws Exception {// 0、得到文件输入流FileInputStream fis = new FileInputStream(PATH + "03版本excel.xls");// 1、工作簿Workbook workbook = new HSSFWorkbook(fis);// 2.得到表可以根据索引也可以根据表的名称Sheet sheet = workbook.getSheetAt(0);// 3、得到行Row row = sheet.getRow(1);// 4、得到列锁定单元格Cell cell = row.getCell(0);// 5、根据类型得到单元格中的内容String value = cell.getStringCellValue();System.out.println(value);}

XSSF 读数据

@Testpublic void Read07() throws Exception {// 0、得到文件输入流FileInputStream fis = new FileInputStream(PATH + "07版本excel.xls");// 1、工作簿Workbook workbook = new XSSFWorkbook(fis);// 2.得到表可以根据索引也可以根据表的名称Sheet sheet = workbook.getSheetAt(0);// 3、得到行Row row = sheet.getRow(1);// 4、得到列锁定单元格Cell cell = row.getCell(0);// 5、根据类型得到单元格中的内容String value = cell.getStringCellValue();System.out.println(value);}

循环读取多个不同类型的数据

// 读取多个@Testpublic void Read03teset02() throws Exception {String path = PATH + "人员.xlsx";getData(path);}public static void getData(String path){// 0、得到文件输入流FileInputStream fis = null;try {fis = new FileInputStream(path);Workbook workbook = new XSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);// 1.拿到第一行所有为String类型的数据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) {String cellTitle = cell.getStringCellValue();System.out.print(cellTitle + "|");}}System.out.println("");}// 2.拿到剩下行数中的数据int rowCount = sheet.getPhysicalNumberOfRows(); // 总行数for (int rowNum = 1; rowNum < rowCount; rowNum++) {Row rowData = sheet.getRow(rowNum);int cellCount = rowData.getPhysicalNumberOfCells(); //总单元格数String cellValue = "";for (int cellNum = 0; cellNum < cellCount; cellNum++) {Cell cell = rowData.getCell(cellNum);if (cell != null) {// 判断类型int cellType = cell.getCellType();switch (cellType) {case Cell.CELL_TYPE_STRING: // todo 字符串cellValue = cell.getStringCellValue();break;case Cell.CELL_TYPE_NUMERIC: // todo 数值 || 日期if (HSSFDateUtil.isCellDateFormatted(cell)){// 日期Date date= cell.getDateCellValue();cellValue = new DateTime().toString("yyyy-MM-dd hh:mm:ss");}else{// 数字防止数字过长cell.setCellType(XSSFCell.CELL_TYPE_STRING);cellValue = cell.toString();}break;case Cell.CELL_TYPE_BOOLEAN: // todo 布尔boolean boolean_value = cell.getBooleanCellValue();cellValue = String.valueOf(boolean_value);break;case Cell.CELL_TYPE_BLANK: // todo 为空System.out.print("[BLANK]");break;case Cell.CELL_TYPE_ERROR: // todo 数据类型错误System.out.print("[数据类型错误]");break;}System.out.print(cellValue+"|");}}System.out.println("");}} catch (IOException e) {e.printStackTrace();}finally {// 3 释放资源try {fis.close();} catch (IOException e) {e.printStackTrace();}}}

计算公式

在excel表格中存在着很多个公式如：sum、排序、求平均值等。这个时候就需要进行判断。了解即可，需要时可以再看

@Testpublic void test1() throws Exception {FileInputStream fis = new FileInputStream("E:\\JavaCode\\Maven\\excel-demo\\公式.xls");Workbook workbook = new HSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);Row row = sheet.getRow(2);Cell cell = row.getCell(0);// 得到表中的计算公式FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);// 得到单元格的内容int cellType = cell.getCellType();switch (cellType){case Cell.CELL_TYPE_FORMULA: // 公式// 拿到公式String cellFormula = cell.getCellFormula();System.out.println(cellFormula);//计算CellValue evaluate = FormulaEvaluator.evaluate(cell);System.out.println("evaluate---"+evaluate); // org.apache.poi.ss.usermodel.CellValue [700.0]String value = evaluate.formatAsString(); // 将数据格式化为字符串System.out.println(value);break;}}

easyExcel读写数据

根据实体类自动生成表

第一步：导入依赖

该依赖中自带了很多种依赖，如lombok、spring-boot等，需要我们在引入依赖时将自己已经导入的依赖删除，不然会报依赖冲突的错误

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.4</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.8</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.75</version></dependency>

·写数据

第二步：创建excel表对应的实体类

@Datapublic class DemoData {@ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")private Date date;@ExcelProperty("数字标题")private Double aDouble;// 忽略该字段@ExcelIgnoreprivate String ignore;}

第三步：编写设置数据的方法

使用集合list来写入excel

public List easyTest1(){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.setADouble(0.12);list.add(data);}return list;}

第四步：编写测试类

使用链式编写的方式

write(文件路径，excel表对应的java类)

sheet(设置表名)

doWrite(数据)

@Testpublic void test1(){String fileName ="E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(easyTest1());}

·读数据

1、每执行一条excel表中的数据都会执行一次监听文件中的invoke方法，所以如果需要修改可以修改invoke方法中的内容
2、DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去

第二步：准备一个对应excel表中字段的类

与写操作中使用同一个类

第三步：创建数据层 Mapper || Dao

public class DemoDAO {public void save(List<DemoData> list) {// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入}}

第四步：创建监听器

package excel.readEasy;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.metadata.CellExtra;import com.alibaba.excel.metadata.data.ReadCellData;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.excel.util.ListUtils;import com.alibaba.fastjson.JSON;import excel.easy.DemoData;import lombok.extern.slf4j.Slf4j;import java.util.List;import java.util.Map;// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去@Slf4jpublic class DemoDataListener implements ReadListener<DemoData> {//每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收private static final int BATCH_COUNT = 100;//缓存的数据private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);//假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用private DemoDAO demoDAO;// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数public DemoDataListener() {demoDAO = new DemoDAO();}//如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来public DemoDataListener(DemoDAO demoDAO) {this.demoDAO = demoDAO;}@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {System.out.println("111");}//这个每一条数据解析都会来调用@Overridepublic void invoke(DemoData data, AnalysisContext analysisContext) {System.out.println("2222");System.out.println(JSON.toJSONString(data));// log.info("解析到一条数据:{}", JSON.toJSONString(data));cachedDataList.add(data);// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOMif (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}@Overridepublic void extra(CellExtra extra, AnalysisContext context) {}//所有数据解析完成了都会来调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {saveData();log.info("所有数据解析完成！");}@Overridepublic boolean hasNext(AnalysisContext context) {return false;}/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", cachedDataList.size());demoDAO.save(cachedDataList);log.info("存储数据库成功！");}}

第五步：测试

@Testpublic void test3() {String fileName = "E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {for (DemoData demoData : dataList) {System.out.println(JSON.toJSONString(demoData));}})).sheet().doRead();}