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

news/2024/7/21 6:01:32 标签: excel, java

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

一、 代码实现

  1. controller下载入口
java">/**
     *下载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();
        }
    }
  1. 实体对象
java">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;

}
  1. 下拉框拦截器
java">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;
    }

    @Override
    public 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;
    }
}
  1. 批注拦截器
java">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;
    }


    @Override
    public 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);

        }
    }
}

二、实现效果

  1. 批注效果
    在这里插入图片描述
  2. 下拉框效果
    在这里插入图片描述

三、参考文档

easyExcel自定义拦截器


http://www.niftyadmin.cn/n/5367514.html

相关文章

HTTP 协议在互联网中的作用是什么？

HTTP 协议在互联网中的作用是什么？

首先&#xff0c;HTTP 协议是互联网上应用最为广泛的一种网络协议。它被设计为无状态的&#xff0c;意味着服务器不会为每个请求保持状态。HTTP 协议的请求方法有 GET、POST、PUT、DELETE 等&#xff0c;分别对应着不同的操作。例如&#xff0c;GET 方法用于请求数据&#xff0…
阅读更多...
CentOS设置定时任务

CentOS设置定时任务

CentOS设置定时任务 安装定时任务&#xff1a;cron yum install -y cronie启动 systemctl start crond.service设置开机自启动 systemctl enable crond.service检查定时任务状态 systemctl status crond.service编辑定时任务 crontab -e定时任务配置。例如&#xff1a;在…
阅读更多...
android开发中遇到的问题和注意点

android开发中遇到的问题和注意点

&#xff08;1&#xff09;放在drawble下的图片资源名称不能大写 &#xff08;2&#xff09;ScrollView只能容纳一个子视图 &#xff08;3&#xff09;报错&#xff1a;Algorithm HmacPBESHA256 not available。 解决&#xff1a;设置 > 构建、执行、部署 > 构建工具 …
阅读更多...
FRP内网穿透如何避免SSH暴力破解（二）——指定地区允许访问

FRP内网穿透如何避免SSH暴力破解（二）——指定地区允许访问

背景 上篇文章说到&#xff0c;出现了试图反复通过FRP的隧道&#xff0c;建立外网端口到内网服务器TCP链路的机器人&#xff0c;同时试图暴力破解ssh。这些连接造成了流量的浪费和不必要的通信开销。考虑到服务器使用者主要分布在A、B、C地区和国家&#xff0c;我打算对上一篇…
阅读更多...
leetcode680 验证回文串 II

leetcode680 验证回文串 II

文章目录 1. 解法2. 原题 [680. 验证回文串 II](https://leetcode.cn/problems/valid-palindrome-ii/) 1. 解法 利用双指针&#xff0c;分别指向s的头尾&#xff0c;如果字母相同&#xff0c;left,right–&#xff0c;当走到某处发现left ! right&#xff0c;需要删除一个字符…
阅读更多...
101 C++内存高级话题 内存池概念，代码实现和详细分析

101 C++内存高级话题 内存池概念，代码实现和详细分析

零 为什么要用内存池&#xff1f; 从前面的知识我们知道&#xff0c;当new 或者 malloc 的时候&#xff0c;假设您想要malloc 10个字节&#xff0c; char * pchar new char[10]; char *pchar1 malloc(10); 实际上编译器为了 记录和管理这些数据&#xff0c;做了不少事情&…
阅读更多...
VUE 全局设置防重复点击

VUE 全局设置防重复点击

请求后端防止重复点击&#xff0c;用户点击加入遮罩层&#xff0c;请求完毕关闭遮罩层 我们利用请求拦截器&#xff0c;在用户点击的时候&#xff0c;弹出遮罩层 本文采用i18n国际化 element plus UI&#xff0c;提取你想要的&#xff0c;这里不做简化 完整代码如下&#xf…
阅读更多...
Unix五种I/O模型（阻塞、非阻塞、多路复用、信号驱动、异步）

Unix五种I/O模型（阻塞、非阻塞、多路复用、信号驱动、异步）

文章目录 概要一、I/O基础二、阻塞式I/O三、非阻塞式I/O三、I/O多路复用四、信号驱动I/O五、异步I/O六、小结 概要 在工作中&#xff0c;经常使用Nginx、Redis等开源组件&#xff0c;常提到其高性能的原因是网络I/O的实现是基于epoll&#xff08;多路复用&#xff09;。这次呢…
阅读更多...
最新文章

Copyright @ 2022~2023