poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: OutOfMemory Issue
Date Fri, 26 Jun 2009 07:00:12 GMT

POI is quite memory hungrey once yo begin working with larger sheets and the
only option you really have is to increase the amount of memory available to
the VM.

Crudely, the BIFF8 file format consists of sections of 'data' that are
'linked' together using pointers. These pointers effectively indicate where
- in terms of an offset expressed as a number of bytes - in the file certain
information can be found. As a result, the file cannot be written out in
'chunks' as it is not possible to determine in advance where in the file
certain pieces of information might be written. So POI has to hold in memory
quite a large number of objects.

Depending on your requirement, one way that is used to get around problems
like this is to create a CSV file that contains the data you want to see in
the worksheet. All you then need to do is to use Excel to open that file and
it will automatically create and populate a worksheet for you. The obvious
drawback with this approach is that it is not possible to perform any
complex cell formatting.

Are you compelled to use the binary file format? Could you instead switch to
using the OpenXML file format that was introduced in Office 2007? If so,
then it could be worthwhile searching carefully through the posts made to
this list over the last year or so. Somewhere at the back of my mind I can
remember Yegor posting a solution that involved merging the data with
another object in some way to create a workbook. If I have the time, I will
look myself but cannot promise anything.


Mark B

Hima Bindu wrote:
> Hi All,
> Iam trying to evaluate Apache POI so that I can use it for my project in
> company.
> Following is the sample program I ran with the following JVM options:
> -Xms256m -Xmx1024m
> import java.io.File;
> import java.io.FileOutputStream;
> import org.apache.poi.hssf.usermodel.HSSFCell;
> import org.apache.poi.hssf.usermodel.HSSFRow;
> import org.apache.poi.hssf.usermodel.HSSFSheet;
> import org.apache.poi.hssf.usermodel.HSSFWorkbook;
> public class TestExcel {
>     public TestExcel() {
>     }
>     public static void main(String[] args) throws Exception {
>         HSSFWorkbook mainWorkBook = new HSSFWorkbook();
>         HSSFSheet mainSheet = mainWorkBook.createSheet();
>         HSSFRow newRow = null;
>         HSSFCell newCell = null;
>         for (int i = 0; i < 30000; i++) {
>             newRow = mainSheet.createRow(i);
>             for (int j = 0; j < 255; j++) {
>                 newRow.createCell(j).setCellValue("Test");
>             }
>         }
>         FileOutputStream output = new FileOutputStream(new
> File("C:\\Test\\Test.xls"));
>         mainWorkBook.write(output);
>         output.flush();
>         output.close();
>     }
> }
> The program works fine till 20000 rows, but I get OutOfMemory exception
> for
> 30000 rows. I did search in some forums regarding this issue, but I didnt
> get proper solution.
> Can you guys suggest me how can I do it? Is it possible to write part of
> the
> workbook data to the file ->  clear the workbook -> write to the file etc?
> -- 
> With regards,
> B.R.Hima Bindu.
> Yogi Berra <http://www.brainyquote.com/quotes/authors/y/yogi_berra.html> 
> -
> "I never said most of the things I said."

View this message in context: http://www.nabble.com/OutOfMemory-Issue-tp24203585p24215859.html
Sent from the POI - User mailing list archive at Nabble.com.

To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

View raw message