导出Excel模板实现级联

​

 前言

Spring Boot 生态里关于「如何优雅地生成带级联约束的 Excel 模板」的完整示例凤毛麟角。官方文档止步于基础 API，社区帖子大多只解决「下拉」却忽略「级联」。今天这篇文章，我就用最新 Apache POI 5.2.3 来填这个坑：从依赖引入、隐藏工作表设计，一步一步带你跑通「零依赖前端组件、纯后端生成、下载即用」的级联下拉模板。读完你不仅能直接 Ctrl+C / Ctrl+V 代码，还能理解每一条 POI 调用背后的 Excel 机制，日后想扩展三级、四级甚至动态级联，都能举一反三。

我的公众号（附有面试资料）：Java乐享

一。搭建spring boot框架

1.访问start.spring.io/

​

2.生成的压缩包解压后在idea中打开

3.设置maven仓库地址

File->Settings,在搜索框中搜索maven,设置成自己的maven路径

​

​

4.将application.properties修改为yml后缀，添加如下内容

server:
  port: 8080

5.下载JDK17

www.oracle.com/java/，然后设置环境变量，把JAVA_HOME和PATH中替换成JAVA17的路径

二。引入相关依赖

<!-- 加入slf4j的核心API -->
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-api</artifactId>
			<version>2.17.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.17.2</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.2.3</version>
			<exclusions>
				<!-- 排除 POI 自带的低版本 Log4j -->
				<exclusion>
					<groupId>org.apache.logging.log4j</groupId>
					<artifactId>log4j-api</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.3</version>
			<exclusions>
				<!-- 排除 POI 自带的低版本 Log4j -->
				<exclusion>
					<groupId>org.apache.logging.log4j</groupId>
					<artifactId>log4j-api</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.8.40</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-collections4</artifactId>
			<version>4.2</version>
		</dependency>

三。工具类

package com.lexiang.demo.util;

import cn.hutool.core.collection.CollectionUtil;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

public class ExcelUtil {
    /**
     * 影响最大行数
     */
    private static final int XLS_MAX_ROW = 50000;

    /**
     * 导出模板
     *
     * @param classList  年级列表
     * @param academyMap  学院下拉列表<key是年级，value是学院列表>
     * @param professionalMap  专业下拉列表<key是学院，value是专业列表>
     */
    public static void exportHSSFTemplateUniversity(HttpServletResponse response, List<String> classList, Map<String, List<String>> academyMap, Map<String, List<String>> professionalMap) {

        // 变更表头数据，相应的修改firstCol和lastCol的数字，从0开始
        String[] titleList = new String[]{"姓名", "学号", "身份证", "学校名称", "年级", "学院", "专业", "是否毕业"};

        //创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet");
        sheet.setDefaultColumnWidth(18);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = getStyle(workbook);

        for (int i = 0; i < titleList.length; i++) {
            String title = titleList[i];
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(title);
            cell.setCellStyle(style);
            cell.setCellType(CellType.STRING);
        }
        // 创建隐藏目录
        createHideSheetHSSFAcademy(workbook, classList, academyMap);
        if (MapUtils.isNotEmpty(professionalMap)) {
            List<String> list = professionalMap.keySet().stream().map(Objects::toString).distinct().collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(list)) {
                createHideSheetHSSFProfessional(workbook, list, professionalMap);
            }
        }

