11

I am using the following code:

File file = new File("abc.xlsx");
InputStream st = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(st);

The xlsx file itself has 25,000 rows and each row has content in 500 columns. During debugging, I saw that the third row where I create a XSSFWorkbook, it takes a lot of time (1 hour!) to complete this statement.

Is there a better way to access the values of the original xlsx file?

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
London guy
  • 27,522
  • 44
  • 121
  • 179

2 Answers2

23

First up, don't load a XSSFWorkbook from an InputStream when you have a file! Using an InputStream requires buffering of everything into memory, which eats up space and takes time. Since you don't need to do that buffering, don't!

If you're running with the latest nightly builds of POI, then it's very easy. Your code becomes:

File file = new File("C:\\D\\Data Book.xlsx");
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

Otherwise, it's very similar:

File file = new File("C:\\D\\Data Book.xlsx");
OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath());
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);
Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • 1
    If that does not completely solve the problem, then you can use poi event api as a low memory footprint way to read a large file. The poi documentation contains an example here: http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api – Howard Schutzman Jun 22 '12 at 23:59
  • Thanks, will try this out. Just curious to know how will this solve the problem? Won't it buffer the contents into memory? Or will it just access the data using the original references by any chance? – London guy Jun 25 '12 at 06:34
  • 2
    If you open it with a file, less will be buffered than if you open with an inputstream – Gagravarr Jun 26 '12 at 02:10
  • This approach is not working for me, my execution time stops for 5 mins at this line ' XSSFWorkbook workbook = new XSSFWorkbook(); ' and then executes the next line – akaushik Jun 23 '22 at 12:32
  • @akaushik It works for almost everyone else, so it's probably a bug with your system. Do a Java thread dump and see where it is blocking, then fix that – Gagravarr Jun 23 '22 at 14:36
1

Consider using the Streaming version of POI. This will load a subset of the file into memory as needed. It is the recommended method when dealing with large files.

POI SXSSF

John B
  • 32,493
  • 6
  • 77
  • 98