• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Java JExcel API java.lang.outofmemoryerror

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
mynus482
Asked:
mynus482
  • 7
  • 5
  • 2
1 Solution
 
CEHJCommented:
Why not just write it as CSV? You can open it afterwards as a spreadsheet
0
 
mynus482Author Commented:
Formatting is necessary
0
 
CEHJCommented:
Couldn't you format it as you write it?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mynus482Author Commented:
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
 
CEHJCommented:
My suggestion is to use a csv format. You don't need an Excel API and you'll get around the memory problems
0
 
mynus482Author Commented:
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
 
CEHJCommented:
Tell me more about the formatting
0
 
mynus482Author Commented:
Bold title color font, bold color column headings, as well as alternating colors for each row to make reading simpler, etc.
0
 
CEHJCommented:
Ah i see - that's different then. My suggestion doesn't apply. Perhaps you should try POI HSSF instead
0
 
mynus482Author Commented:
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
 
objectsCommented:
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
 
mynus482Author Commented:
How would i start a new VM from within my app?
0
 
mynus482Author Commented:
Is there another way with not as much overhead?
0
 
objectsCommented:
> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now