I have to implement export of users into XLS. After a little research JXLS came up as simple solution, but there is one problem. I want to avoid OutOfMemory exception, so I have to fetch users and write them to Excel in chunks, and I don't know how to do that with JXLS.
Here is my code (based on solutions found all over the Internet):
try {
Workbook workbook = WorkbookFactory.create(new FileInputStream("/Users/javaer/Documents/xlsx/templates/users_template.xlsx"));
SelectSheetsForStreamingPoiTransformer transformer = new SelectSheetsForStreamingPoiTransformer(workbook);
transformer.setDataSheetsToUseStreaming(Set.of("Template"));
List<User> users = userRepository.findAcceptedBetLogs(filter);
Context context = new PoiContext();
context.getConfig().setIsFormulaProcessingRequired(false);
context.putVar("users", users);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();
Area xlsArea = xlsAreaList.get(0);
xlsArea.applyAt(new CellRef("Template!A1"), context);
transformer.getWorkbook().write(fileOutputStream);
transformer.getWorkbook().close();
transformer.dispose();
} catch (Exception ex) {
logger.error(ex.getMessage());
}
So, here I have to fetch all users into list, and then pass that list to xlsArea which is not good enough in my case.