EasyPoi大数据导出下拉列表错误 can‘t be bigger than 255 characters ASCII
站长
· 阅读数 26
背景
老项目中，为保证导入和导出的数据一致性，避免用户输入和输出数据不一致，导出时采用动态指定下拉列表业务数据供给客户选择后并导入，但Poi本身有自己的限制，对于少量数据的下拉列表可以使用EasyPoi所提供的@Excel中的dict属性和addressList属性实现IExcelDictHandler来实现，但是对于大量数据的下拉列表则会报错
具体错误信息
String literals in formulas can't be bigger than 255 characters ASCII
解决之前代码
Excel Vo对象
package org.jeecg.assemble.beta.pitfall.entity.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;
@Data
public class PitfallInspectionExcelVo {
/**检查日期*/
@Excel(name = "检查日期", format = "yyyy-MM-dd", width = 20)
private LocalDate checkDate;
/**责任单位*/
@Excel(name = "责任单位", dict = "sysDepart", addressList = true, width = 20)
private String responsibleDeptId;
/**
* 检查内容和整改建议
*/
@Excel(name = "检查内容和整改建议", width = 15)
@ApiModelProperty(value = "检查内容和整改建议")
private String rectificationSuggestions;
/**
* 信息分类
*/
@Excel(name = "信息分类", dict = "hazardType", addressList = true, width = 20)
@ApiModelProperty(value = "隐患分类")
private String hazardType;
/**整改期限*/
private Date rectificationDate;
/**整改期限（天）*/
@Excel(name = "整改期限（天）", width = 20)
private String rectificationDateDay;
/**整改单位负责人*/
@Excel(name = "整改单位负责人", dict = "sysUser", addressList=true, width = 20)
private String workManager;
/** 整改单位负责人名字 */
private String workManagerName;
/**考核类型*/
@Excel(name = "考核类型", dict = "rpType", addressList = true, width = 20)
private String type;
/**考核数额（元）*/
@Excel(name = "考核数额（元）", width = 20)
private String money;
/**
* 隐患等级
*/
@Excel(name = "隐患等级", dict = "hazardLevel", addressList = true, width = 20)
@ApiModelProperty(value = "隐患等级")
private String hazardLevel;
/**考核单位*/
@Excel(name = "考核单位", dict = "sysDepart", addressList = true, width = 20)
private String examineDeptId;
/**属地单位*/
@Excel(name = "属地单位", dict = "sysDepart", addressList = true, width = 20)
private String sdDeptId;
// /**
// * 问题记录
// */
// @Excel(name = "问题记录", width = 15)
// @ApiModelProperty(value = "问题记录")
// private String problemRecord;
}
此业务，用户数据为下拉列表的大量数据
导出处理器 NormalExcelHandler
package org.jeecg.assemble.beta.pitfall.handler;
import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import org.jeecg.beta.api.ISysBaseExtendAPI;
import org.jeecg.beta.api.ISysDepartAPI;
import org.jeecg.beta.api.ISysDictAPI;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.entity.beta.system.SysDepart;
import org.jeecg.common.entity.beta.system.SysDictItem;
import org.jeecg.common.entity.beta.system.SysUser;
import org.jeecg.common.util.SpringContextUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class NormalExcelHandler implements IExcelDictHandler {
private static List<SysDepart> sysDepartList;
private static Map<String, String> departKeyValueMap;
private static Map<String, String> departValueKeyMap;
private static List<SysDictItem> hazardTypeList;
private static Map<String, String> hazardTypeKeyValueMap;
private static Map<String, String> hazardTypeValueKeyMap;
private static List<SysDictItem> hazardLevelList;
private static Map<String, String> hazardLevelKeyValueMap;
private static Map<String, String> hazardLevelValueKeyMap;
private static List<SysUser> sysUserList;
private static Map<String, String> sysUserKeyValueMap;
private static Map<String, String> sysUserValueKeyMap;
private static List<String> rpTypeList;
private static Map<String, String> rpTypeKeyValueMap;
private static Map<String, String> rpTypeValueKeyMap;
static {
ISysDepartAPI iSysDepartAPI = SpringContextUtils.getBean(ISysDepartAPI.class);
Result<List<SysDepart>> sysDepartResult = iSysDepartAPI.queryListByParentId("c6d7cb4deeac411cb3384b1b31278596");
List<SysDepart> departList = sysDepartResult.getResult();
sysDepartList = CollUtil.newArrayList(departList);
departKeyValueMap = departList.stream().collect(Collectors.toMap(SysDepart::getId, SysDepart::getDepartName));
departValueKeyMap = departList.stream().collect(Collectors.toMap(SysDepart::getDepartName, SysDepart::getId));
ISysDictAPI iSysDictAPI = SpringContextUtils.getBean(ISysDictAPI.class);
Result<List<SysDictItem>> hazardTypeResult = iSysDictAPI.itemList("hidden_dangers");
List<SysDictItem> hazardTypeTmpList = hazardTypeResult.getResult();
hazardTypeList = CollUtil.newArrayList(hazardTypeTmpList);
hazardTypeKeyValueMap = hazardTypeTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemValue, SysDictItem::getItemText));
hazardTypeValueKeyMap = hazardTypeTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemText, SysDictItem::getItemValue));
Result<List<SysDictItem>> hazardLevelResult = iSysDictAPI.itemList("danger_classification");
List<SysDictItem> hazardLevelTmpList = hazardLevelResult.getResult();
hazardLevelList = CollUtil.newArrayList(hazardLevelTmpList);
hazardLevelKeyValueMap = hazardLevelTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemValue, SysDictItem::getItemText));
hazardLevelValueKeyMap = hazardLevelTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemText, SysDictItem::getItemValue));
ISysBaseExtendAPI iSysBaseExtendAPI = SpringContextUtils.getBean(ISysBaseExtendAPI.class);
List<SysUser> sysUserTmpList = iSysBaseExtendAPI.getAllUser();
sysUserList = CollUtil.newArrayList(sysUserTmpList);
sysUserKeyValueMap = sysUserTmpList.stream().collect(Collectors.toMap(SysUser::getId, SysUser::getRealname));
sysUserValueKeyMap = sysUserTmpList.stream().collect(Collectors.toMap(SysUser::getRealname, SysUser::getId));
}
/**
* 自定义dict下拉列表数据
*/
@Override
public List<Map> getList(String dict) {
// 部门下拉列表
if("sysDepart".equals(dict)){
if(CollUtil.isNotEmpty(sysDepartList)) {
List<Map> dictListMap = sysDepartList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getId());
map.put("dictValue", i.getDepartName());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 信息分类下拉列表
} else if("hazardType".equals(dict)){
if(CollUtil.isNotEmpty(hazardTypeList)) {
List<Map> dictListMap = hazardTypeList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getItemValue());
map.put("dictValue", i.getItemText());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 隐患等级下拉列表
} else if("hazardLevel".equals(dict)){
if(CollUtil.isNotEmpty(hazardLevelList)) {
List<Map> dictListMap = hazardLevelList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getItemValue());
map.put("dictValue", i.getItemText());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 用户下拉列表（此为大数据）
} else if("sysUser".equals(dict)){
if(CollUtil.isNotEmpty(sysUserList)) {
List<Map> dictListMap = sysUserList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getId());
map.put("dictValue", i.getRealname());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 考核类型下拉列表
}else if("rpType".equals(dict)) {
List<Map> dictListMap = new ArrayList<>();
Map<String, String> rewardMap = new HashMap();
rewardMap.put("dictKey", "1");
rewardMap.put("dictValue", "奖励");
Map<String, String> punishmentMap = new HashMap();
punishmentMap.put("dictKey", "2");
punishmentMap.put("dictValue", "惩罚");
dictListMap.add(rewardMap);
dictListMap.add(punishmentMap);
return dictListMap;
}
return IExcelDictHandler.super.getList(dict);
}
/**
* 自定义下拉列表数据展示的名字（通常导出时会调用）
*/
@Override
public String toName(String s, Object o, String s1, Object o1) {
String value = (String) o1;
if("sysDepart".equals(s)){
if(StrUtil.isNotEmpty(departKeyValueMap.get(value))) {
return departKeyValueMap.get(value);
}
} else if("hazardType".equals(s)){
if(StrUtil.isNotEmpty(hazardTypeKeyValueMap.get(value))) {
return hazardTypeKeyValueMap.get(value);
}
} else if("hazardLevel".equals(s)){
if(StrUtil.isNotEmpty(hazardLevelKeyValueMap.get(value))) {
return hazardLevelKeyValueMap.get(value);
}
}else if("sysUser".equals(s)){
if(StrUtil.isNotEmpty(sysUserKeyValueMap.get(value))) {
return sysUserKeyValueMap.get(value);
}
} else if("rpType".equals(s)){
switch (value){
case "1":
return "奖励";
case "2":
return "惩罚";
}
}
return "";
}
/**
* 自定义下拉列表数据真实数值（通常导入时会调用）
*/
@Override
public String toValue(String s, Object o, String s1, Object o1) {
String value = (String) o1;
if("sysDepart".equals(s)){
if(StrUtil.isNotEmpty(departValueKeyMap.get(value))) {
return departValueKeyMap.get(value);
}
}else if("hazardType".equals(s)){
if(StrUtil.isNotEmpty(hazardTypeValueKeyMap.get(value))) {
return hazardTypeValueKeyMap.get(value);
}
} else if("hazardLevel".equals(s)){
if(StrUtil.isNotEmpty(hazardLevelValueKeyMap.get(value))) {
return hazardLevelValueKeyMap.get(value);
}
} else if("sysUser".equals(s)){
if(StrUtil.isNotEmpty(sysUserValueKeyMap.get(value))) {
return sysUserValueKeyMap.get(value);
}
}else if("rpType".equals(s)){
switch (value){
case "奖励":
return "1";
case "惩罚":
return "2";
}
}
return "";
}
}
Controller 导出逻辑代码
/**
* 下载excel模板
*
* @return
*/
@RequestMapping(value = "/downloadPitfallInspectionExcelTemplate", method = RequestMethod.GET)
public void downloadPitfallInspectionExcelTemplate(@RequestParam Map<String, Object> params, HttpServletResponse response) {
String group = (String) params.get("group");
// 标题
StringBuilder titleBuilder = new StringBuilder("");
titleBuilder.append(group).append("_");
titleBuilder.append("小组检查表");
List<PitfallInspectionExcelVo> dataList = new ArrayList<>();
// 导出参数
ExportParams exportParams = new ExportParams(titleBuilder.toString(), "sheet0");
// 给参数添加NormalExcelHandler 导出处理器
exportParams.setDictHandler(new NormalExcelHandler());
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, PitfallInspectionExcelVo.class, dataList);
// 输出流响应
OutputStream outputStream = null;
try {
String fileName = titleBuilder.toString();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"ISO-8859-1"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
// // ModelAndView导出方式（不推荐）
// ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
// mv.addObject(NormalExcelConstants.FILE_NAME, titleBuilder.toString()+".xlsx"); // 文件名称，但是前端会重新命名
// mv.addObject(NormalExcelConstants.CLASS, PitfallInspectionExcelVo.class);
// mv.addObject(NormalExcelConstants.PARAMS, new ExportParams(titleBuilder.toString(), "sheet1")); // 标题，sheet名称
// mv.addObject(NormalExcelConstants.DATA_LIST, new ArrayList<>()); // list数据
// return mv;
}
解决思路
- （1）在导出时，专门新建一个
sheet页来存储此下拉列表所需的大量业务数据 - （2）主
sheet页的下拉列表使用setRefersToFormula引用其存储数据的sheet页中的单元格区域
解决之后的代码
Excel Vo对象
注意dict和addressList的配合和复用
package org.jeecg.assemble.beta.pitfall.entity.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;
@Data
public class PitfallInspectionExcelVo {
/**检查日期*/
@Excel(name = "检查日期", format = "yyyy-MM-dd", width = 20)
private LocalDate checkDate;
/**责任单位*/
@Excel(name = "责任单位", dict = "sysDepart", addressList = true, width = 20)
private String responsibleDeptId;
/**
* 检查内容和整改建议
*/
@Excel(name = "检查内容和整改建议", width = 15)
@ApiModelProperty(value = "检查内容和整改建议")
private String rectificationSuggestions;
/**
* 信息分类
*/
@Excel(name = "信息分类", dict = "hazardType", addressList = true, width = 20)
@ApiModelProperty(value = "隐患分类")
private String hazardType;
/**整改期限*/
// @Excel(name = "整改期限", format = "yyyy-MM-dd", width = 20)
private Date rectificationDate;
/**整改期限（天）*/
@Excel(name = "整改期限（天）", width = 20)
private String rectificationDateDay;
/**整改单位负责人*/
@Excel(name = "整改单位负责人", dict = "sysUser", width = 20) //注意：这里不加addressList属性，因为不需要从导出处理器的getList获取下拉数据，但是还使用了dict，因为需要导入的toValue方法映射真实数据值
private String workManager;
/** 整改单位负责人名字 */
private String workManagerName;
/**考核类型*/
@Excel(name = "考核类型", dict = "rpType", addressList = true, width = 20)
private String type;
/**考核数额（元）*/
@Excel(name = "考核数额（元）", width = 20)
private String money;
/**
* 隐患等级
*/
@Excel(name = "隐患等级", dict = "hazardLevel", addressList = true, width = 20)
@ApiModelProperty(value = "隐患等级")
private String hazardLevel;
/**考核单位*/
@Excel(name = "考核单位", dict = "sysDepart", addressList = true, width = 20)
private String examineDeptId;
/**属地单位*/
@Excel(name = "属地单位", dict = "sysDepart", addressList = true, width = 20)
private String sdDeptId;
// /**
// * 问题记录
// */
// @Excel(name = "问题记录", width = 15)
// @ApiModelProperty(value = "问题记录")
// private String problemRecord;
}
导出处理器 NormalExcelHandler
去掉getList中关于dict="sysUser"的逻辑判断因为不需要走这里获取下拉列表数据了
去掉toName中关于dict="sysUser"的逻辑判断因为不需要导出时处理展示的名字了
package org.jeecg.assemble.beta.pitfall.handler;
import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import org.jeecg.beta.api.ISysBaseExtendAPI;
import org.jeecg.beta.api.ISysDepartAPI;
import org.jeecg.beta.api.ISysDictAPI;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.entity.beta.system.SysDepart;
import org.jeecg.common.entity.beta.system.SysDictItem;
import org.jeecg.common.entity.beta.system.SysUser;
import org.jeecg.common.util.SpringContextUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
public class NormalExcelHandler implements IExcelDictHandler {
private static List<SysDepart> sysDepartList;
private static Map<String, String> departKeyValueMap;
private static Map<String, String> departValueKeyMap;
private static List<SysDictItem> hazardTypeList;
private static Map<String, String> hazardTypeKeyValueMap;
private static Map<String, String> hazardTypeValueKeyMap;
private static List<SysDictItem> hazardLevelList;
private static Map<String, String> hazardLevelKeyValueMap;
private static Map<String, String> hazardLevelValueKeyMap;
private static List<SysUser> sysUserList;
private static Map<String, String> sysUserKeyValueMap;
private static Map<String, String> sysUserValueKeyMap;
private static List<String> rpTypeList;
private static Map<String, String> rpTypeKeyValueMap;
private static Map<String, String> rpTypeValueKeyMap;
static {
ISysDepartAPI iSysDepartAPI = SpringContextUtils.getBean(ISysDepartAPI.class);
Result<List<SysDepart>> sysDepartResult = iSysDepartAPI.queryListByParentId("c6d7cb4deeac411cb3384b1b31278596");
List<SysDepart> departList = sysDepartResult.getResult();
sysDepartList = CollUtil.newArrayList(departList);
departKeyValueMap = departList.stream().collect(Collectors.toMap(SysDepart::getId, SysDepart::getDepartName));
departValueKeyMap = departList.stream().collect(Collectors.toMap(SysDepart::getDepartName, SysDepart::getId));
ISysDictAPI iSysDictAPI = SpringContextUtils.getBean(ISysDictAPI.class);
Result<List<SysDictItem>> hazardTypeResult = iSysDictAPI.itemList("hidden_dangers");
List<SysDictItem> hazardTypeTmpList = hazardTypeResult.getResult();
hazardTypeList = CollUtil.newArrayList(hazardTypeTmpList);
hazardTypeKeyValueMap = hazardTypeTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemValue, SysDictItem::getItemText));
hazardTypeValueKeyMap = hazardTypeTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemText, SysDictItem::getItemValue));
Result<List<SysDictItem>> hazardLevelResult = iSysDictAPI.itemList("danger_classification");
List<SysDictItem> hazardLevelTmpList = hazardLevelResult.getResult();
hazardLevelList = CollUtil.newArrayList(hazardLevelTmpList);
hazardLevelKeyValueMap = hazardLevelTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemValue, SysDictItem::getItemText));
hazardLevelValueKeyMap = hazardLevelTmpList.stream().collect(Collectors.toMap(SysDictItem::getItemText, SysDictItem::getItemValue));
// 报错 String literals in formulas can‘t be bigger than 255 characters ASCII
ISysBaseExtendAPI iSysBaseExtendAPI = SpringContextUtils.getBean(ISysBaseExtendAPI.class);
List<SysUser> sysUserTmpList = iSysBaseExtendAPI.getAllUser();
sysUserList = CollUtil.newArrayList(sysUserTmpList);
sysUserKeyValueMap = sysUserTmpList.stream().collect(Collectors.toMap(SysUser::getId, SysUser::getRealname));
sysUserValueKeyMap = sysUserTmpList.stream().collect(Collectors.toMap(SysUser::getRealname, SysUser::getId));
}
@Override
public List<Map> getList(String dict) {
// 部门下拉列表
if("sysDepart".equals(dict)){
if(CollUtil.isNotEmpty(sysDepartList)) {
List<Map> dictListMap = sysDepartList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getId());
map.put("dictValue", i.getDepartName());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 信息分类下拉列表
} else if("hazardType".equals(dict)){
if(CollUtil.isNotEmpty(hazardTypeList)) {
List<Map> dictListMap = hazardTypeList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getItemValue());
map.put("dictValue", i.getItemText());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
// 隐患等级下拉列表
} else if("hazardLevel".equals(dict)){
if(CollUtil.isNotEmpty(hazardLevelList)) {
List<Map> dictListMap = hazardLevelList.stream().map(i-> {
Map<String, String> map = new HashMap();
map.put("dictKey", i.getItemValue());
map.put("dictValue", i.getItemText());
return map;
}).collect(Collectors.toList());
return dictListMap;
}
}
// 去掉sysUser的getList逻辑
// 报错 String literals in formulas can‘t be bigger than 255 characters ASCII
// else if("sysUser".equals(dict)){
// if(CollUtil.isNotEmpty(sysUserList)) {
// List<Map> dictListMap = sysUserList.stream().map(i-> {
// Map<String, String> map = new HashMap();
// map.put("dictKey", i.getId());
// map.put("dictValue", i.getRealname());
// return map;
// }).collect(Collectors.toList());
//
// return dictListMap;
// }
// }
// 考核类型下拉列表
else if("rpType".equals(dict)) {
List<Map> dictListMap = new ArrayList<>();
Map<String, String> rewardMap = new HashMap();
rewardMap.put("dictKey", "1");
rewardMap.put("dictValue", "奖励");
Map<String, String> punishmentMap = new HashMap();
punishmentMap.put("dictKey", "2");
punishmentMap.put("dictValue", "惩罚");
dictListMap.add(rewardMap);
dictListMap.add(punishmentMap);
return dictListMap;
}
return IExcelDictHandler.super.getList(dict);
}
@Override
public String toName(String s, Object o, String s1, Object o1) {
String value = (String) o1;
if("sysDepart".equals(s)){
if(StrUtil.isNotEmpty(departKeyValueMap.get(value))) {
return departKeyValueMap.get(value);
}
} else if("hazardType".equals(s)){
if(StrUtil.isNotEmpty(hazardTypeKeyValueMap.get(value))) {
return hazardTypeKeyValueMap.get(value);
}
} else if("hazardLevel".equals(s)){
if(StrUtil.isNotEmpty(hazardLevelKeyValueMap.get(value))) {
return hazardLevelKeyValueMap.get(value);
}
} else if("rpType".equals(s)){
switch (value){
case "1":
return "奖励";
case "2":
return "惩罚";
}
}
return "";
}
@Override
public String toValue(String s, Object o, String s1, Object o1) {
String value = (String) o1;
if("sysDepart".equals(s)){
if(StrUtil.isNotEmpty(departValueKeyMap.get(value))) {
return departValueKeyMap.get(value);
}
}else if("hazardType".equals(s)){
if(StrUtil.isNotEmpty(hazardTypeValueKeyMap.get(value))) {
return hazardTypeValueKeyMap.get(value);
}
} else if("hazardLevel".equals(s)){
if(StrUtil.isNotEmpty(hazardLevelValueKeyMap.get(value))) {
return hazardLevelValueKeyMap.get(value);
}
} else if("sysUser".equals(s)){
if(StrUtil.isNotEmpty(sysUserValueKeyMap.get(value))) {
return sysUserValueKeyMap.get(value);
}
}else if("rpType".equals(s)){
switch (value){
case "奖励":
return "1";
case "惩罚":
return "2";
}
}
return "";
}
}
Controller 导出逻辑代码
这里主要增加以下代码，用来生成新的sheet来存储用户数据，并创建引用其存储的用户数据区域
...
// ========= BEGIN 处理报错：String literals in formulas can‘t be bigger than 255 characters ASCII ================
String sheetName = "sysUser";
List<SysUser> sysUserTmpList = iSysBaseExtendAPI.getAllUser();
String sheetNames = sysUserTmpList.stream().map(SysUser::getRealname).collect(Collectors.joining(","));
String[] sheetNameArr = sheetNames.split(",");
//create hidden sheet
Sheet hidden = workbook.createSheet(sheetName);
Cell cell =null;
for (int i = 0; i <sheetNameArr.length ; i++) {
String name = sheetNameArr[i];
Row row = hidden.createRow(i);
cell =row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = workbook.createName();
namedCell.setNameName(sheetName);
namedCell.setRefersToFormula(sheetName+"!$A$1:$A$"+sheetNameArr.length);
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(sheetName);
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, 5, 5);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
// workbook.setSheetHidden(1,true);
workbook.getSheetAt(0).addValidationData(dataValidation);
// ========= END 处理报错：String literals in formulas can‘t be bigger than 255 characters ASCII ================
...
完整代码
/**
* 下载excel模板
*
* @return
*/
@RequestMapping(value = "/downloadPitfallInspectionExcelTemplate", method = RequestMethod.GET)
public void downloadPitfallInspectionExcelTemplate(@RequestParam Map<String, Object> params, HttpServletResponse response) {
String group = (String) params.get("group");
//标题
StringBuilder titleBuilder = new StringBuilder("");
titleBuilder.append(group).append("_");
titleBuilder.append("小组检查表");
List<PitfallInspectionExcelVo> dataList = new ArrayList<>();
// 导出参数
ExportParams exportParams = new ExportParams(titleBuilder.toString(), "sheet0");
// 给参数添加NormalExcelHandler 导出处理器
exportParams.setDictHandler(new NormalExcelHandler());
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, PitfallInspectionExcelVo.class, dataList);
// 解决大数据导出下拉列表的问题
// ========= BEGIN 处理报错：String literals in formulas can‘t be bigger than 255 characters ASCII ================
String sheetName = "sysUser";
List<SysUser> sysUserTmpList = iSysBaseExtendAPI.getAllUser();
String sheetNames = sysUserTmpList.stream().map(SysUser::getRealname).collect(Collectors.joining(","));
String[] sheetNameArr = sheetNames.split(",");
//create hidden sheet
Sheet hidden = workbook.createSheet(sheetName);
Cell cell =null;
for (int i = 0; i <sheetNameArr.length ; i++) {
String name = sheetNameArr[i];
Row row = hidden.createRow(i);
cell =row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = workbook.createName();
namedCell.setNameName(sheetName);
namedCell.setRefersToFormula(sheetName+"!$A$1:$A$"+sheetNameArr.length);
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(sheetName);
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, 5, 5);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
// workbook.setSheetHidden(1,true);
workbook.getSheetAt(0).addValidationData(dataValidation);
// ========= END 处理报错：String literals in formulas can‘t be bigger than 255 characters ASCII ================
// 输出流响应
OutputStream outputStream = null;
try {
String fileName = titleBuilder.toString();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"ISO-8859-1"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
// // ModelAndView导出方式（不推荐）
// ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
// mv.addObject(NormalExcelConstants.FILE_NAME, titleBuilder.toString()+".xlsx"); // 文件名称，但是前端会重新命名
// mv.addObject(NormalExcelConstants.CLASS, PitfallInspectionExcelVo.class);
// mv.addObject(NormalExcelConstants.PARAMS, new ExportParams(titleBuilder.toString(), "sheet1")); // 标题，sheet名称
// mv.addObject(NormalExcelConstants.DATA_LIST, new ArrayList<>()); // list数据
// return mv;
}
最终效果
主sheet
存储下拉数据的sheet（sysUser）
结语
关于这块内容，可以封装一个通用的util方法，方便使用~这里懒了望伙计们海涵AQA
需要注意的是：
如果在一个导出处理器处理了相同
dict的列，可以公用，不需要再创建一个新的导出处理器来重复处理；如果导出参数配置了导出处理器，想只要导入导出时对列值（只使用
toName和toValue）的进行处理，而不需要下拉列表的数据也从导出处理器的getList方法中获取，则可以不配置addressList，只配置dict即可；
又是码代码的一天~
【彩蛋】导出下拉列表的两种简单方式
P1 方案一：replace+addressList
第一种方案是最简单的实现方案，只需要添加一些配置即可：
@Excel(name = "性别",replace = { "男生_1", "女生_2" }, addressList = true)
主要是replace和addressList这两个属性配置。
导出结果：
在这里要注意下，这里就不能使用suffix这个属性了，如果是以下这个写法：
@Excel(name = "性别",replace = { "男_1", "女_2" }, suffix = "生", addressList = true)
导出结果：
这里的suffix并不能作为下拉的选项后缀，这个是否是框架应该优化的一个点呢~
P2 方案二：dict+addressList
这种方案是使用属性dict，指定一个key，这里的key的逻辑是要实现一个接口IExcelDictHandler进行处理的。
（1）在注解中添加属性dict：
@Excel(name = "性别",dict = "sex",addressList = true)
（2）实现接口IExcelDictHandler，添加dict=sex的字段信息：
package com.example.config;
（3）在导出的方法中，添加字段处理类：
ExportParams exportParams = new ExportParams(title, sheetName,ExcelType.XSSF);
这里核心的代码是：
exportParams.setDictHandler(new ExcelDiceAddressListHandlerImpl());
导出结果：
P3 隐藏的方案三
这里思考一下，一种使用
dict/addressList + 实现IExportHandler + 建立新sheet存储下拉列表数据并引用的方式，是不是可以是第三种方案呢~当然，它能够处理大量数据的下拉列表，上限更高
AQA
特别鸣谢：知乎博主-悟纤
转载自:https://juejin.cn/post/7408147106890104859