java使用POI导出Excel，下拉列表联动，单元格合并，日期校验，锁定指定列禁止修改

java POI 工具类

1 导出Excel
2 导出带下拉列表联动的Excel
3 导出单元格合并的Excel
4 导出带日期校验的Excel
5 导出锁定指定列禁止修改的Excel

---

前言

需求java导出的Excel要添加指定列的下拉框限制。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、引用maven

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
            <scope>compile</scope>
        </dependency>

二、上代码

1.ExcelTemplateBean 实体类

代码如下：

public class ExcelTemplateBean {

    // 数据页名
    private String dataSheetName = "sheet";
    // 字典页名
    private String dictSheetName = "dict";

    // 标题行
    private List<String> titles = new ArrayList<>();
    // 数据集合
    private List<Map<Integer, String>> dataList = new ArrayList<>();
    // 名称管理器-字典数据集合
    private Map<String, List<String>> dictMap = new HashMap<>();
    // 数据有效性公式-下标集合
    private Map<String, Integer> formulaIndex = new HashMap<>();
    // 默认开始日期
    private String startDate = "1970-01-01";
    // 默认结束日期
    private String endDate = "9999-12-31";
    // 日期格式验证
    private Set<Integer> dateFormulaIndex = new HashSet<>();
    // 上锁列-下标集合
    private Set<Integer> lockColumnIndex = new HashSet<>();
    // 是否上锁
    private Boolean lock = false;
    // 密码
    private String password = "";
    // 列宽
    private Integer columnWidth = 5000;
    // 隐藏字典页
    private Boolean dictSheetHidden = true;
    // 合并(4个参数，分别为起始行，结束行，起始列，结束列)
    // 行和列都是从0开始计数，且起始结束都会合并
    private List<List<Integer>> mergeRegion = new ArrayList<>();

    public ExcelTemplateBean() {
    }

    public ExcelTemplateBean(List<String> titles, List<Map<Integer, String>> dataList) {
        this.titles = titles;
        this.dataList = dataList;
    }

