前段时间,因为项目需要,要将excel模板下载功能从附件下载迭代为后台动态生成;但是这个表格里面包含了下拉框,耗时两天才算是把这个excel表格搞完,现在记录一下,方便后续回顾。
首先,要实现的效果是这样:
根据D列的检查类型,E列弹出不同的下拉选项,F列弹出不同的问题属性;
Service层导出excel代码:
@Transactional(rollbackFor = Exception.class)
public void download(HttpServletResponse response) {
//导出文件格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
//设置导出文件名
String filename = "";
try {
filename = URLEncoder.encode("Excel名称" + DateUtil.format(new Date(), "yyyy-MM-dd"), "UTF-8").replaceAll("\\+", "%20");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + "." + ExcelFormat.XLS.getValue());
//模板实体类
List<SecondInspProbExcelDTO> secondInspProbExcelDTOS = new ArrayList<>();
//普通下拉数据
Map<Integer, String[]> map = ordinarySelect();
//检查类型-子类
Map<String, List<String>> inspNameMap = inspNameSelect();
//检查类型-父类
String[] proInspNameMap = proInspNameSelect();
//问题属性
Map<String, List<String>> probPropsNameMap = probPropsNameSelect();
try(OutputStream outputStream = response.getOutputStream()) {
EasyExcel.write(outputStream, SecondInspProbExcelDTO.class)
.registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap))
.excelType(ExcelTypeEnum.XLS)
.sheet("sheet1")
.doWrite(secondInspProbExcelDTOS);
} catch (IOException e) {
e.printStackTrace();
}
}
其中,这几个方法是为了创建下拉数据
//普通下拉数据
Map<Integer, String[]> map = ordinarySelect();
//检查类型-子类
Map<String, List<String>> inspNameMap = inspNameSelect();
//检查类型-父类
String[] proInspNameMap = proInspNameSelect();
//问题属性
Map<String, List<String>> probPropsNameMap = probPropsNameSelect();
普通下拉数据,就是如同性别、是否之类的数据;Map中的key就是要关联的列,注意:excel列从0开始,value就是下拉数据的数组
/**
* 普通下拉数据
* @return
*/
private Map<Integer, String[]> ordinarySelect() {
Map<Integer, String[]> map = new HashMap<>();
//是否现场已完成整改 是否直接作业类问题
String[] isZgArray = {"是", "否"};
//二级单位检查类型
String[] inspPlanTypeArray = {"无计划", "有计划", "计划外新增"};
map.put(2, isZgArray);
map.put(6, isZgArray);
map.put(12, inspPlanTypeArray);
return map;
}
级联的数据,父类:
/**
* 检查类型-父类
* @return
*/
private String[] proInspNameSelect() {
String[] proInspNameList = new String[10];
proInspNameList[2] = "专项检查";
return proInspNameList;
}
子类:
/**
* 检查类型-子类
* @return
*/
private Map<String, List<String>> inspNameSelect() {
Map<String, List<String>> inspNameArray = new HashMap<>();
//检查类型-专项检查(子类)
List<String> zxjcInspNameList = new ArrayList<>();
zxjcInspNameList.add("季节性");
zxjcInspNameList.add("节前");
zxjcInspNameList.add("专业性重大危险源");
inspNameArray.put("专项检查", zxjcInspNameList);
return inspNameArray;
}
其中,子类返回的map中,key就是父类返回数组的值,value就是子类列的下拉列表
接下来就是核心的拦截器,需实现
SheetWriteHandler接口
直接上代码,代码中有注释
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.Iterator;
import java.util.List;
import java.util.Map;
public class CustomCellWriteHandler implements SheetWriteHandler {
/**
* 普通下拉数据
*/
private Map<Integer, String[]> map;
/**
* 检查类型-父类
*/
private String[] proInspNameArray;
/**
* 检查类型-子类
*/
private Map<String, List<String>> inspNameArray;
/**
* 问题属性
*/
private Map<String, List<String>> probPropsNameArray;
public CustomCellWriteHandler(Map<Integer, String[]> map, String[] proInspNameArray, Map<String, List<String>> inspNameArray, Map<String, List<String>> probPropsNameArray) {
this.map = map;
this.proInspNameArray = proInspNameArray;
this.inspNameArray = inspNameArray;
this.probPropsNameArray = probPropsNameArray;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 获取第一个sheet页
Sheet sheet = writeSheetHolder.getCachedSheet();
// 获取sheet页的数据校验对象
DataValidationHelper helper = sheet.getDataValidationHelper();
// 获取工作簿对象,用于创建存放下拉数据的字典sheet数据页
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 普通数据,迭代索引,用于存放下拉数据的字典sheet数据页命名
int index = 1;
for (Map.Entry<Integer, String[]> entry : map.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().length;
for (int i = 0; i < rowLen; i++) {
// 向字典sheet写数据,从第一行开始写,此处可根据自己业务需要,自定
// 义从第几行还是写,写的时候注意一下行索引是从0开始的即可
dictSheet.createRow(i).createCell(0).setCellValue(entry.getValue()[i]);
}
// 设置关联数据公式,这个格式跟Excel设置有效性数据的表达式是一样的
String refers = dictSheetName + "!$A$1:$A$" + entry.getValue().length;
Name name = workbook.createName();
name.setNameName(dictSheetName);
// 将关联公式和sheet页做关联
name.setRefersToFormula(refers);
// 将上面设置好的下拉列表字典sheet页和目标sheet关联起来
DataValidationConstraint constraint = helper.createFormulaListConstraint(dictSheetName);
setValidation(sheet, helper, constraint, infoList);
}
//检查类型-父类
Sheet hideSheet = workbook.createSheet("site");
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), false);
// 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。
int rowId = 0;
Row proviRow = hideSheet.createRow(rowId++);
proviRow.createCell(0).setCellValue("大类列表");
for (int i = 0; i < proInspNameArray.length; i++) {
Cell proviCell = proviRow.createCell(i + 1);
proviCell.setCellValue(proInspNameArray[i]);
}
//检查类型-子类
Iterator<String> keyIterator = inspNameArray.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = inspNameArray.get(key);
Row row = hideSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = workbook.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
}
//问题属性
Iterator<String> probPropsNameIterator = probPropsNameArray.keySet().iterator();
while (probPropsNameIterator.hasNext()) {
String key = probPropsNameIterator.next();
List<String> son = probPropsNameArray.get(key);
Row row = hideSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = workbook.createName();
name.setNameName(key);
String formula = "site!" + range;
name.setRefersToFormula(formula);
}
///开始设置(大类小类)下拉框
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
// 大类规则
DataValidationConstraint expConstraint = dvHelper.createExplicitListConstraint(proInspNameArray);
CellRangeAddressList expRangeAddressList = new CellRangeAddressList(1, 1048575, 3, 3);
setValidation(sheet, dvHelper, expConstraint, expRangeAddressList);
//检查类型-子类
// 小类规则(各单元格按个设置)
// "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据,如果A2是浙江省,那么此处就是浙江省下面的市
// 为了让每个单元格的公式能动态适应,使用循环挨个给公式。
// 循环几次,就有几个单元格生效,次数要和上面的大类影响行数一一对应,要不然最后几个没对上的单元格实现不了级联
for (int i = 2; i < inspNameArray.size() + 10; i++) {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 4, 4);
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($D$" + i + ")");
setValidation(sheet, dvHelper, formula, rangeAddressList);
}
//问题属性
for (int i = 2; i < probPropsNameArray.size() + 10; i++) {
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i-1 , i-1, 5, 5);
DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT($E$" + i + ")");
setValidation(sheet, dvHelper, formula, rangeAddressList);
}
}
/**
* 设置验证规则
* @param sheet sheet对象
* @param helper 验证助手
* @param constraint createExplicitListConstraint
* @param addressList 验证位置对象
*/
private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList) {
DataValidation dataValidation = helper.createValidation(constraint, addressList);
dataValidation.createErrorBox("错误提示", "您输入的内容,不符合限制条件");
sheet.addValidationData(dataValidation);
}
/**
* @param offset 偏移量,如果给0,表示从A列开始,1,就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
* @author denggonghai 2016年8月31日 下午5:17:49
*/
public String getRange(int offset, int rowId, int colCount) {
char start = (char) ('A' + offset);
if (colCount <= 25) {
char end = (char) (start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间,包括边界(仅两次字母表计算)
if ((colCount - 25) % 26 == 0) {// 边界值
endSuffix = (char) ('A' + 25);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
endPrefix = (char) (endPrefix + (colCount - 25) / 26);
}
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
}
这个拦截器是在service层使用的,就是最上面的代码
try(OutputStream outputStream = response.getOutputStream()) {
EasyExcel.write(outputStream, SecondInspProbExcelDTO.class)
.registerWriteHandler(new CustomCellWriteHandler(map, proInspNameMap, inspNameMap, probPropsNameMap))
.excelType(ExcelTypeEnum.XLS)
.sheet("sheet1")
.doWrite(secondInspProbExcelDTOS);
导出excel表头背景色,文字颜色大小
@Data
//设置表头背景色
@HeadStyle(fillForegroundColor = 49)
//设置表头行高,默认单位磅
@HeadRowHeight(value = 34)
//设置表头问题颜色,大小,黑色8,白色7;
@HeadFontStyle(color = 8, fontHeightInPoints = 12)
@ExcelIgnoreUnannotated
public class SecondInspProbExcelDTO {
颜色对照表