前言
项目进行到新的一个迭代了，赶了1周需求，接口终于处理完了。分享记录下迭代中处理导入、导出、下载模版功能的细节吧。
一、场景
懂的都懂，没有其他啥好说的，直接上代码吧。
二、使用步骤
1.导出
controller导出方法
/**
* 导出排班计划
*/
@ResourceAction(id = "exportShiftPlan", name = "导出排班计划")
@GetMapping("/exportShiftPlan")
public void exportShiftPlan(PotAttendShiftPlanQueryRequestVo requestVo, HttpServletResponse response) {
try {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_PATTERN) + "排班计划导出" + ".xlsx", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//组织导出数据
Map exportDataMap = service.exportShiftPlan(requestVo);
List headList = (List) exportDataMap.get("headList");
List dataList = (List) exportDataMap.get("dataList");
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream());
excelWriterBuilder.head(headList).sheet("排班计划").doWrite(dataList);
} catch (Exception e) {
throw new RuntimeException("导出排班计划失败");
}
}
exportShiftPlan方法实现
@Override
public Map exportShiftPlan(PotAttendShiftPlanQueryRequestVo requestVo) {
Map dataMap = new HashMap();
//处理动态列头
List headList = new ArrayList();
//前3列姓名、部门、员工账号
List xmHeard = new ArrayList();
xmHeard.add("姓名");
headList.add(xmHeard);
List bmHeard = new ArrayList();
bmHeard.add("部门");
headList.add(bmHeard);
List yghHeard = new ArrayList();
yghHeard.add("员工账号");
headList.add(yghHeard);
//时间处理
Date startTime = requestVo.getStartTime();
Date endTime = requestVo.getEndTime();
List rangeDateList = DateUtil.rangeToList(startTime, endTime, DateField.DAY_OF_MONTH);
for (DateTime dateTime : rangeDateList) {
List dateHead = new ArrayList();
//日期
dateHead.add(DateUtil.format(dateTime, DatePattern.NORM_DATE_PATTERN));
//星期
int dayOfWeek = DateUtil.dayOfWeek(dateTime);
String weekName = AttendConstant.weekDict.get(String.valueOf(dayOfWeek)).toString();
dateHead.add(weekName);
headList.add(dateHead);
}
//列头
dataMap.put("headList", headList);
//do your code，循环处理组织一行数据放到list，再放到map
//动态列部分数据根据列头时间循环处理
dataMap.put("dataList", dataList);
return dataMap;
}
2.下载模版
controller方法
/**
* 下载排班计划导入模版
*
* @param response 响应
*/
@ResourceAction(id = "exportShiftPlanTemplate", name = "导出排班计划模版")
@GetMapping("/exportShiftPlanTemplate")
public void exportShiftPlanTemplate(PotAttendShiftPlanQueryRequestVo requestVo, HttpServletResponse response) {
try {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = BusinessSerialNoUtil.genDateFmtSeqCode(redisService, "", "", 4, CommonConstant.SHIFT_EXPORT_TEMPLATE, "0", CommonConstant.COMPLET_BEFORE, DatePattern.PURE_DATE_PATTERN) + "排班计划模版.xlsx";
//防止乱码
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//组织导出数据
Map exportDataMap = service.exportShiftPlanTemplate(requestVo);
List headList = (List) exportDataMap.get("headList");
List dataList = (List) exportDataMap.get("dataList");
List shiftList = (List) exportDataMap.get("shiftList");
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream());
//动态设置下拉框
excelWriterBuilder.registerWriteHandler(new CustomCellWriteHeightStrategy((short) 30, (short) 30));
excelWriterBuilder.registerWriteHandler(new ShiftSheetWriteHandler(shiftList, headList.size(), dataList.size()));
ExcelWriterSheetBuilder excelWriterSheetBuilder = excelWriterBuilder.head(headList).sheet("排班计划");
excelWriterSheetBuilder.doWrite(dataList);
} catch (Exception e) {
String errMsg = e.getMessage();
if (StringUtils.isBlank(errMsg)) {
errMsg = "导出导入模版失败";
}
throw new RuntimeException(errMsg);
}
}
实际上就是在导出的基础上增加了一个样式核心拦截、一个数据处理的handler
CustomCellWriteHeightStrategy
自定义单元格高度策略
/**
* 自定义单元格高度策略
* @author zwmac
*/
public class CustomCellWriteHeightStrategy extends AbstractRowHeightStyleStrategy {
/**
* 默认高度
*/
private static final Integer DEFAULT_HEIGHT = 30;
/**
* 头部行高
*/
private Short headRowHeight = DEFAULT_HEIGHT.shortValue();
/**
* 内容行高
*/
private Short contentRowHeight = DEFAULT_HEIGHT.shortValue();
public CustomCellWriteHeightStrategy(Short headRowHeight, Short contentRowHeight) {
this.headRowHeight = headRowHeight;
this.contentRowHeight = contentRowHeight;
}
@Override
protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
int maxHeight = autoCalMaxHeight(row);
//row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
row.setHeightInPoints(maxHeight * DEFAULT_HEIGHT);
}
@Override
protected void setContentColumnHeight(Row row, int relativeRowIndex) {
int maxHeight = autoCalMaxHeight(row);
//row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
row.setHeightInPoints(maxHeight * DEFAULT_HEIGHT);
}
private int autoCalMaxHeight(Row row) {
Iterator cellIterator = row.cellIterator();
if (!cellIterator.hasNext()) {
return 1;
}
// 默认为 1行高度
int maxHeight = 1;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case STRING:
if (cell.getStringCellValue().contains("\n")) {
int length = cell.getStringCellValue().split("\n").length;
maxHeight = Math.max(maxHeight, length);
}
break;
default:
break;
}
}
return maxHeight;
}
}
ShiftSheetWriteHandler
下拉框处理，这里用的是隐藏域方式，之前其实分享过另一种处理方式，也提到了这种方式，下拉框处理
/**
* 排班sheetHandler
*
* @author zwmac
*/
public class ShiftSheetWriteHandler implements SheetWriteHandler {
/**
* 一行的列数
*/
private Integer rowCelNum = 0;
/**
* 行数
*/
private Integer rowNum = 0;
/**
* 班次数据
*/
private List shiftList;
/*
public ShiftSheetWriteHandler(List shiftList, int rowCelNum) {
this.shiftList = shiftList;
this.rowCelNum = rowCelNum;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
//班次转map
Map mapDropDown = new HashMap();
String[] shiftTimeArr = new String[shiftList.size()];
for (int i = 0; i 
