个人网站建设设计,上海网站制作,网络工程技术适合女生吗,怎么找wordpress模板代码一、阿里巴巴EasyExcel的优势 首先说下EasyExcel相对 Apache poi的优势#xff1a; EasyExcel也是阿里研发在poi基础上做了封装#xff0c;改进产物。它替开发者做了注解列表解析#xff0c;表格填充等一系列代码编写工作#xff0c;并将此抽象成通用和可扩展的框架。相对p…
一、阿里巴巴EasyExcel的优势 首先说下EasyExcel相对 Apache poi的优势 EasyExcel也是阿里研发在poi基础上做了封装改进产物。它替开发者做了注解列表解析表格填充等一系列代码编写工作并将此抽象成通用和可扩展的框架。相对poi在数据量比较大的时候它有着更优越的性能体现。导出的时候easyexcel使用优化的反射技术避免poi频繁的去创建cell和row对象导入的时候它的解析器AnalysisEventListener可设置批量阈值 BATCH_COUNT达到阈值就往数据库插入数据然后清空解析器内部缓存相同的表格easyexcel导入所占用的内存要比poi节省90%避免了大数据量导入的时候造成的内存占用井喷这使得stop the world的时间可能会被集中而系统可能会出现短暂的停摆。而GC不能均衡调动垃圾回收。同时也避免堆积数据后sql的巨量数据的批量插入导致超出mybatis批量插入语句能承受的最大长度限制。 二、EasyExcel核心util类 Slf4j
public class EasyExcels {public static final String EXT_NAME_XLSX xlsx;public static final String EXT_NAME_XLS xls;/**** param response* param data* param filename* param sheetName* param selectMap 自定义下拉列但是既然数据都导出了下拉用处何在这个需求比较少* param T* throws IOException*/public static T void write(HttpServletResponse response, ListT data, String filename, String sheetName,ListKeyValueExcelColumn, ListString selectMap) throws IOException {setResponse(response, filename);if (StringUtils.isBlank(sheetName)) {sheetName filename;}// 输出 Exceltry {EasyExcel.write(response.getOutputStream(), data ! null !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度自动适配。最大 255 宽度.registerWriteHandler(new CustomCellWriteWeightConfig()) // Excel 列宽自适应.registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy) //内容样式.registerWriteHandler(new SelectWriteHandler(selectMap)) // 基于固定 sheet 实现下拉框.sheet(sheetName).doWrite(data);} catch (Exception e) {e.printStackTrace();} finally {response.getOutputStream().close();}}// 简单导入读取不做解析不做校验public static T ListT read(MultipartFile file, ClassT head) throws IOException {return EasyExcel.read(file.getInputStream(), head, null).autoCloseStream(false) // 不要自动关闭交给 Servlet 自己处理.doReadAllSync();}// 需要配合监听器解析数据public static T void read(MultipartFile file, ClassT head, ReadListenerT listener) throws IOException {EasyExcel.read(file.getInputStream(), head, listener).sheet().doRead();}// 不带下拉列的导出用的比较多public static T void export(HttpServletResponse response, ListT data, String filename, String sheetName) throws IOException {setResponse(response, filename);if (StringUtils.isBlank(sheetName)) {sheetName filename;}EasyExcel.write(response.getOutputStream(), data ! null !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).sheet(sheetName).doWrite(data);}// 用于合并单元格列的导出public static T void export(HttpServletResponse response, ListT data, String filename, String sheetName, RowWriteHandler handler) throws IOException {setResponse(response, filename);EasyExcel.write(response.getOutputStream(), data ! null !data.isEmpty() ? data.get(0).getClass() : null).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).registerWriteHandler(handler).sheet(sheetName).doWrite(data);}// 用于导出表头模板填充导入数据用的excel模板因为是模板所以肯定会有下拉列的需求public static T void export(HttpServletResponse response, ClassT clazz, String filename) throws IOException {setResponse(response, filename);MapInteger, ExcelSelectedResolve selectedMap resolveSelectedAnnotation(clazz);EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// .registerWriteHandler(new CustomCellWriteHeightConfig()).registerWriteHandler(new CustomCellWriteWeightConfig()).registerWriteHandler(EasyExcelStyle.horizontalCellStyleStrategy).registerWriteHandler(new SelectSheetWriteHandler(selectedMap)).sheet(filename).doWrite(Collections.emptyList());}/*** 解析表头类中的下拉注解* param head 表头类* param T 泛型* return Map下拉框列索引, 下拉框内容 map*/private static T MapInteger, ExcelSelectedResolve resolveSelectedAnnotation(ClassT head) {MapInteger, ExcelSelectedResolve selectedMap new HashMap();// getDeclaredFields(): 返回全部声明的属性getFields(): 返回public类型的属性Field[] fields head.getDeclaredFields();for (int i 0; i fields.length; i){Field field fields[i];// 解析注解信息ExcelSelected selected field.getAnnotation(ExcelSelected.class);ExcelProperty property field.getAnnotation(ExcelProperty.class);if (selected ! null) {ExcelSelectedResolve excelSelectedResolve new ExcelSelectedResolve();String[] source excelSelectedResolve.resolveSelectedSource(selected);if (source ! null source.length 0){excelSelectedResolve.setSource(source);excelSelectedResolve.setFirstRow(selected.firstRow());excelSelectedResolve.setLastRow(selected.lastRow());if (property ! null property.index() 0){selectedMap.put(property.index(), excelSelectedResolve);} else {selectedMap.put(i, excelSelectedResolve);}}}}return selectedMap;}public static void setResponse(HttpServletResponse response, String filename) throws IOException {setResponse(response, filename, EXT_NAME_XLSX);}public static void setResponse(HttpServletResponse response, String filename, String extName) throws IOException {String exportFilename URLEncoder.encode(filename, StandardCharsets.UTF_8.name());response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(utf-8);response.setHeader(Access-Control-Expose-Headers, token,Content-Type,Content-disposition);response.setHeader(Access-Control-Allow-Headers, Origin, X-Requested-With, Content-Type, Accept, Connection, User-Agent, Cookie, token,Content-Type,Content-disposition);response.setHeader(Content-disposition, exportFilename . extName);}}三、导入解析监听器
那要使用easyexcel首先要解决解析器抽象类的实现 当时第一次使用easyexcel的时候对这个工具框架不熟悉项目时间被催的紧没时间去做设计当时修改每个类的字段注解index属性每个字段单独写校验语句简直苦不堪言。我只想说磨刀不误砍柴工不注重设计的公司只会被拖延更多的时间。 /*** Title: ExcelImportReadListener* Description: 其他人如果觉得invoke()方法不满足其需求可以自己实现一下* Author: wenrong* Date: 2024/4/25 17:08* Version:1.0*/
Data
public abstract class ExcelImportReadListenerT extends ValidateBaseBo extends AnalysisEventListenerT {private static final Logger log LoggerFactory.getLogger(excelReadListener);public static int BATCH_COUNT 1000;private AtomicLong successNum new AtomicLong();private final ClassT clazz;private ValidatorT validator;private ListT successData new ArrayList();private ListT failureData new ArrayList();public ExcelImportReadListener(ClassT clazz) {this.clazz clazz;}Overridepublic void invoke(T data, AnalysisContext context) {log.info(解析到一条数据:{}, JSONObject.toJSON(data));StringBuilder errMsg new StringBuilder();try {//根据excel数据实体中的javax.validation 正则表达式来校验excel数据errMsg.append(EasyExcelValidateHandler.validateEntity(data));// 额外自定义校验以及设置数据属性的逻辑if (validator ! null) {errMsg.append(validator.validate(data));}} catch (NoSuchFieldException e) {log.error(e.getMessage());}if (StringUtils.isNotEmpty(errMsg.toString())) {data.setErrMsg(errMsg.toString());failureData.add(data);} else {successData.add(data);successNum.incrementAndGet();}if (BATCH_COUNT ! 0 successData.size() BATCH_COUNT) {try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}successData.clear();}}Overridepublic final void invokeHeadMap(MapInteger, String headMap, AnalysisContext context) {// 验证表头if (headMap.isEmpty()) {throw new ServiceException(无表头);}if (clazz ! null) {try {MapInteger, String indexNameMap getIndexNameMap(clazz);for (Integer index : indexNameMap.keySet()) {log.info(表头字段{}, headMap.get(index));if (StringUtils.isEmpty(headMap.get(index))) {throw new ServiceException(未设置index);}// 对比excel表头和解析数据的java实体类的看是否匹配if (!headMap.get(index).equals(indexNameMap.get(index))) {throw new ServiceException(导入模板错误);}}} catch (NoSuchFieldException e) {log.error(e.getMessage(), e);}}}Overridepublic final void doAfterAllAnalysed(AnalysisContext context) {log.info(所有数据解析完成共校验成功{}条数据校验失败{}条数据, successNum.get(), failureData.size());try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}}/*** 将该类做成抽象类在各service中实现saveDate方法* 不侵入业务同时不会让解析占用内存*/public void saveData() throws Exception {log.info(开始往数据库插入数据);}private MapInteger, String getIndexNameMap(ClassT clazz) throws NoSuchFieldException {MapInteger, String excelPropertyMap new HashMap();Field field;Field[] fields clazz.getDeclaredFields();int sequence 0;for (Field item : fields) {field clazz.getDeclaredField(item.getName());field.setAccessible(true);ExcelProperty excelProperty field.getAnnotation(ExcelProperty.class);if (excelProperty ! null) {// 避免每个列都要写index,插入或删除一个字段所有的index都需要修改。默认为java实体类中字段的顺序。int index excelProperty.index() -1 ? sequence : excelProperty.index();String[] values excelProperty.value();StringBuilder value new StringBuilder();for (String v : values) {value.append(v);}excelPropertyMap.put(index, value.toString());sequence;}}return excelPropertyMap;}}
3.1、解析成功的数据直接落库错误数据导出 ValidateBaseBo用在导入的时候将校验的错误保留下来然后再把有问题的数据过滤出来再导出或者显示在前端的导入结果里操作者可以按照错误信息把表格里的数据修改好后再次导入而且只将导入失败的数据导出不用去原表中大片的数据中去找有错误信息的数据目的是方便操作者快速定位表格里的问题数据。
/*** Title: ValidateBaseBo* Description:* Author: wenrong* Date: 2024/10/17 上午11:02* Version:1.0*/
Data
public abstract class ValidateBaseBo {ExcelProperty(value 错误信息)TableField(exist false)ApiModelProperty(hidden true)private String errMsg;
}3.2、解析过程中校验数据正确性 除了javax.validation基础的注解校验之外如果还需要额外的校验就自定义校验器作补充。
/*** Title: ValidData* Description: javax.validation 以外校验函数* Author: wenrong* Date: 2024/4/26 19:51* Version:1.0*/
public interface ValidatorT {/*** 这里的实现方法最后返回的如果为null一定要返回否则会被转化为null** param T t* return */String validate(T t);
} 3.3、导入/导出 Convertor excel导入数据对应的实体类要注意表格中的汉字和实际存入到数据库中数值的转换Convertor
/*** author wenrong* date 2024-11-25 17:38:26*/
Data
Builder
NoArgsConstructor
AllArgsConstructor
TableName(····)
public class YourClass extends ValidateBaseBo implements Serializable {private static final long serialVersionUID 1L;public static MapString, DyzProvinceSchool provinceMap new HashMap();public static MapString, DyzProvinceSchool schoolMap new HashMap();ExcelIgnoreTableId(type IdType.AUTO)private Long id;ApiModelProperty(value 省份ID)ExcelProperty(value 省份, converter ProvinceConvertor.class)NotNull(message 不能为空)private Integer provinceId;ApiModelProperty(value 学校ID)ExcelProperty(value 学校, converter SchoolConvertor.class)NotNull(message 不能为空)private Integer schoolId;ApiModelProperty(value 节目代码)ExcelProperty(value 节目代码)NotNull(message 不能为空)private String worksNumber;ApiModelProperty(value 节目/项目名称)ExcelProperty(value 节目/项目名称)NotNull(message 不能为空)private String ;public String validate(MapInteger, DyzProvinceSchool provinceMap, MapInteger, DyzProvinceSchool schoolMap) {StringBuilder sb new StringBuilder(this.getErrMsg() null ? : this.getErrMsg());if (provinceMap.get(this.provinceId) null) {sb.append(省份不存在: ).append(provinceId);}if (schoolMap.get(this.schoolId) null) {sb.append(学校不存在: ).append(schoolId);}return sb.toString();}public static class GroupTypeConvertor implements ConverterInteger {//导入的时候将表格的汉字转换成java对应数据库的字段Overridepublic Integer convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {switch (cellData.getStringValue()) {default:return 2;case 小学组:return 0;case 中学组:return 1;}}// 导出的时候将数据库中存储的值转换为用户能看懂的汉字Overridepublic WriteCellData? convertToExcelData(Integer value,ExcelContentProperty excelContentProperty,GlobalConfiguration globalConfiguration) {switch (value) {default:return new WriteCellData(其他组);case 0:return new WriteCellData(小学组);case 1:return new WriteCellData(中学组);}}}public static class PresentConvertor implements ConverterInteger {Overridepublic Integer convertToJavaData(ReadCellData? cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {if (cellData.getStringValue().equals(否)) {return 0;} else {return 1;}}Overridepublic WriteCellData? convertToExcelData(Integer value,ExcelContentProperty excelContentProperty,GlobalConfiguration globalConfiguration) {switch (value) {default:return new WriteCellData(-);case 0:return new WriteCellData(否);case 1:return new WriteCellData(是);}}}public static class ProvinceConvertor implements ConverterInteger {Overridepublic Integer convertToJavaData(ReadCellData? cellData,ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception {if (provinceMap.isEmpty()) {throw new Exception(省份配置数据为空);}return StringUtils.isBlank(cellData.getStringValue()) ? null : provinceMap.get(cellData.getStringValue()).getId();}}public static class SchoolConvertor implements ConverterInteger {Overridepublic Integer convertToJavaData(ReadCellData? cellData,ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws Exception {if (schoolMap.isEmpty()) {throw new Exception(学校配置数据为空);}return StringUtils.isBlank(cellData.getStringValue()) ? null : schoolMap.get(cellData.getStringValue()).getId();}// 导出转换省略掉}}3.4、实现导入解析监听器 上面的解析监听器是个抽象类是一种模板模式的设计思想应用我们根据不同的业务自己扩展invoke方法和saveData方法但其实saveData也可以做成模板方法只是需要依赖内部绑定一个数据层dao接口Mapper对于有的人来说会耦合dao层但我觉得如果dao层取一个接口那么也没什么耦合的问题。节省不必要的重复代码还是值得的。
那么上述的那个模板抽象解析监听器可以改为
/*** Title: ExcelImportReadListener* Description: 其他人如果觉得invoke()方法不满足其需求可以自己实现一下* Author: wenrong* Date: 2024/4/25 17:08* Version:1.0*/
Data
public abstract class ExcelImportReadListenerT extends ValidateBaseBo, S extends IServiceT extends AnalysisEventListenerT {private static final Logger log LoggerFactory.getLogger(excelReadListener);public static int BATCH_COUNT 1000;private AtomicLong successNum new AtomicLong();private final ClassT clazz;private S service;private ValidatorT validator;private ListT successData new ArrayList();private ListT failureData new ArrayList();public ExcelImportReadListener(ClassT clazz, S service) {this.clazz clazz;this.service service;}Overridepublic void invoke(T data, AnalysisContext context) {log.info(解析到一条数据:{}, JSONObject.toJSON(data));StringBuilder errMsg new StringBuilder();try {//根据excel数据实体中的javax.validation 正则表达式来校验excel数据errMsg.append(EasyExcelValidateHandler.validateEntity(data));// 额外自定义校验以及设置数据属性的逻辑if (validator ! null) {errMsg.append(validator.validate(data));}} catch (NoSuchFieldException e) {log.error(e.getMessage());}if (StringUtils.isNotEmpty(errMsg.toString())) {data.setErrMsg(errMsg.toString());failureData.add(data);} else {successData.add(data);successNum.incrementAndGet();}if (BATCH_COUNT ! 0 successData.size() BATCH_COUNT) {try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}successData.clear();}}Overridepublic final void invokeHeadMap(MapInteger, String headMap, AnalysisContext context) {// 验证表头if (headMap.isEmpty()) {throw new ServiceException(无表头);}if (clazz ! null) {try {MapInteger, String indexNameMap getIndexNameMap(clazz);for (Integer index : indexNameMap.keySet()) {log.info(表头字段{}, headMap.get(index));if (StringUtils.isEmpty(headMap.get(index))) {throw new ServiceException(未设置index);}if (!headMap.get(index).equals(indexNameMap.get(index))) {throw new ServiceException(导入模板错误);}}} catch (NoSuchFieldException e) {log.error(e.getMessage(), e);}}}Overridepublic final void doAfterAllAnalysed(AnalysisContext context) {log.info(所有数据解析完成共校验成功{}条数据校验失败{}条数据, successNum.get(), failureData.size());try {saveData();} catch (Exception e) {log.error(e.getMessage(), e);}}/*** 将该类做成抽象类在各service中实现saveDate方法* 不侵入业务同时不会让解析占用内存*/public void saveData() throws Exception {log.info(开始往数据库插入数据);ListT successData this.getSuccessData();ListT failureData this.getFailureData();boolean saved service.saveBatch(successData);if (!saved) {successData.forEach(work - work.setErrMsg(保存失败));failureData.addAll(successData);} else {this.setSuccessData(successData);}}private MapInteger, String getIndexNameMap(ClassT clazz) throws NoSuchFieldException {MapInteger, String excelPropertyMap new HashMap();Field field;Field[] fields clazz.getDeclaredFields();int sequence 0;for (Field item : fields) {field clazz.getDeclaredField(item.getName());field.setAccessible(true);ExcelProperty excelProperty field.getAnnotation(ExcelProperty.class);if (excelProperty ! null) {int index excelProperty.index() -1 ? sequence : excelProperty.index();String[] values excelProperty.value();StringBuilder value new StringBuilder();for (String v : values) {value.append(v);}excelPropertyMap.put(index, value.toString());sequence;}}return excelPropertyMap;}}
业务代码中实现模板解析监听器的代码示例 OverrideTransactionalpublic ExcelImportReadListenerBasicWorks importExcel(MultipartFile file) throws IOException {ListDyzProvinceSchool schoolList dyzProvinceSchoolService.getSchoolList();ListDyzProvinceSchool provinceList dyzProvinceSchoolService.getProvinceList();MapString, DyzProvinceSchool schoolMap schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getSchoolName, s - s));MapInteger, DyzProvinceSchool schoolMap1 schoolList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s - s));MapString, DyzProvinceSchool provinceMap provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getProvinceName, s - s));MapInteger, DyzProvinceSchool provinceMap1 provinceList.stream().collect(Collectors.toMap(DyzProvinceSchool::getId, s - s));BasicWorks.schoolMap schoolMap;BasicWorks.provinceMap provinceMap;// 匿名内部类扩展模板监听器ExcelImportReadListenerBasicWorks readListener new ExcelImportReadListenerBasicWorks(BasicWorks.class) {Overridepublic void invoke(BasicWorks data, AnalysisContext context) {SetString allDataExistInExcel new HashSet();SetString allDataExistInDataSource list().stream().map(BasicWorks::getWorksNumber).collect(Collectors.toSet());ListBasicWorks failureData this.getFailureData();StringBuilder errMsg new StringBuilder(data.getErrMsg() null ? : data.getErrMsg());if (StringUtils.isBlank(data.getWorksNumber())) {errMsg.append(节目代码不能为空);data.setErrMsg(errMsg.toString());failureData.add(data);} else {if (allDataExistInExcel.contains(data.getWorksNumber())) {errMsg.append(Excel表格中存在重复的数据).append(节目代码).append(data.getWorksNumber());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getWorksNumber());}if (allDataExistInDataSource.contains(data.getWorksNumber())) {errMsg.append(数据库中存在重复的数据).append(节目代码).append(data.getWorksNumber());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getWorksNumber());}}allDataExistInExcel.add(data.getWorksNumber());super.invoke(data, context);}// 设置javax.validation以外校验器将会在invoke方法里执行校验readListener.setValidator(work - work.validate(provinceMap1, schoolMap1));// 导入 ExcelEasyExcels.read(file, BasicWorks.class, readListener);return readListener;} 另外还有需要将表格中图片导入后自动上传到文件服务然后将url保存在数据库的需求
public ExcelImportReadListenerBasicHotel importExcel(MultipartFile file) throws IOException {//获取图片联合Apache 的ExcelUtilExcelPicUtil工具类获取图片数据对象PictureDataExcelReader reader ExcelUtil.getReader(file.getInputStream());MapString, PictureData picMap ExcelPicUtil.getPicMap(reader.getWorkbook(), 0);ExcelImportReadListenerBasicHotel readListener new ExcelImportReadListenerBasicHotel(BasicHotel.class) {Overridepublic void invoke(BasicHotel data, AnalysisContext context) {SetString allDataExistInDataSource list().stream().map(BasicHotel::getHotelName).collect(Collectors.toSet());SetString allDataExistInExcel new HashSet();ListBasicHotel failureData this.getFailureData();StringBuilder errMsg new StringBuilder(data.getErrMsg() null ? : data.getErrMsg());if (StringUtils.isEmpty(data.getErrMsg())) {errMsg.append(酒店名称不能为空);data.setErrMsg(errMsg.toString());failureData.add(data);} else {if (allDataExistInExcel.contains(data.getHotelName())) {errMsg.append(Excel表格中存在重复的数据).append(酒店名称).append(data.getHotelName());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getHotelName());}if (allDataExistInDataSource.contains(data.getHotelName())) {errMsg.append(数据库中存在重复的数据).append(酒店名称).append(data.getHotelName());data.setErrMsg(errMsg.toString());failureData.add(data);allDataExistInExcel.add(data.getHotelName());}}allDataExistInExcel.add(data.getHotelName());String err ;int rowIndex context.readRowHolder().getRowIndex() 1;PictureData pictureData picMap.get(rowIndex _0);if (pictureData null) {err String.format(data.getErrMsg() 第%s行%s, rowIndex, 酒店照片为空);}try {// 上传图片String fileUrl ossFileController.ftpUploadFile(pictureData.getData(), , data.getHotelName());data.setPicture(fileUrl);} catch (IOException ex) {err String.format(data.getErrMsg() 第%s行%s, rowIndex, 酒店照片为空上传失败);}data.setErrMsg(err);super.invoke(data, context);}};// 导入 ExcelEasyExcels.read(file, BasicHotel.class, readListener);return readListener;
} 这是导入部分导出部分五花八门的需求就比较多了。 四、导出 4.1、导出 数据导入模板
模板一般会有下拉选项列的需求下拉列一般用注解枚举几个就行了 import java.lang.annotation.*;/*** 标注导出的列为下拉框类型并为下拉框设置内容*/
Documented
Retention(RetentionPolicy.RUNTIME)
Target(ElementType.FIELD)
public interface ExcelSelected {/*** 固定下拉内容*/String[] source() default {};/*** 设置下拉框的起始行默认为第二行*/int firstRow() default 1;/*** 设置下拉框的结束行默认为最后一行*/int lastRow() default 0x10000;
}下拉注解解析器
Data
Slf4j
public class ExcelSelectedResolve {/*** 下拉内容*/private String[] source;/*** 设置下拉框的起始行默认为第二行*/private int firstRow;/*** 设置下拉框的结束行默认为最后一行*/private int lastRow;public String[] resolveSelectedSource(ExcelSelected excelSelected) {if (excelSelected null) {return null;}// 获取固定下拉框的内容String[] source excelSelected.source();if (source.length 0) {return source;}// // 获取动态下拉框的内容
// Class? extends ExcelDynamicSelect[] classes excelSelected.sourceClass();
// if (classes.length 0) {
// try {
// ExcelDynamicSelect excelDynamicSelect classes[0].newInstance();
// String[] dynamicSelectSource excelDynamicSelect.getSource();
// if (dynamicSelectSource ! null dynamicSelectSource.length 0) {
// return dynamicSelectSource;
// }
// } catch (InstantiationException | IllegalAccessException e) {
// log.error(解析动态下拉框数据异常, e);
// }
// }return null;}}下拉handler:
public class SelectSheetWriteHandler implements SheetWriteHandler {private final MapInteger, ExcelSelectedResolve selectedMap;public SelectSheetWriteHandler(MapInteger, ExcelSelectedResolve selectedMap) {this.selectedMap selectedMap;}/*** Called before create the sheet*/Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** Called after the sheet is created*/Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 这里可以对cell进行任何操作Sheet sheet writeSheetHolder.getSheet();DataValidationHelper helper sheet.getDataValidationHelper();selectedMap.forEach((k, v) - {// 设置下拉列表的行 首行末行首列末列CellRangeAddressList rangeList new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);// 设置下拉列表的值DataValidationConstraint constraint helper.createExplicitListConstraint(v.getSource());// 设置约束DataValidation validation helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox(提示, 请输入下拉选项中的内容);sheet.addValidationData(validation);});}} 其实如果是动态的下拉列表不能固定枚举的话直接从配置数据表中拉出业务配置列表将列表作为传参使用util类EasyExcels第一个方法就好。 ExcelProperty(value 组别)ExcelSelected(source {小学组, 初中组})ApiModelProperty(value 组别:0-小学组1-初中组2-其他组)NotNull(message 不能为空)private String groupType;ExcelProperty(value 是否出席)ExcelSelected(source {是, 否})ApiModelProperty(value 是否出席0-否1是)NotNull(message 不能为空)private String present; 4.2、图片导出convertor
public class UrlPictureConverter implements ConverterString {public static int urlConnectTimeout 2000;public static int urlReadTimeout 6000;Overridepublic Class? supportJavaTypeKey() {return String.class;}Overridepublic WriteCellData? convertToExcelData(String url, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws IOException {InputStream inputStream null;try {URL value new URL(url);if (ObjectUtils.isEmpty(value)) {return new WriteCellData();}URLConnection urlConnection value.openConnection();urlConnection.setConnectTimeout(urlConnectTimeout);urlConnection.setReadTimeout(urlReadTimeout);inputStream urlConnection.getInputStream();byte[] bytes IoUtils.toByteArray(inputStream);return new WriteCellData(bytes);} catch (Exception e) {log.info(图片获取异常, e);return new WriteCellData(图片获取异常);} finally {if (inputStream ! null) {inputStream.close();}}}
}
4.3、有合并单元格导出
Data
Builder
AllArgsConstructor
NoArgsConstructor
ExcelIgnoreUnannotated
Slf4j
public class WorkJudgesStatisticsVo implements Serializable {private static final long serialVersionUID 1L;ExcelProperty(value 序号, index 0)private String sequence;public static class MergeStrategy implements RowWriteHandler {private int totalRowNum;public MergeStrategy(int totalRowNum) {this.totalRowNum totalRowNum;}public static MergeStrategy build(int totalRowNum) {return new MergeStrategy(totalRowNum);}Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {if (isHead) {// 处理表头return;}log.info(当前行号:{}, row.getRowNum());log.info(总行数:{}, totalRowNum);// 合并if (row.getRowNum() ! totalRowNum 1) {return;}writeSheetHolder.getSheet().addMergedRegion(new CellRangeAddress(writeSheetHolder.getLastRowIndex(), writeSheetHolder.getLastRowIndex(), 5, 6));}}}4.4、行转列并使用模板的方式导出
Override
public void selectWorksJudgesResultReview(HttpServletResponse response) throws IOException {ListMapString, Object views scoreReviewWorksJudgesMapper.selectWorksJudgesResultReview();ListDyzScoreReviewWorksJudges reviewWorksJudges scoreReviewWorksJudgesMapper.selectList();ListDyzScoreWorksFiles scoreWorksFiles worksFilesMapper.selectList();HashMapLong, ListDyzScoreWorksFiles fileMap new HashMap();scoreWorksFiles.forEach(f - {ListDyzScoreWorksFiles files fileMap.computeIfAbsent(f.getWorksId(), k - new ArrayList());files.add(f);});HashMapLong, ListDyzScoreReviewWorksJudges scoreMap new HashMap();reviewWorksJudges.forEach(judge - {ListDyzScoreReviewWorksJudges judges scoreMap.computeIfAbsent(judge.getWorksId(), k - new ArrayList());judges.add(judge);});AtomicInteger sequence new AtomicInteger(0);views.forEach(map - {map.put(sequence, String.valueOf(sequence.incrementAndGet()));Long workId Long.valueOf(map.get(workId).toString());ListDyzScoreReviewWorksJudges judges scoreMap.get(workId);for (int i 0; i 15; i) {map.put(score (i 1), );map.put(correctness (i 1), );}map.put(avgScore, );map.put(avgScore1, );if (judges ! null judges.size() 0) {AtomicInteger serialNo new AtomicInteger(0);AtomicInteger serialNo1 new AtomicInteger(0);judges.forEach(j - {map.put(score serialNo.incrementAndGet(), j.getScore());map.put(correctness serialNo1.incrementAndGet(), j.getRemark());});judges.sort(Comparator.comparing(DyzScoreReviewWorksJudges::getScore));BigDecimal sum judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);BigDecimal avg sum.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);map.put(avgScore, avg);if (judges.size() 3) {judges.remove(0);judges.remove(judges.size() - 1);BigDecimal sum1 judges.stream().map(DyzScoreReviewWorksJudges::getScore).reduce(BigDecimal.ZERO, BigDecimal::add);BigDecimal avg1 sum1.divide(BigDecimal.valueOf(judges.size()), 2, BigDecimal.ROUND_HALF_UP);map.put(avgScore1, avg1);}}for (int i 0; i 4; i) {map.put(fileName (i 1), );}ListDyzScoreWorksFiles files fileMap.get(workId);if (files ! null files.size() 0) {AtomicInteger serialNo new AtomicInteger(0);files.forEach(f - map.put(fileName serialNo.incrementAndGet(), f.getUrl()));}});ConcurrentHashSetString columns views.stream().flatMap(map - map.keySet().stream()).collect(Collectors.toCollection(ConcurrentHashSet::new));ListString scoreColumns columns.stream().filter(c - c.contains(score) || c.contains(avgScore)).collect(Collectors.toList());ListString correctnessColumns columns.stream().filter(c - c.contains(correctness)).collect(Collectors.toList());//输入流InputStream inputStream null;ServletOutputStream outputStream null;ExcelWriter excelWriter null;try {org.springframework.core.io.Resource templateFile resourceLoader.getResource(classpath:templates\\XXXX报表.xlsx);inputStream templateFile.getInputStream();// 获取文件名并转码response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);outputStream response.getOutputStream();// 创建填充配置FillConfig fillConfig FillConfig.builder().forceNewRow(true).build();// 创建写对象excelWriter EasyExcel.write(outputStream).withTemplate(inputStream).build();// 创建Sheet对象WriteSheet sheet EasyExcel.writerSheet(0, 报名数量统计).build();excelWriter.fill(views, fillConfig, sheet);excelWriter.fill(new FillWrapper(scoreColumns, scoreColumns), sheet);excelWriter.fill(new FillWrapper(correctnessColumns, correctnessColumns), sheet);} catch (Exception e) {log.error(导出失败{}, e.getMessage());} finally {if (excelWriter ! null) {excelWriter.finish();}//关闭流if (outputStream ! null) {try {outputStream.close();} catch (IOException e) {log.error(关闭输出流失败, e);}}if (inputStream ! null) {try {inputStream.close();} catch (IOException e) {log.error(关闭输入流失败, e);}}}
}
模板里面的取值占位符写法 还有一些表格宽度高度自适应策略美化风格的代码就不贴了需要的话到我的资源中去下载。
