设为首页收藏本站
开启辅助访问

easyexcel导出功能修改模板中的公式和下拉框获取

 您所在的位置：网站首页 › excel生成排名序号 › easyexcel导出功能修改模板中的公式和下拉框获取

easyexcel导出功能修改模板中的公式和下拉框获取

2023-06-17 02:32| 来源: 网络整理| 查看: 265

文章目录 步骤获取模板并创建临时模板不创建对象读模板信息写模板信息创建转换器创建写入时的处理器导出 补充知识点urlEncode和urlDecode：getResource和getResourceAsStream的区别： 需求描述：将原先的excel模板公式改成动态获取的公式，并导出具有级联关系的excel文件。 官网学习地址： 添加链接描述

步骤 获取模板并创建临时模板

a. 方法一：

// 获取target下的模板地址 String path = this.getClass().getClassLoader().getResource("").toURI().getPath(); URLDecoder.decode(path, "UTF-8"); File file = new File(path+"monitor_data_cx_relation.xlsx"); // 创建临时模板文件，模板文件一定要记得删除，不然缓存爆炸 File fileTemp = File.createTempFile(String.valueOf(System.currentTimeMillis()),".xlsx");

b. 方法二：

InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("monitor_data_cx_relation.xlsx");

方法一获取到的是本地c盘代码所在位置的target目录下的模板文件，如果上线会导致导出文件出现undefind.xlsx文件下载情况。

不创建对象读模板信息

继承AnalysisEventListener监听器，通过cachedDadaList存放excel表中的信息

