0

I have been following Stack Overflow from quite some time to get help whenever and wherever needed. Most of the questions I have are usually answered already. :) This one is answered earlier too but has not been helpful yet so asking here directly.

The requirement I am working on is: To get into an excel file (irrespective of the size) and to count the number of sheets and total rows (sum of rows in all the sheets) in this excel file. My code is working fine with smaller files but when it comes to read from larger files, Eclipse gives up.

My code is :

import java.io.File;
import java.util.*;
import java.io.*;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportCheck
{
public static void main(String[] args)
{
    List<String> listFile = new ArrayList<String>();

    int count = 0; int pageCount;
    int i; int totalRows, currentSheetRows;
    int sheetCount = 0;
    String filePath="";
    String path;
    System.out.println("enter path:");
    Scanner in = new Scanner(System.in);
    path= in.nextLine();
    File f = new File(path);
    File[] files = f.listFiles();

    if (files != null)
    {
        //getting the name of the file
        for (i = 0; i < files.length; i++) 
        {
            listFile.add(files[i].getName());
            count++;
        }
        //getting the file size and extension of the file
        for(int j=0; j<count;j++)
        {
            filePath= path+"/"+listFile.get(j);

            String fileExtension = getExtension(filePath);
            System.out.println("File "+(j+1)+":\nName:"+listFile.get(j)+"\tSize: "+getFileSize(filePath)+" KB");
            //excel operations - to get no. of worksheets and total no. of rows in excel
            if(fileExtension.equals("xlsx"))
            {
                try
                {
                    totalRows=0;
                    FileInputStream inputStream = new FileInputStream(new File(filePath));
                    Workbook wb = new XSSFWorkbook(inputStream);
                    sheetCount = wb.getNumberOfSheets();


                    for(i=0;i<sheetCount;i++)
                    {
                        Sheet currentSheet = wb.getSheetAt(i);
                        currentSheetRows=currentSheet.getPhysicalNumberOfRows();
                        totalRows=totalRows+currentSheetRows;
                    }
                    System.out.println("No. of sheets in excel: "+sheetCount+"\tTotal  No. of rows: "+totalRows);
                    wb.close();
                }
                catch (Exception e) 
                {
                    e.printStackTrace();
                } 
            }
        }
    }   
}

public static long getFileSize(String filename)
{
    File file = new File(filename);
    if (!file.exists() || !file.isFile())
    {
        System.out.println("File doesn\'t exist");
        return -1;
    }
    long length = file.length()/1024;

    return length;
}

public static String getExtension(String filename)
{
    String extension = "";
    int i = filename.lastIndexOf('.');
    if (i > 0) 
        extension = filename.substring(i+1);
    return(extension);
}
}

I have increased the maximum heap to 1 gig but still this is not helpful.

The error I am getting is :

Eclipse Screenshot

Any help will be appreciated.

Lucky
  • 3
  • 2
  • What can we see in the screenshot? – MordechayS Nov 24 '16 at 07:45
  • Possible duplicate of [Processing large xlsx file in Java](http://stackoverflow.com/questions/4897766/processing-large-xlsx-file-in-java) – MordechayS Nov 24 '16 at 07:47
  • I guess the problem is that the excel is to big to fit in the 1gb heap. You have to mention that the POI libary will build a Object Model out of your excel file. Therefor there is a object created for each page, each row, eack cell and so forth. Maybe you can increase your heap size or you have to think of a alternative way of parsing the excel. – ZeusNet Nov 24 '16 at 07:50
  • can you tell us a bit more about the file you try it with? like amount of sheets and rows for each sheet – XtremeBaumer Nov 24 '16 at 08:12
  • Hi, the code is failing for a 5 MB excel as well containing 18 sheets and 2300 rows in total (divided unequally) – Lucky Nov 24 '16 at 08:55
  • 2
    Did you try following the steps in the [POI memory FAQ entry](http://poi.apache.org/faq.html#faq-N10165)? – Gagravarr Nov 24 '16 at 09:12

0 Answers0