poi模板动态导出，下拉框联动，公式设置

🕗 发布于 2024-11-25 16:07 java

背景：有一个动态导出模板的需求，根据页面维护的数据比如模板名称，模板的sheet名称，列名称宽度高度等，导出excel文件。打破以往把excel上传到代码的resource中，或者文件服务中，再下载出来导入。这样的弊端显而易见不易维护，在做导入的时候必须技术去修改xlsx文件然后发版，非常不方便。至此我们想要动态去识别列以及列中的字段，做一个通用的导出和导入功能。当然导入我已经用设计模式做完了可以参考通用导入下面就重点说一下导出模板的维护实现。

根据模板编码和sheet编码查询配置信息
生成数据源sheet比如行政区划，设置为隐藏
生成业务sheet其中包含下拉框，以及下拉框联动比如省市区联动公式设置，联动效果展示

添加依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>

整体代码


@Slf4j
@RestController
public class CreateXlsxController {
    static Map<String,String> colMap;
    static {
        colMap = new HashMap<>();
        colMap.put("0","A");
        colMap.put("1","B");
        colMap.put("2","C");
        colMap.put("3","D");
        colMap.put("4","E");
        colMap.put("5","F");
        colMap.put("6","G");
        colMap.put("7","H");
        colMap.put("8","I");
        colMap.put("9","J");
        colMap.put("10","K");
        colMap.put("11","L");
        colMap.put("12","M");
        colMap.put("13","N");
        colMap.put("14","O");
        colMap.put("15","P");
        colMap.put("16","Q");
        colMap.put("17","R");
        colMap.put("18","S");
        colMap.put("19","T");
        colMap.put("20","U");
        colMap.put("21","V");
        colMap.put("22","W");
        colMap.put("23","X");
        colMap.put("24","Y");
        colMap.put("25","Z");
    }

