easyexcel调用公共导出方法导出数据

easyexcel备忘

@Slf4j
public class ConditionDownloadUtil {//扫描在xboot 包下所有IService 接口的子类, 每次启动服务后, 重新扫描public final static Class[] classesExtendsIService = ClassUtil.scanPackageBySuper("cn.exrick.xboot", IService.class).toArray(new Class[1]);public static <T, E> void nonMultiQueryDownload(Class<T> clazz,QueryWrapper<T> queryWrapper,String excelName,Class<E> excelClazz,HttpServletResponse response) {if (ObjectUtils.isNotEmpty(clazz)) {//1. 根据入参中的class 来获取对应的IService BeanIService<T> targetIService = getTargetIService(clazz);/** 2. 由于查询/排序字段在每个模块中不同, 所以需要让调用方提供, 不再统一提供* 调用IService 的list() 方法获取查询符合条件的数据*/List<T> list = targetIService.list(queryWrapper);if (list == null || list.size() == 0) {throw ExceptionUtil.wrapRuntime("数据为空, 无法下载excel!");}//3. 将实体类List 转换为Excel 类ListList<E> excelList = list.stream().map(x -> BeanUtil.toBean(x, excelClazz, CopyOptions.create().setIgnoreError(true))).collect(Collectors.toList());//4. 调用EasyExcel 通用方法输出ExcelafterMultiQueryDownload(excelName, excelList, excelClazz, new CustomMergeStrategy(excelClazz, excelList.size()), response);} else {throw ExceptionUtil.wrapRuntime("实体类不能为空!");}}public static <E> void afterMultiQueryDownload(String excelName,List<E> excelList,Class<E> excelClass,WriteHandler writeHandler,HttpServletResponse response) {//1. 判断需要输入的Excel List 是否为空, 如果不为空, 则输入, 否则, 抛出异常if (CollectionUtil.isNotEmpty(excelList)) {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + URLUtil.encode(excelName, StandardCharsets.UTF_8)  + ".xlsx");//2. 输入Exceltry {ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(response.getOutputStream()).excelType(ExcelTypeEnum.XLSX).head(excelClass).sheet();if (ObjectUtil.isNotEmpty(writeHandler)) {excelWriterSheetBuilder.registerWriteHandler(writeHandler);}//3. 清除Convertor 中的线程变量DictConverter.removeThreadLocal();excelWriterSheetBuilder.doWrite(excelList);} catch (Exception e) {StringWriter sw = new StringWriter();PrintWriter pw = new PrintWriter(sw);e.printStackTrace(pw);log.error(sw.toString());if (e.getCause() instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e.getCause();String cellMsg = "";CellData cellData = excelDataConvertException.getCellData();//这里有一个celldatatype的枚举值,用来判断CellData的数据类型CellDataTypeEnum type = cellData.getType();if (type.equals(CellDataTypeEnum.NUMBER)) {cellMsg = cellData.getNumberValue().toString();} else if (type.equals(CellDataTypeEnum.STRING)) {cellMsg = cellData.getStringValue();} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {cellMsg = cellData.getBooleanValue().toString();}String errorMsg = String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!<span style=\"color:red\">请检查其他的记录是否有同类型的错误!</span>", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex(), cellMsg);log.error(errorMsg);}}} else {throw ExceptionUtil.wrapRuntime("数据为空, 无法下载excel!");}}public <T, E> void dynamicHeadDownload(List<List<String>> headList,String excelName,List data,HttpServletResponse response) {//1. 判断Head List 是否为空, 如果不为空, 则输入, 否则, 抛出异常if (ObjectUtil.isNotEmpty(headList)) {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");//2. 输入Exceltry {EasyExcel.write(response.getOutputStream()).head(headList).sheet().doWrite(data);} catch (Exception e) {StringWriter sw = new StringWriter();PrintWriter pw = new PrintWriter(sw);e.printStackTrace(pw);log.error(sw.toString());if (e.getCause() instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e.getCause();String cellMsg = "";CellData cellData = excelDataConvertException.getCellData();//这里有一个celldatatype的枚举值,用来判断CellData的数据类型CellDataTypeEnum type = cellData.getType();if (type.equals(CellDataTypeEnum.NUMBER)) {cellMsg = cellData.getNumberValue().toString();} else if (type.equals(CellDataTypeEnum.STRING)) {cellMsg = cellData.getStringValue();} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {cellMsg = cellData.getBooleanValue().toString();}String errorMsg = String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!<span style=\"color:red\">请检查其他的记录是否有同类型的错误!</span>", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex(), cellMsg);log.error(errorMsg);}}} else {throw new RuntimeException("数据为空, 无法下载excel!");}}/*** 添加表头* @param head* @param headers*/public static void addHead(String head, List<List<String>> headers) {ArrayList<String> list = new ArrayList<>(1);list.add(head);headers.add(list);}/*@SneakyThrowspublic void tablesDownload(ReportDownloadModel reportDownloadModel, HttpServletResponse response) {String excelName = URLEncoder.encode(reportDownloadModel.getExcelName(), "utf-8");List<ReportTableModel> tableList = reportDownloadModel.getTables();response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(new CustomCellWriteHeightConfig()).needHead(Boolean.TRUE).build();//1. 定义标题二维数组ArrayList<List<String>> titleHead = new ArrayList<>();//2. 定义最终输出的excel 二维数组ArrayList<List<Object>> finalData = new ArrayList<>();//3. 定义空行ArrayList<Object> emptyLine = new ArrayList<>();emptyLine.add("");try {for (int i = 0; i < tableList.size() * 2; i = i + 2) {ReportTableModel reportTableModel = tableList.get(i / 2);List<List<String>> heads = reportTableModel.getHeads();List<List<Object>> data = reportTableModel.getData();String title = reportTableModel.getTitle();//3. 定义表名行ArrayList<String> titleLine = new ArrayList<>();titleLine.add(title);titleHead.add(titleLine);for (int j = 0; j < heads.size() - 1; j++) {ArrayList<String> empty = new ArrayList<>();empty.add("");titleHead.add(empty);}WriteTable writeTableForTitle = EasyExcel.writerTable(i).needHead(Boolean.TRUE).head(titleHead).build();excelWriter.write(null, writeSheet, writeTableForTitle);//4. 在表数据后添加空行finalData.addAll(data);finalData.add(emptyLine);WriteTable writeTableForData = EasyExcel.writerTable(i + 1).needHead(Boolean.TRUE).head(heads).build();excelWriter.write(finalData, writeSheet, writeTableForData);titleHead.clear();finalData.clear();}} catch (Exception e) {throw new RuntimeException("导出Excel 失败, 原因:" + e.getMessage());} finally {if (excelWriter != null) {excelWriter.finish();}}}*//*** 根据入参的类, 查找IService 中第一个泛型类型为入参类型的IService Bean* @param referenceType* @return*/private static <T> IService<T> getTargetIService(Class<T> referenceType) {Class targetClass = Arrays.stream(classesExtendsIService).filter(classExtendsIService -> ClassUtil.getTypeArgument(classExtendsIService).equals(referenceType)).findFirst().orElseThrow(() -> ExceptionUtil.wrapRuntime("没有对应的类!"));return (IService<T>) SpringUtil.getBean(targetClass);}}

public class CustomMergeStrategy implements RowWriteHandler {/*** 主键下标*/private Integer pkIndex;/*** 需要合并的列的下标集合*/private List<Integer> needMergeColumnIndex = new ArrayList<>();/*** DTO数据类型*/private Class<?> elementType;private Integer total;public CustomMergeStrategy(Class<?> elementType, Integer total) {this.elementType = elementType;this.total = total;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {// 如果是标题,则直接返回if (isHead) {return;}// 获取当前sheetSheet sheet = writeSheetHolder.getSheet();if (null == pkIndex) {this.lazyInit(writeSheetHolder);}// 判断是否需要和上一行进行合并// 不能和标题合并，只能数据行之间合并if (row.getRowNum() <= 1) {return;}// 获取上一行数据Row lastRow = sheet.getRow(row.getRowNum() - 1);// 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并if (!lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())|| row.getRowNum() == total) {int maxRow = sheet.getMergedRegions().stream().max(Comparator.comparingInt(CellRangeAddressBase::getLastRow)).orElse(new CellRangeAddress(0, 0, 0, 0)).getLastRow();needMergeColumnIndex.forEach(needMerIndex -> {int rowStart = maxRow + 1;int rowEnd = row.getRowNum() == total ? row.getRowNum() : row.getRowNum() - 1;if (rowEnd > rowStart) {sheet.addMergedRegionUnsafe(new CellRangeAddress(rowStart,rowEnd,needMerIndex,needMerIndex));}});}}/*** 初始化主键下标和需要合并字段的下标*/private void lazyInit(WriteSheetHolder writeSheetHolder) {// 获取当前sheetSheet sheet = writeSheetHolder.getSheet();// 获取标题行Row titleRow = sheet.getRow(0);// 获取DTO的类型Class<?> eleType = this.elementType;// 获取DTO所有的属性Field[] fields = eleType.getDeclaredFields();// 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数Arrays.stream(fields).forEach(field -> {// 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标ExcelProperty easyExcelAnno = field.getAnnotation(ExcelProperty.class);// 为空,则表示该字段不需要导入到excel,直接处理下一个字段if (null == easyExcelAnno) {return;}// 获取自定义的注解，用于合并单元格CustomMerge customMerge = field.getAnnotation(CustomMerge.class);// 没有@CustomMerge注解的默认不合并if (null == customMerge) {return;}for (int index = 0; index < fields.length; index++) {Cell theCell = titleRow.getCell(index);// 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPEif (null == theCell) {continue;}// 将字段和excel的表头匹配上if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {if (customMerge.isPk()) {pkIndex = index;}if (customMerge.needMerge()) {needMergeColumnIndex.add(index);}}}});// 没有指定主键，则异常if (null == this.pkIndex) {throw new IllegalStateException("使用@CustomMerge注解必须指定主键");}}}

@Data
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
public class RiskAnnualExcel implements Serializable {@ExcelProperty("风险编号")@CustomMerge(needMerge = true, isPk = true)@ColumnWidth(15)@NotNullprivate String number;@ExcelProperty(value = "风险类别", converter = DictConverter.class)@DictType("risk-type")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String type;@ExcelProperty("风险名称")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String name;@ExcelProperty("风险描述")@CustomMerge(needMerge = true)@ColumnWidth(30)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)@NotNullprivate String description;@ExcelProperty("可能造成的后果")@CustomMerge(needMerge = true)@ColumnWidth(15)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)@NotNullprivate String consequence;@ExcelProperty(value = "风险等级", converter = DictConverter.class)@DictType("risk-level-for-company")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String level;@ExcelProperty(value = "风险性质", converter = DictConverter.class)@DictType("risk-character")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String character;@ExcelProperty(value = "风险主要防控措施")@ColumnWidth(80)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)@NotNullprivate String precaution;@ExcelProperty(value = "措施等级", converter = DictConverter.class)@DictType("risk-precaution-level")@ColumnWidth(15)@NotNullprivate String precautionLevel;@ExcelProperty("风险认领领导")@ColumnWidth(20)private String leaders;@ExcelProperty("管控部门")@ColumnWidth(20)@NotNullprivate String deptsResponsible;@ExcelProperty("风险涉及单位")@ColumnWidth(20)private String deptsInvolved;@ExcelProperty("措施类型")@ColumnWidth(15)@NotNullprivate String typeMeasures;@ExcelProperty("措施类别")@ColumnWidth(15)@NotNullprivate String categoryMeasures;@ExcelProperty("预警条件")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String warningConditions;@ExcelProperty("风险点")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String riskPoints;@ExcelProperty("应急措施")@CustomMerge(needMerge = true)@ColumnWidth(15)@NotNullprivate String emergencyMeasure;@ExcelProperty("备注")@CustomMerge(needMerge = true)@ColumnWidth(15)private String remark;}

调用公共导出方法导出数据

ConditionDownloadUtil.
afterMultiQueryDownload(riskBankAnnual.getName(),excelList, RiskAnnualExcel.class, new CustomMergeStrategy(RiskAnnualExcel.class,excelList.size()), httpServletResponse);