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
}