?
Solved

Java JExcel API java.lang.outofmemoryerror

Posted on 2007-08-03
14
Medium Priority
?
525 Views
Last Modified: 2012-06-21
I am currently running a database query and returning a Resultset object of over 12000+ records.  I process this resultset into an arraylist of an string arraylist, with each inner arraylist representing a record from the database.  Everything works fine until I try to write all this data to an excel file using JExcel.  I run the java garbage collector (System.gc()) before the following code, which is where a java.lang.outofmemoryerror error is thrown:
    List record;
    Object value;
    Label theCell;
    List dataList; //contains arraylist of arraylists
    String cellValue;
    for(int i=1;i<dataList.size();i++)
    {
      record = null;
      record = (ArrayList) dataList.get(i);
      colNo=0;
      rowNo++;
      for(int j=0;j<record.size();j++)
      {
        value = null;
        cellValue = null;
        theCell = null;
        value = record.get(j);
        if(value!=null)
        {
          cellValue = value.toString();
        }
        // Sets color scheme for every other row
        if(rowNo%2!=0)
        {
          theCell = new Label(colNo,rowNo,cellValue,formatRecord);
        }
        else
        {
          theCell = new Label(colNo,rowNo,cellValue);
        }
       
        theSheet.addCell(theCell);
        colNo++;
      }
    }
Anyway that I can split up the processing to avoid the error?  I don't have access to any command line arguments since this is a small portion of a much bigger jsp project.  Therefore, changing the heap size is not an option. As far as I know, with a big enough resultset, altering the heap size would still not take care of the issue.

Any and all comments are welcome/needed!

Thanks
0
Comment
Question by:mynus482
  • 7
  • 5
  • 2
14 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 19627756
Why not just write it as CSV? You can open it afterwards as a spreadsheet
0
 

Author Comment

by:mynus482
ID: 19627779
Formatting is necessary
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19627814
Couldn't you format it as you write it?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mynus482
ID: 19627859
What do you mean?
The basic function is that a user enters a bunch of criteria into a jsp page, database stored proc is run based on the criteria and aformatted excel file with the results is generated and output for the user to open/save. I am currently outputting the jexcel created file using a servletoutputstream.  Not sure what your suggestion is....
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19627873
My suggestion is to use a csv format. You don't need an Excel API and you'll get around the memory problems
0
 

Author Comment

by:mynus482
ID: 19627928
Right but if I output to a csv and output that to excel, I will have no control over the format that the user will get the excel file in.  I was confused about your second suggestion: "Couldn't you format it as you write it?"
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19627944
Tell me more about the formatting
0
 

Author Comment

by:mynus482
ID: 19628007
Bold title color font, bold color column headings, as well as alternating colors for each row to make reading simpler, etc.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 19628150
Ah i see - that's different then. My suggestion doesn't apply. Perhaps you should try POI HSSF instead
0
 

Author Comment

by:mynus482
ID: 19628212
Yeah....at this point switching to POI isn't an option -- Ironically, I had moved from POI to JExcel since it was recommended to me as being more scalable.
0
 
LVL 92

Expert Comment

by:objects
ID: 19628954
JExcel is a memory pig unfortunately. I'd be considering starting up a new VM that you could increase the memory foortprint. You could bring down your entire app running it from from within the apps VM.
0
 

Author Comment

by:mynus482
ID: 19628966
How would i start a new VM from within my app?
0
 

Author Comment

by:mynus482
ID: 19629049
Is there another way with not as much overhead?
0
 
LVL 92

Accepted Solution

by:
objects earned 1500 total points
ID: 19629743
> How would i start a new VM from within my app?

runtime.exec()

> Is there another way with not as much overhead?

not really, otherwise u risk bringing the entire app down.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question