Easyexcel导出带下拉框选项excel模板(解决下拉框超50个的问题)
1、为了避免excel下拉框选项过多会导致内容不显示(或者生成的时候报错:String literals in formulas can't be bigger than 255 characters ASCII easyexcel),将下拉框的内容都存储在另一个新建的固定的sheet页,再通过引用公式关联单元格的下拉框内容,从而形成能够存储多数值的下拉框。
2、导出代码(这里演示的是一次性导出多个模板,采用压缩包的形式下载,并且表头是动态的):
- /**
- * 导出模板
- * @param response
- * @param businessViewId
- */
- @Override
- public void exportTemplate(HttpServletResponse response, String businessViewId) throws IOException {
- String[] split = businessViewId.split(",");
-
- String zipName = "模板.zip";
- response.setCharacterEncoding("utf-8");
- response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
- response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(zipName, "UTF-8"));
- ServletOutputStream out = response.getOutputStream();
- ZipOutputStream zipOutputStream = new ZipOutputStream(out);
-
- for (String viewid : split) {
-
- List<BusinessField> field = baseMapper.findBusinessFieldByViewId(viewid);
- //根据表id查询父表id
- String parentId = this.findParentIdByViewId(viewid);
- List<BusinessField> parentFieldList = baseMapper.findBusinessFieldByViewId(parentId);
- //定义表头
- List<List<String>> headList = new ArrayList<>();
- //定义数据体
- List<List<Object>> dataList = new ArrayList<>();
- // 指定标红色的列
- List<Integer> columns = Arrays.asList();
- // 指定批注
- HashMap<Integer, String> annotationsMap = new HashMap<>();
- HashMap<Integer, List<String>> dropDownMap = new HashMap<>();
- //主表字段
- for (int i = 0;i<parentFieldList.size();i++){
- BusinessField businessField = parentFieldList.get(i);
- headList.add(Lists.newArrayList(businessField.getName()));
- if (StringUtils.isNotBlank(businessField.getControlType())){
- if (businessField.getControlType().contains("select")){
- List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());
- // 存储需要下拉框的值,这里的key是需要设置为下拉框的列数,value是下拉框的值,是list
- if (tDataDictionaries != null && tDataDictionaries.size()>0) {
- dropDownMap.put(i,tDataDictionaries);
- }
- }
- }
- }
- //子表字段
- for (int i = 0;i<field.size();i++){
- BusinessField businessField = field.get(i);
- headList.add(Lists.newArrayList(businessField.getName()));
- if (StringUtils.isNotBlank(businessField.getControlType())){
- if (businessField.getControlType().contains("select")){
- List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());
- // 存储需要下拉框的值,这里的key是需要设置为下拉框的列数,value是下拉框的值,是list
- if (tDataDictionaries != null && tDataDictionaries.size()>0){
- dropDownMap.put(i+parentFieldList.size(),tDataDictionaries);
- }
- }
- }
- }
- ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();
- //构建一个sheet页
- WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
- // TltleHandler titleHandler = new TltleHandler(columns, IndexedColors.RED.index,annotationsMap,dropDownMap);
- // ExayExcelUtils.writeExcelWithModel(response.getOutputStream(), dataList, headList, "sheet1", (CellWriteHandler) titleHandler);
- // 头的策略
- WriteCellStyle headWriteCellStyle = new WriteCellStyle();
- // 单元格策略
- WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
- // 初始化表格样式
- HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
- // SelectSheetWriteHandler(dropDownMap) 是设置下拉框的类
- WriteTable writeTable = EasyExcel.writerTable(0).head(headList).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new SelectSheetWriteHandler(dropDownMap)).needHead(Boolean.TRUE).build();
- excelWriter.write(dataList, writeSheet, writeTable);
- // 开始导出
- // excelWriterSheetBuilder.doWrite(dataList);
- Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
- //创建压缩文件
- String nameByid = baseMapper.getNameByid(viewid);
- ZipEntry zipEntry = new ZipEntry(nameByid+".xls");
- zipOutputStream.putNextEntry(zipEntry);
-
- //将excel对象以流的形式写入压缩流
- workbook.write(zipOutputStream);
-
- }
-
- zipOutputStream.flush();
- zipOutputStream.close();
-
- }
3、设置下拉框的类:
- package com.customization.BusinessFilIdExcel.utils;
-
- import com.alibaba.excel.write.handler.SheetWriteHandler;
- import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
- import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
- import org.apache.poi.hssf.usermodel.HSSFDataValidation;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.ss.util.CellRangeAddressList;
-
- import java.util.List;
- import java.util.Map;
-
- public class SelectSheetWriteHandler implements SheetWriteHandler {
-
- private Map<Integer, List<String>> selectMap;
-
- private int index;
-
- private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
- 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
-
- public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap) {
- this.selectMap = selectMap;
- this.index = 0;
- }
-
- @Override
- public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
-
- }
-
- @Override
- public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
- if (selectMap == null || selectMap.size() == 0) {
- return;
- }
- // 需要设置下拉框的sheet页
- Sheet curSheet = writeSheetHolder.getSheet();
- DataValidationHelper helper = curSheet.getDataValidationHelper();
- String dictSheetName = "字典sheet";
-
- Workbook workbook = writeWorkbookHolder.getWorkbook();
-
- // 数据字典的sheet页
- Sheet dictSheet = workbook.createSheet(dictSheetName);
- // 从第二个工作簿开始隐藏,为了用户的友好性,将字典sheet隐藏掉
- this.index++;
- // 设置隐藏
- workbook.setSheetHidden(this.index, true);
- for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
- // 设置下拉单元格的首行、末行、首列、末列
- CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());
- int rowLen = entry.getValue().size();
- // 设置字典sheet页的值 每一列一个字典项
- for (int i = 0; i < rowLen; i++) {
- Row row = dictSheet.getRow(i);
- if (row == null) {
- row = dictSheet.createRow(i);
- }
- row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
- }
- String excelColumn = getExcelColumn(entry.getKey());
- // 下拉框数据来源 eg:字典sheet!$B1:$B2
- String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
- // 创建可被其他单元格引用的名称
- Name name = workbook.createName();
- // 设置名称的名字
- name.setNameName("dict" + entry.getKey());
- // 设置公式
- name.setRefersToFormula(refers);
- // 设置引用约束
- DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
- // 设置约束
- DataValidation validation = helper.createValidation(constraint, rangeAddressList);
- if (validation instanceof HSSFDataValidation) {
- validation.setSuppressDropDownArrow(false);
- } else {
- validation.setSuppressDropDownArrow(true);
- validation.setShowErrorBox(true);
- }
- // 阻止输入非下拉框的值
- validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
- validation.createErrorBox("提示", "此值与单元格定义格式不一致!");
- // 添加下拉框约束
- writeSheetHolder.getSheet().addValidationData(validation);
- }
-
- }
-
- /**
- * 将数字列转化成为字母列
- *
- * @param num
- * @return
- */
- private String getExcelColumn(int num) {
- String column = "";
- int len = alphabet.length - 1;
- int first = num / len;
- int second = num % len;
- if (num <= len) {
- column = alphabet[num] + "";
- } else {
- column = alphabet[first - 1] + "";
- if (second == 0) {
- column = column + alphabet[len] + "";
- } else {
- column = column + alphabet[second - 1] + "";
- }
- }
- return column;
-
- }
-
- }
4、效果如下: