Excel XSSF 添加下拉框

1. 代码效果

2. 示例代码

package com.example.demo.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.Objects;


public class ExportExcel {
	
	public static void main(String[] args) throws IOException {
		XSSFWorkbook workbook = new XSSFWorkbook();
		workbook.createSheet("下拉列表测试");
		int num = 50;
		String[] data = new String[num];
		for (int i = 0; i < num; i++) {
			data[i] = ("TEST1_TEST1" + i);
		}
		// 第1列添加下拉项
		setValid(workbook, data, 1,1,true);
		num = 80;
		data = new String[num];
		for (int i = 0; i < num; i++) {
			data[i] = ("TEST2_TEST2" + i);
		}
		// 第2列添加下拉项
		setValid(workbook, data, 1,2,true);
		FileOutputStream stream = new FileOutputStream("test.xlsx");
		workbook.write(stream);
		stream.close();
	}
	
	/**
	 * @param workbook workbook
	 * @param data     下拉项数据
	 * @param firstRow 需要添加下拉项的起始行下标, 默认添加到  firstRow-500行
	 * @param column   需要添加下拉项的列下标
	 * @param valid    是否禁止修改下拉项数据
	 */
	public static void setValid(Workbook workbook, String[] data, Integer firstRow, Integer column, Boolean valid) {
		Sheet dataSheet = workbook.getSheetAt(0);
		CellRangeAddressList addressList = new CellRangeAddressList(firstRow, 500, column, column);
		DataValidationHelper helper = dataSheet.getDataValidationHelper();
		DataValidationConstraint constraint = buildConstraint(workbook, data);
		// 关联上面的Name的名称
		DataValidation validation = helper.createValidation(constraint, addressList);
		// 是否显示下拉框箭头
		validation.setSuppressDropDownArrow(true);
		if (valid) {
			// 单元格值和下拉项不符合时提示的值
			validation.setShowErrorBox(true);
			validation.createErrorBox("珊瑚智造", "非下拉项中的字典值无法导入,请勿修改");
			/**
			 * 	DataValidation.ErrorStyle.STOP 提示框为红色,(重试,取消,帮助)选择重试继续编辑,选择取消恢复为选择的下拉项值
			 * 	DataValidation.ErrorStyle.WARNING 提示框为黄色,(是,否,取消,帮助)选择是可保存非下拉框值,选择否继续编辑,选择取消恢复为选择的下拉项值
			 * 	DataValidation.ErrorStyle.INFO 提示框为蓝色,(确定,取消,帮助)选择确定可保存非下拉框值
			 */
			validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
		}
		// 选择单元格时候显示的提示语
		validation.setShowPromptBox(true);
		validation.createPromptBox("XXX","请选择下拉项中的字典值");
		// 选择下拉项后是否允许清除单元格
		validation.setEmptyCellAllowed(false);
		dataSheet.addValidationData(validation);
	}
	
	/**
	 * 构建 DataValidationConstraint
	 * 数据总字符小于255时用官方api构建下拉项
	 * 大于255时使用隐藏sheet存储下拉项数据
	 *
	 * @param workbook workbook
	 * @param data     下拉项数据
	 * @return
	 */
	public static DataValidationConstraint buildConstraint(Workbook workbook, String[] data) {
		Sheet dataSheet = workbook.getSheetAt(0);
		DataValidationHelper dateSheetHelper = dataSheet.getDataValidationHelper();
		Arrays.stream(data).peek(po-> System.out.println(po.length()));
		int sum = Arrays.stream(data).mapToInt(String::length).sum();
		if (sum < 255) {
			// 下拉项总字符 < 255, 使用原生方式添加下拉框
			return dataSheet.getDataValidationHelper().createExplicitListConstraint(data);
		}
		// 通过隐藏sheet方式保存下拉项数据
		int total = workbook.getNumberOfSheets();
		Sheet validSheet;
		if (total < 2) {
			// 默认导出的Excel只有一个sheet,小于2表示未创建隐藏sheet,此时手动创建隐藏sheet
			validSheet = workbook.createSheet("数据验证");
			workbook.setSheetHidden(1, false);
		} else {
			// 隐藏sheet已创建,直接获取
			validSheet = workbook.getSheetAt(1);
		}
		// 本次在哪一列写入下拉项数据
		Integer validIndex = 0;
		boolean notFind = true;
		for (int i = 0, length = data.length; i < length; i++) {
			Row row = validSheet.getRow(i);
			if (Objects.isNull(row)){
				row = validSheet.createRow(i);
			}
			if (notFind){
				notFind = false;
				while (Objects.nonNull(row.getCell(validIndex))){
					validIndex++;
				}
			}
			row.createCell(validIndex).setCellValue(data[i]);
		}
		String validSheetName = validSheet.getSheetName();
		String nameName = validSheetName + validIndex;
		Name name = workbook.createName();
		// 设置Name的名称
		name.setNameName(nameName);
		//4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
		String reg = "!$%s$1:$%s$%d";
		char a = 'A';
		char c = (char) (a + validIndex);
		String format = String.format(reg, c, c, data.length);
		String formatName = validSheetName + format;
		// 设置引用哪个sheet的哪一列多少行的数据
		name.setRefersToFormula(formatName);
		// helper 由 dataSheet 创建, nameName 中包含了隐藏sheet的引用的下拉项信息, 以此关联二者
		return dateSheetHelper.createFormulaListConstraint(nameName);
	}
	
}

3. 依赖项

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>