本文章可解决EasyExcel导出Excel时,添加的下拉列表无法展示数据问题
1.背景
EasyExcel导出Excel的时候,可能会遇到要给Excel中的某个或者某些单元格加上下拉列表,让用户从下拉列表里选值,而不需要手动输入。
2.实现方案
该需求有两种实现方案:
(1)设置下拉列表的值,并封装到单元格中
该方案有个缺点,当下拉列表数据项过多,比如三四十条的时候,会导致导出的Excel下拉列表为空,无法展示下拉数据项,仅适合下拉列表数据项在20条以内的需求,此处不作讨论。
(2)将下拉列表的值,写入到隐藏的sheet页中,然后在目标单元格,设置数据引用
该方案有效的解决了上一个方案的痛点,将数据写入隐藏sheet页,并设置数据关联,数据项很多是时候,下拉列表也可以正常展示,适合数据项很大的情况。
3.实现demo
下面以将下拉选项数据写入隐藏sheet为例,展示实现方案
3.1 maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
3.2 Excel标题行bean
import com.alibaba.excel.annotation.ExcelProperty;
public class Header {
@ExcelProperty("列1")
private String title1;
@ExcelProperty("列2")
private String title2;
@ExcelProperty("列3")
private String title3;
@ExcelProperty("列4")
private String title4;
@ExcelProperty("列5")
private String title5;
public String getTitle1() {
return title1;
}
public void setTitle1(String title1) {
this.title1 = title1;
}
public String getTitle2() {
return title2;
}
public void setTitle2(String title2) {
this.title2 = title2;
}
public String getTitle3() {
return title3;
}
public void setTitle3(String title3) {
this.title3 = title3;
}
public String getTitle4() {
return title4;
}
public void setTitle4(String title4) {
this.title4 = title4;
}
public String getTitle5() {
return title5;
}
public void setTitle5(String title5) {
this.title5 = title5;
}
}
3.3 Excel下拉数据处理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 org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CustomSheetWriteHandler implements SheetWriteHandler {
private List<String> selectDataList;
public CustomSheetWriteHandler(List<String> selectDataList){
this.selectDataList=selectDataList;
}
/**
* 想实现Excel引用其他sheet页数据作为单元格下拉选项值,
* 需要重写该方法
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 构造下拉选项单元格列的位置,以及下拉选项可选参数值的map集合
// key:下拉选项要放到哪个单元格,比如A列的单元格那就是0,C列的单元格,那就是2
// value:key对应的那个单元格下拉列表里的数据项,比如这里就是下拉选项1..100
Map<Integer, List<String>> selectParamMap = new HashMap<>();
selectParamMap.put(2, selectDataList);
// 获取第一个sheet页
Sheet sheet = writeSheetHolder.getCachedSheet();
// 获取sheet页的数据校验对象
DataValidationHelper helper = sheet.getDataValidationHelper();
// 获取工作簿对象,用于创建存放下拉数据的字典sheet数据页
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 迭代索引,用于存放下拉数据的字典sheet数据页命名
int index = 1;
for (Map.Entry<Integer, List<String>> entry : selectParamMap.entrySet()) {
// 设置存放下拉数据的字典sheet,并把这些sheet隐藏掉,这样用户交互更友好
String dictSheetName = "dict_hide_sheet" + index;
Sheet dictSheet = workbook.createSheet(dictSheetName);
// 隐藏字典sheet页
workbook.setSheetHidden(index++, true);
// 设置下拉列表覆盖的行数,从第一行开始到最后一行,这里注意,Excel行的
// 索引是从0开始的,我这边第0行是标题行,第1行开始时数据化,可根据实
// 际业务设置真正的数据开始行,如果要设置到最后一行,那么一定注意,
// 最后一行的行索引是1048575,千万别写成1048576,不然会导致下拉列表
// 失效,出不来
CellRangeAddressList infoList = new CellRangeAddressList(1, 1048575, entry.getKey(), entry.getKey());
int rowLen = entry.getValue().size();
for (int i = 0; i < rowLen; i++) {
// 向字典sheet写数据,从第一行开始写,此处可根据自己业务需要,自定
// 义从第几行还是写,写的时候注意一下行索引是从0开始的即可
dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue().get(i));
}
// 设置关联数据公式,这个格式跟Excel设置有效性数据的表达式是一样的
String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().size();
Name name = workbook.createName();
name.setNameName(dictSheetName);
// 将关联公式和sheet页做关联
name.setRefersToFormula(refers);
// 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
DataValidation dataValidation = helper.createValidation(constraint, infoList);
sheet.addValidationData(dataValidation);
}
}
}
3.4 测试程序
@Test
void test02() {
File file = new File("C:\\Users\\Administrator\\Desktop\\模板.xlsx");
//测试数据,实际开发中一般查询查询库,调用Service查询获得
List<String>list=new ArrayList<>();
for (int i = 0; i < 100; i++) {
list.add("下拉选项" + i);
}
EasyExcel.write(file, Header.class)
// 导出Excel时在此处注册handler
.registerWriteHandler(new CustomSheetWriteHandler(list))
.sheet(0)
.doWrite(new ArrayList<>());
}
导出结果
注意:easyexcel行和列都是从0开始。
转载自EasyExcel导出Excel,单元格添加下拉列表_easyexcel 导出下拉框-CSDN博客,本文略作修改。