废话不多说 直接上代码:
public static void exportCleanPlan(HttpServletResponse response, List<CleanPlanRequest> list, CleanPlanRequest request, List<String> dicList, Set<String> set) {
if (CollectionUtil.isEmpty(list)) {
list = requestList();
}
try (ExcelWriter writer = ExcelUtil.getWriter()) {
//重写Sheet
writer.renameSheet("导入数据");
// 表头处理
List<String> rowHead = CollUtil.newLinkedList("网格组", "日期", "道路名称", "开始时间", "结束时间",
"清洗方式", "清洗人员", "清洗人员手机号", "补位人员", "补位人员手机号", "清洗车辆", "替班车辆");
List<List<Object>> rows = new LinkedList<>();
//表头写入第一行
writer.writeHeadRow(CollUtil.newLinkedList("清洗计划"));
//writer.merge(rowHead.size() - 1, false);
writer.merge(0, 0, 0, rowHead.size() - 1, "清洗计划", false);
//表头写入第二行
writer.writeHeadRow(CollUtil.newLinkedList("项目:" + request.getItemName(), "开始日期:" + request.getStartDate(), "结束日期:" + request.getEndDate()));
writer.merge(1, 1, 0, 1, "项目:" + request.getItemName(), false);
writer.merge(1, 1, 2, 5, "开始日期:" + request.getStartDate(), false);
writer.merge(1, 1, 6, 11, "结束日期:" + request.getEndDate(), false);
//表头第三行
writer.writeHeadRow(rowHead);
//增加下拉列
//writer.addSelect(2, 3, String.join(",", set));
//writer.addSelect(new CellRangeAddressList(3, 7, 2, 2), String.join(",", set));
//writer.addSelect(5, 3, String.join(",", dicList));
writer.addSelect(new CellRangeAddressList(3, 7, 5, 5), String.join(",", dicList));
if (list.size() > 0) {
list.stream().forEach(item -> {
LinkedList<Object> row = CollUtil.newLinkedList(
item.getGridName()
, item.getCleanDate()
, item.getGeoName()
, item.getCleanStartTime()
, item.getCleanEndTime()
, item.getDicName()
, item.getListCleanPer()
, item.getListPhone()
, item.getListCleanPerCover()
, item.getListPhoneAlt()
, item.getCleanVehicle()
, item.getCleanVehicle()
);
rows.add(row);
});
}
//内容
writer.write(rows, true);
//设置单元格为文本
StyleSet styleSet = writer.getStyleSet();
//CellStyle cellStyle = styleSet.getCellStyleForNumber();
CellStyle cellStyle1 = styleSet.getCellStyleForDate();
DataFormat format = writer.getWorkbook().createDataFormat();
//cellStyle.setDataFormat(format.getFormat("@"));
cellStyle1.setDataFormat(format.getFormat("@"));
writer.setStyleSet(styleSet);
layout(writer, list);
//创建第二个Sheet
writer.setSheet("Sheet2");
//writer.setFreezePane(3);
writer.write(set, true);
//将Sheet2中的数据引用到Sheet1中的下拉框
Workbook workbook = writer.getWorkbook();
Name namedCell = workbook.createName();
namedCell.setNameName("Sheet2");
namedCell.setRefersToFormula("Sheet2!$A$1:$A$" + set.size());
//加载数据,将名称为hidden的
DVConstraint constraint = DVConstraint.createFormulaListConstraint("Sheet2");
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(3, 100, 2, 2);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
//隐藏Sheet2
workbook.setSheetHidden(1, true);
writer.getSheets().get(0).addValidationData(validation);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("UTF-8");
// 弹出下载对话框的文件名,中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(request.getItemName() + "清洗计划", "utf-8") + ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out);
writer.close();
IoUtil.close(out);
} catch (Exception e) {
log.error("清洗计划导出失败{}", e.getMessage());
e.printStackTrace();
}
}