mardi 9 juin 2015

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

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