文章目录
需求描述：将原先的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<Map<Integer,String>> {
/**
* 每隔5条存储数据库，实际使用中可以1000条，然后清理list ，方便内存回收
*/
private static final int BATCH_Count = 10000;
// integer：列值0，1，2；String表示值
private static List<Map<Integer,String>> cachedDataList = new ArrayList<>();
public List<Map<Integer,String>> getCachedDataList(){
return cachedDataList;
}
@Override
public void invoke(Map<Integer, String> 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<Map<Integer, String>> getCachedDataList(File file, Integer sheetNo) throws Exception{
List<String> 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<Map<Integer, String>> 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<String> tenders_id;
@ExcelProperty(value="work_point_id",converter = MonitorDataCxConverter.class)
private CellData<String> work_point_id;
@ExcelProperty(value="分层信息（分层名称,深度m）")
private String depthInformation;
创建转换器
转换器继承了sheetWriteHandler和cellWriteHandler。
用来处理单元格的公式和创建sheet的值。
单元格的修改，公式添加，继承cellWriteHandler：
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 读模板，根据项目号读取总行数，第一行为标题，所以跳过
if(cell.getRowIndex()>0) {
// 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数
Map<Integer,Integer> 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<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 读模板，根据项目号读取总行数，第一行为标题，所以跳过
if(cell.getRowIndex()>0) {
// 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数
Map<Integer,Integer> 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<Integer, Integer> getFormulaIndex() throws Exception {
// 0：要存所有名字总个数69，1：第一列的值个数143，2：第三列的个数
Map<Integer,Integer> formulaIndex = new HashMap<>();
List<Map<Integer, String>> cachedDataList = getCachedDataList(file, sheetNo);
Integer i1=0,i2=0,i3=0;
for (Map<Integer, String> 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<String> headers = Arrays.asList("序号","标段","tenders_id","工点","work_point_id","基坑编码","测点编号","分层信息（分层名称,深度m）");
// 模板中隐藏表的值，Integer表示行（0开始），String保存值
List<Map<Integer, String>> cachedDataList = getCachedDataList(file,sheetNo);
// 一级菜单值
List<String> fatherInfo = getFatherInfo(cachedDataList);
// 存二级菜单的值，String为一级菜单的值
Map<String,String[]> map = getSonValue(fatherInfo,cachedDataList);
// 重写数据进新建的easyexcel其他模板中
List<String> sheetNameList = ExcelTemplate.getInstance().readSheetName(new FileInputStream(file));
for (int i = 1; i < sheetNameList.size(); i++) {
// 创建隐藏sheet
Sheet sheet_son = workbook.createSheet(sheetNameList.get(i));
// 存值
// 这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
workbook.setSheetHidden(workbook.getSheetIndex(sheet_son),true);
int rowId = 0;
// 设置第一行，由于隐藏表中第一行没有标题，所以从下面开始
// 将具体的数据写入到每一行中。
for(int j = 0;j < cachedDataList.size();j++){
Map<Integer, String> fatherOption = cachedDataList.get(j);
Row row = sheet_son.createRow(rowId++);
int two_select_length = 0;
for(int k = 0; k < fatherOption.size(); k ++){
Cell cell = row.createCell(k);
cell.setCellValue(fatherOption.get(k));
if(k>9){
// 存二级目录的个数
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 < length; p++) {
// 开始的行数i，列数k
hiddenSheet.createRow(p).createCell(k).setCellValue(v[p]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
String excelLine = getExcelLine(k);
// =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
// 将刚才设置的sheet引用到下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, provRangeAddressList);
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(dataValidation);
// 设置存储下拉列值得sheet为隐藏
int hiddenIndex = workbook.getSheetIndex(sheetName);
if (!workbook.isSheetHidden(hiddenIndex)) {
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 < 20;j++){
setDataValidation("B" ,sheet,j,4);
}
}
}
private List<Map<Integer, String>> getCachedDataList(File file, Integer sheetNo) throws Exception{
List<String> 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<Map<Integer, String>> cachedDataList = new MonitorDataCxRelationListener().getCachedDataList();
return cachedDataList;
}
private Map<String, String[]> getSonValue(List<String> fatherInfo, List<Map<Integer, String>> cachedDataList) {
// integer:要存的列，string下拉框的值
Map<String, String[]> map = new HashMap<>();
List<String> saveSelectTwoList = new ArrayList<>();
for (int i = 0; i < cachedDataList.size(); i++) {
Map<Integer, String> integerStringMap = cachedDataList.get(i);
if(ObjectUtils.isNotEmpty(integerStringMap.get(9))) {
// 第一个下拉框的值
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<String> getFatherInfo(List<Map<Integer, String>> cachedDataList ){
List<String> saveSelectOneList = new ArrayList<>();
for (int i = 0; i < cachedDataList.size(); i++) {
Map<Integer, String> integerStringMap = cachedDataList.get(i);
if(ObjectUtils.isNotEmpty(integerStringMap.get(9))) {
// 第一个下拉框的值
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<=25){
char end = (char)(start+two_select_length-1);
return "$"+start+"$"+rowId+":$"+end+"$"+rowId;
}else{
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<CellData> {
@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<CellData>(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获取资源的并且获取的是绝对路径.
1916
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
