Monday, April 26, 2004

POI Optimization - eliminating trailing empty rows from an HSSFSheet.

While my spreadsheet has only 7 rows with data, POI creates over 65,000 rows in the HSSFSheet object. This leads to a large amount of essentially unused memory. In order to free that memory, the following code snippet from my version of the public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes) method works from bottom towards the top of the sheet removing empty rows. The code in bold performs the optimization.

            HSSFSheet hsheet = new HSSFSheet(workbook, sheet);

            boolean stop = false;
            boolean nonBlankRowFound;
            short c;
            HSSFRow lastRow = null;
            HSSFCell cell = null;

            while (stop == false) {
                nonBlankRowFound = false;
                lastRow = hsheet.getRow(hsheet.getLastRowNum());
                for (c = lastRow.getFirstCellNum(); c <= lastRow.getLastCellNum(); c++) {
                    cell = lastRow.getCell(c);
                    if (cell != null && lastRow.getCell(c).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                        nonBlankRowFound = true;
                    }
                }
                if (nonBlankRowFound == true) {
                    stop = true;
                } else {
                    hsheet.removeRow(lastRow);
                }
            }

            sheets.add(hsheet);
Post a Comment