EasyExcel导出表格固定列生成下拉框并且下拉框数据突破255限制,采用的方式是生成虚拟sheet页
1、实现 SheetWriteHandler方法,代码如下
该处理器仅支持固定列生成下拉框,可动态指定下拉框行数范围
package com.shareworx.handler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
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 java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 导出excel固定列增加下拉框处理器(通用模板)
*
* @author yong.li_1
* @version since Shareworx platform 4.0.6
*/
@Slf4j
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BuildSpinnerWriteHandler implements SheetWriteHandler {
private Map<Integer, List<String>> dropDownMap; // 下拉框数据源的映射,key为列索引,value为下拉框数据源
private int firstRow = 6; // 起始行
private int lastRow = 45000; // 结束行
public BuildSpinnerWriteHandler(Map<Integer, List<String>> dropDownMap) {
this.dropDownMap = dropDownMap;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
List<String> sheetNames = new ArrayList<>();
for (Map.Entry<Integer, List<String>> entry : dropDownMap.entrySet()) {
int columnIndex = entry.getKey();
List<String> dropDownValues = entry.getValue();
if (CollectionUtils.isEmpty(dropDownValues)) {
continue;
}
// 突破下拉框255的限制
String sheetName = "_" + columnIndex + "sheet";
if (!sheetNames.contains(sheetName)) {
//获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
//定义sheet的名称
//1.创建一个隐藏的sheet 名称为 proviceSheet
Sheet proviceSheet = workbook.createSheet(sheetName);
// 设置隐藏
workbook.setSheetHidden(sheetNames.size() + 1, true);
//2.循环赋值(为了防止下拉框的行数与隐藏域的行数相对应,将隐藏域加到结束行之后)
for (int j = 0, length = dropDownValues.size(); j < length; j++) {
// i:表示你开始的行数 0表示你开始的列数
proviceSheet.createRow(j).createCell(0).setCellValue(dropDownValues.get(j));
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
//4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (dropDownValues.size()));
sheetNames.add(sheetName);
}
//5 将刚才设置的sheet引用到你的下拉列表中
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
DataValidationConstraint constraint8 = sheet.getDataValidationHelper().createFormulaListConstraint(sheetName);
DataValidation dataValidation3 = sheet.getDataValidationHelper().createValidation(constraint8, addressList);
writeSheetHolder.getSheet().addValidationData(dataValidation3);
}
}
}
2、处理器如何使用代码如下
package com.shareworx.ho;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.util.ListUtils;
import com.shareworx.handler.BuildSpinnerWriteHandler;
import org.apache.commons.compress.utils.Lists;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author yong.li_1
**/
public class ExcelExportTest {
public static void main(String[] args) {
String fileName = "E:\\" + System.currentTimeMillis() + ".xlsx";
// 下拉框数据
Map<Integer, List<String>> dropDownMap = new HashMap<>();
List<String> list1 = new ArrayList<>();
for (int i = 0; i < 100; i++) {
list1.add("字符串" + i);
}
List<String> list2 = new ArrayList<>();
for (int i = 0; i < 100; i++) {
list2.add("字符" + i);
}
// 第一列
dropDownMap.put(0, list1);
// 第二列
dropDownMap.put(1, list2);
EasyExcelFactory.write(fileName).registerWriteHandler(new BuildSpinnerWriteHandler(dropDownMap, 1, 255))
// 这里放入动态头
.head(head()).sheet("模板").doWrite(Lists.newArrayList());
}
private static List<List<String>> head() {
List<List<String>> list = ListUtils.newArrayList();
List<String> head0 = ListUtils.newArrayList();
head0.add("字符串" + System.currentTimeMillis());
List<String> head1 = ListUtils.newArrayList();
head1.add("字符" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
return list;
}
}