spring boot 根据easyexcel导出excel 设置下拉框（字典值）

handler

kotlin版

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.util.CellRangeAddressList

/**
 * CustomSheetWriteHandler
 * @Auther: mtx
 * @Date: 2024/04/08 14:45
 */
class CustomSheetWriteHandler : SheetWriteHandler {

    /**
     * key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
     * value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100
     */
    lateinit var selectParamMap: Map<Int, List<String>>

    companion object {
        fun build(selectParamMap: Map<Int, List<String>>): CustomSheetWriteHandler {
            return CustomSheetWriteHandler().apply {
                this.selectParamMap = selectParamMap
            }
        }
    }

    override fun beforeSheetCreate(writeWorkbookHolder: WriteWorkbookHolder, writeSheetHolder: WriteSheetHolder) {
    }

    /**
     * 想实现Excel引用其他sheet页数据作为单元格下拉选项值
     */
    override fun afterSheetCreate(writeWorkbookHolder: WriteWorkbookHolder, writeSheetHolder: WriteSheetHolder) {
        // 获取第一个sheet页
        val sheet = writeSheetHolder.cachedSheet
        // 获取sheet页的数据校验对象
        val helper = sheet.dataValidationHelper
        // 获取工作簿对象，用于创建存放下拉数据的字典sheet数据页
        val workbook = writeWorkbookHolder.workbook
        // 迭代索引，用于存放下拉数据的字典sheet数据页命名
        var index = 1

        selectParamMap.forEach {
            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            val dictSheetName = "option_$index"
            val dictSheet = workbook.createSheet(dictSheetName)
            // 隐藏字典sheet页
            workbook.setSheetHidden(index++, true)
            // 设置下拉列表覆盖的行数，从第一行开始到最后一行，这里注意，Excel行的
            // 索引是从0开始的，我这边第0行是标题行，第1行开始时数据化，可根据实际业务设置真正的数据开始行，
            // 如果要设置到最后一行，那么一定注意，最后一行的行索引是1048575，千万别写成1048576，不然会导致下拉列表失效，出不来
            val infoList = CellRangeAddressList(1, 1048575, it.key, it.key)
            val rowLen = it.value.size

            it.value.forEachIndexed { index, s ->
                // 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定
                // 义从第几行还是写，写的时候注意一下行索引是从0开始的即可
                dictSheet.createRow(index).createCell(0).setCellValue(s)
            }
            val name = workbook.createName()
            // 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的
            name.nameName = dictSheetName
            // 将关联公式和sheet页做关联
            name.refersToFormula = "$dictSheetName!\$A$1:\$A\$$rowLen"

            // 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
            val constraint = helper.createFormulaListConstraint(dictSheetName)
            val dataValidation = helper.createValidation(constraint, infoList)
            dataValidation.showErrorBox = true
            dataValidation.createErrorBox("错误提示","您输入的内容不符合限制条件。")
            sheet.addValidationData(dataValidation)
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

java版

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.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

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

/**
 * CustomSheetWriteHandler
 *
 * @author 马天祥
 * @Date: 2024/04/10 14:01
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {

    /**
     * key：下拉选项要放到哪个单元格，比如A列的单元格那就是0，C列的单元格，那就是2
     * value：key对应的那个单元格下拉列表里的数据项，比如这里就是下拉选项1..100
     */
    private final Map<Integer, List<String>> selectParamMap;
    public CustomSheetWriteHandler(Map<Integer, List<String>> selectParamMap) {
        this.selectParamMap = selectParamMap;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    /**
     * 想实现Excel引用其他sheet页数据作为单元格下拉选项值
     */
    @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, List<String>> entry : selectParamMap.entrySet()) {

            // 设置存放下拉数据的字典sheet，并把这些sheet隐藏掉，这样用户交互更友好
            String dictSheetName = "option_" + 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().size();
            for (int i = 0; i < rowLen; i++) {
                // 向字典sheet写数据，从第一行开始写，此处可根据自己业务需要，自定
                // 义从第几行还是写，写的时候注意一下行索引是从0开始的即可
                dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));
            }

            // 设置关联数据公式，这个格式跟Excel设置有效性数据的表达式是一样的
            String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().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);
            dataValidation.setShowErrorBox(true);
            dataValidation.createErrorBox("错误提示","您输入的内容不符合限制条件。");
            sheet.addValidationData(dataValidation);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

引用

kotlin

@PostMapping(value = ["/download-template"])
    @ApiOperation(value = "下载模板", notes = "下载模板")
    fun downloadTemplate(response: HttpServletResponse) {
        response.contentType = "application/vnd.ms-excel"
        response.characterEncoding = "utf-8"
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        val fileName = URLEncoder.encode("导入模板" + DateUtil.time(), "UTF-8")
        response.setHeader("Content-disposition", "attachment;filename=$fileName.xlsx")

        val map = mutableMapOf<Int, List<String>>()
        // 承包商名称 第一列A列
        map[0] = listOf("选项1","选项2")
        EasyExcel.write(response.outputStream, Entry::class.java)
            .registerWriteHandler(CustomSheetWriteHandler.build(map))
            .sheet("模板").doWrite(listOf<String>())
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

java

@PostMapping(value = "/download-template")
    @ApiOperation(value = "下载模板", notes = "下载模板")
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        URLEncoder.encode("导入模板" + DateUtil.time(), StandardCharsets.UTF_8);
        response.setHeader("Content-disposition", "attachment;filename=$fileName.xlsx");

        Map<Integer, List<String>> map = new HashMap<>();
        // 承包商名称 第一列A列
        map.put(0, Arrays.asList("选项1","选项2"));
        EasyExcel.write(response.getOutputStream())
            .registerWriteHandler(new CustomSheetWriteHandler(map))
                .sheet("模板").doWrite(new ArrayList<>());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16