JXLS - how to write data to workbook in batches

579 Views Asked by At

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.

0

There are 0 best solutions below