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