package com.stec.promis.web.controller.rest.security; import cn.hutool.core.lang.Console; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.util.ListUtils; import com.alibaba.fastjson.JSON; import java.util.ArrayList; import java.util.List; import java.util.Map; public class MonitorDataCxRelationListener extends AnalysisEventListener { /** * 每隔5条存储数据库，实际使用中可以1000条，然后清理list ，方便内存回收 */ private static final int BATCH_Count = 10000; // integer：列值0，1，2；String表示值 private static List cachedDataList = new ArrayList(); public List getCachedDataList(){ return cachedDataList; } @Override public void invoke(Map integerStringMap, AnalysisContext analysisContext) { // Console.log("解析到数据:{}", JSON.toJSONString(integerStringMap)); cachedDataList.add(integerStringMap); if(cachedDataList.size() >= BATCH_Count){ saveData(); cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_Count); } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { saveData(); //Console.log("数据解析完"); } // 存储数据库 private void saveData(){ } }

每次读取前都会清一下static的缓存，防止重复添加excel数据

private List getCachedDataList(File file, Integer sheetNo) throws Exception{ List sheetNameList = ExcelTemplate.getInstance().readSheetName(new FileInputStream(file)); // 先清空，再读，防止静态值存在 new MonitorDataCxRelationListener().getCachedDataList().clear(); EasyExcel.read(file.getAbsolutePath(), new MonitorDataCxRelationListener()).sheet(sheetNameList.get(sheetNo)).headRowNumber(0).doRead(); List cachedDataList = new MonitorDataCxRelationListener().getCachedDataList(); return cachedDataList; } 写模板信息 ExcelWriter writer = EasyExcel.write(fileTemp.getAbsolutePath()).build(); // registerWriteHandler为创建的转换器 WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").head(MonitorDataCxRelaionExcelFormula.class).registerWriteHandler(new MonitorDataCxRelationRegister(fileTemp,sheetName,sheetNo)).build(); writer.write(monitorDataCxRelaionExcelFormulas,writeSheet); // write必须关闭文件流，如果EasyExcel.dowrite会自动关闭流 writer.finish();

实体类monitorDataCxRelaionExcelFormulas：

@ExcelProperty(value="序号") private String cx_id; @ExcelProperty(value="tenders_id",converter = MonitorDataCxConverter.class) private CellData tenders_id; @ExcelProperty(value="work_point_id",converter = MonitorDataCxConverter.class) private CellData work_point_id; @ExcelProperty(value="分层信息（分层名称,深度m）") private String depthInformation; 创建转换器

转换器继承了sheetWriteHandler和cellWriteHandler。 用来处理单元格的公式和创建sheet的值。 单元格的修改，公式添加，继承cellWriteHandler：

public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 读模板，根据项目号读取总行数，第一行为标题，所以跳过 if(cell.getRowIndex()>0) { // 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数 Map formulaIndex = null; try { formulaIndex = getFormulaIndex(); } catch (Exception e) { e.printStackTrace(); } // 首行去掉 int rowValue = cell.getRowIndex()+1; if (2 == cell.getColumnIndex()) { String cellFormula = excel中单元格的公式; cell.setCellFormula(cellFormula); } if (4 == cell.getColumnIndex()) { String cellFormula = "VLOOKUP(D"+rowValue+","+sheetName+"!$C$1:$D$"+formulaIndex.get(2)+",2,FALSE)"; cell.setCellFormula(cellFormula); } } }

下拉框的添加

// 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 65535,1 , 1); // 规则，v是下拉框的值 DataValidationConstraint provConstraint = helper.createExplicitListConstraint(v); DataValidation provinceDataValidation = helper.createValidation(provConstraint, provRangeAddressList); //验证 provinceDataValidation.createErrorBox("error", "请选择正确数据"); provinceDataValidation.setShowErrorBox(true); provinceDataValidation.setSuppressDropDownArrow(true); sheet.addValidationData(provinceDataValidation);

二级下拉框的添加，需要添加名称管理器，和间接引用INDIRECT（这里的名称管理器的二级目录是按行存储的） 总体代码如下

public class MonitorDataCxRelationRegister implements SheetWriteHandler,CellWriteHandler { // 下拉框的限制数量 private static final Integer LIMIT_NUMBER = 30; // 模板文件，包含模板值 private File file; // 哪一个表用来作为下拉框值的存储 private String sheetName; // 当前显示表名，由于重写文件的原因，必须得到模板文件的number号，否则找不到该sheetname private Integer sheetNo; public MonitorDataCxRelationRegister(File file, String sheetName,Integer sheetNo) { this.file = file; this.sheetName = sheetName; this.sheetNo = sheetNo; } // 用来做单元格公式的添加 @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 读模板，根据项目号读取总行数，第一行为标题，所以跳过 if(cell.getRowIndex()>0) { // 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数 Map formulaIndex = null; try { formulaIndex = getFormulaIndex(); } catch (Exception e) { e.printStackTrace(); } // 首行去掉 int rowValue = cell.getRowIndex()+1; if (2 == cell.getColumnIndex()) { String cellFormula = excel中单元格的公式; cell.setCellFormula(cellFormula); } if (4 == cell.getColumnIndex()) { String cellFormula = "VLOOKUP(D"+rowValue+","+sheetName+"!$C$1:$D$"+formulaIndex.get(2)+",2,FALSE)"; cell.setCellFormula(cellFormula); } } } private Map getFormulaIndex() throws Exception { // 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数 Map formulaIndex = new HashMap(); List cachedDataList = getCachedDataList(file, sheetNo); Integer i1=0,i2=0,i3=0; for (Map integerStringMap : cachedDataList) { if(ObjectUtils.isNotEmpty(integerStringMap.get(6))){ i1++; } if(ObjectUtils.isNotEmpty(integerStringMap.get(1))){ i2++; } if(ObjectUtils.isNotEmpty(integerStringMap.get(3))){ i3++; } } formulaIndex.put(0,i1); formulaIndex.put(1,i2); formulaIndex.put(2,i3); return formulaIndex; } // 在controller层创建sheet后的操作 @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { try { getAllValue(writeWorkbookHolder,writeSheetHolder); } catch (Exception e) { e.printStackTrace(); } } private void getAllValue(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) throws Exception { // 获取一个workbook Workbook workbook = writeWorkbookHolder.getWorkbook(); Sheet sheet = writeSheetHolder.getSheet(); // 设置下拉框 DataValidationHelper helper = sheet.getDataValidationHelper(); List headers = Arrays.asList("序号","标段","tenders_id","工点","work_point_id","基坑编码","测点编号","分层信息（分层名称,深度m）"); // 模板中隐藏表的值，Integer表示行（0开始），String保存值 List cachedDataList = getCachedDataList(file,sheetNo); // 一级菜单值 List fatherInfo = getFatherInfo(cachedDataList); // 存二级菜单的值，String为一级菜单的值 Map map = getSonValue(fatherInfo,cachedDataList); // 重写数据进新建的easyexcel其他模板中 List sheetNameList = ExcelTemplate.getInstance().readSheetName(new FileInputStream(file)); for (int i = 1; i Map fatherOption = cachedDataList.get(j); Row row = sheet_son.createRow(rowId++); int two_select_length = 0; for(int k = 0; k // 存二级目录的个数 two_select_length++; } } // 添加名称管理器,去掉带空格的值。这里可学习excel操作https://baijiahao.baidu.com/s?id=1630067593628190313 if(fatherOption.size() > 9) { String range = getRange(10, rowId, two_select_length); Name name = workbook.createName(); //key不可重复，必须和excel表中搜索名（名称管理器中的名称）对上，这样才能找到对应搜索 // 10为code值，名称管理器不能为空格，’-‘ // 重名的名称管理器加了’_v2‘表示，但是会导致查询不到 String replace_value = fatherOption.get(9).replace(" ", "_"); if(i > 1){ // 同名会报错 replace_value = replace_value + "_v2"; } name.setNameName(replace_value); String formula = sheetNameList.get(i)+"!" + range; name.setRefersToFormula(formula); } } // 要存的一级目录值 String[] v = fatherInfo.toArray(new String[]{}); // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 65535,1 , 1); // 如果下拉值总数大于30，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到 if (v.length > LIMIT_NUMBER) { // 列数k Integer k=1; //定义sheet的名称 //1.创建一个隐藏的sheet 名称为 sheet名 + k（防止多个sheet同时下拉框时多数据生成相同的sheetName） String sheetName = sheet.getSheetName() + "reflect"+i; Sheet hiddenSheet = workbook.createSheet(sheetName); for (int p = 0, length = v.length; p workbook.setSheetHidden(hiddenIndex, true); } } // 规则 DataValidationConstraint provConstraint = helper.createExplicitListConstraint(v); DataValidation provinceDataValidation = helper.createValidation(provConstraint, provRangeAddressList); //验证 provinceDataValidation.createErrorBox("error", "请选择正确数据"); provinceDataValidation.setShowErrorBox(true); provinceDataValidation.setSuppressDropDownArrow(true); sheet.addValidationData(provinceDataValidation); //对前20行设置有效性，下标从1 for(int j = 2;j List sheetNameList = ExcelTemplate.getInstance().readSheetName(new FileInputStream(file)); // 先清空，再读，防止静态值存在 new MonitorDataCxRelationListener().getCachedDataList().clear(); EasyExcel.read(file.getAbsolutePath(), new MonitorDataCxRelationListener()).sheet(sheetNameList.get(sheetNo)).headRowNumber(0).doRead(); List cachedDataList = new MonitorDataCxRelationListener().getCachedDataList(); return cachedDataList; } private Map getSonValue(List fatherInfo, List cachedDataList) { // integer:要存的列，string下拉框的值 Map map = new HashMap(); List saveSelectTwoList = new ArrayList(); for (int i = 0; i // 第一个下拉框的值 String key = integerStringMap.get(9); if(integerStringMap.size()>=9){ // 第二个联动下拉框 saveSelectTwoList = new ArrayList(); for (Integer integer : integerStringMap.keySet()) { if(integer > 9) { saveSelectTwoList.add(integerStringMap.get(integer)); } } // 保证其没空值 String[] value = saveSelectTwoList.toArray(new String[saveSelectTwoList.size()]); map.put(key,value); } } } return map; } private List getFatherInfo(List cachedDataList ){ List saveSelectOneList = new ArrayList(); for (int i = 0; i // 第一个下拉框的值 saveSelectOneList.add(integerStringMap.get(9)); } } return saveSelectOneList; } /** * 设置有效性 * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动 * @param sheet * @param rowNum 行数 * @param colNum 列数 */ public static void setDataValidation(String offset,Sheet sheet, int rowNum,int colNum) { DataValidationHelper helper = sheet.getDataValidationHelper(); DataValidation data_validation_list = getDataValidationByFormula( "INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum,helper); sheet.addValidationData(data_validation_list); } /** * 加载下拉列表内容 * @param formulaString 索引到的公式 * @param naturalRowIndex 一级的单元格位置，即影响联动那个 * @param naturalColumnIndex 需要联动的单元格 * @param helper 设置约束 * @return */ private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex,DataValidationHelper helper) { // 加载下拉列表内容 // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据， //如果A2是江苏省，那么此处就是江苏省下的市信息。 DataValidationConstraint dvConstraint = helper.createFormulaListConstraint(formulaString); // 设置数据有效性加载在哪个单元格上。 // 四个参数分别是：起始行、终止行、起始列、终止列 int firstRow = naturalRowIndex -1; int lastRow = naturalRowIndex -1; int firstCol = naturalColumnIndex - 1; int lastCol = naturalColumnIndex - 1; CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); // 数据有效性对象 // 绑定 DataValidation data_validation_list = helper.createValidation(dvConstraint, regions); data_validation_list.setEmptyCellAllowed(false); if (data_validation_list instanceof DataValidation) { data_validation_list.setSuppressDropDownArrow(true); data_validation_list.setShowErrorBox(true); } else { data_validation_list.setSuppressDropDownArrow(false); } // 设置输入信息提示信息 data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！"); // 设置输入错误提示信息 return data_validation_list; } /** * 计算formula * @param i 偏移量：如果给0，就从A开始，1表示从B * @param rowId 第几行 * @param two_select_length 一共多少列 * @return 入参1，1，3 则值在B1-D1，返回$B$1:$D$1 */ private String getRange(int i, int rowId, int two_select_length) { char start = (char)('A'+i); if(two_select_length char endPrefix = 'A'; char endSuffix = 'A'; // 26-51 if( (two_select_length - 25)/26 == 0 || two_select_length == 51){ if( (two_select_length-25)%26 == 0){ // 边界 endSuffix = (char)('A' + 25); } else { endSuffix = (char)('A' + (two_select_length - 25) % 26 - 1); } }else{ // 大于51 if( (two_select_length-25)%26==0){ // 边界 endSuffix = (char)('A'+25); endPrefix = (char)(endPrefix + (two_select_length-25)/26 -1); }else{ endSuffix = (char)('A' + (two_select_length - 25) % 26 - 1); endPrefix = (char)(endPrefix + (two_select_length - 25) / 26); } } return "$"+start+"$"+rowId+":$"+endPrefix+endSuffix+"$"+rowId; } } public static String getExcelLine(int num) { String line = ""; int first = num/26; int second = num % 26; if (first>0) { line = (char)('A'+first-1)+""; } line += (char)('A'+second)+""; return line; } } 创建写入时的处理器

由于实体类有celldata，处理器写出实体类上的converter=

package com.stec.promis.web.excel; import com.alibaba.excel.converters.Converter; import com.alibaba.excel.converters.ReadConverterContext; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.data.CellData; import com.alibaba.excel.metadata.data.ReadCellData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.metadata.property.ExcelContentProperty; public class MonitorDataCxConverter implements Converter { @Override public Class supportJavaTypeKey() { return Integer.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public CellData convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { if (cellData == null){ cellData = new ReadCellData(""); } return cellData; } @Override public WriteCellData convertToExcelData(CellData value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new WriteCellData(value.getFormulaData().getFormulaValue()); } } 导出 FileInputStream fis = new FileInputStream(fileTempTwo); OutputStream out = getOutputStream(fileName,response); byte buffer[] = new byte[1024]; int len = 0; //循环将输入流中的内容读取到缓冲区当中 while ((len = fis.read(buffer)) > 0) { //输出缓冲区的内容到浏览器，实现文件下载 out.write(buffer, 0, len); } //关闭文件输入流 fis.close(); //关闭输出流 out.close(); if (fileTemp.exists()) { fileTemp.delete(); } 补充知识点 urlEncode和urlDecode： 学习链接：[添加链接描述](https://blog.csdn.net/qq_43842093/article/details/122732429) urlencoder：是HTML格式编码的实用工具类。该类包含了将string转换为applicaion/x-www-form-urlencoded MIME格式的静态方法。 对string编码时，使用以下规则：

字母数字字符a-z\A-Z和0-9保持不变

特殊字符.-_*保持不变

空格字符转换为+

所有其他字符不安全，因此需要首先使用一些编码机制将它们转换为一个或多个字节，然后每个字节包含3个字符的字符串‘%xy’表示，其中xy为该字节的两位十六进制表示形式。推荐utf-8

urlEncoder：URI必须时可写的ASCII字符。 需要转化的字符：

ASCII的控制字符：这些字符都是不可打印的

非ASCII字符：非法的字符范围

一些保留字符：&

不安全字符，空格转为+

urlEncoder和urlDecoder的作用和使用

1.URLEncoder.encode(String s, String enc) 使用指定的编码机制将字符串转换为 application/x-www-form-urlencoded 格式

URLDecoder.decode(String s, String enc) 使用指定的编码机制对 application/x-www-form-urlencoded 字符串解码。

2.发送的时候使用URLEncoder.encode编码，接收的时候使用URLDecoder.decode解码，都按指定的编码格式进行编码、解码，可以保证不会出现乱码

3.主要用来http get请求不能传输中文参数问题。http请求是不接受中文参数的。

这就需要发送方，将中文参数encode，接收方将参数decode，这样接收方就能收到准确的原始字符串了。

getResource和getResourceAsStream的区别：

学习链接添加链接描述 Class.getResource(String path) path不以’/‘开头时，默认是从此类所在的包下取资源； path以’/‘开头时，则是从项目的ClassPath根下获取资源。在这里’/‘表示ClassPath的根目录 Class.getClassLoader().getResource(String path) path不能以’/‘开头，path是指类加载器的加载范围，在资源加载的过程中，使用的逐级向上委托的形式加载的,’/'表示Boot ClassLoader，类加载器中的加载范围.

实际上 class.getResource(“/”) == class.getClassLoader().getResource(“”) Class.getResourceAsStream(String path) path不以’/‘开头时，默认是指所在类的相对路径，从这个相对路径下取资源； path以’/'开头时，则是从项目的ClassPath根下获取资源，就是要写相对于classpath根下的绝对路径。 Class.getClassLoader.getResourceAsStream(String path) 默认则是从ClassPath根下获取，path不能以’/'开头，最终是由ClassLoader获取资源。

如果以‘/’ 开头，则 返回的是classLoader加载器Boot ClassLoader的加载范围，返回的是null，所以不能以 / 开头。

综上: 如果直接通过getResource获取资源则加上"/“默认获取的是绝对路径,通过getResourceAsStream获取资源则不能加上”/",此时是通过classLoader获取资源的并且获取的是绝对路径.



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3