Easyexcel导出带下拉框选项excel模板(解决下拉框超50个的问题)

1、为了避免excel下拉框选项过多会导致内容不显示(或者生成的时候报错:String literals in formulas can't be bigger than 255 characters ASCII easyexcel),将下拉框的内容都存储在另一个新建的固定的sheet页,再通过引用公式关联单元格的下拉框内容,从而形成能够存储多数值的下拉框。

2、导出代码(这里演示的是一次性导出多个模板,采用压缩包的形式下载,并且表头是动态的):

  1. /**
  2. * 导出模板
  3. * @param response
  4. * @param businessViewId
  5. */
  6. @Override
  7. public void exportTemplate(HttpServletResponse response, String businessViewId) throws IOException {
  8. String[] split = businessViewId.split(",");
  9. String zipName = "模板.zip";
  10. response.setCharacterEncoding("utf-8");
  11. response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
  12. response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(zipName, "UTF-8"));
  13. ServletOutputStream out = response.getOutputStream();
  14. ZipOutputStream zipOutputStream = new ZipOutputStream(out);
  15. for (String viewid : split) {
  16. List<BusinessField> field = baseMapper.findBusinessFieldByViewId(viewid);
  17. //根据表id查询父表id
  18. String parentId = this.findParentIdByViewId(viewid);
  19. List<BusinessField> parentFieldList = baseMapper.findBusinessFieldByViewId(parentId);
  20. //定义表头
  21. List<List<String>> headList = new ArrayList<>();
  22. //定义数据体
  23. List<List<Object>> dataList = new ArrayList<>();
  24. // 指定标红色的列
  25. List<Integer> columns = Arrays.asList();
  26. // 指定批注
  27. HashMap<Integer, String> annotationsMap = new HashMap<>();
  28. HashMap<Integer, List<String>> dropDownMap = new HashMap<>();
  29. //主表字段
  30. for (int i = 0;i<parentFieldList.size();i++){
  31. BusinessField businessField = parentFieldList.get(i);
  32. headList.add(Lists.newArrayList(businessField.getName()));
  33. if (StringUtils.isNotBlank(businessField.getControlType())){
  34. if (businessField.getControlType().contains("select")){
  35. List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());
  36. // 存储需要下拉框的值,这里的key是需要设置为下拉框的列数,value是下拉框的值,是list
  37. if (tDataDictionaries != null && tDataDictionaries.size()>0) {
  38. dropDownMap.put(i,tDataDictionaries);
  39. }
  40. }
  41. }
  42. }
  43. //子表字段
  44. for (int i = 0;i<field.size();i++){
  45. BusinessField businessField = field.get(i);
  46. headList.add(Lists.newArrayList(businessField.getName()));
  47. if (StringUtils.isNotBlank(businessField.getControlType())){
  48. if (businessField.getControlType().contains("select")){
  49. List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());
  50. // 存储需要下拉框的值,这里的key是需要设置为下拉框的列数,value是下拉框的值,是list
  51. if (tDataDictionaries != null && tDataDictionaries.size()>0){
  52. dropDownMap.put(i+parentFieldList.size(),tDataDictionaries);
  53. }
  54. }
  55. }
  56. }
  57. ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();
  58. //构建一个sheet页
  59. WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
  60. // TltleHandler titleHandler = new TltleHandler(columns, IndexedColors.RED.index,annotationsMap,dropDownMap);
  61. // ExayExcelUtils.writeExcelWithModel(response.getOutputStream(), dataList, headList, "sheet1", (CellWriteHandler) titleHandler);
  62. // 头的策略
  63. WriteCellStyle headWriteCellStyle = new WriteCellStyle();
  64. // 单元格策略
  65. WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
  66. // 初始化表格样式
  67. HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
  68. // SelectSheetWriteHandler(dropDownMap) 是设置下拉框的类
  69. WriteTable writeTable = EasyExcel.writerTable(0).head(headList).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new SelectSheetWriteHandler(dropDownMap)).needHead(Boolean.TRUE).build();
  70. excelWriter.write(dataList, writeSheet, writeTable);
  71. // 开始导出
  72. // excelWriterSheetBuilder.doWrite(dataList);
  73. Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
  74. //创建压缩文件
  75. String nameByid = baseMapper.getNameByid(viewid);
  76. ZipEntry zipEntry = new ZipEntry(nameByid+".xls");
  77. zipOutputStream.putNextEntry(zipEntry);
  78. //将excel对象以流的形式写入压缩流
  79. workbook.write(zipOutputStream);
  80. }
  81. zipOutputStream.flush();
  82. zipOutputStream.close();
  83. }

