西安购物网站建设,wordpress now,网站 建设在作用,江苏华柯建设发展有限公司网站2.0.5版本做了很大改变#xff0c;记录2.1.6的所使用的工具类及方法
其实持续对easyexcel的git进行关注是最方便的#xff0c;上面也有完整的demo以及工具类等等 1.easyExcel的pom坐标 !-- easyexcel --dependencygroupIdcom.alibaba/groupId记录2.1.6的所使用的工具类及方法
其实持续对easyexcel的git进行关注是最方便的上面也有完整的demo以及工具类等等 1.easyExcel的pom坐标 !-- easyexcel --dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.1.6/version/dependency
2.easyExcel工具类
package com.chinargb.baseadmin.util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.formula.functions.T;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;/*** author: * date: * description:*/
public class EasyExcelUtil
{/*** 同步无模型读默认读取sheet0,从第2行开始读* param filePath* return*/public static ListMapInteger, String syncRead(String filePath){return EasyExcelFactory.read(filePath).sheet().doReadSync();}/*** 同步无模型读默认表头占一行从第2行开始读* param filePath* param sheetNo sheet页号从0开始* return*/public static ListMapInteger, String syncRead(String filePath, Integer sheetNo){return EasyExcelFactory.read(filePath).sheet(sheetNo).doReadSync();}/*** 同步无模型读指定sheet和表头占的行数* param inputStream* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return ListMapcolNum, cellValue*/public static ListMapInteger, String syncRead(InputStream inputStream, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();}/*** 同步无模型读指定sheet和表头占的行数* param file* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return ListMapcolNum, cellValue*/public static ListMapInteger, String syncRead(File file, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();}/*** 同步无模型读指定sheet和表头占的行数* param filePath* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return ListMapcolNum, cellValue*/public static ListMapInteger, String syncRead(String filePath, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();}/*** 同步按模型读默认读取sheet0,从第2行开始读* param filePath* param clazz 模型的类类型excel数据会按该类型转换成对象* return*/public static ListT syncReadModel(String filePath, Class clazz){return EasyExcelFactory.read(filePath).sheet().head(clazz).doReadSync();}/*** 同步按模型读默认表头占一行从第2行开始读* param filePath* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* return*/public static ListT syncReadModel(String filePath, Class clazz, Integer sheetNo){return EasyExcelFactory.read(filePath).sheet(sheetNo).head(clazz).doReadSync();}/*** 同步按模型读指定sheet和表头占的行数* param inputStream* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static ListT syncReadModel(InputStream inputStream, Class clazz, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();}/*** 同步按模型读指定sheet和表头占的行数* param file* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static ListT syncReadModel(File file, Class clazz, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();}/*** 同步按模型读指定sheet和表头占的行数* param filePath* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static ListT syncReadModel(String filePath, Class clazz, Integer sheetNo, Integer headRowNum){return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();}/*** 异步无模型读默认读取sheet0,从第2行开始读* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param filePath 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static void asyncRead(String filePath, AnalysisEventListenerT excelListener){EasyExcelFactory.read(filePath, excelListener).sheet().doRead();}/*** 异步无模型读默认表头占一行从第2行开始读* param filePath 表头占的行数从0开始如果要连表头一起读出来则传0* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param sheetNo sheet页号从0开始* return*/public static void asyncRead(String filePath, AnalysisEventListenerT excelListener, Integer sheetNo){EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).doRead();}/*** 异步无模型读指定sheet和表头占的行数* param inputStream* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static void asyncRead(InputStream inputStream, AnalysisEventListenerT excelListener, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(inputStream, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 异步无模型读指定sheet和表头占的行数* param file* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static void asyncRead(File file, AnalysisEventListenerT excelListener, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(file, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 异步无模型读指定sheet和表头占的行数* param filePath* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0* return*/public static void asyncRead(String filePath, AnalysisEventListenerT excelListener, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 异步按模型读取默认读取sheet0,从第2行开始读* param filePath* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param clazz 模型的类类型excel数据会按该类型转换成对象*/public static void asyncReadModel(String filePath, AnalysisEventListenerT excelListener, Class clazz){EasyExcelFactory.read(filePath, clazz, excelListener).sheet().doRead();}/*** 异步按模型读取默认表头占一行从第2行开始读* param filePath* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始*/public static void asyncReadModel(String filePath, AnalysisEventListenerT excelListener, Class clazz, Integer sheetNo){EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).doRead();}/*** 异步按模型读取* param inputStream* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0*/public static void asyncReadModel(InputStream inputStream, AnalysisEventListenerT excelListener, Class clazz, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(inputStream, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 异步按模型读取* param file* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0*/public static void asyncReadModel(File file, AnalysisEventListenerT excelListener, Class clazz, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(file, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 异步按模型读取* param filePath* param excelListener 监听器在监听器中可以处理行数据LinkedHashMap表头数据异常处理等* param clazz 模型的类类型excel数据会按该类型转换成对象* param sheetNo sheet页号从0开始* param headRowNum 表头占的行数从0开始如果要连表头一起读出来则传0*/public static void asyncReadModel(String filePath, AnalysisEventListenerT excelListener, Class clazz, Integer sheetNo, Integer headRowNum){EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();}/*** 无模板写文件* param filePath* param head 表头数据* param data 表内容数据*/public static void write(String filePath, ListListString head, ListListObject data){EasyExcel.write(filePath).head(head).sheet().doWrite(data);}/*** 无模板写文件* param filePath* param head 表头数据* param data 表内容数据* param sheetNo sheet页号从0开始* param sheetName sheet名称*/public static void write(String filePath, ListListString head, ListListObject data, Integer sheetNo, String sheetName){EasyExcel.write(filePath).head(head).sheet(sheetNo, sheetName).doWrite(data);}/*** 根据excel模板文件写入文件* param filePath* param templateFileName* param headClazz* param data*/public static void writeTemplate(String filePath, String templateFileName, Class headClazz, List data){EasyExcel.write(filePath, headClazz).withTemplate(templateFileName).sheet().doWrite(data);}/*** 根据excel模板文件写入文件* param filePath* param templateFileName* param data*/public static void writeTemplate(String filePath, String templateFileName, List data){EasyExcel.write(filePath).withTemplate(templateFileName).sheet().doWrite(data);}/*** 按模板写文件* param filePath* param headClazz 表头模板* param data 数据*/public static void write(String filePath, Class headClazz, List data){EasyExcel.write(filePath, headClazz).sheet().doWrite(data);}/*** 按模板写文件* param filePath* param headClazz 表头模板* param data 数据* param sheetNo sheet页号从0开始* param sheetName sheet名称*/public static void write(String filePath, Class headClazz, List data, Integer sheetNo, String sheetName){EasyExcel.write(filePath, headClazz).sheet(sheetNo, sheetName).doWrite(data);}/*** 按模板写文件* param filePath* param headClazz 表头模板* param data 数据* param writeHandler 自定义的处理器比如设置table样式设置超链接、单元格下拉框等等功能都可以通过这个实现需要注册多个则自己通过链式去调用* param sheetNo sheet页号从0开始* param sheetName sheet名称*/public static void write(String filePath, Class headClazz, List data, WriteHandler writeHandler, Integer sheetNo, String sheetName){EasyExcel.write(filePath, headClazz).registerWriteHandler(writeHandler).sheet(sheetNo, sheetName).doWrite(data);}/*** 按模板写文件包含某些字段* param filePath* param headClazz 表头模板* param data 数据* param includeCols 过滤包含的字段根据字段名称过滤* param sheetNo sheet页号从0开始* param sheetName sheet名称*/public static void writeInclude(String filePath, Class headClazz, List data, SetString includeCols, Integer sheetNo, String sheetName){EasyExcel.write(filePath, headClazz).includeColumnFiledNames(includeCols).sheet(sheetNo, sheetName).doWrite(data);}/*** 按模板写文件排除某些字段* param filePath* param headClazz 表头模板* param data 数据* param excludeCols 过滤排除的字段根据字段名称过滤* param sheetNo sheet页号从0开始* param sheetName sheet名称*/public static void writeExclude(String filePath, Class headClazz, List data, SetString excludeCols, Integer sheetNo, String sheetName){EasyExcel.write(filePath, headClazz).excludeColumnFiledNames(excludeCols).sheet(sheetNo, sheetName).doWrite(data);}/*** 多个sheet页的数据链式写入* ExcelUtil.writeWithSheets(outputStream)* .writeModel(ExcelModel.class, excelModelList, sheetName1)* .write(headData, data,sheetName2)* .finish();* param outputStream* return*/public static EasyExcelWriterFactory writeWithSheets(OutputStream outputStream){EasyExcelWriterFactory excelWriter new EasyExcelWriterFactory(outputStream);return excelWriter;}/*** 多个sheet页的数据链式写入* ExcelUtil.writeWithSheets(file)* .writeModel(ExcelModel.class, excelModelList, sheetName1)* .write(headData, data,sheetName2)* .finish();* param file* return*/public static EasyExcelWriterFactory writeWithSheets(File file){EasyExcelWriterFactory excelWriter new EasyExcelWriterFactory(file);return excelWriter;}/*** 多个sheet页的数据链式写入* ExcelUtil.writeWithSheets(filePath)* .writeModel(ExcelModel.class, excelModelList, sheetName1)* .write(headData, data,sheetName2)* .finish();* param filePath* return*/public static EasyExcelWriterFactory writeWithSheets(String filePath){EasyExcelWriterFactory excelWriter new EasyExcelWriterFactory(filePath);return excelWriter;}/*** 多个sheet页的数据链式写入失败了会返回一个有部分数据的Excel* ExcelUtil.writeWithSheets(response, exportFileName)* .writeModel(ExcelModel.class, excelModelList, sheetName1)* .write(headData, data,sheetName2)* .finish();* param response* param exportFileName 导出的文件名称* return*/public static EasyExcelWriterFactory writeWithSheetsWeb(HttpServletResponse response, String exportFileName) throws IOException{response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码String fileName URLEncoder.encode(exportFileName, UTF-8);response.setHeader(Content-disposition, attachment;filename fileName .xlsx);EasyExcelWriterFactory excelWriter new EasyExcelWriterFactory(response.getOutputStream());return excelWriter;}
}/*** 默认按模型读取的监听器* param T*/
Slf4j
class DefaultExcelListenerT extends AnalysisEventListenerT
{private final ListT rows new ArrayList();Overridepublic void invokeHeadMap(MapInteger, String headMap, AnalysisContext context) {log.info(解析到一条头数据:{}, JSON.toJSONString(headMap));}Overridepublic void invoke(T object, AnalysisContext context) {rows.add(object);// 实际数据量比较大时rows里的数据可以存到一定量之后进行批量处理比如存到数据库// 然后清空列表以防止内存占用过多造成OOM}Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info(read {} rows, rows.size());}/*** 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。* param exception* param context* throws Exception*/Overridepublic void onException(Exception exception, AnalysisContext context) {log.error(解析失败但是继续解析下一行:{}, exception.getMessage());if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException (ExcelDataConvertException)exception;log.error(第{}行第{}列解析异常数据为:{}, excelDataConvertException.getRowIndex(),excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());}}public ListT getRows() {return rows;}
}
3.EasyExcel工厂类
package com.chinargb.baseadmin.util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;import java.io.File;
import java.io.OutputStream;
import java.util.List;/*** 自定义EasyExcel写工厂*/
public class EasyExcelWriterFactory
{private int sheetNo 0;private ExcelWriter excelWriter null;public EasyExcelWriterFactory(OutputStream outputStream) {excelWriter EasyExcel.write(outputStream).build();}public EasyExcelWriterFactory(File file) {excelWriter EasyExcel.write(file).build();}public EasyExcelWriterFactory(String filePath) {excelWriter EasyExcel.write(filePath).build();}/*** 链式模板表头写入* param headClazz 表头格式* param data 数据 ListExcelModel 或者ListListObject* return*/public EasyExcelWriterFactory writeModel(Class headClazz, List data, String sheetName){excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo, sheetName).head(headClazz).build());return this;}/*** 链式自定义表头写入* param head* param data 数据 ListExcelModel 或者ListListObject* param sheetName* return*/public EasyExcelWriterFactory write(ListListString head, List data, String sheetName){excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo, sheetName).head(head).build());return this;}public void finish() {excelWriter.finish();}
}
4.模板模型对象
package com.chinargb.baseadmin.util.excelModel;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;/*** excel模型对象*/
EqualsAndHashCode(callSuper true)
Data
public class TableHeaderExcelProperty extends BaseRowModel {/*** value: 表头名称* index: 列的号, 0表示第一列*/ExcelProperty(value 姓名, index 0)private String name;ExcelProperty(value 年龄,index 1)private int age;ExcelProperty(value 学校,index 2)private String school;
}
5.excel导入测试及数据结果 RunWith(SpringRunner.class)
SpringBootTest(webEnvironment SpringBootTest.WebEnvironment.RANDOM_PORT)
public class BaseAdminApplicationTests {Testpublic void easyExcelTest() {String FilePathD:/学生信息.xlsx;ListT tList EasyExcelUtil.syncReadModel(FilePath, TableHeaderExcelProperty.class);System.out.println(tList);}
}
