EaxyExcel设置下拉框并解决excel下拉框255限制

最新推荐文章于 2024-07-15 10:37:03 发布

龙叔的技术笔记

最新推荐文章于 2024-07-15 10:37:03 发布

阅读量1.2w

点赞数 14

分类专栏： EasyExcel 文章标签： java

本文链接：https://blog.csdn.net/qq_42747210/article/details/113063645

版权

EasyExcel 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

前言

最近有一个excel导出需求，要在单元格提供下拉框。
其中一个下拉框有300多个选项，使用EasyExcel导出，发现excel无法正常打开。网上一搜，发现excel本身每个单元格限制了255个字符，下拉框总字符超过255就无法正常打开。

在EasyExcel的issue有相关讨论
下拉框数据量超过88条记录，文件无法打开

里面提到一个思路，大致是创建另一个sheet存放下拉选项，本sheet的单元格通过公式链接到那个sheet的内容。
主要是在SheetWriteHandler的实现类的afterSheetCreate()，用这个指定

int TotalRowCount=88; // sheet2里面下拉框对应的一列需要放多少行数据
String enumSheetName = "sheet2"; // 下拉框数据所在的sheet页名称
String[] operationCity; // 下拉框的数据
CellRangeAddressList cellRangeAddressList5 = new CellRangeAddressList(1, TotalRowCount, 5, 5);
String cityEnum = "=" + enumSheetName + "!$A$2:$A$" + (operationCity.length + 1);
DataValidationConstraint constraint2 = helper.createFormulaListConstraint(cityEnum);
DataValidation dataValidation2 = helper.createValidation(constraint2, cellRangeAddressList5);
writeSheetHolder.getSheet().addValidationData(dataValidation2);

其实讲得还不够详细，下面直接上我的代码

先展示最终效果

在这里插入图片描述

实现

先引入EasyExcel和Guava工具类，还有lombok

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

		<dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1-jre</version>
        </dependency>

		<!-- springboot test启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

实现接口

要实现SheetWriteHandler接口，在afterSheetCreate方法设置下拉框

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.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * 无需模板，导出时设置下拉框
 * 
 * 尝试创建sheet，突破下拉框255字段的限制
 *
 * @date 01/22/2021 04:09
 */
@Slf4j
public class MyDropSheetWriteHandlerForProvice implements SheetWriteHandler {

    /**
     * 创建sheet页前的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        log.info("创建sheet之前");
    }

    /**
     * 创建sheet页后的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

        // excel管方提供的下拉框实现，适用于小数据量
        // 省市下拉框
//        CellRangeAddressList addressList = new CellRangeAddressList(4, 10, 1, 1);
//        DataValidationConstraint constraint1 = helper.createExplicitListConstraint(new String[]{"北京市", "上海市", "深圳市"});
//        DataValidation validation = helper.createValidation(constraint1, addressList);
//        writeSheetHolder.getSheet().addValidationData(validation);

        // 省市下拉框
        String[] strings = {"北京市", "上海市", "深圳市"};
        // 创建sheet，突破下拉框255的限制
        //获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        //定义sheet的名称
        String sheetName = "省市区列表";
        //1.创建一个隐藏的sheet 名称为 proviceSheet
        Sheet proviceSheet = workbook.createSheet(sheetName);
        // 设置隐藏
//        workbook.setSheetHidden(1,true);
        //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
        for (int i = 0, length = strings.length; i < length; i++) {
            // i:表示你开始的行数  0表示你开始的列数
            proviceSheet.createRow(i).createCell(0).setCellValue(strings[i]);
        }
        Name category1Name = workbook.createName();
        category1Name.setNameName(sheetName);
        //4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
        category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (strings.length));
        //5 将刚才设置的sheet引用到你的下拉列表中
        CellRangeAddressList addressList = new CellRangeAddressList(3, 20, 1, 1);
        DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
        DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
        writeSheetHolder.getSheet().addValidationData(dataValidation3);

    }
}

单元测试

准备一些数据看看效果

public class ProviceDemo {

    @Test
    public void test() throws IOException {
        // 文件输出位置
        OutputStream out = new FileOutputStream("/Users/quanlinglong/Downloads/mergeDemo/demo" + System.currentTimeMillis() + ".xlsx");
        ExcelWriter writer = EasyExcelFactory
                .write(out)
                .registerWriteHandler(new MyDropSheetWriteHandlerForProvice())
                .build();

        // 动态添加表头，适用一些表头动态变化的场景
        WriteSheet sheet1 = new WriteSheet();
        sheet1.setSheetName("客户明细");
        sheet1.setSheetNo(0);
        // 写多个sheet

        // 创建一个表格，用于 Sheet 中使用
        WriteTable table = new WriteTable();
        table.setTableNo(1);
        table.setHead(head());
        // 写数据
        writer.write(contentData(), sheet1, table);
        writer.finish();
        out.close();
    }

    private List<List<Object>> contentData() {
        List<List<Object>> contentList = Lists.newArrayList();
        //这里一个List<Object>才代表一行数据，需要映射成每行数据填充，横向填充（把实体数据的字段设置成一个List<Object>）
        contentList.add(Lists.newArrayList("苹果🍎", "深圳市"));
        contentList.add(Lists.newArrayList("橙子🍊", "北京市"));
        return contentList;
    }

    private List<List<String>> head() {
        List<List<String>> headTitles = Lists.newArrayList();
        String empty = " ";
        //第一列，1/2/3行
        headTitles.add(Lists.newArrayList(empty, empty, "姓名"));
        //第二列，1/2/3行
        headTitles.add(Lists.newArrayList(empty, empty, "城市"));
        return headTitles;
    }
}