|
EasyExcel导出表格——下拉筛选
单元格添加下拉筛选
确保输入内容规范和准确减少数据查询，提高用户使用感根据用户权限，下拉筛选项控制
Controller层
/**
* 模板下载
* @param response
*/
@GetMapping(value = "/download")
public void download(HttpServletResponse response) {
// 导出模板名称
String fileName = "负责人批量修改模板.xlsx";
ClassPathResource classPathResource = new ClassPathResource("template/payment_permission.xlsx");
try (InputStream inputStream = classPathResource.getInputStream();
OutputStream out = response.getOutputStream();) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// URLEncoder.encode防止中文乱码
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")
+ ";filename*=UTF-8" + URLEncoder.encode(fileName, "UTF-8"));
// 表格填充数据
List excelList = permissionSettingService.listPermissionExcel();
ExcelWriterBuilder writerBuilder = EasyExcel.write(out).withTemplate(inputStream);
// PermissionExcelWriteHandler处理下拉的handler
writerBuilder.sheet(0).registerWriteHandler(new PermissionExcelWriteHandler()).doFill(excelList);
} catch (IOException e) {
log.error(e.getMessage(), e);
response.setStatus(500);
}
}
处理字典项的Handler（下拉选项不超过50个）
public class PermissionExcelWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
// 设置下拉单元格的首行、末行、首列、末列
CellRangeAddressList accountTypeCellRangeAddressList = new CellRangeAddressList(1, 2000, 2, 2);
// AccountTypeEnum.dropIndex() 等于 String[] array = new String[]{"账户1", "账户2"};
DataValidationConstraint accountTypeConstraint = helper.createExplicitListConstraint(AccountTypeEnum.dropIndex());
DataValidation accountTypeDataValidation = helper.createValidation(accountTypeConstraint, accountTypeCellRangeAddressList);
writeSheetHolder.getSheet().addValidationData(accountTypeDataValidation);
}
}
当下拉选项值超过50个时，使用上述方式handler处理，导出的表格中下拉选项不显示，这时候需要将下拉选的内容存在另一个sheet页中。
处理字典项的Handler（下拉选项超过50个）
阿里云参考文档
public class SocialBillExtendExcelWriteHandler implements SheetWriteHandler {
/**
* key 下拉选项需要填充的列序号，value存放对应的下拉选项内容
*/
private Map selectMap;
private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
public SocialBillExtendExcelWriteHandler(Map selectMap) {
this.selectMap = selectMap;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (CollUtil.isEmpty(selectMap)) {
return;
}
// 需要设置下拉框的sheet页
Sheet curSheet = writeSheetHolder.getSheet();
DataValidationHelper helper = curSheet.getDataValidationHelper();
String dictSheetName = "数据字典";
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 创建数据字典的sheet页
Sheet dictSheet = workbook.createSheet(dictSheetName);
// 提升用户体验，隐藏数据字典的sheet页
workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);
for (Map.Entry entry : selectMap.entrySet()) {
// 设置下拉单元格的首行、末行、首列、末列
CellRangeAddressList rangeAddressList = new CellRangeAddressList(2, 100, entry.getKey(), entry.getKey());
int rowLen = entry.getValue().size();
// 设置字典sheet页的值 每一列一个字典项
Iterator iterator = entry.getValue().iterator();
int rowIndex = 0;
while (iterator.hasNext()) {
Row row = dictSheet.getRow(rowIndex);
if (row == null) {
row = dictSheet.createRow(rowIndex);
}
row.createCell(entry.getKey()).setCellValue(iterator.next());
rowIndex++;
}
String excelColumn = getExcelColumn(entry.getKey());
// 下拉框数据来源 eg:字典sheet!$B1:$B2
String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
// 创建可被其他单元格引用的名称
Name name = workbook.createName();
// 设置名称的名字
name.setNameName("dict" + entry.getKey());
// 设置公式
name.setRefersToFormula(refers);
// 设置引用约束
DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeAddressList);
if (validation instanceof HSSFDataValidation) {
validation.setSuppressDropDownArrow(false);
} else {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
}
// 阻止输入非下拉框的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("提示", "此值与单元格定义格式不一致！");
// 添加下拉框约束
writeSheetHolder.getSheet().addValidationData(validation);
}
}
/**
* 将数字列转化成为字母列
* @param num
*/
private String getExcelColumn(int num) {
String column = "";
int len = alphabet.length - 1;
int first = num / len;
int second = num % len;
if (num |