        // 如果excel位置不是如上titleList中的位置，需要变更则需要INDIRECT中所在的列名称
        // 年级规则
        DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(classList.toArray(new String[]{}));
        CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, XLS_MAX_ROW, 4, 4);
        DataValidation classDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
        classDataValidation.createErrorBox("error", "请选择正确的年级");
        sheet.addValidationData(classDataValidation);

        // 学院下拉规则
        CellRangeAddressList academyRange = new CellRangeAddressList(1, XLS_MAX_ROW, 5, 5);
        DataValidation academyValidation = new HSSFDataValidation(academyRange, DVConstraint.createFormulaListConstraint("INDIRECT($E1)"));
        academyValidation.createErrorBox("error", "请选择正确的学院");
        sheet.addValidationData(academyValidation);

        // 专业下拉规则
        CellRangeAddressList professionalRange = new CellRangeAddressList(1, XLS_MAX_ROW, 6, 6);
        DataValidation professionalValidation = new HSSFDataValidation(professionalRange, DVConstraint.createFormulaListConstraint("INDIRECT($F1)"));
        professionalValidation.createErrorBox("error", "请选择正确的专业");
        sheet.addValidationData(professionalValidation);

        // 是否毕业下拉
        List<String> graduateList = new ArrayList<>();
        graduateList.add("是");
        graduateList.add("否");
        DVConstraint graduateConstraint = DVConstraint.createExplicitListConstraint(graduateList.toArray(new String[]{}));
        CellRangeAddressList graduateRangeAddressList = new CellRangeAddressList(1, XLS_MAX_ROW, 7, 7);
        DataValidation graduateDataValidation = new HSSFDataValidation(graduateRangeAddressList, graduateConstraint);
        graduateDataValidation.createErrorBox("error", "请选择正确的毕业选项");
        sheet.addValidationData(graduateDataValidation);

        //使用response导出
        OutputStream output;
        try {
            output = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" +  URLEncoder.encode("student_import.xlsx", "utf8"));
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            workbook.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建隐藏页
     *
     * @param workbook   sheet页
     * @param classList  级联父级
     * @param academyMap 级联子级
     */
    public static void createHideSheetHSSFAcademy(HSSFWorkbook workbook, List<String> classList, Map<String, List<String>> academyMap) {
        //创建一个专门用来存专业信息的隐藏sheet页
        HSSFSheet hideSheet = workbook.createSheet("academy");

        int rowId = 0;
        // 设置第1行，存年级的信息
        Row claRow = hideSheet.createRow(rowId++);
        for (int i = 0; i < classList.size(); i++) {
            Cell claCell = claRow.createCell(i);
            claCell.setCellValue(classList.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        if ( !academyMap.isEmpty() ) {
            for (String cla : classList) {
                // 获取年级信息对应的学院信息列表
                List<String> academyList = academyMap.get(cla);
                Row prow = hideSheet.createRow(rowId++);
                prow.createCell(0).setCellValue(cla);
                for (int j = 0; j < academyList.size(); j++) {
                    Cell cell = prow.createCell(j + 1);
                    cell.setCellValue(academyList.get(j));
                }
                // 添加名称管理器
                String range = getRange(1, rowId, academyList.size());
                Name name = workbook.createName();
                // key不可重复
                name.setNameName(cla);
                String formula = "academy!" + range;
                name.setRefersToFormula(formula);
            }
        }
        workbook.setSheetHidden(1, true);
    }

    /**
     * 创建隐藏页
     *
     * @param workbook   sheet页
     * @param classList  级联父级
     * @param professionalMap 级联子级
     */
    public static void createHideSheetHSSFProfessional(HSSFWorkbook workbook, List<String> classList, Map<String, List<String>> professionalMap) {
        //创建一个专门用来存放专业信息的隐藏sheet页
        HSSFSheet hideSheet = workbook.createSheet("professional");

        int rowId = 0;
        // 设置第1行，存学院的信息
        Row professRow = hideSheet.createRow(rowId++);
        for (int i = 0; i < classList.size(); i++) {
            Cell professCell = professRow.createCell(i);
            professCell.setCellValue(classList.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        if ( !professionalMap.isEmpty() ) {
            for (String profess : classList) {
                // 获取学院信息对应的专业信息列表
                List<String> professList = professionalMap.get(profess);
                Row prow = hideSheet.createRow(rowId++);
                prow.createCell(0).setCellValue(profess);
                for (int j = 0; j < professList.size(); j++) {
                    Cell cell = prow.createCell(j + 1);
                    cell.setCellValue(professList.get(j));
                }
                // 添加名称管理器
                String range = getRange(1, rowId, professList.size());
                Name name = workbook.createName();
                // key不可重复
                name.setNameName(profess);
                String formula = "professional!" + range;
                name.setRefersToFormula(formula);
            }
        }
        workbook.setSheetHidden(2, true);
    }

    /**
     * 样式
     *
     * @param workbook sheet页
     * @return HSSFCellStyle 返回样式
     */
    private static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 自动换行
        //cellStyle.setWrapText(true);
        HSSFFont font = workbook.createFont();
        // 设置字体名称
        font.setFontName("宋体");
        // 字体大小
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static 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;
        }
    }
}

四。测试

package com.lexiang.demo.controller;

import com.lexiang.demo.util.ExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

@Controller
public class POIController {


    @RequestMapping("/export")
    public void export(HttpServletResponse response) {
        List<String> classList = Arrays.asList("_24级", "_25级");
        Map<String, List<String>> academyMap = new HashMap<>();
        academyMap.put("_24级", Arrays.asList("信息工程学院", "土木工程学院"));
        academyMap.put("_25级", Arrays.asList("生命科学学院", "经济学院"));

        Map<String, List<String>> professMap = new HashMap<>();
        professMap.put("信息工程学院", Arrays.asList("网络工程", "信息技术工程"));
        professMap.put("土木工程学院", Arrays.asList("建筑工程", "建设与房地产", "工程力学"));
        ExcelUtil.exportHSSFTemplateUniversity(response, classList, academyMap, professMap);
    }
}

启动项目后用apifox或者postman调用接口测试

​

效果如下,这样就实现了级联下拉

​

注意：

1.如果你修改你的表头数据titleList数据，相对应修改firstCol和lastCol的数字，从0开始，并且修改listFormula（比如学院的数据依赖年级，所以这里是E1，根据自己的需要灵活变动）

​ 2.Excel 的名称管理器（Named Range）要求名称必须以字母或下划线开头， 之前把班级设置成

List<String> classList = Arrays.asList("24级", "25级");

结果报错Invalid name: '24级': first character must be underscore or a letter

所以加了下划线_,确保符合 Excel 命名规则

注：安装包请通过公众号获取，关注Java乐享公众号，回复“jdk”获取

​

​