需求背景:
业务系统需要做EXCEL的导入和导出功能,某些字段是自定义下拉选择,需要从数据库中获取后,写入excel中,当下拉数量为十几个时是可以正常显示的,但是超过50个就不显示了,折腾了一早上,也没有报错信息,只能设置断点一步一步查看,结果也没有异常,百度查看是因为poi导出限制了,下面是我的一些解决经验分享给大家
方法:总体思路就是创建一个隐藏的sheet用来存放下拉选择的数据,再引入到原来的表格当中实现下拉选择
1.在原有的ExcelUtil中增加createDropDownExpandList方法
/**
* 创建下拉列表选项【适用长度超过255,或者下拉框数量超过50】
* @param workbook
* @param values 下拉框的选项值
* @param firstRow 起始行(从0开始)
* @param lastRow 终止行(从0开始)
* @param firstCol 起始列(从0开始)
* @param lastCol 终止列(从0开始)
*/
public static void createDropDownExpandList(Workbook workbook, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
Sheet sheet = workbook.getSheetAt(0);
//创建名为"hidden"的新Sheet页
Sheet hidden = workbook.createSheet("hidden");
//遍历选项值填充到"hidden"页的第一列
Cell cell = null;
for (int i = 0; i < values.length; i++) {
String value = values[i];
Row row = hidden.createRow(i); //第i行
cell = row.createCell(0); //第1列
cell.setCellValue(value);
}
//指定"hidden"页面选项值的坐标,上面均填充到第一列 所以从A1开始到A values的个数
Name name = workbook.createName();
name.setNameName("hidden");
name.setRefersToFormula("hidden!$A$1:$A$" + values.length);
//将"hidden"的数据加载
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
//设置下拉框作用区域,起始行 终止行 起始列 终止列
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//设置第二个sheet页为隐藏
workbook.setSheetHidden(1, true);
// Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
2.在创建样式中增加相应的判断,我的这里是自定义的extendCombo,判断数量或者长度是否超过,超过就调用createDropDownExpandList新建一个隐藏的sheet,否则就不用
/**
* 创建表格样式
*/
public void setDataValidation(Excel attr, Row row, int column)
{
if (attr.name().indexOf("注:") >= 0)
{
sheet.setColumnWidth(column, 6000);
}
else
{
// 设置列宽
sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
}
if(StringUtils.isNotBlank(attr.extendCombo())){
if(extendCombos!=null && extendCombos.containsKey(attr.extendCombo())){
if (extendCombos.get(attr.extendCombo()).length > 15 || StringUtils.join(extendCombos.get(attr.extendCombo())).length() > 255) {
// 如果下拉数大于15或字符串长度大于255,则使用一个新sheet存储,避免生成的模板下拉值获取不到
createDropDownExpandList(wb, extendCombos.get(attr.extendCombo()), 1, 20000, column, column);
} else {
// 提示信息或只能选择不能输入的列内容.
setPromptOrValidation(sheet, extendCombos.get(attr.extendCombo()), attr.prompt(), 1, 20000, column, column);
}
}
}else if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0)
{
// 提示信息或只能选择不能输入的列内容. 100
setPromptOrValidation(sheet, attr.combo(), attr.prompt(), 1, 20000, column, column);
}else if(StringUtils.isNotEmpty(attr.dictType())){
//
String[] dictLabels = DictUtils.getDictLabelForImport(attr.dictType(), ";");
if(dictLabels!=null){
setPromptOrValidation(sheet, dictLabels, attr.prompt(), 1, 20000, column, column);
}
}
}
附:配置下拉项(导入用户信息,导入模板的部门项为下拉选择项)
在Excel.java中增加
/**
* 另一个类中的属性名称,支持多级获取,以小数点隔开
*/
public String targetAttr() default "";
/**
* 列表下拉选择,对应Map的key值
* @return
*/
public String extendCombo() default "";
在对应导入的实体类中增加部门对象
/**
* 所属公司
*/
@Excel(name = "所属公司", type = Excel.Type.EXPORT)
private String deptName;
/**
* 所属公司
*/
private Long deptId;
/** 部门对象 */
@Excels({
@Excel(name = "所属公司", targetAttr = "deptName", extendCombo = "dept", prompt = "必填", type = Excel.Type.IMPORT)
})
@Excel(type = Excel.Type.SHOW)
private SysDept dept;
在ExcelUtil中增加
/**
* 用于下拉列表选择 如部门下拉选择
*/
private Map<String, String[]> extendCombos;
public Map<String, String[]> getExtendCombos() {
return extendCombos;
}
public void setExtendCombos(Map<String, String[]> extendCombos) {
this.extendCombos = extendCombos;
}
配置好上面的之后,在导出模板的接口中增加获取部门信息,将信息传入excel中
@PostMapping("/export")
@ResponseBody
public AjaxResult export(StockBaseInfo stockBaseInfo) {
Map<String, String[]> map = new HashMap<>();
SysDept dept = new SysDept();
List<String> depts = deptService.selectSysDeptForImport(dept);
List<StockBaseInfo> list = stockBaseInfoService.selectStockBaseList(stockBaseInfo);
ExcelUtil<StockBaseInfo> util = new ExcelUtil<StockBaseInfo>(StockBaseInfo.class);
if(StringUtils.isNotEmpty(depts)) {
String[] arr = new String[depts.size()];
map.put("dept", depts.toArray(arr));
}
util.setExtendCombos(map);
return util.exportExcel(list, "信息数据");
}