EasyPoi 导出同文件多Sheet/多文件zip
同文件多Sheet
主要代码块
XSSFWorkbook resultWorkbook = new XSSFWorkbook();
exportVo.getReportTypeList().forEach(type -> {
switch (type) {
case 1://1,日报;
XSSFWorkbook workbook = this.getDayWorkbook(exportVo);
copySheet(resultWorkbook, workbook.getSheetAt(0));
break;
case 2://2,月报;
workbook = this.getMonthWorkbook(exportVo);
copySheet(resultWorkbook, workbook.getSheetAt(0));
break;
case 3://3,打卡记录 纵向记录;
workbook = this.getCheckInRecordWorkbook(exportVo);
copySheet(resultWorkbook, workbook.getSheetAt(0));
break;
case 4://4,考勤异常;
workbook = this.getAttendanceErrorWorkbook(exportVo);
copySheet(resultWorkbook, workbook.getSheetAt(0));
break;
case 5://5,统计图表;
break;
default: throw new RuntimeException("报表类型异常");
}
});
try {
FileOutputStream out = new FileOutputStream(exportVo.getFileName() + ".xlsx");
resultWorkbook.write(out);
out.close();
} catch (Exception e) {
e.getStackTrace();
throw new RuntimeException(e);
}
复制sheet
private void copySheet(XSSFWorkbook wbCreat, XSSFSheet sheet) {
XSSFSheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());
// 复制源表中的合并单元格
MergerRegion(sheetCreat, sheet);
int firstRow = sheet.getFirstRowNum();
int lastRow = sheet.getLastRowNum();
for (int i = firstRow; i <= lastRow; i++) {
// 创建新建excel Sheet的行
XSSFRow rowCreat = sheetCreat.createRow(i);
// 取得源有excel Sheet的行
XSSFRow row = sheet.getRow(i);
//行所以单元格与行首单元格样式一致。
final XSSFCellStyle style = rowCreat.getSheet().getWorkbook().createCellStyle();
// 克隆出一个 style
style.cloneStyleFrom(row.getCell(0).getCellStyle());
// 单元格式样
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
for (int j = firstCell; j < lastCell; j++) {
final XSSFCell cell = rowCreat.createCell(j);
cell.setCellStyle(style);
String strVal = "";
if (row.getCell(j) != null) {
strVal = row.getCell(j).getStringCellValue();
}
cell.setCellValue(strVal);
// 设置最大列宽
final int width = sheetCreat.getColumnWidth(j) / 256;
final int length = strVal.getBytes().length;
if (width < length) {
sheetCreat.setColumnWidth(j, length * 256);
}
}
}
}
/**
* 复制原有sheet的合并单元格到新创建的sheet
*
* @param sheetCreat 新创建sheet
* @param sheet 原有的sheet
*/
private static void MergerRegion(XSSFSheet sheetCreat, XSSFSheet sheet) {
int sheetMergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
sheetCreat.addMergedRegion(sheet.getMergedRegion(i));
}
}
多文件zip
主要代码块
List<XSSFWorkbook> excels = new ArrayList<>();
exportVo.getReportTypeList().forEach(type -> {
switch (type) {
case 1://1,日报;
excels.add(this.getDayWorkbook(exportVo));
break;
case 2://2,月报;
excels.add(this.getMonthWorkbook(exportVo));
break;
case 3://3,打卡记录 纵向记录;
excels.add(this.getCheckInRecordWorkbook(exportVo));
break;
case 4://4,考勤异常;
excels.add(this.getAttendanceErrorWorkbook(exportVo));
break;
case 5://5,统计图表;
break;
default: throw new RuntimeException("报表类型异常");
}
});
ExcelUtil.downFileByStream(response, excels, exportVo.getFileName());
/**
* 直接下载zip包
* @param response response
* @param excels wb集合
*/
public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels, String fileName){
try {
// FileOutputStream toClient = new FileOutputStream(fileName +".zip");
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("filename", URLEncoder.encode(fileName + ".zip", "UTF-8"));
ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);
for (XSSFWorkbook excel : excels) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
// 将Workbook写入内存流
excel.write(baos);
ZipEntry zipEntry = new ZipEntry(excel.getSheetAt(0).getSheetName() + ".xlsx");
zipOutputStream.putNextEntry(zipEntry);
// 将内存流写入Zip文件
zipOutputStream.write(baos.toByteArray());
}
zipOutputStream.closeEntry();
zipOutputStream.flush();
zipOutputStream.close();
toClient.close();
}catch (Exception e){
LOGGER.error("downFileByStream==========fail:{}", e.getMessage());
}
}
相关依赖
<!-- easypoi相关依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>