3、设置下拉框的类:

  1. package com.customization.BusinessFilIdExcel.utils;
  2. import com.alibaba.excel.write.handler.SheetWriteHandler;
  3. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  4. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
  5. import org.apache.poi.hssf.usermodel.HSSFDataValidation;
  6. import org.apache.poi.ss.usermodel.*;
  7. import org.apache.poi.ss.util.CellRangeAddressList;
  8. import java.util.List;
  9. import java.util.Map;
  10. public class SelectSheetWriteHandler implements SheetWriteHandler {
  11. private Map<Integer, List<String>> selectMap;
  12. private int index;
  13. private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
  14. 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
  15. public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap) {
  16. this.selectMap = selectMap;
  17. this.index = 0;
  18. }
  19. @Override
  20. public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  21. }
  22. @Override
  23. public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
  24. if (selectMap == null || selectMap.size() == 0) {
  25. return;
  26. }
  27. // 需要设置下拉框的sheet页
  28. Sheet curSheet = writeSheetHolder.getSheet();
  29. DataValidationHelper helper = curSheet.getDataValidationHelper();
  30. String dictSheetName = "字典sheet";
  31. Workbook workbook = writeWorkbookHolder.getWorkbook();
  32. // 数据字典的sheet页
  33. Sheet dictSheet = workbook.createSheet(dictSheetName);
  34. // 从第二个工作簿开始隐藏,为了用户的友好性,将字典sheet隐藏掉
  35. this.index++;
  36. // 设置隐藏
  37. workbook.setSheetHidden(this.index, true);
  38. for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
  39. // 设置下拉单元格的首行、末行、首列、末列
  40. CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());
  41. int rowLen = entry.getValue().size();
  42. // 设置字典sheet页的值 每一列一个字典项
  43. for (int i = 0; i < rowLen; i++) {
  44. Row row = dictSheet.getRow(i);
  45. if (row == null) {
  46. row = dictSheet.createRow(i);
  47. }
  48. row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
  49. }
  50. String excelColumn = getExcelColumn(entry.getKey());
  51. // 下拉框数据来源 eg:字典sheet!$B1:$B2
  52. String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
  53. // 创建可被其他单元格引用的名称
  54. Name name = workbook.createName();
  55. // 设置名称的名字
  56. name.setNameName("dict" + entry.getKey());
  57. // 设置公式
  58. name.setRefersToFormula(refers);
  59. // 设置引用约束
  60. DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
  61. // 设置约束
  62. DataValidation validation = helper.createValidation(constraint, rangeAddressList);
  63. if (validation instanceof HSSFDataValidation) {
  64. validation.setSuppressDropDownArrow(false);
  65. } else {
  66. validation.setSuppressDropDownArrow(true);
  67. validation.setShowErrorBox(true);
  68. }
  69. // 阻止输入非下拉框的值
  70. validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
  71. validation.createErrorBox("提示", "此值与单元格定义格式不一致!");
  72. // 添加下拉框约束
  73. writeSheetHolder.getSheet().addValidationData(validation);
  74. }
  75. }
  76. /**
  77. * 将数字列转化成为字母列
  78. *
  79. * @param num
  80. * @return
  81. */
  82. private String getExcelColumn(int num) {
  83. String column = "";
  84. int len = alphabet.length - 1;
  85. int first = num / len;
  86. int second = num % len;
  87. if (num <= len) {
  88. column = alphabet[num] + "";
  89. } else {
  90. column = alphabet[first - 1] + "";
  91. if (second == 0) {
  92. column = column + alphabet[len] + "";
  93. } else {
  94. column = column + alphabet[second - 1] + "";
  95. }
  96. }
  97. return column;
  98. }
  99. }

4、效果如下:

 

来源:https://blog.csdn.net/rjkkaikai/article/details/123448047
posted @ 2022-07-08 01:11  程序员小明1024  阅读(2537)  评论(0编辑  收藏  举报