    /**
     * 4个参数，分别为起始行，结束行，起始列，结束列
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public void addMergeRegion(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
        List<Integer> region = new ArrayList<>();
        region.add(firstRow);
        region.add(lastRow);
        region.add(firstCol);
        region.add(lastCol);
        this.mergeRegion.add(region);
    }

}

2.ExcelTemplateUtil 工具类

代码如下：

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;

public class ExcelTemplateUtils {

    /**
     * 计算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) {
        if (colCount <= 0) {
            colCount = 1;
        }
        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 if (colCount > 51 && colCount <= 701) {// 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);
                }
            } else {
                if (colCount > 16383) {
                    colCount = 16383;
                }
                char left = 'A';
                char centre = 'A';
                char right = 'A';
                if (colCount / 26 % 26 == 0) {
                    centre = (char) ('A' + 25);// 边界值
                    left = (char) (left + (colCount / 26 / 26 % 26 - 2));
                } else {
                    left = (char) (left + (colCount / 26 / 26 % 26 - 1));
                    centre = (char) (centre + (colCount / 26 % 26 - 1));
                }

                right = (char) (right + colCount % 26);
                return "$" + start + "$" + rowId + ":$" + left + centre + right + "$" + rowId;
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }


    /**
     * 设置有效性，去名称管理器查询集合
     *
     * @param formula   有效性：公式(名称管理器/左侧单元格)
     * @param dvHelper  数据约束辅助类
     * @param collIndex 限制列所在的下标
     */
    public static DataValidation getDataValidation(String formula, DataValidationHelper dvHelper, Integer collIndex) {
        // 数据验证约束
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formula);// 公式
        CellRangeAddressList regions = new CellRangeAddressList(-1, -1, collIndex, collIndex);// 单元格，适用范围
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);// 创建验证

        dataValidation.setEmptyCellAllowed(true); // 设置单元允许为空(不为null的画，输错了就从单元格出不来了)
        dataValidation.setSuppressDropDownArrow(true); // 设置下拉箭头
        dataValidation.setShowErrorBox(true); // 设置显示错误框;
        // dataValidation.setShowPromptBox(true); // 设置显示下拉框提示(一直有提示，很烦人的)
        dataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");// 设置输入信息提示信息

        return dataValidation; // 添加验证到表单
    }

    /**
     * 设置时间格式有效性，默认1970-01-01 到 9999-12-31
     * 默认格式："yyyy-MM-dd"
     *
     * @param excel
     * @param dvHelper
     * @param collIndex
     * @return
     */
    public static DataValidation getDateDataValidation(ExcelTemplateBean excel, DataValidationHelper dvHelper, Integer collIndex) {
        // 事件格式验证
        DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DVConstraint.ValidationType.DATE, excel.getStartDate(), excel.getEndDate(), "yyyy-MM-dd");
        CellRangeAddressList regions = new CellRangeAddressList(-1, -1, 8, 8);// 单元格，适用范围
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);// 创建验证
        /*设置单元允许为空*/
        dataValidation.setEmptyCellAllowed(true);
        /* 设置显示错误框;*/
        dataValidation.setShowErrorBox(true);
        dataValidation.createErrorBox("格式错误", "请输入正确的日期格式！");
        return dataValidation; // 添加验证到表单
    }

    /**
     * 返回一个，有下拉，有数据的 workbook模板
     * 联动：https://blog.csdn.net/m0_37956938/article/details/78084503
     * 锁定：https://www.cnblogs.com/minxl/p/10170806.html
     */
    public static SXSSFWorkbook getSXSSFWorkbookTemplate(ExcelTemplateBean excel) {


        // 创建一个excel
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 创建需要用户填写的sheet
        SXSSFSheet dataSheet = book.createSheet(excel.getDataSheetName());
        // 单元格，格式化样式
        DataFormat dataFormat = book.createDataFormat();
        // 锁定样式
        CellStyle lockStyle = book.createCellStyle();
        lockStyle.setLocked(true);
        lockStyle.setDataFormat(dataFormat.getFormat("@")); // 设置单元格文本样式为文本(长数字显示会以科学计数法展示)
        // 不锁定样式
        CellStyle unlockStyle = book.createCellStyle();
        unlockStyle.setLocked(false);
        unlockStyle.setDataFormat(dataFormat.getFormat("@")); // 设置单元格文本样式为文本
        // lockstyle.setWrapText(true); // 开启单元格自动换行(默认关闭，页面有点乱)
        // unlockstyle.setWrapText(true); // 开启单元格自动换行

        // 数据表，标题写入
        Row titlesRow = dataSheet.createRow(0);
        // 开启锁定（默认整张表都锁定）
        titlesRow.setRowStyle(lockStyle);
        List<String> titles = excel.getTitles();
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = titlesRow.createCell(i);
            // 设置空白行列默认为不锁定
            if (excel.getLockColumnIndex().contains(i)) {
                dataSheet.setDefaultColumnStyle(i, lockStyle);
            } else {
                dataSheet.setDefaultColumnStyle(i, unlockStyle);
            }
            cell.setCellValue(titles.get(i));
            if (excel.getColumnWidth() != null) {
                dataSheet.setColumnWidth(i, excel.getColumnWidth());
            }
            // 填充数据时，此单元格锁定：cell.setCellStyle(lockStyle);
            // 填充数据时，此单元格解除锁定：cell.setCellStyle(unlockStyle);
        }

        // 填充数据
        List<Map<Integer, String>> dataList = excel.getDataList();
        for (int i = 0; i < dataList.size(); i++) {
            Row row = dataSheet.createRow(i + 1);
            Map<Integer, String> data = dataList.get(i);
            for (Map.Entry<Integer, String> entry : data.entrySet()) {
                Cell cell = row.createCell(entry.getKey());
                if (excel.getLockColumnIndex().contains(entry.getKey())) {
                    cell.setCellStyle(lockStyle);
                } else {
                    cell.setCellStyle(unlockStyle);
                }
                cell.setCellValue(entry.getValue());
            }

        }

        //创建一个专门用来存放字典信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        Sheet dictSheet = book.createSheet(excel.getDictSheetName());
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(dictSheet), excel.getDictSheetHidden());
        Map<String, List<String>> dictMap = excel.getDictMap();
        // 往字典表，写入数据，并添加到名称管理器
        int rowId = 0;
        Set<String> keys = dictMap.keySet();
        Iterator<String> iterator = keys.iterator();
        while (iterator.hasNext()) {
            String key = iterator.next();
            List<String> childrenList = dictMap.get(key);
            if (childrenList == null) {
                childrenList = new ArrayList<>();
            }
            key = replaceValue(key);

            Row row = dictSheet.createRow(rowId++);
            row.setRowStyle(lockStyle);
            row.createCell(0).setCellValue(key);

            for (int i = 0; i < childrenList.size(); i++) {
                Cell cell = row.createCell(i + 1);
                String value = replaceValue(childrenList.get(i));
                cell.setCellValue(value);
            }
            // 添加名称管理器
            String range = getRange(1, rowId, childrenList.size());
            Name name = book.createName();
            //key不可重复
            name.setNameName(key);
            String formula = excel.getDictSheetName() + "!" + range;
            name.setRefersToFormula(formula);
        }

        // 单元格合并
        for (List<Integer> cell : excel.getMergeRegion()) {
            if (cell.size() == 4) {
                CellRangeAddress region = new CellRangeAddress(cell.get(0), cell.get(1), cell.get(2), cell.get(3));
                dataSheet.addMergedRegion(region);
            }
        }


        // 数据验证助手
        DataValidationHelper dvHelper = dataSheet.getDataValidationHelper();

        // 下拉列表验证
        Map<String, Integer> formulaIndex = excel.getFormulaIndex();
        for (Map.Entry<String, Integer> entry : formulaIndex.entrySet()) {
            dataSheet.addValidationData(getDataValidation(entry.getKey(), dvHelper, entry.getValue()));
        }
        // 时间格式，时间区间验证
        for (Integer index : excel.getDateFormulaIndex()) {
            dataSheet.addValidationData(getDateDataValidation(excel, dvHelper, index));
        }

        // 上锁
        if (excel.getLock()) {
            dataSheet.protectSheet(excel.getPassword());
        }
        dictSheet.protectSheet(excel.getPassword());

        return book;
    }

    public static String replaceValue(String value) {
        // 只保留，汉字，字母（大小写），数字（0-9），下划线（_）
        String regex = "[^\\u4e00-\\u9fa50-9a-zA-Z]+";
        return value.replaceAll(regex, "_");
    }

