3

I am developing a web application which reads data from excel file (xlsx). I am using POI for reading excel sheet. The problem is when I try to read excel file, the server throws the following error:

enter image description here

The excel file I am trying to read has size of almost 80 MB. Any solution to this problem?

Actually user is uploading file and application after saving file to disk try to read file. The code snippet I am using for testing is:

 File savedFile = new File(file_path);

FileInputStream fis = null;
            try {

                fis = new FileInputStream(savedFile);
                XSSFWorkbook xWorkbook = new XSSFWorkbook(fis);
                XSSFSheet xSheet = xWorkbook.getSheetAt(5);

                Iterator rows = xSheet.rowIterator();
                while (rows.hasNext()) {
                    XSSFRow row = (XSSFRow) rows.next();
                    Iterator cells = row.cellIterator();

                    List data = new ArrayList();
                    while (cells.hasNext()) {
                        XSSFCell cell = (XSSFCell) cells.next();
                        System.out.println(cell.getStringCellValue());
                        data.add(cell);
                    }

                }
            } catch (IOException e) {
                e.printStackTrace();
            }
Khawar Raza
  • 15,870
  • 24
  • 70
  • 127

7 Answers7

3

One thing that'll make a small difference is when opening the file to start with. If you have a file, then pass that in! Using an InputStream requires buffering of everything into memory, which eats up space. 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(file_path);
OPCPackage opcPackage = OPCPackage.open(file);
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

Otherwise, it's very similar:

File file = new File(file_path);
OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath());
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

That'll free you a bit of memory, which might be enough. If it isn't, and if you can't increase your Java heap space enough to cope, then you'll have to stop using the XSSF UserModel.

In addition to the current, friendly UserModel that you've been using, POI also supports a lower level way to process files. This lower level way is harder to use, as you don't have the various helpers around that require the whole file in memory. However, it's much much more memory efficient, as you process the file in a streaming way. To get started, see the XSSF and SAX (Event API) How-To section on the POI website. Try that out, and also have a look at the various examples.

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
2

You should probably change the settings of you JVM. Try to add -Xmx1024 -Xms1024 to the launcher.

Alexis Dufrenoy
  • 11,784
  • 12
  • 82
  • 124
  • 1
    It is considered bad practice, to set `Xms` equal to `Xmx`. Doing this, you basically disallow JVM to resize it's memory pools, and worsen memory management. – npe Jun 20 '12 at 11:45
  • 1024 bytes? perhaps you meant `-Xmx1024m` which is the same as `-mx1g` – Peter Lawrey Jun 20 '12 at 11:58
  • @npe: You're right. It's not a problem if you application will use much memory anyway. But I don't know if it's the case here. More generally, the idea is to set the Xmx to a higher value. It actually depends on the available memory and the size of the data. – Alexis Dufrenoy Jun 20 '12 at 12:40
  • Just one more thing: the default value is 64M, so for complex processing of large amounts of data, it can fail pretty quickly. – Alexis Dufrenoy Jun 20 '12 at 12:42
  • The default is 64M on the 32-bit windows JVM. On the other platforms its 1/4 of main memory up to 32 GB. – Peter Lawrey Jun 20 '12 at 13:02
  • @Peter: We're both wrong. It's even more complicated. Anyway, thank you for making me check it out! http://www.techpaste.com/2012/02/default-jvm-settings-gc-jit-java-heap-sizes-xms-xmx-operating-systems/ – Alexis Dufrenoy Jun 20 '12 at 13:12
1

You could try to increase your Java heap size.

DP Greveling
  • 409
  • 5
  • 12
1

I think you have to increase the size of the Heap. You can do it by editing the catalina.bat-file. Add -Xms1024m -Xmx1024m to the CATALINA_OPTS variable.

  • Xms = initial java heap size
  • Xmx = maximum java heap size

EDIT: from Catalina.bat 


rem   CATALINA_OPTS   (Optional) Java runtime options used when the "start",
rem                   "run" or "debug" command is executed.
rem                   Include here and not in JAVA_OPTS all options, that should
rem                   only be used by Tomcat itself, not by the stop process,
rem                   the version command etc.
rem                   Examples are heap size, GC logging, JMX ports etc.
lhlmgr
  • 2,087
  • 1
  • 22
  • 38
  • I opened catalina.bat file. I found two to three entries for JAVA_POSTS. Which one to edit? – Khawar Raza Jun 20 '12 at 11:52
  • I actually did following change and pasting the file segment: ------ if not exist "%CATALINA_BASE%\conf\logging.properties" goto noJuliConfig set LOGGING_CONFIG=-Djava.util.logging.config.file="%CATALINA_BASE%\conf \logging.properties" :noJuliConfig set JAVA_OPTS=%JAVA_OPTS% %LOGGING_CONFIG% if not "%LOGGING_MANAGER%" == "" goto noJuliManager set LOGGING_MANAGER=-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager :noJuliManager set JAVA_OPTS=%-Xms1024m -Xmx1024m% %LOGGING_MANAGER% rem ----- Execute The Requested Command --------------------------------------- Is it OK? – Khawar Raza Jun 20 '12 at 12:02
  • there should be a line `set JAVA_OPTS=%JAVA_OPTS% %LOGGING_CONFIG%`change it to `set JAVA_OPTS=%JAVA_OPTS% -Xmx1024m - Xms1024m %LOGGING_CONFIG%` – lhlmgr Jun 20 '12 at 12:16
  • thanks but having same problem. Even I increased these values to 2048. My excel file is 80MB in size. – Khawar Raza Jun 20 '12 at 12:23
  • did you restart the tomcat service? – lhlmgr Jun 20 '12 at 12:25
  • I am using Eclipse EE IDE. I restarted my eclipse but in vain. – Khawar Raza Jun 20 '12 at 12:31
0

I have solved the problem through change in implementation. Actually firstly I was fetching all data from Excel file and data was being stored in ArrayList type. After that I was inserting data into DB and that was the real problem. Now I am not storing data at all. As I get one record from ResultSet, I insert it into DB immediately instead of storing it into arraylist. I know this one by one insertion is not a good approach but for time being I am using this approach. In future if I find better one, I definitely switch to that one. Thanks to all.

Khawar Raza
  • 15,870
  • 24
  • 70
  • 127
0

Improvement to your current approach could be to read around 100 lines (experiment with this figure to get optimum value) from excel and do a batch update in database. This will be more faster.

Also you can possibly perform some optimizations in your code, move the list creation out of outer loop (loop for reading row data)

List data = new ArrayList();

Read contents of all the cells present in a row in a string buffer (possibly delimited with "comma") and then add it to arraylist "data"

You are adding an object of type XSSFRow to the arraylist. There is no point in storing the whole object of excel cell. Take out its contents and discard the object.

Later before inserting the contents in to Database you can split the delimited cell contents and perform insertion.

Hope this helps!

SarZ
  • 256
  • 2
  • 8
-1

You better store them in file and try to load them in database at then end. This will avoid single insert a

vikrant Mhakal
  • 1