    @PostMapping("/generate-excel")
    public ResponseEntity<byte[]> generateExcel(@RequestBody List<TemplateDO> data) throws IOException {
        Assert.isTrue(data.size()!=0, "data is empty");
        Map<String,String> proviceCityCountryMap = new HashMap<>();
        Workbook workbook = new XSSFWorkbook();
        int fillRow = 1000;
        //生成省市区的基础数据
        String hiddenSheetName = "area";
        createArea(workbook,hiddenSheetName);
        for (TemplateDO templateDO : data) {
            // Create a workbook and sheet
            for (TemplateSheet templateSheet : templateDO.getSheetList()) {
                Sheet sheet = workbook.createSheet(templateSheet.getSheetName());
                //在第一行创建
                Row row1 = sheet.createRow(0);
                row1.setHeight((short) templateSheet.getTitleHeight());
                //标明是第一行
                Cell titleCell = row1.createCell(0);
                //创建了一个新的单元格样式对象
                CellStyle titleStyle = workbook.createCellStyle();
                // 设置文本左对齐
                titleStyle.setAlignment(HorizontalAlignment.LEFT);
                // 垂直居中
                titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                titleStyle.setWrapText(true);
                titleCell.setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, templateSheet.getTitleWidth()));
                titleCell.setCellValue(templateSheet.getTitleContext());
                //下面生成列信息
                int rowNum = 1;
                Row row = sheet.createRow(rowNum);
                int colNum = 0;
                for (TemplateSheetCol sheetCol : templateSheet.getColumnList()) {
                    sheet.setColumnWidth(colNum,sheetCol.getColumnWidth());
                    Cell cell = row.createCell(colNum);
                    //必输字段颜色展示+*号
                    if(("Y").equalsIgnoreCase(sheetCol.getNullableFlag())){
                        // 创建一个单元格样式并设置背景颜色
                        CellStyle style = workbook.createCellStyle();
                        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(style);
                        cell.setCellValue("*"+sheetCol.getColumnName());
                    }else{
                        cell.setCellValue(sheetCol.getColumnName());
                    }
                    if(StringUtils.isNotBlank(sheetCol.getChangeDataFlag()) && "Y".equalsIgnoreCase(sheetCol.getChangeDataFlag()) && StringUtils.isNotBlank(sheetCol.getValidateSet()) ){
                        //配置的值集代码是省侧的设置下拉框
                        if("province_code".equalsIgnoreCase(sheetCol.getValidateSet())){
                            CellStyle style = workbook.createCellStyle();
                            style.setFillForegroundColor(IndexedColors.RED.getIndex());
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(style);
                            int rowIndex = cell.getRowIndex();
                            int columnIndex = cell.getColumnIndex();
                            proviceCityCountryMap.put("province_code",rowIndex+2+","+columnIndex);
                            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
                            StringBuffer listFormula = new StringBuffer();
                            listFormula.append("=INDIRECT(").append(hiddenSheetName).append("!$A$1)");
                            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());
                            //从第3行开始往下填充  第1行是说明  第2行是标题
                            CellRangeAddressList regions = new CellRangeAddressList(2,fillRow, colNum, colNum);
                            XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                            data_validation_list.setSuppressDropDownArrow(true);
                            data_validation_list.setShowErrorBox(true);
                            sheet.addValidationData(data_validation_list);
                        }else if("city_code".equalsIgnoreCase(sheetCol.getValidateSet())){
                            CellStyle style = workbook.createCellStyle();
                            style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(style);
                            int rowIndex = cell.getRowIndex();
                            int columnIndex = cell.getColumnIndex();
                            proviceCityCountryMap.put("city_code",rowIndex+2+","+columnIndex);
                            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
                            Integer provinceRow = Integer.parseInt(proviceCityCountryMap.get("province_code").split(",")[0]);
                            String provinceCol = proviceCityCountryMap.get("province_code").split(",")[1];
                            for (int i = 0; i < fillRow; i++) {
                                StringBuffer listFormula = new StringBuffer();
                                listFormula.append("=INDIRECT(").append("$").append(colMap.get(provinceCol)).append("$").append(provinceRow).append(")");
                                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());
                                CellRangeAddressList regions = new CellRangeAddressList(provinceRow-1,provinceRow-1, colNum, colNum);
                                provinceRow++;
                                XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                                data_validation_list.setSuppressDropDownArrow(true);
                                data_validation_list.setShowErrorBox(true);
                                sheet.addValidationData(data_validation_list);
                            }
                        }else if("country_code".equalsIgnoreCase(sheetCol.getValidateSet())){
                            CellStyle style = workbook.createCellStyle();
                            style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cell.setCellStyle(style);
                            int rowIndex = cell.getRowIndex();
                            int columnIndex = cell.getColumnIndex();
                            proviceCityCountryMap.put("country_code",rowIndex+","+columnIndex);
                            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
                            Integer cityRow = Integer.parseInt(proviceCityCountryMap.get("city_code").split(",")[0]);
                            String cityCol = proviceCityCountryMap.get("city_code").split(",")[1];
                            for (int i = 0; i < fillRow; i++) {
                                StringBuffer listFormula = new StringBuffer();
                                listFormula.append("=INDIRECT(").append("$").append(colMap.get(cityCol)).append("$").append(cityRow).append(")");
                                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());
                                CellRangeAddressList regions = new CellRangeAddressList(cityRow-1,cityRow-1, colNum, colNum);
                                cityRow++;
                                XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                                data_validation_list.setSuppressDropDownArrow(true);
                                data_validation_list.setShowErrorBox(true);
                                sheet.addValidationData(data_validation_list);
                            }
                        }else if("gender_code".equalsIgnoreCase(sheetCol.getValidateSet())){
                            DataValidationHelper validationHelper = sheet.getDataValidationHelper();
                            //根据validateSet去数据库查
                            DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(new String[]{"M-男","W-女"});
                            CellRangeAddressList addressList = new CellRangeAddressList(rowNum+1, 100000, colNum, colNum);
                            DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
                            dataValidation.setSuppressDropDownArrow(true);
                            dataValidation.setShowErrorBox(true);
                            sheet.addValidationData(dataValidation);
                        }
                    }
                    colNum++;
                }
            }
        }
        // Write the output to a byte array
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        workbook.write(out);
        workbook.close();
        // Set response headers
        HttpHeaders headers = new HttpHeaders();
        String headerValue = "attachment; filename="+data.get(0).getTemplateName()+".xlsx";
        headers.add(HttpHeaders.CONTENT_DISPOSITION, headerValue);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return ResponseEntity.ok()
                .headers(headers)
                .body(out.toByteArray());
    }
    public void createArea(Workbook workbook,String sheetName) throws IOException {
        // 创建工作簿和工作表
        String[] provinceArr = {"江苏省","安徽省"};
        //依次列出各省的市、各市的县
        String[] cityJiangSu = {"南京市","苏州市","盐城市"};
        String[] cityAnHui = {"合肥市","安庆市"};
        //将有子区域的父区域放到一个数组中
        String[] areaFatherNameArr ={"江苏省","安徽省"};
        Map<String,String[]> areaMap = new HashMap<>();
        areaMap.put("江苏省", cityJiangSu);
        areaMap.put("安徽省",cityAnHui);
        Map<String,List<String>> countryMap = new HashMap<String,List<String>>(){{
            put("南京市",Arrays.asList("南京区1","南京区2"));
            put("苏州市",Arrays.asList("苏州区1","苏州区2"));
            put("盐城市",Arrays.asList("盐城市区1","盐城市区2"));
            put("合肥市",Arrays.asList("合肥市区1","合肥市区2"));
            put("安庆市",Arrays.asList("安庆市区1","安庆市区2"));
        }};
        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        Sheet hideSheet = workbook.createSheet(sheetName);
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
        //整个sheet页的colNum 持续递增
        int colNum = 0;
        int procinceRowNum = 0;
        // 设置第一行，存省的信息
        Row zeroRow = hideSheet.createRow(procinceRowNum++);
        String procinceTitle = "省列表";
        zeroRow.createCell(0).setCellValue(procinceTitle);
        for(int i = 0; i < provinceArr.length; i ++){
            Row row1 = hideSheet.createRow(procinceRowNum++);
            Cell cell = row1.createCell(colNum);
            cell.setCellValue(provinceArr[i]);
        }
        // 添加名称管理器
        String rangeProvice = getRange(colNum, procinceRowNum);
        Name nameProvice = workbook.createName();
        //key不可重复,将父区域名作为key
        nameProvice.setNameName(procinceTitle);
        String formulaProvice = sheetName +"!" + rangeProvice;
        nameProvice.setRefersToFormula(formulaProvice);
        colNum++;
        for (int i = 0; i < areaFatherNameArr.length; i++) {
            Cell cell = zeroRow.createCell(colNum);
            cell.setCellValue(areaFatherNameArr[i]);
            String[] cityArr = areaMap.get(areaFatherNameArr[i]);
            int rowNum =1;
            for (int j = 0; j < cityArr.length; j++) {
                Row tmpRow = hideSheet.getRow(rowNum);
                if(tmpRow == null){
                    Row row = hideSheet.createRow(rowNum);
                    Cell cell1 = row.createCell(colNum);
                    cell1.setCellValue(cityArr[j]);
                }else {
                    Cell cell1 = tmpRow.createCell(colNum);
                    cell1.setCellValue(cityArr[j]);
                }
                rowNum++;
            }
            // 添加名称管理器
            String rangeCity = getRange(colNum, rowNum);
            Name nameCity = workbook.createName();
            //key不可重复,将父区域名作为key
            nameCity.setNameName(areaFatherNameArr[i]);
            String formula = sheetName +"!" + rangeCity;
            nameCity.setRefersToFormula(formula);
            colNum++;
        }
        for (Map.Entry<String, List<String>> entry : countryMap.entrySet()) {
            //市区
            String key = entry.getKey();
            Cell cell = zeroRow.createCell(colNum);
            cell.setCellValue(key);
            List<String> countryList = entry.getValue();
            int rowNum =1;
            for (int j = 0; j < countryList.size(); j++) {
                Row tmpRow = hideSheet.getRow(rowNum);
                if(tmpRow == null){
                    Row row = hideSheet.createRow(rowNum);
                    Cell cell1 = row.createCell(colNum);
                    cell1.setCellValue(countryList.get(j));
                }else {
                    Cell cell1 = tmpRow.createCell(colNum);
                    cell1.setCellValue(countryList.get(j));
                }
                rowNum++;
            }
            // 添加名称管理器
            String rangeCountry = getRange(colNum, rowNum);
            Name nameCountry = workbook.createName();
            //key不可重复,将父区域名作为key
            nameCountry.setNameName(key);
            String formula = sheetName +"!" + rangeCountry;
            nameCountry.setRefersToFormula(formula);
            colNum++;
        }
    }

    /**
     *  计算formula
     * @param colNum   代表A B C D E F G ......
     * @param rowNum
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     *
     * */
    public String getRange(int colNum, int rowNum) {
        String abc = colMap.get(String.valueOf(colNum));
        //默认都是从第二行开始
        return "$" + abc + "$" + 2 + ":$" + abc + "$" + rowNum;
    }
}

