EasyExcel实现动态生成包含下拉框、级联下拉框的excel表格

前段时间,因为项目需要,要将excel模板下载功能从附件下载迭代为后台动态生成;但是这个表格里面包含了下拉框,耗时两天才算是把这个excel表格搞完,现在记录一下,方便后续回顾。

首先,要实现的效果是这样:

根据D列的检查类型,E列弹出不同的下拉选项,F列弹出不同的问题属性;

 Service层导出excel代码:

@Transactional(rollbackFor = Exception.class)
    public void download(HttpServletResponse response) {
        //导出文件格式
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        //设置导出文件名
        String filename = "";

        try {
            filename = URLEncoder.encode("Excel名称" + DateUtil.format(new Date(), "yyyy-MM-dd"), "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + "." + ExcelFormat.XLS.getValue());
        //模板实体类
        List<SecondInspProbExcelDTO> secondInspProbExcelDTOS = new ArrayList<>();
       //普通下拉数据
        Map<Integer, String[]> map = ordinarySelect();
        //检查类型-子类
        Map<String, List<String>> inspNameMap = inspNameSelect();
        //检查类型-父类
        String[] proInspNameMap = proInspNameSelect();
        //问题属性
        Map<String, List<String>> probPropsNameMap = probPropsNameSelect();
        try(OutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream, SecondInspProbExcelDTO.class)
                    .registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap))
                    .excelType(ExcelTypeEnum.XLS)
                    .sheet("sheet1")
                    .doWrite(secondInspProbExcelDTOS);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

其中,这几个方法是为了创建下拉数据

//普通下拉数据
        Map<Integer, String[]> map = ordinarySelect();
        //检查类型-子类
        Map<String, List<String>> inspNameMap = inspNameSelect();
        //检查类型-父类
        String[] proInspNameMap = proInspNameSelect();
        //问题属性
        Map<String, List<String>> probPropsNameMap = probPropsNameSelect();

普通下拉数据,就是如同性别、是否之类的数据;Map中的key就是要关联的列,注意:excel列从0开始,value就是下拉数据的数组

/**
     * 普通下拉数据
     * @return
     */
    private Map<Integer, String[]> ordinarySelect() {
        Map<Integer, String[]> map = new HashMap<>();

        //是否现场已完成整改 是否直接作业类问题
        String[] isZgArray = {"是", "否"};
        //二级单位检查类型
        String[] inspPlanTypeArray = {"无计划", "有计划", "计划外新增"};
        map.put(2, isZgArray);
        map.put(6, isZgArray);
        map.put(12, inspPlanTypeArray);
return map;
    }

级联的数据,父类:

/**
     * 检查类型-父类
     * @return
     */
    private String[] proInspNameSelect() {
        String[] proInspNameList = new String[10];
        proInspNameList[2] = "专项检查";

        return proInspNameList;
    }

子类:

/**
     * 检查类型-子类
     * @return
     */
    private Map<String, List<String>> inspNameSelect() {
        Map<String, List<String>> inspNameArray = new HashMap<>();
        //检查类型-专项检查(子类)
        List<String> zxjcInspNameList = new ArrayList<>();
        zxjcInspNameList.add("季节性");
        zxjcInspNameList.add("节前");
        zxjcInspNameList.add("专业性重大危险源");
        inspNameArray.put("专项检查", zxjcInspNameList);

        return inspNameArray;
    }

其中,子类返回的map中,key就是父类返回数组的值,value就是子类列的下拉列表

接下来就是核心的拦截器,需实现

SheetWriteHandler接口

直接上代码,代码中有注释

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

public class CustomCellWriteHandler implements SheetWriteHandler {

    /**
     * 普通下拉数据
     */
    private Map<Integer, String[]> map;

    /**
     * 检查类型-父类
     */
    private String[] proInspNameArray;

    /**
     * 检查类型-子类
     */
    private Map<String, List<String>> inspNameArray;

    /**
     * 问题属性
     */
    private Map<String, List<String>> probPropsNameArray;

    public CustomCellWriteHandler(Map<Integer, String[]> map, String[] proInspNameArray, Map<String, List<String>> inspNameArray, Map<String, List<String>> probPropsNameArray) {
        this.map = map;
        this.proInspNameArray = proInspNameArray;
        this.inspNameArray = inspNameArray;
        this.probPropsNameArray = probPropsNameArray;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取第一个sheet页
        Sheet sheet = writeSheetHolder.getCachedSheet();
        // 获取sheet页的数据校验对象
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 获取工作簿对象,用于创建存放下拉数据的字典sheet数据页
        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // 普通数据,迭代索引,用于存放下拉数据的字典sheet数据页命名
        int index = 1;
        for (Map.Entry<Integer, String[]> entry : map.entrySet()) {

            // 设置存放下拉数据的字典sheet,并把这些sheet隐藏掉,这样用户交互更友好
            String dictSheetName = "dict_hide_sheet" + index;
            Sheet dictSheet = workbook.createSheet(dictSheetName);
            // 隐藏字典sheet页
            workbook.setSheetHidden(index++, true);

            // 设置下拉列表覆盖的行数,从第一行开始到最后一行,这里注意,Excel行的
            // 索引是从0开始的,我这边第0行是标题行,第1行开始时数据化,可根据实
            // 际业务设置真正的数据开始行,如果要设置到最后一行,那么一定注意,
            // 最后一行的行索引是1048575,千万别写成1048576,不然会导致下拉列表
            // 失效,出不来
            CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().length;
            for (int i = 0; i < rowLen; i++) {
                // 向字典sheet写数据,从第一行开始写,此处可根据自己业务需要,自定
                // 义从第几行还是写,写的时候注意一下行索引是从0开始的即可
                dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue()[i]);
            }

            // 设置关联数据公式,这个格式跟Excel设置有效性数据的表达式是一样的
            String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().length;
            Name name = workbook.createName();
            name.setNameName(dictSheetName);
            // 将关联公式和sheet页做关联
            name.setRefersToFormula(refers);

            // 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
            DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
            setValidation(sheet, helper, constraint, infoList);
        }


        //检查类型-父类
        Sheet hideSheet = workbook.createSheet("site");
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), false);
        // 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。
        int rowId = 0;
        Row proviRow = hideSheet.createRow(rowId++);
        proviRow.createCell(0).setCellValue("大类列表");
        for (int i = 0; i < proInspNameArray.length; i++) {
            Cell proviCell = proviRow.createCell(i + 1);
            proviCell.setCellValue(proInspNameArray[i]);
        }
        //检查类型-子类
        Iterator<String> keyIterator = inspNameArray.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = inspNameArray.get(key);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = workbook.createName();
            name.setNameName(key);
            String formula = "site!" + range;
            name.setRefersToFormula(formula);
        }

        //问题属性
        Iterator<String> probPropsNameIterator = probPropsNameArray.keySet().iterator();
        while (probPropsNameIterator.hasNext()) {
            String key = probPropsNameIterator.next();
            List<String> son = probPropsNameArray.get(key);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = workbook.createName();
            name.setNameName(key);
            String formula = "site!" + range;
            name.setRefersToFormula(formula);
        }

        ///开始设置(大类小类)下拉框
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        // 大类规则
        DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(proInspNameArray);
        CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 1048575, 3, 3);
        setValidation(sheet, dvHelper, expConstraint, expRangeAddressList);

        //检查类型-子类
        // 小类规则(各单元格按个设置)
        // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据,如果A2是浙江省,那么此处就是浙江省下面的市
        // 为了让每个单元格的公式能动态适应,使用循环挨个给公式。
        // 循环几次,就有几个单元格生效,次数要和上面的大类影响行数一一对应,要不然最后几个没对上的单元格实现不了级联
        for (int i = 2; i < inspNameArray.size() + 10; i++) {
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 4, 4);
            DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($D$" + i + ")");
            setValidation(sheet, dvHelper, formula, rangeAddressList);
        }

        //问题属性
        for (int i = 2; i < probPropsNameArray.size() + 10; i++) {
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 5, 5);
            DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($E$" + i + ")");
            setValidation(sheet, dvHelper, formula, rangeAddressList);
        }
    }

    /**
     * 设置验证规则
     * @param sheet			sheet对象
     * @param helper		验证助手
     * @param constraint	createExplicitListConstraint
     * @param addressList	验证位置对象
     */
    private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.createErrorBox("错误提示", "您输入的内容,不符合限制条件");
        sheet.addValidationData(dataValidation);
    }

    /**
     * @param offset   偏移量,如果给0,表示从A列开始,1,就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     * @author denggonghai 2016年8月31日 下午5:17:49
     */
    public String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间,包括边界(仅两次字母表计算)
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
}

这个拦截器是在service层使用的,就是最上面的代码

try(OutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream, SecondInspProbExcelDTO.class)
                    .registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap))
                    .excelType(ExcelTypeEnum.XLS)
                    .sheet("sheet1")
                    .doWrite(secondInspProbExcelDTOS);

导出excel表头背景色,文字颜色大小

@Data
//设置表头背景色
@HeadStyle(fillForegroundColor = 49)
//设置表头行高,默认单位磅
@HeadRowHeight(value = 34)
//设置表头问题颜色,大小,黑色8,白色7;
@HeadFontStyle(color = 8, fontHeightInPoints = 12)
@ExcelIgnoreUnannotated
public class SecondInspProbExcelDTO {

颜色对照表

  • 3
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值