目录
前言
本次导出采用的是拦截器进行重新封装参数。
代码示例:
// SpinnerWriterHandler拦截器有参 stockName等是动态传的参数
EasyExcel.write(response.getOutputStream(), StockImportVo.class)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(new SpinnerWriteHandlerStock(stockName,stockPType,stockMeteringUnit,productName))
.sheet("设备信息模板")
.doWrite(objects);
问题描述
在实现自定义导出excel表格数据的时候,我在设置单元格下拉导出出现一下问题:
A valid formula or a list of values must be less than or equal to 255 characters (including separators).
提示超出255个字符,问题截图如下:
解决思路
通过创建新的sheet,然后从新的sheet读取数据,可以实现多个下拉框参数,代码示例:
拦截器处理方法
package com.vastwin.framework.handler;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.RandomUtil;
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.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @author chendazui
* @date 2023/2/8
*/
public class SpinnerWriteHandlerStock implements SheetWriteHandler
{
// 需要传递参数进来就定义一个变量和构造方法
private List<String> stockName;
private List<String> stockPType;
private List<String> stockMeteringUnit;
private List<String> productName;
public SpinnerWriteHandlerStock() {
}
//传递参数
public SpinnerWriteHandlerStock(List<String> stockName,List<String> stockPType,List<String> stockMeteringUnit,List<String> productName) {
this.stockName = stockName;
this.stockPType = stockPType;
this.stockMeteringUnit = stockMeteringUnit;
this.productName = productName;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
{
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder)
{
String[] stockNames = stockName.toArray(new String[stockName.size()]);
String[] stockPTypes = stockPType.toArray(new String[stockPType.size()]);
String[] stockMeteringUnits = stockMeteringUnit.toArray(new String[stockMeteringUnit.size()]);
String[] productNames = productName.toArray(new String[productName.size()]);
Map<Integer, String[]> mapDropDown = new HashMap<>();
// 这里的key值 对应导出列的顺序 从0开始
mapDropDown.put(0, stockNames);
mapDropDown.put(1, stockPTypes);
mapDropDown.put(2, productNames);
mapDropDown.put(5, stockMeteringUnits);
// 获取到当前的sheet
Sheet sheet = writeSheetHolder.getSheet();
/// 开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框
int key = 0;
for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet())
{
/*** 起始行、终止行、起始列、终止列 **/
CellRangeAddressList addressList = new CellRangeAddressList(1, 1000,entry.getKey(), entry.getKey());
String[] grades = entry.getValue();
// 解决办法从这里开始
//获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
//定义sheet的名称
String hiddenName = "hidden"+entry.getKey();
//1.创建一个隐藏的sheet 名称为 hidden
Sheet hidden = workbook.createSheet(hiddenName);
key = key + 1;
//2.循环赋值(为了防止下拉框的行数与隐藏域的行数相对应,将隐藏域加到结束行之后)
for (int i = 0, length = grades.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
hidden.createRow( 2+ i).createCell(entry.getKey()).setCellValue(grades[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);
//4 A1:A代表隐藏域创建第N列createCell(N)时。以A1列开始A行数据获取下拉数组
category1Name.setRefersToFormula(hiddenName + "!A1:A" + (grades.length + 3));
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint8 = helper.createFormulaListConstraint(hiddenName);
DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
sheet.addValidationData(dataValidation3);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(key, true);
}
}
}
这样就解决了下拉框超出的问题