前言
暑期学习ing
【8p=1’18’13】
推荐
【【狂神说Java】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
<?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> <!--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>4.1.2</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> </project>
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> <!--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>4.1.2</version>--> <!-- </dependency>--> <!-- <!–xLsx(07)–>--> <!-- <dependency>--> <!-- <groupId>org.apache.poi</groupId>--> <!-- <artifactId>poi-ooxml</artifactId>--> <!-- <version>4.1.2</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> </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）读取操作
具体操作：
<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 段落
