POI及EasyExcel【Java提高】_日星月云

大大的周 02-07 6735

POI及EasyExcel【Java提高】前言推荐POI及EasyExcel讲解0、简单介绍Apache POIeasyExcel 1、POI-Excel写创建项目测试1.1 大文件写HSSF1.2 大文件写XSSF1.3 大文件写SXSSF 2、POI-Excel读2.1 Excel 03类型2.2 Excel 07类型2.3 读取不同类型2.4 计算公式 3、EasyExcel 操作1、导入依赖2、读写操作（1）写入操作（2）读取操作 4、小结-学习方式最后

前言

暑期学习ing 【8p=1’18’13】

推荐

【【狂神说Java】POI及EasyExcel一小时搞定通俗易懂-哔哩哔哩】

遇见狂神说Apach POI,EasyExcel笔记

POI及EasyExcel讲解

常用场景

1、将用户信息导出为excel表格

2、将Excel表中的信息导入到数据库

在开发中我们经常会涉及到excel的处理，如导出Excel，导入Excel到数据库中！

操作Excel目前比较流行的就是 Apache POI和阿里巴巴的easyExcel！

0、简单介绍 Apache POI

Apache POI 官网 https://poi.apache.org/

easyExcel

easyExcel 官网地址 https://easyexcel.opensource.alibaba.com/

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节约内存著称。

它能大量减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从一个磁盘上一行行读取数据，逐个解析。

内存问题：POI =100w 先加载到内存，再写入文件 EasyExcel 1

下图是EasyExcel和POI造解析Excel的对比图

官方文档：EasyExcel · 语雀

1、POI-Excel写创建项目

1、建立一个空项目kuang-excel 2、再创建普通Maven的Moudle kuang-poi 3、添加依赖

xLs(03)和xLs(07)的版本号需要相同，否则会报错 java.lang.NoSuchFieldError: DEFAULT

pom.xml

4、Excel表格中的几个概念：

03版最多支持65536行数据，07没有限制

对象说明

测试

直接创建测试类，编写测试即可：

