How to avoid out of memory exception in case of reading large .xlsx file in java using apache poi library class XSSFWorkbook

304 views Asked by At

My .xlsx file with more than 50000 records is not working fine when i am reading it through apache poi library class XSSFWorkbook (it is working fine for less records).

But .xls same file i am able to read it through apache poi library class HSSFWorkbook.

Here is my code -

FileInputStream fis = new FileInputStream(fileName);

// Create Workbook instance for xlsx/xls file input stream
org.apache.poi.ss.usermodel.Workbook workbook = null;
if (fileName.toLowerCase().endsWith("xlsx")) {
    workbook = new XSSFWorkbook(fis);
} else if (fileName.toLowerCase().endsWith("xls")) {
    workbook = new HSSFWorkbook(fis);
}
int numberOfSheets = workbook.getNumberOfSheets();

// loop through each of the sheets
for (int i = 0; i < numberOfSheets; i++) {

    // Get the nth sheet from the workbook
    Sheet sheet = workbook.getSheetAt(i);

    // every sheet has rows, iterate over them
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        String category = "";
        String product = "";
        String sales = "";
        String quarter = "";
        String quantity = "";

        ProductBean productBean = new ProductBean();

        // Get the row object
        Row row = rowIterator.next();

        // Every row has columns, get the column iterator and iterate
        // over them
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            // Get the Cell object
            Cell cell = cellIterator.next();

            // check the cell type and process accordingly
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                if (category.equalsIgnoreCase("")) {
                    category = cell.getStringCellValue().trim();
                    productBean.setCategory(category);
                } else if (product.equalsIgnoreCase("")) {
                    // 2nd column
                    product = cell.getStringCellValue().trim();
                    productBean.setProduct(product);
                } else if (quarter.equalsIgnoreCase("")) {
                    // 4nd column
                    quarter = cell.getStringCellValue().trim();
                    productBean.setQuarter(quarter);
                } else {
                    // random data, leave it
                    System.out.println("Random data::" + cell.getStringCellValue());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (sales.equalsIgnoreCase("")) {
                    // 3nd column
                    sales = String.valueOf(cell.getNumericCellValue());
                    productBean.setSales(sales);
                } else if (quantity.equalsIgnoreCase("")) {
                    // 5nd column
                    quantity = String.valueOf(cell.getNumericCellValue());
                    productBean.setQuantity(quantity);
                } else {
                    // random data, leave it
                    System.out.println("Random data::" + cell.getNumericCellValue());
                }
                break;
            }
        } // end of cell iterator
        // ProductBean c = new ProductBean(category, product,sales,quarter,quantity);

        productList.add(productBean);
    } // end of rows iterator
}
0

There are 0 answers