2. 使用

2.1 普通导出：

		// 文件名
        String fileName = "历史" + System.currentTimeMillis() + ".xlsx";

        // 标题行（下标即列的位置）
        List<String> titles = getTitles2();
        // 数据集合（map为一行的数据，map的key即列的下标）
        List<Map<Integer, String>> dataList = getDataList2(list);

        ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);

        SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);
		// 使用io导出workbook 就可以了
		

2.2下拉框限制，多级联动导出：

本质是创建了一个sheet2，为sheet1的指定列创建一个数据验证的规则，指向sheet2或者指定列

		// 数据有效性字典 ，用于限制下拉框的内容
        Map<String, List<String>> dictMap = new HashMap<>();
        //得到第一级省名称，放在列表里
        ArrayList<String> provinceList = new ArrayList<>();
        provinceList.add("上海市_310100000000");
        dictMap.put("省市列表", provinceList);

        // 获取 第二列(传感器类型)，第三列(所属应用)数据
        Map<String, List<String>> sensorMap = this.getAppSensor();
        List<String> appList = sensorMap.get("app");
        dictMap.put("所属应用", appList);// 所属应用
        List<String> typeList = sensorMap.get("sensor");
        dictMap.put("传感器类型", typeList);// 传感器类型

        List<String> commisionNameString = this.getCommisionNameList();
        dictMap.put("委办类型", commisionNameString);// 传感器类型

        Map<String, Integer> formulaIndex = new HashMap<>();
        // 数据有效性，直接来自名称管理器（只允许中文、数字、字母、下划线）
        formulaIndex.put("传感器类型", 2);
        formulaIndex.put("所属应用", 3);
        formulaIndex.put("省市列表", 4);
        formulaIndex.put("上海市_310100000000", 5);//区
        // 数据有效性，直接来前一列，间接查询名称管理器
        // formulaIndex.put("INDIRECT($D1)",4);//区
        formulaIndex.put("INDIRECT($F1)", 6);//街道
        formulaIndex.put("INDIRECT($G1)", 7);//片区
        formulaIndex.put("INDIRECT($H1)", 8);//居委
        formulaIndex.put("INDIRECT($I1)", 9);//小区
        formulaIndex.put("委办类型", 10);

        String password = "1234567890";

		ExcelTemplateBean excel = new ExcelTemplateBean();
		// 将数据放入 excel 对象
		// 省略代码…………
        SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);
		// 使用io导出workbook 就可以了

