数据量小的话可以setMemory 为true
已解决。自定义SheetWriteHandler,将固定数据写入sheet,然后写入数据时,跳过n行才写入表头和表数据。由于这里是动态头,所以使用了无对象的写入方式。可根据需要自行调整
@Slf4j
public class ProjectExportSheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
//
Row row1 = sheet.createRow(0);
Cell cell = row1.createCell(0);
cell.setCellValue("报表逻辑: 展示时点情况,每日封存(以每天00:00为节点,今日修改后要24点后导出才能查看最新情况)");
cell.setCellStyle(buildRow1CellStyle(workbook));
//
Row row2 = sheet.createRow(1);
Cell cell1 = row2.createCell(0);
cell1.setCellValue("");
}
private CellStyle buildRow1CellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
Font font = workbook.createFont();
font.setBold(false);
font.setColor(HSSFColor.HSSFColorPredefined.ORANGE.getIndex());
cellStyle.setFont(font);
return cellStyle;
}
}
导出时
public static <T> void writeWith(HttpServletResponse response, String filename, String sheetName,
List<List<String>> head, List<T> data, WriteHandler... writeHandlers) throws IOException {
// 设置 header 和 contentType
setResponse(response, filename);
// 输出 Excel
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream())
.head(head);
for (WriteHandler writeHandler : writeHandlers) {
excelWriterBuilder.registerWriteHandler(writeHandler);
}
// 不要自动关闭,交给 Servlet 自己处理
excelWriterBuilder.autoCloseStream(false)
.relativeHeadRowIndex(3) // 关键点
.excelType(ExcelTypeEnum.XLS) // 关键点
.sheet(sheetName)
.doWrite(data);
}