入参模拟查询配置信息

[
    {
        "templateName": "people_base_info",
        "sheetList": [
            {
                "sheetName": "sheet1",
                "titleContext": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\njjjjjjjjjjjjjjjjjjjjjjjjjjj\n",
                "titleWidth": 8,
                "titleHeight": 1000,
                "columnList": [
                    {
                        "columnName": "序号",
                        "columnCode": "number",
                        "columnWidth": 2000,
                        "columnHeight": 20,
                        "nullableFlag": "N"
                    },
                    {
                        "columnName": "年龄",
                        "columnCode": "age",
                        "columnWidth": 5000,
                        "columnHeight": 20,
                        "nullableFlag": "N"
                    },
                    {
                        "columnName": "性别",
                        "columnCode": "gender",
                        "columnWidth": 5000,
                        "columnHeight": 20,
                        "nullableFlag": "Y",
                        "changeDataFlag": "Y",
                        "validateSet": "gender_code"
                    },
                    {
                        "columnName": "省份",
                        "columnCode": "province",
                        "columnWidth": 5000,
                        "columnHeight": 20,
                        "nullableFlag": "Y",
                        "changeDataFlag": "Y",
                        "validateSet": "province_code"
                    },
                    {
                        "columnName": "城市",
                        "columnCode": "city",
                        "columnWidth": 5000,
                        "columnHeight": 20,
                        "nullableFlag": "Y",
                        "changeDataFlag": "Y",
                        "validateSet": "city_code"
                    },
                    {
                        "columnName": "区县",
                        "columnCode": "country",
                        "columnWidth": 5000,
                        "columnHeight": 20,
                        "nullableFlag": "Y",
                        "changeDataFlag": "Y",
                        "validateSet": "country_code"
                    }
                ]
            }
        ]
    }
]

