Easyexcel导出带下拉框选项excel模板

2024-07-04 16:51| 来源: 网络整理| 查看: 265

需求：导出的excel模板，表头字段动态生成、sheet页下拉框内容动态生成

解决思路：为了避免excel下拉框选项过多会导致内容不显示，将下拉框的内容都存储在另一个新建的固定的sheet页，再通过引用公式关联单元格的下拉框内容。

maven依赖

com.alibaba easyexcel 2.2.7

导出代码

@RequestMapping("/download") public void download(HttpServletResponse response) throws IOException { String fileName = "test"; response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码当然和easyexcel没有关系 String fileNameEncode = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ExcelTypeEnum.XLS.getValue()); // 模拟下拉框内容 Map selectMap = new HashMap(); List sexList = new ArrayList(); sexList.add("男"); sexList.add("女"); selectMap.put(1, sexList); List typeList = new ArrayList(); typeList.add("股票"); typeList.add("基金"); typeList.add("债券"); selectMap.put(2, typeList); //模拟表头 List list = new ArrayList(); List field1 = new ArrayList(); List field2 = new ArrayList(); List field3 = new ArrayList(); field1.add("编号"); field2.add("性别"); field3.add("类型"); list.add(field1); list.add(field2); list.add(field3); EasyExcelFactory.write(response.getOutputStream()) .registerWriteHandler(new SelectSheetWriteHandler(selectMap)) .excelType(ExcelTypeEnum.XLS) .head(list) .sheet("测试sheet") .doWrite(new ArrayList()); }

下拉框设置代码

package com.test.excel; 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; /** * @author * @date 2021/12/20 */ public class SelectSheetWriteHandler implements SheetWriteHandler { private Map selectMap; 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 selectMap) { this.selectMap = selectMap; } @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); for (Map.Entry 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

【本文地址】

公司简介

联系我们