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 can 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
Aucun commentaire:
Enregistrer un commentaire