EasyExcel 导出带下拉选项数据的excel

最新推荐文章于 2025-02-20 20:55:33 发布

fmi110

最新推荐文章于 2025-02-20 20:55:33 发布

阅读量812

点赞数 4

文章标签： excel

本文链接：https://blog.youkuaiyun.com/fmi110/article/details/140787221

版权

1 自定义SheetWriterHandler

package com.hisql.excel;

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

import java.util.List;

/**
 * EasyExcel 导出时,设置某一列数据为下拉选项
 * EasyExcel.write(response.getOutputStream(), Register.class).registerWriteHandler(new CustomSheetWriteHandler(selectDataList)).sheet("模板").doWrite(registerList);
 * <p>
 * 原文链接：https://blog.youkuaiyun.com/sumMStar/article/details/136309305
 */
public class CellSelectorSheetWriteHandler implements SheetWriteHandler {

    List<CellSelector> list;

    public CellSelectorSheetWriteHandler(List<CellSelector> list) {
        this.list = list;
    }

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

    /**
     * 想实现Excel引用其他sheet页数据作为单元格下拉选项值，
     * 需要重写该方法
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 构造样例数据，该数据可根据实际需要，换成业务数据
        // 实际数据可通过构造方法，get、set方法等由外界传入
//        for (int i = 0; i < 100; i++) {
//            selectDataList.add("下拉选项" + i);
//        }

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

        // 迭代索引，用于存放下拉数据的字典sheet数据页命名
        for (int idx = 0; idx < list.size(); idx++) {
            CellSelector cellSelector = list.get(idx);

            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            Integer      columnIndex    = cellSelector.getColumnIndex();
            List<String> selectDataList = cellSelector.getSelectDataList();
            String       dictSheetName  = "dict_hide_sheet" + columnIndex;
            Sheet        dictSheet      = workbook.createSheet(dictSheetName);
            // 隐藏字典sheet页
            workbook.setSheetHidden(idx + 1, true);


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

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

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

    /**
     * 构造下拉选项单元格列的位置，以及下拉选项可选参数值的map集合
     * columnIndex：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
     * selectDataList：columnIndex对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100
     */
    @Data
    public static class CellSelector {
        /**
         * 下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
         */
        private Integer columnIndex;

        /**
         * 下拉选项的数据
         */
        private List<String> selectDataList;

        public CellSelector(Integer columnIndex, List<String> selectDataList) {
            this.columnIndex = columnIndex;
            this.selectDataList = selectDataList;
        }
    }
}

2 EasyExcel中使用

 @GetMapping("downloadTemplate")
    @Operation(summary = "下载导入模版")
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        BizProductExcelVO vo = new BizProductExcelVO();
        vo.setId(1l);
        vo.setCategory("兽药类");
        vo.setDosageType("粉针剂");
        vo.setProductName("莫可欣");
        vo.setProductCommonName("注射用阿莫西林钠 4ｇ");
        vo.setSpec("4ｇ/瓶×10 瓶/盒×24 盒/箱");
        vo.setMainContent("阿莫西林钠（4ｇ）");
        vo.setUnitName("瓶");
        vo.setUnitPrice(new BigDecimal("3.47"));
        vo.setVipPrice(new BigDecimal("2.47"));

        List<BizProductExcelVO> dataList = Arrays.asList(vo);

        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = "export_data.xlsx";
        try {
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        List<Map>                                             existsDosages    = dictDataService.selectListByDictCode("dosageType");
        Set<String>                                           existsDosageName = existsDosages.stream().map(m -> m.get("name") + "").collect(Collectors.toSet());
        Set<String>                                           catesExists      = categoryService.list().stream().map(BizProductCategoryEntity::getName).collect(Collectors.toSet());
        ArrayList<CellSelectorSheetWriteHandler.CellSelector> cellSelectors    = new ArrayList<>();
        CellSelectorSheetWriteHandler.CellSelector            cellSelector     = new CellSelectorSheetWriteHandler.CellSelector(1, new ArrayList<>(catesExists));
        CellSelectorSheetWriteHandler.CellSelector            cellSelector2    = new CellSelectorSheetWriteHandler.CellSelector(2, new ArrayList<>(existsDosageName));
        cellSelectors.add(cellSelector);
        cellSelectors.add(cellSelector2);
        // 创建ExcelWriter对象
        EasyExcel.write(response.getOutputStream(), BizProductExcelVO.class)
                // 自动列表
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                // 下拉选项处理
                .registerWriteHandler(new CellSelectorSheetWriteHandler(cellSelectors))
                .sheet("Sheet1")
                .doWrite(dataList);
    }