本文介绍了如何使用EasyExcel在Java中导出包含多个工作簿和sheet的Excel报告，支持数据动态填充模板并确保公式刷新功能。
0.背景
导出的报表需要以同一个模板，根据数据的不同，导出在一个workbook中的不同sheet中，同时，需要对该模板数据进行一个单个workbook导出。总的来说，一个n+1个workbook，n个单个的，1个汇总所有sheet页的，这都需要共用一个模板
1.导出主体
@Override
public void exportRXZCFZ(String period, BigDecimal exchangeRate) {
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();
ZipOutputStream zipOut = null;
ByteArrayOutputStream outputStream = null;
String templatePath = "/excel/salesdept/SDReptRXZCFZTemp.xlsx";
String foreignTemplatePath = "/excel/salesdept/SDReptRXZCFZ_foreignTemp.xlsx";
String rmbSingleTemplatePath = "/excel/salesdept/SDReptRXZCFZTemp_single.xlsx";
String foreignSingleTemplatePath = "/excel/salesdept/SDReptRXZCFZ_foreignTemp_single.xlsx";
FillConfig fillCfgHor = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).forceNewRow(Boolean.TRUE).build();
try{
String fileName = "人行资产负债报表"+ DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS").format(LocalDateTime.now())+".zip";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-Disposition", "attachment;filename=" +
URLEncoder.encode(new String(fileName.getBytes(), "UTF-8"), "UTF-8"));
ClassPathResource rmbTempResource = new ClassPathResource(templatePath);
ClassPathResource rmbSingleResource = new ClassPathResource(rmbSingleTemplatePath);
ClassPathResource foreignTempResource = new ClassPathResource(foreignTemplatePath);
ClassPathResource foreignSingleTempResource = new ClassPathResource(foreignSingleTemplatePath);
//处理导出的zip 文件名称，避免中文乱码
zipOut = new ZipOutputStream(response.getOutputStream());
//本币
//准备数据
List<ExcelFillModel4SDReptRXZCFZ> rmbMdatas = rxzcfzService.getSDDataSumRXZCFZBaseData(period,true,exchangeRate);
//准备模板
XSSFWorkbook rmbWorkbook = new XSSFWorkbook(rmbTempResource.getInputStream());
//分支需要调整列数
XSSFSheet sheet0 = rmbWorkbook.cloneSheet(0, "RMB-"+rmbMdatas.get(0).getCompanyName());
for(int startIndex = 41; startIndex >= 6 ;startIndex--){
ExcelUtil.deleteColumn(sheet0,startIndex);
}
for(int i = 1; i < rmbMdatas.size(); i++){
//复制模板，得到第i个sheet
rmbWorkbook.cloneSheet(1, "RMB-"+rmbMdatas.get(i).getCompanyName());
}
rmbWorkbook.cloneSheet(0, "RMB-分支汇总");
rmbWorkbook.removeSheetAt(0);
// 刷新公式
rmbWorkbook.setForceFormulaRecalculation(true);
//使用evaluateFormulaCell对函数单元格进行强行更新计算
rmbWorkbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
ByteArrayOutputStream rmbBos = new ByteArrayOutputStream();
rmbWorkbook.write(rmbBos);
//设置 zip 中文件名称
zipOut.putNextEntry(new ZipEntry("本币-人行资产负债报表.xlsx"));
InputStream rmbCopyInputStream = new ByteArrayInputStream(rmbBos.toByteArray());
outputStream = new ByteArrayOutputStream();
ExcelWriter rmbWriter = EasyExcel.write(outputStream).withTemplate(rmbCopyInputStream).excelType(ExcelTypeEnum.XLSX).build();
//单个
int sheetNo = 0;
for(ExcelFillModel4SDReptRXZCFZ mdata : rmbMdatas){
WriteSheet rmbSheet = EasyExcel.writerSheet(sheetNo).build();
List<ExcelFillModel4SDReptRXZCFZ> tempList = new ArrayList<>();
tempList.add(mdata);
rmbWriter.fill(tempList,fillCfgHor,rmbSheet);
sheetNo++;
}
//汇总
WriteSheet rmbSheet = EasyExcel.writerSheet(sheetNo).build();
rmbWriter.fill(rmbMdatas,fillCfgHor,rmbSheet);
//刷到输出流中
rmbWriter.finish();
outputStream.writeTo(zipOut);
outputStream.flush();
outputStream.close();
zipOut.closeEntry();
//单省份workbook
for(ExcelFillModel4SDReptRXZCFZ rmbModel : rmbMdatas){
//准备模板
XSSFWorkbook rmbWbk = new XSSFWorkbook(rmbSingleResource.getInputStream());
// 刷新公式
rmbWbk.setForceFormulaRecalculation(true);
//使用evaluateFormulaCell对函数单元格进行强行更新计算
rmbWbk.getCreationHelper().createFormulaEvaluator().evaluateAll();
ByteArrayOutputStream bos= new ByteArrayOutputStream();
rmbWbk.write(bos);
//设置 zip 中文件名称
zipOut.putNextEntry(new ZipEntry("本币-人行资产负债报表-"+rmbModel.getCompanyName()+".xlsx"));
InputStream is = new ByteArrayInputStream(bos.toByteArray());
outputStream = new ByteArrayOutputStream();
ExcelWriter writer = EasyExcel.write(outputStream).withTemplate(is).excelType(ExcelTypeEnum.XLSX).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
writer.fill(rmbModel,fillCfgHor,sheet);
writer.finish();
outputStream.writeTo(zipOut);
outputStream.flush();
outputStream.close();
zipOut.closeEntry();
}
//外币
//准备数据
List<ExcelFillModel4SDReptRXZCFZ> foreignMdatas = rxzcfzService.getSDDataSumRXZCFZBaseData(period,false,exchangeRate);
//准备模板
XSSFWorkbook foreignWorkbook = new XSSFWorkbook(foreignTempResource.getInputStream());
//分支需要调整列数
XSSFSheet foreignSheet0 = foreignWorkbook.cloneSheet(0, "外币-"+foreignMdatas.get(0).getCompanyName());
for(int startIndex = 41; startIndex >= 6 ;startIndex--){
ExcelUtil.deleteColumn(foreignSheet0,startIndex);
}
for(int i = 1; i < foreignMdatas.size(); i++){
//复制模板，得到第i个sheet
foreignWorkbook.cloneSheet(1, "外币-"+foreignMdatas.get(i).getCompanyName());
}
foreignWorkbook.cloneSheet(0, "外币-分支汇总");
foreignWorkbook.removeSheetAt(0);
// 刷新公式
foreignWorkbook.setForceFormulaRecalculation(true);
//使用evaluateFormulaCell对函数单元格进行强行更新计算
foreignWorkbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
ByteArrayOutputStream foreignBos = new ByteArrayOutputStream();
foreignWorkbook.write(foreignBos);
//设置 zip 中文件名称
zipOut.putNextEntry(new ZipEntry("外币-人行资产负债报表.xlsx"));
InputStream foreignCopyInputStream = new ByteArrayInputStream(foreignBos.toByteArray());
outputStream = new ByteArrayOutputStream();
ExcelWriter foreignWriter = EasyExcel.write(outputStream).withTemplate(foreignCopyInputStream).excelType(ExcelTypeEnum.XLSX).build();
//单个
int foreignSheetNo = 0;
for(ExcelFillModel4SDReptRXZCFZ mdata : foreignMdatas){
WriteSheet foreignSheet = EasyExcel.writerSheet(foreignSheetNo).build();
List<ExcelFillModel4SDReptRXZCFZ> tempList = new ArrayList<>();
tempList.add(mdata);
foreignWriter.fill(tempList,fillCfgHor,foreignSheet);
foreignSheetNo++;
}
//汇总
WriteSheet foreignSheet = EasyExcel.writerSheet(foreignSheetNo).build();
foreignWriter.fill(foreignMdatas,fillCfgHor,foreignSheet);
//刷到输出流中
foreignWriter.finish();
outputStream.writeTo(zipOut);
outputStream.flush();
outputStream.close();
zipOut.closeEntry();
//单省份workbook
for(ExcelFillModel4SDReptRXZCFZ foreignModel : foreignMdatas){
//准备模板
XSSFWorkbook foreignWbk = new XSSFWorkbook(foreignSingleTempResource.getInputStream());
// 刷新公式
foreignWbk.setForceFormulaRecalculation(true);
//使用evaluateFormulaCell对函数单元格进行强行更新计算
foreignWbk.getCreationHelper().createFormulaEvaluator().evaluateAll();
ByteArrayOutputStream bos= new ByteArrayOutputStream();
foreignWbk.write(bos);
//设置 zip 中文件名称
zipOut.putNextEntry(new ZipEntry("外币-人行资产负债报表-"+foreignModel.getCompanyName()+".xlsx"));
InputStream is = new ByteArrayInputStream(bos.toByteArray());
outputStream = new ByteArrayOutputStream();
ExcelWriter writer = EasyExcel.write(outputStream).withTemplate(is).excelType(ExcelTypeEnum.XLSX).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
writer.fill(foreignModel,fillCfgHor,sheet);
writer.finish();
outputStream.writeTo(zipOut);
outputStream.flush();
outputStream.close();
zipOut.closeEntry();
}
}catch (Exception ex){
log.error("下载失败:period:{},错误:{}", period, ExceptionUtils.getStackTrace(ex));
ExcelUtil.exportFail("下载失败");
} finally {
if (null != outputStream) {
try {
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != zipOut) {
try {
zipOut.flush();
zipOut.finish();
zipOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
2.删除多余列
/**
* 删除列
* @param sheet
* @param columnToDelete
*/
public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
Row row = sheet.getRow(rId);
for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
Cell cOld = row.getCell(cID);
if (cOld != null) {
row.removeCell(cOld);
}
Cell cNext = row.getCell(cID + 1);
if (cNext != null) {
Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
cloneCell(cNew, cNext);
//Set the column width only on the first row.
//Other wise the second row will overwrite the original column width set previously.
if (rId == 0) {
sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
}
}
}
}
}
扫一扫
9484
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
