在 java中当导出的excel数据存在下拉框的时,在下拉框的数据长度较小的情况下可以使用简单的下拉框导出模式
private void createDropDownList(final HSSFWorkbook wb,
final HSSFSheet sheet,
final List<String> list,
final int firstRow,
final int lastRow,
final int firstColumn,
final int lastColumn) {
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstColumn, lastColumn);
String[] options = list.subList(0, list.size() - 1).toArray(new String[0]);
DVConstraint constraint = DVConstraint.createExplicitListConstraint(options);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
}
但是当使用简单创建下拉选项的方式时 ·DVConstraint.createExplicitListConstraint(options); 方法里面会将数据下拉选项options 构建出一个以,分割的字符串 pt,addValidationData()方法将下拉框选项的校验装载入sheet时会校验pt的长度,而poi的默认最大长度是255,因此当pt的最大长度超过255时就会报"String literals in formulas can't be bigger than 255 characters ASCII"
因此在这种情况下,需要建立一个隐藏的sheet,并将这个sheet的一个数据与真实展示数据的sheet需要构建下拉选项的区域形成一个指向的映射关系,从而解决下拉数据过大问题
/**
*
* @param wb Excel簿
* @param sheet 展示数据的sheet
* @param param 需要设置下拉选项的参数
*/
@Override
protected void generateDropDownBox(HSSFWorkbook wb, HSSFSheet sheet, Object... param) {
MemberShiftExportParams exportParam = (MemberShiftExportParams) param[0];
List<String> shiftScheduleNameList = new ArrayList<>(exportParam.getSchedules().size() + 1);
int month = exportParam.getDimMonth() % 100;
int year = exportParam.getDimMonth() / 100;
Integer monthDays = DimDayUtil.toDimDay(DateUtil.getLastDay(year,month))%100;
for (Schedule schedule : exportParam.getSchedules()) {
shiftScheduleNameList.add(schedule.getName());
}
// 下拉框补充‘休息’
String rest = i18nUtil.getMessage("member.shift.rest");
shiftScheduleNameList.add(rest);
/**
* 初始化参数
*/
int firstColumn = 4;
int startRow = 4; // 开始行
String[] strs = shiftScheduleNameList.toArray(new String[0]);//下拉框数据
for (int i = 0; i < monthDays; i++) {
setValidationData(wb, sheet, firstColumn + i, strs);
}
}
/**
*
* @param wb poi wb
* @param sheet 展示数据的sheet页
* @param column 需要创建下拉选项的 列
* @param strs 下拉选项数据组
*/
private static void setValidationData(HSSFWorkbook wb, HSSFSheet sheet, int column, String... strs) {
String hiddenSheet = "categoryHidden" + column;
// cellNum = SpuEnu.CATEGORY_1.getNumber();
HSSFSheet category2Hidden = wb.createSheet(hiddenSheet); // 创建隐藏域
for (int i = 0, length = strs.length; i < length; i++) { // 循环赋值(为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组,将隐藏域加到结束行之后)
category2Hidden.createRow(sheet.getLastRowNum() + i).createCell(column).setCellValue(strs[i]);//column 代表需要
}
HSSFName categoryName = wb.createName();
categoryName.setNameName(hiddenSheet);
categoryName.setRefersToFormula(hiddenSheet + "!A4:A" + (sheet.getLastRowNum())); // 。以A4列开始A行数据获取下拉数组,以及结束行
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
CellRangeAddressList addressList = new CellRangeAddressList(4, sheet.getLastRowNum(), column, column);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
wb.setSheetHidden(wb.getSheetIndex(category2Hidden), true); // 1隐藏、0显示
sheet.addValidationData(validation);
}