EasyExcel下载带下拉框和批注模板

一、代码实现

controller下载入口

/***下载excel模板* @author youlu* @date 2023/8/14 17:31* @param response* @param request* @return void*/@PostMapping("/downloadTemplate")public void downloadExcel(HttpServletResponse response, HttpServletRequest request) throws IOException {//查询字典数据，用于模板下拉框和批注说明使用Map<String, List<SysDictData>> dictDataMap = dictDataService.selectDictDataMapByDictTypeAndStatus("worksheet", "0");//获取供应商类型，不同供应商类型展示的下拉框和批注会有不一样Boolean supplier = getSupplierBoolean();ParamThreadLocal.setParam(supplier);try {long currentTimeMillis = System.currentTimeMillis();String name = "工单模板_" + currentTimeMillis;response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode(name, "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), TWorkSheetReadVO.class).inMemory(true).registerWriteHandler(new CommentWriteHandler(dictDataMap)) //加下拉框的拦截器.registerWriteHandler(new CustomSheetWriteHandler(dictDataMap)) //加批注的拦截器.build();WriteSheet writeSheet = EasyExcel.writerSheet("工单模板").build();excelWriter.write(Lists.newArrayList(), writeSheet);excelWriter.finish();} finally {ParamThreadLocal.clearParam();}}

实体对象

package com.smy.ows.project.worksheet.domain.vo;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.smy.framework.base.DesensitizationAnnotation;
import com.smy.ows.project.worksheet.enums.SheetLevelEnums;
import com.smy.ows.project.worksheet.enums.WorkSheetStatus;
import com.smy.ows.util.*;
import lombok.Data;import java.io.Serializable;
import java.util.Date;/*** 客诉工单对象 t_work_sheet** @author youlu* @date 2023-01-11*/
@Data
public class TWorkSheetReadVO implements Serializable {private static final long serialVersionUID = 5924360788178861972L;/*** 客诉标题*/@ExcelProperty(value = "客诉标题", index = 0)@ColumnWidth(20)private String complaintHeadline;/*** @see SheetLevelEnums*/@ExcelProperty(value = "优先级", index = 1, converter = PriorityIntegerStringConverter.class)@ColumnWidth(10)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer priority;@ExcelProperty(value = "客户姓名", index = 2)@ColumnWidth(20)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String custName;/*** 客户号*/@ExcelProperty(value = "客户号", index = 3)@ColumnWidth(20)private String custNo;@DesensitizationAnnotation@ExcelProperty(value = "客户手机号", index = 4)@ColumnWidth(20)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String custMobile;@DesensitizationAnnotation@ExcelProperty(value = "客户身份证", index = 5)@ColumnWidth(30)private String custIdNo;/*** 投诉时间*/@ExcelProperty(value = "投诉时间(yyyy-MM-dd HH:mm:ss)", index = 6, converter = DateStringConverter.class)@ColumnWidth(40)@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")private Date complaintTime;//反馈渠道@ExcelProperty(value = "反馈渠道", index = 7, converter = ChannelStringStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String feedbackChannel;@ExcelProperty(value = "工单类型", index = 8, converter = TypeIntegerStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer type;@ExcelProperty(value = "业务类型", index = 9, converter = BizTypeIntegerStringConverter.class)@ColumnWidth(15)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private Integer bizType;@DesensitizationAnnotation@ExcelProperty(value = "客户联系方式", index = 10)@ColumnWidth(15)private String custContactMobile;/*** 所属资方*/@ExcelProperty(value = "所属资方", index = 11)@ColumnWidth(15)private String capital;@ExcelProperty(value = "投诉内容", index = 12)@ColumnWidth(30)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)private String content;/*** @see WorkSheetStatus*/@ExcelProperty(value = "工单状态", index = 13, converter = StatusIntegerStringConverter.class)@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)@ColumnWidth(15)private Integer status;@ExcelProperty(value = "处理结果", index = 14, converter = ResultIntegerStringConverter.class)@ColumnWidth(15)private Integer result;/*** 处理情况*/@ExcelProperty(value = "处理情况", index = 15)@ColumnWidth(15)private String handingInfo;}

下拉框拦截器

package com.smy.ows.util;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.smy.ows.common.core.domain.entity.SysDictData;
import com.smy.ows.common.utils.ParamThreadLocal;
import com.smy.ows.project.worksheet.constant.WorksheetDictTypeConstant;
import com.smy.ows.project.worksheet.enums.WorkSheetStatus;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** 自定义拦截器.** @author youlu*/
public class CustomSheetWriteHandler implements SheetWriteHandler {private  Map<String, List<SysDictData>> notationMap;public CustomSheetWriteHandler(Map<String, List<SysDictData>> notationMap) {this.notationMap = notationMap;}@Overridepublic void afterSheetCreate(SheetWriteHandlerContext context) {DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();Map<Integer, String[]> mapDropDown = this.getIntegerMap();for (Integer integer : mapDropDown.keySet()) {//起始行，结束行，元素位置（ExcelProperty中的value值）CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, integer, integer);String[] strings = mapDropDown.get(integer);DataValidationConstraint constraint = helper.createExplicitListConstraint(strings);DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);}}private Map<Integer, String[]> getIntegerMap() {//map中key对应，ExcelProperty中的value值。map中value对应下拉框的值Map<Integer, String[]> mapDropDown = new HashMap<>();for (String key : notationMap.keySet()) {String[] strings = notationMap.get(key).stream().map(k -> k.getDictLabel()).toArray(String[]::new);if (WorksheetDictTypeConstant.WORKSHEET_RESULT.equals(key)) {mapDropDown.put(14, strings);} else if (WorksheetDictTypeConstant.WORKSHEET_TYPE.equals(key)) {mapDropDown.put(8, strings);} else if (WorksheetDictTypeConstant.WORKSHEET_BIZ_TYPE.equals(key)) {mapDropDown.put(9, strings);} else if (WorksheetDictTypeConstant.WORKSHEET_PRIORITY.equals(key)) {mapDropDown.put(1, strings);} else if (WorksheetDictTypeConstant.WORKSHEET_FEEDBACK_CHANNEL.equals(key)) {mapDropDown.put(7, strings);}}Boolean supplier = (Boolean) ParamThreadLocal.getParam();if (supplier) {//供应商 和 资方的，工单状态只能选择【待分配】mapDropDown.put(13, new String[]{WorkSheetStatus.PENDING.getDesc()});//其他的工单状态只能选择【待分配】和 【已处理】} else {mapDropDown.put(13, new String[]{WorkSheetStatus.PENDING.getDesc(), WorkSheetStatus.FINISHED.getDesc()});}return mapDropDown;}
}

批注拦截器

package com.smy.ows.util;import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.google.common.collect.Lists;
import com.smy.ows.common.core.domain.entity.SysDictData;
import com.smy.ows.project.worksheet.constant.WorksheetDictTypeConstant;
import com.smy.ows.project.worksheet.enums.WorkSheetStatus;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;/*** 自定义拦截器.新增注释,第一行头加批注** @author Jiaju Zhuang*/
public class CommentWriteHandler implements RowWriteHandler {private final Map<String, List<SysDictData>> notationMap;public CommentWriteHandler(Map<String, List<SysDictData>> notationMap) {this.notationMap = notationMap;}@Overridepublic void afterRowDispose(RowWriteHandlerContext context) {if (BooleanUtils.isTrue(context.getHead())) {Sheet sheet = context.getWriteSheetHolder().getSheet();Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();// 在第一行 第二列创建一个批注String priorityDesc = Optional.ofNullable(notationMap.get(WorksheetDictTypeConstant.WORKSHEET_PRIORITY)).orElse(Lists.newArrayList()).stream().map(k -> k.getDictValue() + ":" + k.getDictLabel()).collect(Collectors.joining("\r\n"));//对应要加批注的元素的ExcelProperty中的value值       Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));comment.setString(new XSSFRichTextString(priorityDesc));// 将批注添加到单元格对象中sheet.getRow(0).getCell(1).setCellComment(comment);//对应要加批注的元素的ExcelProperty中的value值Comment comment6 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)6, 0, (short)2, 1));comment6.setString(new XSSFRichTextString("yyyy-MM-dd HH:mm:ss"));sheet.getRow(0).getCell(6).setCellComment(comment6);String channelDesc = Optional.ofNullable(notationMap.get(WorksheetDictTypeConstant.WORKSHEET_FEEDBACK_CHANNEL)).orElse(Lists.newArrayList()).stream().map(k -> k.getDictValue() + ":" + k.getDictLabel()).collect(Collectors.joining("\r\n"));Comment comment7 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 7, 0, (short) 2, 1));comment7.setString(new XSSFRichTextString(channelDesc));sheet.getRow(0).getCell(7).setCellComment(comment7);String typeDesc = Optional.ofNullable(notationMap.get(WorksheetDictTypeConstant.WORKSHEET_TYPE)).orElse(Lists.newArrayList()).stream().map(k -> k.getDictValue() + ":" + k.getDictLabel()).collect(Collectors.joining("\r\n"));Comment comment8 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 8, 0, (short) 2, 1));comment8.setString(new XSSFRichTextString(typeDesc));sheet.getRow(0).getCell(8).setCellComment(comment8);String bizDesc = Optional.ofNullable(notationMap.get(WorksheetDictTypeConstant.WORKSHEET_BIZ_TYPE)).orElse(Lists.newArrayList()).stream().map(k -> k.getDictValue() + ":" + k.getDictLabel()).collect(Collectors.joining("\r\n"));Comment comment9 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 9, 0, (short) 2, 1));comment9.setString(new XSSFRichTextString(bizDesc));sheet.getRow(0).getCell(9).setCellComment(comment9);String statusDesc = Arrays.stream(WorkSheetStatus.values()).map(k -> k.getCode() + ":" + k.getDesc()).collect(Collectors.joining("\r\n"));Comment comment13 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 13, 0, (short) 2, 1));comment13.setString(new XSSFRichTextString(statusDesc));sheet.getRow(0).getCell(13).setCellComment(comment13);String resultDesc = Optional.ofNullable(notationMap.get(WorksheetDictTypeConstant.WORKSHEET_RESULT)).orElse(Lists.newArrayList()).stream().map(k -> k.getDictValue() + ":" + k.getDictLabel()).collect(Collectors.joining("\r\n"));Comment comment14 = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 14, 0, (short) 2, 1));comment14.setString(new XSSFRichTextString(resultDesc));sheet.getRow(0).getCell(14).setCellComment(comment14);}}
}