手机支付网站开发,网站开发怎么对接客户,做网站一定要用云解析吗,智能微营销系统POI、EasyExcel 文章目录 POI、EasyExcel1.简介2.结构3.POI-Excel写1.基本操作2.大文件写入HSSF3.大文件写入XSSF4.大文件写入SXSSF 4.POI-Excel读1.基本操作2.数据类型3.计算公式类型数据读取 5.EasyExcel写1.基本写入操作2.日期、数字或者自定义格式转换3.列宽、行高 6.EasyE…POI、EasyExcel 文章目录 POI、EasyExcel1.简介2.结构3.POI-Excel写1.基本操作2.大文件写入HSSF3.大文件写入XSSF4.大文件写入SXSSF 4.POI-Excel读1.基本操作2.数据类型3.计算公式类型数据读取 5.EasyExcel写1.基本写入操作2.日期、数字或者自定义格式转换3.列宽、行高 6.EasyExcel读1.基本操作2.指定列的下标或者列名3.读多个sheet4.日期、数字或者自定义格式转换5.读取公式和单元格类型6.数据转换等异常处理7.web中的读 7.EasyExcel中常用操作1.数据批量导入监听2.导出注解方式复杂表头设置3.导出动态复杂表头设置4.导出文件增加序列号自定义行处理器 8.Poi工具类 1.简介
Apache POI [1] 是用Java编写的免费开源的跨平台的 Java APIApache POI提供API给Java程序对[Microsoft Office](https://baike.baidu.com/item/Microsoft Office/481476?fromModulelemma_inlink)格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写意为“简洁版的模糊实现”。
2.结构
HSSF 提供读写[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel?fromModulelemma_inlink) XLS格式档案的功能。XSSF 提供读写Microsoft Excel OOXML XLSX格式档案的功能。HWPF 提供读写Microsoft Word DOC格式档案的功能。HSLF 提供读写Microsoft PowerPoint格式档案的功能。HDGF 提供读[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio?fromModulelemma_inlink)格式档案的功能。HPBF 提供读[Microsoft Publisher](https://baike.baidu.com/item/Microsoft Publisher?fromModulelemma_inlink)格式档案的功能。HSMF 提供读[Microsoft Outlook](https://baike.baidu.com/item/Microsoft Outlook?fromModulelemma_inlink)格式档案的功能。
3.POI-Excel写
注意03版本的xls结尾使用HSSFWorkbook对象07版本的xlsx结尾使用XSSFWorkbook对象
1.基本操作
1.导入pom !--xls(03)--dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion3.9/version/dependency!--xlsx(07)--dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion3.9/version/dependency
2.excel写入测试
/*** excel写入测试*/
public class ExcelWriteTest {static final String filePathF:\\java\\IDEAproject2\\springboot-poi\\;/*** 03版本excel写入测试*/Testpublic void ExcelWrite03() throws Exception {//1.创建一个工作簿Workbook workbook new HSSFWorkbook();//2.创建一个工作表sheetSheet sheet workbook.createSheet(excel03写入测试表);//3.创建一个行Row row sheet.createRow(0);//4.创建一个单元格(第一行第一个单元格)Cell cell row.createCell(0);cell.setCellValue(excel03写入测试数据);//5.创建写出文件流对象FileOutputStream os new FileOutputStream(filePath excel03写入测试.xls);//6.写出工作簿workbook.write(os);os.close();System.out.println(excel03写出完成);}/*** 07版本excel写入测试*/Testpublic void ExcelWrite07() throws Exception {//1.创建一个工作簿Workbook workbook new XSSFWorkbook();//2.创建一个工作表sheetSheet sheet workbook.createSheet(excel07写入测试表);//3.创建一个行Row row sheet.createRow(0);//4.创建一个单元格(第一行第一个单元格)Cell cell row.createCell(0);cell.setCellValue(excel03写入测试数据);//5.创建写出文件流对象FileOutputStream os new FileOutputStream(filePath excel07写入测试.xlsx);//6.写出工作簿workbook.write(os);os.close();System.out.println(excel07写出完成);}}2.大文件写入HSSF
缺点最多只能处理65536行超出会报异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)优点直接操作缓存不操作磁盘最后一次性写入速度快 Testpublic void ExcelWriteBigData03() throws Exception{long begin System.currentTimeMillis();//1.创建一个工作簿Workbook workbook new HSSFWorkbook();//2.创建一个表Sheet sheet workbook.createSheet(sheet1);//写入数据for (int rowNum 0;rowNum65536;rowNum){//3.创建行Row row sheet.createRow(rowNum);for (int CellNum 0;CellNum10;CellNum){Cell cell row.createCell(CellNum);cell.setCellValue(CellNum);}}//获取io流FileOutputStream fos new FileOutputStream(filePathexcel03大数据写入测试.xls);//生成一张表workbook.write(fos);fos.close();long end System.currentTimeMillis();System.out.println(耗时(end-begin));}
3.大文件写入XSSF
缺点写速度很慢耗内存也会发生内存溢出如100w条数据
优点可以写较大数据如20w条 /*** 大数据07写入测试* throws Exception*/Testpublic void ExcelWriteBigData07() throws Exception{long begin System.currentTimeMillis();//1.创建一个工作簿Workbook workbook new XSSFWorkbook();//2.创建一个表Sheet sheet workbook.createSheet(sheet1);//写入数据for (int rowNum 0;rowNum65536;rowNum){//3.创建行Row row sheet.createRow(rowNum);for (int CellNum 0;CellNum10;CellNum){Cell cell row.createCell(CellNum);cell.setCellValue(CellNum);}}//获取io流FileOutputStream fos new FileOutputStream(filePathexcel07大数据写入测试.xls);//生成一张表workbook.write(fos);fos.close();long end System.currentTimeMillis();System.out.println(耗时(end-begin));}4.大文件写入SXSSF
优点可以写非常大数据量相比XSSF速度更快占用更少内存
注意
过程中会产生临时文件需要清理临时文件。默认由100条记录被保存在内存中如果超过这数量则最前面的数据被写入临时文件如果想自定义内存中数据的数量可以使用new SXSSFWorkbook数量 /*** 大数据07的SXSSF写入测试* throws Exception*/Testpublic void ExcelWriteBigDataSXSSF07() throws Exception{long begin System.currentTimeMillis();//1.创建一个工作簿SXSSFWorkbook workbook new SXSSFWorkbook();//2.创建一个表Sheet sheet workbook.createSheet(sheet1);//写入数据for (int rowNum 0;rowNum100000;rowNum){//3.创建行Row row sheet.createRow(rowNum);for (int CellNum 0;CellNum10;CellNum){Cell cell row.createCell(CellNum);cell.setCellValue(CellNum);}}//获取io流FileOutputStream fos new FileOutputStream(filePathexcel07的SXSSF大数据写入测试.xls);//生成一张表workbook.write(fos);//清除临时文件workbook.dispose();fos.close();long end System.currentTimeMillis();System.out.println(耗时(end-begin));}SXSSFWorkbook-来自官方的解释︰实现BigGridDemo策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存因为任何时候只有可配置的行部分被保存在内存中。
请注意仍然可能会消耗大量内存这些内存基于您正在使用的功能例如合并区域注.…….当然只存储在内存中因此如果广泛使用可能需要大量内存。 再使用POI的时候! 内存问题 可以用Jprofile监控 !
4.POI-Excel读
1.基本操作
/*** excel读取测试*/
public class ExcelReaderTest {/*** 03版本读取* throws Exception*/Testpublic void ExcelReaderTest03() throws Exception {String filePath F:\\java\\IDEAproject2\\springboot-poi\\excel03写入测试.xls;//获取文件流对象FileInputStream is new FileInputStream(filePath);//读取流Workbook workbook new HSSFWorkbook(is);//获取表Sheet sheet workbook.getSheetAt(0);//获取行Row row sheet.getRow(0);//获取单元格Cell cell row.getCell(0);//获取String类型的值String value cell.getStringCellValue();System.out.println(value);is.close();}/*** 07版本读取* throws Exception*/Testpublic void ExcelReaderTest07() throws Exception {String filePath F:\\java\\IDEAproject2\\springboot-poi\\excel07写入测试.xlsx;//获取文件流对象FileInputStream is new FileInputStream(filePath);//读取流Workbook workbook new XSSFWorkbook(is);//获取表Sheet sheet workbook.getSheetAt(0);//获取行Row row sheet.getRow(0);//获取单元格Cell cell row.getCell(0);//获取String类型的值String value cell.getStringCellValue();System.out.println(value);is.close();}}
2.数据类型
1. 3.9版本的pom依赖 /*** 不同格式处理* throws Exception*/Testpublic void ExcelReaderCellType03() throws Exception{String filePath F:\\java\\IDEAproject2\\springboot-poi\\excel03写入测试.xls;//获取文件流FileInputStream fis new FileInputStream(filePath);//获取一个工作簿Workbook workbook new HSSFWorkbook(fis);//获取一个工作表Sheet sheet workbook.getSheetAt(0);//获取第一行内容Row row sheet.getRow(0);if (row ! null){//获取所有的列int Cells row.getPhysicalNumberOfCells();for (int col 0;col Cells;col){//获取当前列Cell cell row.getCell(col);if (cell ! null){//获取当前行的第 col 列的值String cellValue cell.getStringCellValue();System.out.print(cellValue | );}}}//获取内容//获取有多少行int rowCount sheet.getPhysicalNumberOfRows();//从1开始第一行是标题for (int rowNum 1;rowNum rowCount;rowNum){Row rowData sheet.getRow(rowNum);if (rowData ! null){//获取当前行的列数int cellCount rowData.getPhysicalNumberOfCells();for (int col 0;col cellCount;col){//获取当前列的值Cell cellData rowData.getCell(col);//匹配列的类型if (cellData ! null){//获取列的类型int cellType cellData.getCellType();String cellValue ;switch (cellType){case Cell.CELL_TYPE_STRING://字符串System.out.print([string]);cellValue cellData.getStringCellValue();break;case Cell.CELL_TYPE_BOOLEAN://布尔System.out.print([boolean]);cellValue String.valueOf(cellData.getBooleanCellValue());break;case Cell.CELL_TYPE_BLANK://空System.out.print([blank]);break;case Cell.CELL_TYPE_NUMERIC://数字日期、普通数字System.out.print([numeric]);if (HSSFDateUtil.isCellDateFormatted(cellData)){//如果是日期System.out.print([日期] );Date date cellData.getDateCellValue();cellValue new DateTime(date).toString(yyyy-MM-dd HH:mm:ss);}else {//不是日期格式防止数字过长System.out.print([转换字符串输出] );//转为字符串cellData.setCellType(HSSFCell.CELL_TYPE_STRING);cellValue cellData.toString();}break;case Cell.CELL_TYPE_ERROR://错误System.out.print([error]);break;}System.out.print([cellValue]);}}}}fis.close();}2. 高版本4.12依赖 /*** 不同格式处理* throws Exception*/Testpublic void ExcelReaderCellType03() throws Exception{String filePath F:\\java\\IDEAproject2\\springboot-poi\\excel03写入测试.xls;//获取文件流FileInputStream fis new FileInputStream(filePath);//获取一个工作簿Workbook workbook new HSSFWorkbook(fis);//获取一个工作表Sheet sheet workbook.getSheetAt(0);//获取第一行内容Row row sheet.getRow(0);if (row ! null){//获取所有的列int Cells row.getPhysicalNumberOfCells();for (int col 0;col Cells;col){//获取当前列Cell cell row.getCell(col);if (cell ! null){//获取当前行的第 col 列的值String cellValue cell.getStringCellValue();System.out.print(cellValue | );}}}//获取内容//获取有多少行int rowCount sheet.getPhysicalNumberOfRows();//从1开始第一行是标题for (int rowNum 1;rowNum rowCount;rowNum){Row rowData sheet.getRow(rowNum);if (rowData ! null){//获取当前行的列数int cellCount rowData.getPhysicalNumberOfCells();for (int col 0;col cellCount;col){//获取当前列的值Cell cellData rowData.getCell(col);//匹配列的类型if (cellData ! null){//获取列的类型CellType cellType cellData.getCellType();String cellValue ;switch (cellType){case STRING://字符串System.out.print([string]);cellValue cellData.getStringCellValue();break;case BOOLEAN://布尔System.out.print([boolean]);cellValue String.valueOf(cellData.getBooleanCellValue());break;case BLANK://空System.out.print([blank]);break;case NUMERIC://数字日期、普通数字System.out.print([numeric]);if (HSSFDateUtil.isCellDateFormatted(cellData)){//如果是日期System.out.print([日期] );Date date cellData.getDateCellValue();cellValue new DateTime(date).toString(yyyy-MM-dd HH:mm:ss);}else {//不是日期格式防止数字过长System.out.print([转换字符串输出] );//转为字符串cellData.setCellType(CellType.STRING);cellValue cellData.toString();}break;case ERROR://错误System.out.print([error]);break;}System.out.print([cellValue]);}}}}fis.close();}3.计算公式类型数据读取 public void poiFormula(FileInputStream inputStream) throws IOException {Workbook workbook new HSSFWorkbook(inputStream);Sheet sheet workbook.getSheetAt(0);Row row sheet.getRow(1);Cell cell row.getCell(0);// 拿到计算公式 evalFormulaEvaluator 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;}}5.EasyExcel写
官方文档https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read
3版本不允许写方法为空
pom依赖
!--easyExcel--
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.1.0/versionexclusionsexclusionartifactIdpoi-ooxml-schemas/artifactIdgroupIdorg.apache.poi/groupId/exclusion/exclusions
/dependencydependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.3.1/version/dependency内部含有poi依赖。该版本集成为4.12
数据准备 private ListDemoData data() {ListDemoData list new ArrayList();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;}1.基本写入操作
Getter
Setter
EqualsAndHashCode
public class DemoData {ExcelProperty(字符串标题)private String string;ExcelProperty(日期标题)private Date date;ExcelProperty(数字标题)private Double doubleData;/*** 忽略这个字段*/ExcelIgnoreprivate String ignore;
}public class EasyWriterTest {static final String filePathF:\\java\\IDEAproject2\\springboot-poi\\;//生成数据集合对象private ListDemoData data() {ListDemoData list new ArrayList();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;}/*** 最简单的写* p* 1. 创建excel对应的实体对象 参照{link DemoData}* p* 2. 直接写即可*/Testpublic void simpleWrite() {// 注意 simpleWrite在数据量不大的情况下可以使用5000以内具体也要看实际情况数据量大参照 重复多次写入// 写法1 JDK8// since: 3.0.0-beta1String fileName filePath easy写入测试1.xlsx;// 这里 需要指定写用哪个class去写然后写到第一个sheet名字为模板 然后文件流会自动关闭// 如果这里想使用03 则 传入excelType参数即可EasyExcel.write(fileName, DemoData.class).sheet(模板).doWrite(() - {// 分页查询数据return data();});// 写法2fileName filePath easy写入测试2.xlsx;// 这里 需要指定写用哪个class去写然后写到第一个sheet名字为模板 然后文件流会自动关闭// 如果这里想使用03 则 传入excelType参数即可EasyExcel.write(fileName, DemoData.class).sheet(模板).doWrite(data());// 写法3fileName filePath easy写入测试3.xlsx;// 这里 需要指定写用哪个class去写try (ExcelWriter excelWriter EasyExcel.write(fileName, DemoData.class).build()) {WriteSheet writeSheet EasyExcel.writerSheet(模板).build();excelWriter.write(data(), writeSheet);}}}2.日期、数字或者自定义格式转换
Getter
Setter
EqualsAndHashCode
public class ConverterData {/*** 我想所有的 字符串起前面加上自定义三个字*/ExcelProperty(value 字符串标题, converter CustomStringStringConverter.class)private String string;/*** 我想写到excel 用年月日的格式*/DateTimeFormat(yyyy年MM月dd日HH时mm分ss秒)ExcelProperty(日期标题)private Date date;/*** 我想写到excel 用百分比表示*/NumberFormat(#.##%)ExcelProperty(value 数字标题)private Double doubleData;
}3.列宽、行高
Getter
Setter
EqualsAndHashCode
ContentRowHeight(10)
HeadRowHeight(20)
ColumnWidth(25)
public class WidthAndHeightData {ExcelProperty(字符串标题)private String string;ExcelProperty(日期标题)private Date date;/*** 宽度为50*/ColumnWidth(50)ExcelProperty(数字标题)private Double doubleData;
}6.EasyExcel读
1.基本操作
Getter
Setter
EqualsAndHashCode
public class DemoData {private String string;private Date date;private Double doubleData;
}/*** 假设这个是你的DAO存储。当然还要这个类让spring管理当然你不用需要存储也不需要这个类。**/
public class DemoDAO {public void save(ListDemoData list) {// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入}
}监听
// 有个很重要的点 DemoDataListener 不能被spring管理要每次读取excel都要new,然后里面用到spring可以构造方法传进去
Slf4j
public class DemoDataListener implements ReadListenerDemoData {/*** 每隔5条存储数据库实际使用中可以100条然后清理list 方便内存回收*/private static final int BATCH_COUNT 100;/*** 缓存的数据*/private ListDemoData cachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);/*** 假设这个是一个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*/Overridepublic void invoke(DemoData data, AnalysisContext context) {log.info(解析到一条数据:{}, JSON.toJSONString(data));cachedDataList.add(data);// 达到BATCH_COUNT了需要去存储一次数据库防止数据几万条数据在内存容易OOMif (cachedDataList.size() BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/*** 所有数据解析完成了 都会来调用** param context*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据确保最后遗留的数据也存储到数据库saveData();log.info(所有数据解析完成);}/*** 加上存储数据库*/private void saveData() {log.info({}条数据开始存储数据库, cachedDataList.size());demoDAO.save(cachedDataList);log.info(存储数据库成功);}
}读取测试
package com.li.easyexcel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;import java.io.File;
import java.util.List;Slf4j
public class EasyReaderTest {static final String filePathF:\\java\\IDEAproject2\\springboot-poi\\;/*** 最简单的读* p* 1. 创建excel对应的实体对象 参照{link DemoData}* p* 2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link DemoDataListener}* p* 3. 直接读即可*/Testpublic void simpleRead() {// 写法1JDK8 ,不用额外写一个DemoDataListener// since: 3.0.0-beta1String fileName filePath easy写入测试1.xlsx;// 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行// 具体需要返回多少行可以在PageReadListener的构造函数设置EasyExcel.read(fileName, DemoData.class, new PageReadListenerDemoData(dataList - {for (DemoData demoData : dataList) {log.info(读取到一条数据{}, JSON.toJSONString(demoData));System.out.println(读取到一条数据JSON.toJSONString(demoData));}})).sheet().doRead();// 写法2// 匿名内部类 不用额外写一个DemoDataListenerfileName filePath easy写入测试1.xlsx;// 这里 需要指定读用哪个class去读然后读取第一个sheet 文件流会自动关闭EasyExcel.read(fileName, DemoData.class, new ReadListenerDemoData() {/*** 单次缓存的数据量*/public static final int BATCH_COUNT 100;/***临时存储*/private ListDemoData cachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);Overridepublic void invoke(DemoData data, AnalysisContext context) {cachedDataList.add(data);if (cachedDataList.size() BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();}/*** 加上存储数据库*/private void saveData() {log.info({}条数据开始存储数据库, cachedDataList.size());System.out.println( cachedDataList.size()条数据开始存储数据库);log.info(存储数据库成功);System.out.println(存储数据库成功);}}).sheet().doRead();// 有个很重要的点 DemoDataListener 不能被spring管理要每次读取excel都要new,然后里面用到spring可以构造方法传进去// 写法3fileName filePath easy写入测试1.xlsx;// 这里 需要指定读用哪个class去读然后读取第一个sheet 文件流会自动关闭EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();// 写法4fileName filePath easy写入测试1.xlsx;// 一个文件一个readertry (ExcelReader excelReader EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build()) {// 构建一个sheet 这里可以指定名字或者noReadSheet readSheet EasyExcel.readSheet(0).build();// 读取一个sheetexcelReader.read(readSheet);}}
}
2.指定列的下标或者列名
Getter
Setter
EqualsAndHashCode
public class IndexOrNameData {/*** 强制读取第三个 这里不建议 index 和 name 同时用要么一个对象只用index要么一个对象只用name去匹配*/ExcelProperty(index 2)private Double doubleData;/*** 用名字去匹配这里需要注意如果名字重复会导致只有一个字段读取到数据*/ExcelProperty(字符串标题)private String string;ExcelProperty(日期标题)private Date date;
}/*** 指定列的下标或者列名** p1. 创建excel对应的实体对象,并使用{link ExcelProperty}注解. 参照{link IndexOrNameData}* p2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link IndexOrNameDataListener}* p3. 直接读即可*/Testpublic void indexOrNameRead() {String fileName TestFileUtil.getPath() demo File.separator demo.xlsx;// 这里默认读取第一个sheetEasyExcel.read(fileName, IndexOrNameData.class, new IndexOrNameDataListener()).sheet().doRead();}3.读多个sheet /*** 读多个或者全部sheet,这里注意一个sheet不能读取多次多次读取需要重新读取文件* p* 1. 创建excel对应的实体对象 参照{link DemoData}* p* 2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link DemoDataListener}* p* 3. 直接读即可*/Testpublic void repeatedRead() {String fileName TestFileUtil.getPath() demo File.separator demo.xlsx;// 读取全部sheet// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();// 读取部分sheetfileName TestFileUtil.getPath() demo File.separator demo.xlsx;// 写法1try (ExcelReader excelReader EasyExcel.read(fileName).build()) {// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的ListenerReadSheet readSheet1 EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();ReadSheet readSheet2 EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();// 这里注意 一定要把sheet1 sheet2 一起传进去不然有个问题就是03版的excel 会读取多次浪费性能excelReader.read(readSheet1, readSheet2);}}4.日期、数字或者自定义格式转换
Getter
Setter
EqualsAndHashCode
public class ConverterData {/*** 我自定义 转换器不管数据库传过来什么 。我给他加上“自定义”*/ExcelProperty(converter CustomStringStringConverter.class)private String string;/*** 这里用string 去接日期才能格式化。我想接收年月日格式*/DateTimeFormat(yyyy年MM月dd日HH时mm分ss秒)private String date;/*** 我想接收百分比的数字*/NumberFormat(#.##%)private String doubleData;
}自定义转换器
public class CustomStringStringConverter implements ConverterString {Overridepublic Class? supportJavaTypeKey() {return String.class;}Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 这里读的时候会调用** param context* return*/Overridepublic String convertToJavaData(ReadConverterContext? context) {return 自定义 context.getReadCellData().getStringValue();}/*** 这里是写的时候会调用 不用管** return*/Overridepublic WriteCellData? convertToExcelData(WriteConverterContextString context) {return new WriteCellData(context.getValue());}}/*** 日期、数字或者自定义格式转换* p* 默认读的转换器{link DefaultConverterLoader#loadDefaultReadConverter()}* p1. 创建excel对应的实体对象 参照{link ConverterData}.里面可以使用注解{link DateTimeFormat}、{link NumberFormat}或者自定义注解* p2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link ConverterDataListener}* p3. 直接读即可*/Testpublic void converterRead() {String fileName TestFileUtil.getPath() demo File.separator demo.xlsx;// 这里 需要指定读用哪个class去读然后读取第一个sheet EasyExcel.read(fileName, ConverterData.class, new ConverterDataListener())// 这里注意 我们也可以registerConverter来指定自定义转换器 但是这个转换变成全局了 所有java为string,excel为string的都会用这个转换器。// 如果就想单个字段使用请使用ExcelProperty 指定converter// .registerConverter(new CustomStringStringConverter())// 读取sheet.sheet().doRead();}5.读取公式和单元格类型
Getter
Setter
EqualsAndHashCode
public class CellDataReadDemoData {private CellDataString string;// 这里注意 虽然是日期 但是 类型 存储的是number 因为excel 存储的就是numberprivate CellDataDate date;private CellDataDouble doubleData;// 这里并不一定能完美的获取 有些公式是依赖性的 可能会读不到 这个问题后续会修复private CellDataString formulaValue;
}/*** 读取公式和单元格类型** p* 1. 创建excel对应的实体对象 参照{link CellDataReadDemoData}* p* 2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link DemoHeadDataListener}* p* 3. 直接读即可** since 2.2.0-beat1*/Testpublic void cellDataRead() {String fileName TestFileUtil.getPath() demo File.separator cellDataDemo.xlsx;// 这里 需要指定读用哪个class去读然后读取第一个sheetEasyExcel.read(fileName, CellDataReadDemoData.class, new CellDataDemoHeadDataListener()).sheet().doRead();}6.数据转换等异常处理
Getter
Setter
EqualsAndHashCode
public class ExceptionDemoData {*/***** 用日期去接字符串 肯定报错***/*private Date date;
}监听器增加onException方法 /*** 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。** param exception* param context* throws Exception*/Overridepublic void onException(Exception exception, AnalysisContext context) {log.error(解析失败但是继续解析下一行:{}, exception.getMessage());// 如果是某一个单元格的转换异常 能获取到具体行号// 如果要获取头的信息 配合invokeHeadMap使用if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException (ExcelDataConvertException)exception;log.error(第{}行第{}列解析异常数据为:{}, excelDataConvertException.getRowIndex(),excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());}}/*** 数据转换等异常处理** p* 1. 创建excel对应的实体对象 参照{link ExceptionDemoData}* p* 2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link DemoExceptionListener}* p* 3. 直接读即可*/Testpublic void exceptionRead() {String fileName TestFileUtil.getPath() demo File.separator demo.xlsx;// 这里 需要指定读用哪个class去读然后读取第一个sheetEasyExcel.read(fileName, ExceptionDemoData.class, new DemoExceptionListener()).sheet().doRead();}7.web中的读 /*** 文件上传* p* 1. 创建excel对应的实体对象 参照{link UploadData}* p* 2. 由于默认一行行的读取excel所以需要创建excel一行一行的回调监听器参照{link UploadDataListener}* p* 3. 直接读即可*/PostMapping(upload)ResponseBodypublic String upload(MultipartFile file) throws IOException {EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();return success;}7.EasyExcel中常用操作
1.数据批量导入监听
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.util.ArrayList;
import java.util.List;/*** excel读取监听导入数据库* author lijia*/
public class ExcelListener extends AnalysisEventListenerDemo {protected final Logger logger LoggerFactory.getLogger(getClass());private static final int BATCH_COUNT 100;private ListDemo cachedDataList new ArrayList(BATCH_COUNT);private DemoService demoService;public ExcelListener(DemoService demoService) {this.demoService demoService;}/*** 读取数据会执行invoke 方法* VehicleSecureTrainRecord 类型* AnalysisContext 分析上下文** param data* param context*/Overridepublic void invoke(Demo data, AnalysisContext context) {cachedDataList.add(data);// 达到BATCH_COUNT了需要去存储一次数据库防止数据几万条数据在内存容易OOMif (cachedDataList.size() BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList.clear();}}/*** 数据解析完成回调** param context*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据确保最后遗留的数据也存储到数据库saveData();logger.info(数据解析完成);}/*** 加上存储数据库*/private void saveData() {logger.info({}条数据开始存储数据库,cachedDataList.size());demoService.insertDataByExcel(cachedDataList);logger.info(存储数据库成功);}}2.导出注解方式复杂表头设置
复杂表头ExcelProperty需写多级从最顶级父标题开始easyexcel会自动映射
如果涉及动态变化的表头则需要写head方法进行自定义配置不使用注解 import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;import java.math.BigDecimal;/*** author li*/
ColumnWidth(value 15)
HeadFontStyle(fontName 宋体, fontHeightInPoints 11,bold BooleanEnum.TRUE)
HeadStyle(fillPatternType FillPatternTypeEnum.SOLID_FOREGROUND,fillForegroundColor 22)
ExcelIgnoreUnannotated
public class Demo {ExcelProperty(code)private String code;ExcelProperty({标题1, 测试})private String title;ExcelProperty({标题1, 测试2})private String title2;ExcelProperty({标题2, 测试})private BigDecimal title3;ExcelProperty({标题2, 测试2})private BigDecimal title4; }3.导出动态复杂表头设置
跟注解方式相似只是将数据换成自己生成拼接数据
EasyExcel.write(fos,Demo.class).head(head(year)).sheet(sheet1).doWrite(items);/*** 动态复杂表头设置* param year* return*/
private ListListString head(String year) {//初始化表头String bigTitle year;ListString title new ArrayList(Arrays.asList(bigTitle, 标题1));ListString title0 new ArrayList(Arrays.asList(bigTitle, 标题2));ListString title1 new ArrayList(Arrays.asList(bigTitle, 复杂头1,子级1));ListString title2 new ArrayList(Arrays.asList(bigTitle, 复杂头1,子级2));ListString title3 new ArrayList(Arrays.asList(bigTitle, 复杂头2,year年));ListString title4 new ArrayList(Arrays.asList(bigTitle, 复杂头2,(Integer.parseInt(year)1)年));ListString title5 new ArrayList(Arrays.asList(bigTitle, 备注));return new ArrayList(Arrays.asList(title,title0,title1,title2,title3,title4,title5));
}4.导出文件增加序列号自定义行处理器
GetMapping(/year/export)
public void xxx( HttpServletResponse response) throws PortalException {//获取文件OutputStream fos null;try {String fileName URLEncoder.encode(xxx.xlsx, utf-8);fos response.getOutputStream();//设置文件类型以及文件头response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(utf-8);response.setHeader(Content-disposition, attachment;filename fileName);//写数据到输出流ListDemo result xxService.selectData();EasyExcel.write(fos, Demo.class).registerWriteHandler(new CustomSequenceHandler()).sheet(sheet1).doWrite(result);} catch (Exception ex) {ex.printStackTrace();}}import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.property.ExcelWriteHeadProperty;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;import java.util.Map;/*** 自定义 excel 行处理器, 增加序号列* author li*/
public class CustomSequenceHandler implements RowWriteHandler {private boolean init true;private static final String COLUM_TITLE序号;Overridepublic void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {if (init) {// 修改存储头部及对应字段信息的 map, 将其中的内容均右移一位, 给新增的序列号预留为第一列ExcelWriteHeadProperty excelWriteHeadProperty writeSheetHolder.excelWriteHeadProperty();MapInteger, Head headMap excelWriteHeadProperty.getHeadMap();int size headMap.size();for (int current size; current 0; current--) {int previous current - 1;headMap.put(current, headMap.get(previous));}// 空出第一列headMap.remove(0);// 只需要修改一次 map 即可, 故使用 init 变量进行控制init false;}}Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {// 设置首列单元格内容Cell cell row.createCell(0);int rowNum row.getRowNum();if (rowNum 0) {cell.setCellValue(COLUM_TITLE);}else {cell.setCellValue(rowNum);}}Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {if (row.getLastCellNum() 1) {// 将自定义新增的序号列的样式设置与默认的样式一致row.getCell(0).setCellStyle(row.getCell(1).getCellStyle());}}}8.Poi工具类 /*** excel读写工具类*/
public class POIUtil {private static Logger logger Logger.getLogger(POIUtil.class);private final static String xls xls;private final static String xlsx xlsx;/*** 读入excel文件解析后返回* param file* throws IOException*/public static ListString[] readExcel(MultipartFile file) throws IOException{//检查文件checkFile(file);//获得Workbook工作薄对象Workbook workbook getWorkBook(file);//创建返回对象把每行中的值作为一个数组所有行作为一个集合返回ListString[] list new ArrayListString[]();if(workbook ! null){for(int sheetNum 0;sheetNum workbook.getNumberOfSheets();sheetNum){//获得当前sheet工作表Sheet sheet workbook.getSheetAt(sheetNum);if(sheet null){continue;}//获得当前sheet的开始行int firstRowNum sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum sheet.getLastRowNum();//循环除了第一行的所有行for(int rowNum firstRowNum;rowNum lastRowNum;rowNum){//获得当前行Row row sheet.getRow(rowNum);Row firstRow sheet.getRow(firstRowNum);if(row null){continue;}//获得当前行的开始列int firstCellNum firstRow.getFirstCellNum();//获得首行的列数int lastCellNum firstRow.getPhysicalNumberOfCells();String[] cells new String[firstRow.getPhysicalNumberOfCells()];//循环当前行for(int cellNum firstCellNum; cellNum lastCellNum;cellNum){Cell cell row.getCell(cellNum);cells[cellNum] getCellValue(cell);}list.add(cells);}}// workbook.close();}return list;}public static void checkFile(MultipartFile file) throws IOException{//判断文件是否存在if(null file){logger.error(文件不存在);throw new FileNotFoundException(文件不存在);}//获得文件名String fileName file.getOriginalFilename();//判断文件是否是excel文件if(!fileName.endsWith(xls) !fileName.endsWith(xlsx)){logger.error(fileName 不是excel文件);throw new IOException(fileName 不是excel文件);}}public static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName file.getOriginalFilename();//创建Workbook工作薄对象表示整个excelWorkbook workbook null;try {//获取excel文件的io流InputStream is file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if(fileName.endsWith(xls)){//2003workbook new HSSFWorkbook(is);}else if(fileName.endsWith(xlsx)){//2007 及2007以上workbook new XSSFWorkbook(is);}} catch (IOException e) {logger.info(e.getMessage());}return workbook;}public static String getCellValue(Cell cell){String cellValue ;if(cell null){return cellValue;}//把数字当成String来读避免出现1读成1.0的情况if(cell.getCellType() Cell.CELL_TYPE_NUMERIC){cell.setCellType(Cell.CELL_TYPE_STRING);}//判断数据的类型switch (cell.getCellType()){case Cell.CELL_TYPE_NUMERIC: //数字cellValue String.valueOf(cell.getNumericCellValue());break;case Cell.CELL_TYPE_STRING: //字符串cellValue String.valueOf(cell.getStringCellValue());break;case Cell.CELL_TYPE_BOOLEAN: //BooleancellValue String.valueOf(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_FORMULA: //公式cellValue String.valueOf(cell.getCellFormula());break;case Cell.CELL_TYPE_BLANK: //空值cellValue ;break;case Cell.CELL_TYPE_ERROR: //故障cellValue 非法字符;break;default:cellValue 未知类型;break;}return cellValue;}}参考资料https://www.bilibili.com/video/BV1Ua4y1x7BK/
