EasyExcel下拉列表无法显示
故事
最近在一个项目上涉及到数据的导入导出，由于数据在导入时，需要指定一下excel模板，而模板中为了解析方便设置了下拉框限制。本来项目跑的好好的，谁知道，这两天下载的模板文件下拉框不会显示了，绝绝子，最终问题解决了。
解决方式
原来有方式是采用createExplicitListConstraint 进行设置枚举下拉，但是存在很大弊端，数据过多会导致无法显示。
后来通过将下拉数据存于隐藏的sheet中，通过excel中的公式进行设置，这样问题就得到解决。
关键代码
设置TitleHandler继承CellWriteHandler，之后在afterCellDispose进行数据的样式、下拉等操作设置，具体如下：
// 原有的设置下拉的方式
// String[] datas = dropDownMap.get(cell.getColumnIndex());
// DataValidationHelper dvHelper = sheet.getDataValidationHelper();
// DataValidationConstraint dvConstraint = dvHelper
// .createExplicitListConstraint(datas);
// CellRangeAddressList addressList = null;
// DataValidation validation = null;
// for (int i = 1; i < 1000; i++) {
// addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
// validation = dvHelper.createValidation(
// dvConstraint, addressList);
// sheet.addValidationData(validation);
// }
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//定义sheet的名称
String hiddenName = "hidden";
Sheet hidden=workbook.getSheet(hiddenName);
for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
//下拉框的起始行,结束行,起始列,结束列
CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, entry.getKey(), entry.getKey());
//获取excel列名
String excelLine = getExcelLine(entry.getKey());
//2.循环赋值
String[] values = entry.getValue();
//2.循环赋值
for (int i = 0, length = values.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + hiddenName + "!$" + excelLine +
"$1:$" + excelLine + "$" + (values.length+1);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
//设置列为隐藏
int hiddenIndex = workbook.getSheetIndex(hiddenName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
完整代码
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* <pre>
* 操作标题行单元格颜色
* @author sirwsl
*/
@Slf4j
public class TitleHandler implements CellWriteHandler {
/**
* 操作列
*/
private final List<Integer> columnIndex;
/**
* 颜色
*/
private final Short colorIndex;
/**
* 批注<列的下标,批注内容>
*/
private Map<Integer,String> annotationsMap;
/**
* 下拉框值
*/
private Map<Integer, String[]> dropDownMap;
public TitleHandler(List<Integer> columnIndex, Short colorIndex, Map<Integer, String> annotationsMap) {
this.columnIndex = columnIndex;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public TitleHandler(List<Integer> columnIndex, Short colorIndex) {
this.columnIndex = columnIndex;
this.colorIndex = colorIndex;
}
public TitleHandler(List<Integer> columnIndex, Short colorIndex, Map<Integer, String> annotationsMap, Map<Integer, String[]> dropDownMap) {
this.columnIndex = columnIndex;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
this.dropDownMap = dropDownMap;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
//sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
writeSheetHolder.getSheet().getRow(0).setHeight((short) (1.8 * 256));
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 设置标题字体样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);
if (CollectionUtils.isNotEmpty(columnIndex) &&
colorIndex != null &&
columnIndex.contains(cell.getColumnIndex())) {
// 设置字体颜色
headWriteFont.setColor(colorIndex);
}
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0, (short) cell.getColumnIndex() + 3, 1));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
if (null != dropDownMap &&
!dropDownMap.isEmpty() &&
dropDownMap.containsKey(cell.getColumnIndex())) {
// String[] datas = dropDownMap.get(cell.getColumnIndex());
// DataValidationHelper dvHelper = sheet.getDataValidationHelper();
// DataValidationConstraint dvConstraint = dvHelper
// .createExplicitListConstraint(datas);
// CellRangeAddressList addressList = null;
// DataValidation validation = null;
// for (int i = 1; i < 1000; i++) {
// addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
// validation = dvHelper.createValidation(
// dvConstraint, addressList);
// sheet.addValidationData(validation);
// }
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//定义sheet的名称
String hiddenName = "hidden";
Sheet hidden=workbook.getSheet(hiddenName);
for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
//下拉框的起始行,结束行,起始列,结束列
CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, entry.getKey(), entry.getKey());
//获取excel列名
String excelLine = getExcelLine(entry.getKey());
//2.循环赋值
String[] values = entry.getValue();
//2.循环赋值
for (int i = 0, length = values.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + hiddenName + "!$" + excelLine +
"$1:$" + excelLine + "$" + (values.length+1);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
//设置列为隐藏
int hiddenIndex = workbook.getSheetIndex(hiddenName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
}
}
/**
* 返回excel列标A-Z-AA-ZZ
* @param num 列数
* @return java.lang.String
*/
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;
}
}
调用方式：
//设置下拉
HashMap<Integer, String[]> drop = new HashMap<>(16);
drop.put(0, Arrays.asList("A","B","C"));
drop.put(1, Arrays.asList("A","B","C"));
//设置提示
HashMap<Integer, String> annotations = new HashMap<>();
annotations.put(0, "提示1");
annotations.put(1, "提示2");
//设置颜色
List<Integer> columns = Arrays.asList(0, 1, 2, 3, 4);
TitleHandler handler= new TitleHandler(columns, IndexedColors.RED.index, annotations, drop);
//创建
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
// 表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 单元格样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 初始化表格样式
HorizontalCellStyleStrategy horizontal = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
WriteSheet hidden = EasyExcel.writerSheet(0, "hidden").build();
WriteSheet sheet1 = EasyExcel.writerSheet(1, "测试")
.head(BO.class)
.registerWriteHandler(horizontal )
.registerWriteHandler(handler)
.build();
excelWriter.write(dataList,hidden)
.write(dataList, sheet1);
excelWriter.finish();