前言
公司有个需求本来只涉及到两个下拉选项，后面就想能不能实现多个下拉选，当然我这里说的多个下拉选是联动的，比如省、地市、区县这种。
实现步骤
1、添加EasyExcel的Maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
2、一个具有多级关联的数据项
/**
* excel下拉框数据项
* @author lcy
*/
@Data
public class SelectItem {
public SelectItem(Integer columnIndex) {
this.columnIndex = columnIndex;
}
/**
* 下拉框所在列的索引，从0开始
*/
private Integer columnIndex;
/**
* 下拉框的值列表
*/
private List<DataItem> dataItems;
/**
* 子级对应的下拉框数据
*/
private SelectItem subSelect;
public void addDataItem(String mappingKey,List<String> values){
if (this.dataItems == null){
this.dataItems = new ArrayList<>();
}
this.dataItems.add(new DataItem(mappingKey,values));
}
public void addDataItem(List<String> values){
this.addDataItem("_"+UUID.randomUUID().toString().replaceAll("-",""),values);
}
@Data
public static class DataItem{
/**
* 关联上级的key
*/
private String mappingKey;
/**
* 当前下拉框的值
*/
private List<String> values;
/**
* 当前下拉框的引用，隐藏页单元格地址
*/
private String hiddenFormulaRef;
public DataItem(String mappingKey, List<String> values) {
Assert.notBlank(mappingKey,"mappingKey is not blank");
Assert.notEmpty(values,"values is not empty");
this.mappingKey = mappingKey;
this.values = values;
}
}
3、定义一个SheetWriteHandler，这是EasyExcel提供的一个组件，允许我们在sheet页生成前后做一些干预动作。
/**
* @author lcy
*/
public class SelectWriteHandler implements SheetWriteHandler , CellWriteHandler {
private static final int ROW_SIZE = 10000;
private final WriteFont redFont;
private final List<SelectItem> selectItems;
private final String HIDDEN_SHEET_NAME = "hidden_sheet";
private final Set<Integer> selectColumns = new HashSet<>();
private boolean isLoadSelectColumns = false;
private int rowIndex = 0;
public SelectWriteHandler(List<SelectItem> selectItems) {
Assert.notEmpty(selectItems, "selectItems can not be empty");
this.selectItems = selectItems;
redFont = getRedFont();
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.getSheet(HIDDEN_SHEET_NAME);
if (hiddenSheet != null){
return ;
}
hiddenSheet = workbook.createSheet(HIDDEN_SHEET_NAME);
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
Sheet sheet = writeSheetHolder.getSheet();
for (SelectItem selectItem : selectItems) {
buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,selectItem,null);
}
if (!isLoadSelectColumns){
isLoadSelectColumns = true;
}
}
private void buildHiddenSheetSelectRef(Workbook workbook,Sheet sheet,Sheet hiddenSheet, SelectItem selectItem,String formulaRef ) {
if (!isLoadSelectColumns){
selectColumns.add(selectItem.getColumnIndex());
}
List<SelectItem.DataItem> dataItems = selectItem.getDataItems();
for (SelectItem.DataItem dataItem : dataItems) {
setDataAndName(workbook, hiddenSheet, dataItem);
}
// 单元格地址引用
if (formulaRef == null || formulaRef.isEmpty()){
formulaRef = dataItems.get(0).getHiddenFormulaRef();
}
// 创建检验器
DataValidation dataValidation = getDataValidation(sheet, selectItem, formulaRef);
sheet.addValidationData(dataValidation);
SelectItem subSelect = selectItem.getSubSelect();
if (subSelect != null){
buildHiddenSheetSelectRef(workbook,sheet,hiddenSheet,subSelect,getInDirectFormulaRef(selectItem.getColumnIndex()));
}
}
private DataValidation getDataValidation(Sheet sheet, SelectItem selectItem, String formulaRef) {
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(formulaRef);
CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,ROW_SIZE, selectItem.getColumnIndex(), selectItem.getColumnIndex());
DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);
dataValidation.setShowErrorBox(true);
return dataValidation;
}
private void setDataAndName(Workbook workbook, Sheet hiddenSheet, SelectItem.DataItem dataItem) {
// 构建隐藏数据
Row row = hiddenSheet.createRow(rowIndex);
List<String> values = dataItem.getValues();
for (int i = 0; i < values.size(); i++) {
row.createCell(i).setCellValue(values.get(i));
}
// 创建名称命名器
Name name = workbook.createName();
name.setNameName(dataItem.getMappingKey());
name.setRefersToFormula(getFormulaRef(row));
dataItem.setHiddenFormulaRef(name.getRefersToFormula());
rowIndex++;
}
private String getInDirectFormulaRef(Integer columnIndex){
CellReference slectCellReference = new CellReference(1, columnIndex);
return "INDIRECT("+joinFormulaRef(slectCellReference, false)+")";
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
if (!context.getHead()){
Integer columnIndex = context.getColumnIndex();
if (selectColumns.contains(columnIndex)){
// 设置红色字体
context.getFirstCellData().getOrCreateStyle().setWriteFont(redFont);
}
}
CellWriteHandler.super.afterCellDispose(context);
}
private String getFormulaRef(Row prvRow) {
Cell startCell = prvRow.getCell(prvRow.getFirstCellNum());
Cell endCell = prvRow.getCell(prvRow.getLastCellNum() - 1);
return HIDDEN_SHEET_NAME + "!" + joinFormulaRef(new CellReference(startCell),true) + ":" + joinFormulaRef(new CellReference(endCell),true);
}
public String joinFormulaRef(CellReference cellReference,boolean isAbsolute){
StringBuilder sb = new StringBuilder();
String[] refs = cellReference.getCellRefParts();
for (int i = refs.length -1 ; i >= 1; i--) {
if (isAbsolute){
sb.append("$");
}
sb.append(refs[i]);
}
return sb.toString();
}
/**
* 返回一个红色字体
* @return
*/
private WriteFont getRedFont() {
WriteFont redFont = new WriteFont();
redFont.setColor(IndexedColors.RED.getIndex());
return redFont;
}
}
4、准备数据
// 准备数据
SelectItem selectItem = new SelectItem(0);
selectItem.addDataItem(List.of("浙江省","河南省"));
SelectItem subSelectItem = new SelectItem(1);
subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
selectItem.setSubSelect(subSelectItem);
SelectItem selectItem3 = new SelectItem(2);
selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
subSelectItem.setSubSelect(selectItem3);
5、测试
EasyExcel.write("d:\\5555.xlsx")
.registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
.sheet()
.doWrite(Collections.emptyList());
完整的测试代码
public class SelectExcelTest {
public static void main(String[] args) {
// 准备数据
SelectItem selectItem = new SelectItem(0);
selectItem.addDataItem(List.of("浙江省","河南省"));
SelectItem subSelectItem = new SelectItem(1);
subSelectItem.addDataItem("浙江省",List.of("杭州市","宁波市"));
subSelectItem.addDataItem("河南省",List.of("郑州市","洛阳市","开封市"));
selectItem.setSubSelect(subSelectItem);
SelectItem selectItem3 = new SelectItem(2);
selectItem3.addDataItem("杭州市",List.of("滨江区","西湖区"));
selectItem3.addDataItem("宁波市",List.of("宁波市1","宁波市2"));
selectItem3.addDataItem("郑州市",List.of("金水区","二七区"));
selectItem3.addDataItem("洛阳市",List.of("洛阳市1","洛阳市2"));
selectItem3.addDataItem("开封市",List.of("开封市1","开封市2"));
subSelectItem.setSubSelect(selectItem3);
EasyExcel.write("d:\\5555.xlsx")
.registerWriteHandler(new SelectWriteHandler(List.of(selectItem)))
.sheet()
.doWrite(Collections.emptyList());
}
}
6、结果