2.3 合并单元格导出

		// 文件名
        String fileName = "类型统计表" + System.currentTimeMillis() + ".xlsx";

        // 标题行
        List<String> titles = getTitles();
        // 数据集合
        List<Map<Integer, String>> dataList = getDataList(list);

        ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);

        // 设置合并单元格（4个参数，分别为起始行，结束行，起始列，结束列）
        excel.addMergeRegion(0, 1, 0, 0);
        excel.addMergeRegion(0, 1, 1, 1);
        excel.addMergeRegion(0, 0, 2, 4);
        excel.addMergeRegion(0, 0, 5, 7);
        excel.addMergeRegion(0, 0, 8, 10);
        excel.addMergeRegion(0, 0, 11, 13);
        excel.addMergeRegion(0, 0, 14, 16);

        SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);

        // 设置居中
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        SXSSFSheet sheetAt = workbook.getSheetAt(0);
        sheetAt.getRow(0).getCell(0).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(1).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(2).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(5).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(8).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(11).setCellStyle(cellStyle);
        sheetAt.getRow(0).getCell(14).setCellStyle(cellStyle);

		// 使用io导出workbook 就可以了

2.4 日期时间格式校验（禁止修改）导出

		// 默认使用了"yyyy-MM-dd"日期格式，如有需要请自行修改:ExcelTemplateUtils.getDateDataValidation()
		// 文件名
        String fileName = "历史" + System.currentTimeMillis() + ".xlsx";

        // 标题行（下标即列的位置）
        List<String> titles = getTitles2();
        // 数据集合（map为一行的数据，map的key即列的下标）
        List<Map<Integer, String>> dataList = getDataList2(list);

        // 需要进行时间校验的列下标
        Set<Integer> dateFormulaIndex = new HashSet<>();
        dateFormulaIndex.add(7);
        dateFormulaIndex.add(8);
        
        ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);
        excel.setStartDate("2021-01-01"); // 最小日期
        excel.setEndDate("2025-12-31"); // 最大日期
        excel.setDateFormulaIndex(dateFormulaIndex);

        SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);
		// 使用io导出workbook 就可以了

2.5 锁定指定列禁止修改导出

		// 默认使用了"yyyy-MM-dd"日期格式，如有需要请自行修改:ExcelTemplateUtils.getDateDataValidation()
		// 文件名
        String fileName = "历史" + System.currentTimeMillis() + ".xlsx";

        // 标题行（下标即列的位置）
        List<String> titles = getTitles2();
        // 数据集合（map为一行的数据，map的key即列的下标）
        List<Map<Integer, String>> dataList = getDataList2(list);

        // 禁止修改列的下标
        Set<Integer> lockColumnIndex= new HashSet<>();
        lockColumnIndex.add(5);
        lockColumnIndex.add(6);
        
        ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);
        excel.setLockColumnIndex(lockColumnIndex);

        SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);
		// 使用io导出workbook 就可以了

---