package com.kuang; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.joda.time.DateTime; import org.junit.Test; import java.io.FileOutputStream; public class ExcelWriteTest { //构建路径 String path="E:\\IdeaProjects\\kuang-excel\\"; @Test public void testWrite03() throws Exception { //创建新的工作簿 Workbook workbook = new HSSFWorkbook(); //在Excel工作簿中建一张表 //Sheet sheet = workbook.createSheet();//缺省值Sheet1、... // 如要新建一名为"会员登录统计"的工作表，其语句为： Sheet sheet =workbook.createSheet("狂神观众统计表"); //创建行（row1） Row row1 = sheet.createRow(0); //创建单元格(col1-1) Cell cell11 = row1.createCell(0); cell11.setCellValue("今日新增观众"); //创建单元格(col1-2) Cell cell12 = row1.createCell(1); cell12.setCellValue(999); //创建行（row2） Row row2 = sheet.createRow(1); //创建单元格(col2-1) Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); //创建单元格(col2-2) Cell cell22 = row2.createCell(1); String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(time); //新建一个输出文件流（注意先创建文件夹） FileOutputStream out = new FileOutputStream(path + "狂神观众统计表03.xls"); //把相应的Excel工作簿存盘 workbook.write(out); //操作结束，关闭文件 out.close(); System.out.println("文件生成成功"); } @Test public void testWrite07() throws Exception{ //创建新的工作簿,只有对象发生了变化 XSSFWorkbook workbook = new XSSFWorkbook();//报错，请检查03/07的依赖的版本号是否相同 // 如要新建一名为"会员登录统计"的工作表，其语句为： Sheet sheet =workbook.createSheet("狂神观众统计表"); //创建行（row1） Row row1 = sheet.createRow(0); //创建单元格(col1-1) Cell cell11 = row1.createCell(0); cell11.setCellValue("今日新增观众"); //创建单元格(col1-2) Cell cell12 = row1.createCell(1); cell12.setCellValue(999); //创建行（row2） Row row2 = sheet.createRow(1); //创建单元格(col2-1) Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); //创建单元格(col2-2) Cell cell22 = row2.createCell(1); String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(dateTime); //新建一个输出文件流（注意先创建文件夹） FileOutputStream out = new FileOutputStream(path + "狂神观众统计表07.xlsx"); //把相应的Excel工作簿存盘 workbook.write(out); //操作结束，关闭文件 out.close(); System.out.println("文件生成成功"); } }

数据批量导入

1.1 大文件写HSSF

缺点：最多只能处理65536行，否则会抛出异常；

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

@Test public void testWrite03BigData() throws IOException { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个HSSFWorkbook Workbook workbook = new HSSFWorkbook(); //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 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 out = new FileOutputStream(path+"bigdata03.xls"); workbook.write(out); // 操作结束，关闭文件 out.close(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000);//2.467 } 1.2 大文件写XSSF

缺点：写数据时速度非常慢，非常耗内存，数据量过大时会发生内存溢出；

优点：可以写较大的数据量，没有限制；

@Test public void testWrite07BigData() throws IOException { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个HSSFWorkbook Workbook workbook = new XSSFWorkbook();//报错，请检查03/07的依赖的版本号是否相同 //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 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 out = new FileOutputStream(path+"bigdata07.xlsx"); workbook.write(out); // 操作结束，关闭文件 out.close(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end-begin)/1000);//7.497 } 1.3 大文件写SXSSF

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

缺点：过程中会产生临时文件，需要清理临时文件；

//清除临时文件 ((SXSSFWorkbook)workbook).dispose(); @Test public void testWrite07BigDataFast() throws Exception { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个SXSSFWorkbook Workbook workbook = new SXSSFWorkbook(); //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 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 out = new FileOutputStream(path+"bigdata07-fast.xlsx"); workbook.write(out); // 操作结束，关闭文件 out.close(); //清除临时文件 ((SXSSFWorkbook)workbook).dispose(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end - begin)/1000);//3.148 }

运行上述操作结果如下：

2、POI-Excel读 2.1 Excel 03类型 package com.kuang; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.junit.Test; import java.io.FileInputStream; import java.io.InputStream; public class ExcelReadTest { String path="E:\\IdeaProjects\\kuang-excel\\"; @Test public void testRead03() throws Exception{ //获取文件流 InputStream fileInputStream = new FileInputStream(path + "狂神观众统计表03.xls"); //1、创建一个工作簿 Workbook workbook = new HSSFWorkbook(fileInputStream); //2、得到一个工作表 Sheet sheet = workbook.getSheetAt(0); //读取第一行第一列 Row row = sheet.getRow(0); Cell cell11 = row.getCell(0); //输出单元格内容,一定要注意类型 System.out.println(cell11.getRichStringCellValue());//今日新增观众 //操作结束，关闭文件 fileInputStream.close(); } } 2.2 Excel 07类型

你用什么写的，就得用什么读，不是只看后缀，要看实际

org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

@Test public void testRead07() throws Exception{ //获取文件流 InputStream fileInputStream = new FileInputStream(path + "狂神观众统计表07.xlsx"); //1、创建一个工作簿 Workbook workbook = new XSSFWorkbook(fileInputStream); //2、得到一个工作表 Sheet sheet = workbook.getSheetAt(0); //读取第一行第一列 Row row = sheet.getRow(0); Cell cell11 = row.getCell(0); //输出单元格内容,一定要注意类型 System.out.println(cell11.getRichStringCellValue());//今日新增观众 //操作结束，关闭文件 fileInputStream.close(); } 2.3 读取不同类型

注意：读取不同的数据类型，是工作上的重点，这段类型匹配代码工作时可直接复制

新版本区别

// int cellType = cell.getCellType(); //3.5之前的版本 CellType cellType = cell.getCellType();//3.5之后的版本

CellType 枚举类源码

/* Location: jar://C:/Users/lenovo/.m2/repository/org/apache/poi/poi/4.1.2/poi-4.1.2.jar!/org/apache/poi/ss/usermodel/CellType.class * Java language version: 8 * Class File: 52.0 * JD-Core Version: 1.1.3 */ package org.apache.poi.ss.usermodel; import org.apache.poi.util.Internal; public enum CellType { _NONE(-1), NUMERIC(0), STRING(1), FORMULA(2), BLANK(3), BOOLEAN(4), ERROR(5); private final int code; CellType(int code) { this.code = code; } public static CellType forInt(int code) { for (CellType type : values()) { if (type.code == code) { return type; } } throw new IllegalArgumentException("Invalid CellType code: " + code); } public int getCode() { return this.code; } }

测试

//读取不同类型 @Test public void testTypeCell() throws Exception{ //获取文件流 InputStream is = new FileInputStream(path+"明细表.xls"); //创建一个工作簿，使用 excel能操作的这边都可以操作 Workbook workbook = new HSSFWorkbook(is); //得到表 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(); 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=rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowData.getCell(cellNum); if (cell!=null){ //判断单元格类型 // int cellType = cell.getCellType(); CellType cellType = cell.getCellType(); String cellValue=""; switch (cellType){ // case HSSFCell.CELL_TYPE_STRING://字符串 case STRING://字符串 cellValue=String.valueOf(cell.getStringCellValue()); break; // case HSSFCell.CELL_TYPE_BOOLEAN://布尔 case BOOLEAN://布尔 cellValue = String.valueOf(cell.getBooleanCellValue()); break; // case HSSFCell.CELL_TYPE_BLANK://空 case BLANK://空 break; // case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字) case NUMERIC://数字(日期、普通数字) if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期 Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); } else {// 不是日期格式，则防止当数字过长时以科学计数法显示 // cell.setCellType(HSSFCell.CELL_TYPE_STRING);//转换为字符串输出 cell.setCellType(CellType.STRING);//转换为字符串输出 cellValue = cell.toString(); } break; // case Cell.CELL_TYPE_ERROR: case ERROR: System.out.print("【数据类型错误】"); break; } System.out.println(cellValue); } } } } is.close(); }

注意类型转换类型

2.4 计算公式

@Test public void testFormula() throws Exception{ InputStream is = new FileInputStream(path + "公式.xls"); Workbook workbook = new HSSFWorkbook(is); 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(); CellType cellType = cell.getCellType(); switch (cellType) { // case Cell.CELL_TYPE_FORMULA://公式 case FORMULA://公式 //得到公式 String formula = cell.getCellFormula(); System.out.println(formula); //计算 CellValue evaluate = formulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } is.close(); }

3、EasyExcel 操作 1、导入依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.kuang</groupId> <artifactId>kuang-poi</artifactId> <version>1.0-SNAPSHOT</version>  <dependencies>  <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version >1.2.62</version> </dependency>  <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version >2.2.0-beta2</version> </dependency>  <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version >1.18.12</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> </project> 2、读写操作（1）写入操作

创建实体类DemoData：

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

具体操作：

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 EasyExcelTest { String path="E:\\IdeaProjects\\kuang-excel\\"; 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; } // 最简单的写 @Test public void simpleWrite() { // 写法1 String fileName = path+"EasyExcel.xlsx"; // 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板然后文件流会自动关闭 // 如果这里想使用03 则传入excelType参数即可 //write(fileName,格式类) //sheet(表明) //doWrite(数据) EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data()); } }

（2）读取操作

【狂神说Java】POI及EasyExcel

具体操作：

<1>持久层DemoDAO:

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

<2>监听器

package com.kuang.easy; 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); /** * 每隔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("存储数据库成功！"); } }

读取代码

// 最简单的读 @Test public void simpleRead() { String fileName = path+"EasyExcel.xlsx"; // 这里需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); }

结果：

固定套路：

1、写入，固定类格式进行写入 2、读取，根据监听器设置的规则进行读取！

4、小结-学习方式

了解，面向对象的思想，学会面向接口编程！理解使用测试API

作业：把EasyExcel的所有API都测试一下（2~3小时！）

最后

Markdown 17134 字数 848 行数 HTML 15568 字数 650 段落