下面拆解代码，重点代码展示

代码拆解

设置样式

//创建了一个新的单元格样式对象
CellStyle titleStyle = workbook.createCellStyle();
// 设置文本左对齐
titleStyle.setAlignment(HorizontalAlignment.LEFT);
// 垂直居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setWrapText(true);
titleCell.setCellStyle(titleStyle);

合并单元格

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, templateSheet.getTitleWidth()));

设置颜色

// 创建一个单元格样式并设置背景颜色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
cell.setCellValue("*"+sheetCol.getColumnName());

设置`INDIRECT`公式

INDIRECT嵌套名称管理器可以生成下拉框
下拉框嵌套INDIRECT引用前面的下拉框
参考下面的xlsx文件，查看名称管理器，公式有效性的序列属性
链接: https://pan.baidu.com/s/1zz4q0B3jfeybsSB4MrOMdA?pwd=qt5u 提取码: qt5u 复制这段内容后打开百度网盘手机App，操作更方便哦

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
StringBuffer listFormula = new StringBuffer();
listFormula.append("=INDIRECT(").append(hiddenSheetName).append("!$A$1)");
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());
//从第3行开始往下填充  第1行是说明  第2行是标题
CellRangeAddressList regions = new CellRangeAddressList(2,fillRow, colNum, colNum);
XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
sheet.addValidationData(data_validation_list);

