Java 利用Hutool——ExcelWriter 导出Excel 合并单元格,导出多级表头
操作思路,对单个单元格进行操作,并赋值
@ApiOperation("WIP报表接口——数据导出")
@PostMapping("/report/export/dto")
public void exportExcel(HttpServletResponse response,@RequestBody ReportStationRecordWipDto dto){
ExcelWriter writer = ExcelUtil.getWriter(true);
ServletOutputStream out = null;
try {
// URLEncoder.encode 防止中文乱码
String fileName = new String(("WIP报表").getBytes(), "utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
out = response.getOutputStream();
List<ProductionVo> proList = iReportStationRecordService.getOrderNoByLineId(dto);
//设置宽度
writer.setColumnWidth(-1,15);
//合并单元格
writer.merge(0, 1, 0, 0, "生产订单号", false);
writer.merge(0, 1, 1, 1, "产品编码", false);
writer.merge(0, 1, 2, 2, "物料名称", false);
writer.merge(0, 1, 3, 3, "物料规格", false);
writer.merge(0, 1, 4, 4, "生产数量", false);
writer.merge(0, 1, 5, 5, "完成数量", false);
writer.merge(0, 1, 6, 6, "清洗", false);
writer.merge(0, 1, 7, 7, "灭菌", false);
List<Station> stationList = proList.get(1).getStationList();
if (stationList.size()!=0){
writer.merge(0, 0, 8, 8+stationList.size()-1, "工序", false);
for (int i = 0; i < stationList.size() ; i++) {
writer.writeCellValue(8+i,1,stationList.get(i).getStationName());
}
}else{
writer.merge(0, 0, 8, 8, "工序", false);
}
//跳过标题行
writer.passCurrentRow();
//为单元格赋值
int z = 2;
for (int i = 0; i < proList.size(); i++) {
writer.writeCellValue(0,z+i,proList.get(i).getOrderNo());
writer.writeCellValue(1,z+i,proList.get(i).getBomCode());
writer.writeCellValue(2,z+i,proList.get(i).getBomName());
writer.writeCellValue(3,z+i,proList.get(i).getBomStd());
writer.writeCellValue(4,z+i,proList.get(i).getQty());
writer.writeCellValue(5,z+i,proList.get(i).getPassQty());
writer.writeCellValue(6,z+i,proList.get(i).getClearCount());
writer.writeCellValue(7,z+i,proList.get(i).getDisinfectCount());
List<Station> list = proList.get(i).getStationList();
List<ProductionWIPVo> productionWIPList = proList.get(i).getProductionWIPList();
//为工序赋值
if (list.size()!=0){
for (int j = 0; j < list.size(); j++) {
int finalJ = j;
int finalI = i;
productionWIPList.forEach(e->{
if (list.get(finalJ).getProcessId().equals(e.getProcessId())){
writer.writeCellValue(8+finalJ,z+finalI,e.getReCount());
}
});
}
}
}
writer.flush(out, true);
} catch (Exception e) {
logger.error(e.getMessage());
} finally {
// 关闭writer,释放内存
writer.close();
//关闭输出流
IoUtil.close(out);
}
}