Excel XSSF 添加下拉框
1. 代码效果
![](https://img2022.cnblogs.com/blog/1294391/202210/1294391-20221019140310836-615478231.png)
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>
如果文章对您有所帮助,可以点一下推荐