隐藏sheet页

//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);

添加名称管理器，给下拉框用


 // 添加名称管理器
 String rangeCity = getRange(colNum, rowNum);
 Name nameCity = workbook.createName();
 //key不可重复,将父区域名作为key
 nameCity.setNameName(areaFatherNameArr[i]);
 String formula = sheetName +"!" + rangeCity;
 nameCity.setRefersToFormula(formula);


public String getRange(int colNum, int rowNum) {
    String abc = colMap.get(String.valueOf(colNum));
    //默认都是从第二行开始
    return "$" + abc + "$" + 2 + ":$" + abc + "$" + rowNum;
}

判断row是否为空，不为空再创建，否则会覆盖之前的

 Row tmpRow = hideSheet.getRow(rowNum);
 if(tmpRow == null){
     Row row = hideSheet.createRow(rowNum);
     Cell cell1 = row.createCell(colNum);
     cell1.setCellValue(countryList.get(j));
 }else {
     Cell cell1 = tmpRow.createCell(colNum);
     cell1.setCellValue(countryList.get(j));
 }

原文地址：https://blog.csdn.net/Smy_0114/article/details/143898003

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：stable Diffusion官方模型下载
下一篇：【ROS2 中间件RMW】基于FastDDS共享内存实现ROS2跨进程零拷贝通讯

移动充储机器人“小奥”的多场景应用（上）
此外，“小奥”在设计上还考虑了环境适应性，它能够在各种天气条件下稳定工作，无论是炎热的夏季还是寒冷的冬季，都能保证充电服务的连续性和可靠性。它采用的充电技术减少了对电网的冲击，优化了能源的使用效率，有
阅读更多2024-11-26
day07（单片机高级）继电器模块绘制
继电器模块绘制
阅读更多2024-11-26
react函数式组件中的路由传参方式
useParamsstateNavigate路由传参。React Router 提供了多种方式来传递和获取路由参数。
阅读更多2024-11-26
MySQL中的锁与优化SQL查询性能
MySQL作为一种高效、稳定、易用的开源关系型数据库管理系统（RDBMS），在大数据量和高并发的场景中，其性能优化显得尤为重要。锁机制和SQL查询优化是MySQL性能调优的两个关键方面。本文将详细探讨
阅读更多2024-11-26
Cesium教程06_材质设置
cesium设置材质
阅读更多2024-11-26
Three.js CSS2D/CSS3D渲染器
CSS2DRenderer 渲染器用于在 3D 场景中渲染纯 2D 的 HTML 元素。这些元素不会具有 3D 透视效果，但可以与 3D 对象一起移动和旋转，非常适合用于标签、注释等需要固定显示的内容
阅读更多2024-11-26
【过滤器】一文了解 .NET Core 中各种 Filter
ASP.NET Core中，Filter（过滤器）是一种强大的机制，允许你在请求处理管道中的特定阶段运行代码。这些过滤器提供了在请求的不同阶段执行逻辑的能力，比如授权、日志记录、异常处理等。ASP.N
阅读更多2024-11-26
人工智能-深度学习-Torch框架-手动构建回归流程
导入函数，用于生成回归数据集。: 导入math模块，用于进行数学计算，例如向上取整。: 导入random模块，用于随机打乱数据集。: 导入torch库，用于张量操作和神经网络训练。
阅读更多2024-11-26
android 11添加切换分屏功能
自Android 7开始官方就支持分屏显示，但没有切换分屏的功能，即交换上下屏幕。直到Android 13开始才支持切换分屏，操作方式是：分屏模式下双击中间分割线就会交换上下屏位置。本文的目的就是在A
阅读更多2024-11-26
高防服务器HOT：网络安全的无形盾牌，护航业务稳定
高防服务器通过卓越的防护能力，为企业筑起一道坚实的安全屏障，不仅能保障业务稳定，更能为企业争取宝贵的声誉和客户信赖。高防服务器不仅能够提供强大的抗攻击能力，还兼具高性能网络和低延迟特性，广泛应用于对网
阅读更多2024-11-26