Writing large number of rows using jxls to excel in java

Question

I am using the below code to to write data to excel

XLSTransformer transformer = new XLSTransformer();

        InputStream is = this.getServlet().getServletContext()
                .getResourceAsStream(templateFilePath);
        HSSFWorkbook workBook = (HSSFWorkbook) transformer.transformXLS(is, beans);

But but the system hangs after HSSFWorkbook workBook = (HSSFWorkbook) transformer.transformXLS(is, beans); if number of rows is more than 1500. Is there any other way or suggetsion for writing data using template and beans objects for large data sets

score 2 · Answer 1 · answered Mar 11 '16 at 08:02

You can upgrade to Jxls-2 and use SXSSF Transformer support. The code may look like this

            Transformer transformer = PoiTransformer.createSxssfTransformer(workbook, 100, false);
            AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
            List<Area> xlsAreaList = areaBuilder.build();
            Area xlsArea = xlsAreaList.get(0);
            xlsArea.applyAt(new CellRef("Result!A1"), context);

See full example in jxls-demo. Please note that this approach assumes some template restrictions given that only a subset of rows is kept in memory (in particular regarding to formula evaluation).

If you do not need formulas it is recommended to disable formula processing using context.getConfig().setIsFormulaProcessingRequired(false);

score 1 · Answer 2 · answered Mar 11 '16 at 04:44

1

You could try to use SXSSF - the streaming version of the POI workbook. This is a guess.

answered Mar 11 '16 at 04:44

neal

880
6
11

1

using SXXF i cant use template and bean object, – user2025528 Mar 11 '16 at 04:52

Writing large number of rows using jxls to excel in java

2 Answers2