All Charts removed after modifying cell data using 3.5 beta 3

[Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN]

After I use POI 3.5 beta 3 to modify cell data that is referenced by
charts on the same sheet, next time I open the file, I get: "Excel found
unreadable content in 'Dashboard.xlsx'.  Do you want to recover the
contents of this workbook? If you trust the source of this workbook,
click yes."

 

I unzipped a copy of the original file and the same file after right
after using Xssf to modify cell values (unconverted by excel).  The
modified file was missing the Following:

 

 \xl\charts

 \xl\drawings

 \xl\printerSettings

\xl\worksheets\_rels

 \xl\themes

\xl\calcChain.xml

 

Is this known or expected behavior?  If not, what other info would be
helpful in tracking down the problem?

 

Thanks,

 

Paul Dobson

 

[Nick Burch]

On Thu, 2 Oct 2008, Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN wrote:
> I unzipped a copy of the original file and the same file after right
> after using Xssf to modify cell values (unconverted by excel).  The
> modified file was missing the Following:

I know that xssf doesn't copy over all parts of the old file by default,
it only does the bits we support, and some others we don't yet support
but know to keep. Possibly not all of your parts are currently on the
latter list?

Though, I think at least some of the ones you mention should be being
copied over, certainly with the refactorings Yegor has done recently.
Maybe Yegor will have an idea here?

Nick

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

[Yegor Kozlov]

Over the last two weeks I've been working on the read-write round trip in XSSF.
I believe the problem is solved in trunk.

Paul, can you check your code against the latest build of trunk. Nightly builds are available at
http://encore.torchbox.com/poi-svn-build/OOXML-Branch/.

If the problem persists, please open a bug in bugzilla and attach a sample code and the template xlsx with charts.

Yegor

> On Thu, 2 Oct 2008, Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN wrote:
>> I unzipped a copy of the original file and the same file after right
>> after using Xssf to modify cell values (unconverted by excel).  The
>> modified file was missing the Following:
>
> I know that xssf doesn't copy over all parts of the old file by default,
> it only does the bits we support, and some others we don't yet support
> but know to keep. Possibly not all of your parts are currently on the
> latter list?
>
> Though, I think at least some of the ones you mention should be being
> copied over, certainly with the refactorings Yegor has done recently.
> Maybe Yegor will have an idea here?
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@...
> For additional commands, e-mail: user-help@...
>
>

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

[Awais Bajwa-2]

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement is
to pick a specific cell from one row. But when I load the file that is 54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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

[Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN]

I was having the same problem.  I ended up using the event driven model
to parse the file.  Then there is no limit on file size.

-----Original Message-----
From: Awais Bajwa [mailto:abajwa@...]
Sent: Thursday, October 02, 2008 10:34 AM
To: 'POI Users List'
Subject: Out of Memoary Error while reading an EXCEL file

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement
is
to pick a specific cell from one row. But when I load the file that is
54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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


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

[Mahesh Ganapathy]

What is the max jvm heap size? Are you running this on an app server?
Sent via BlackBerry by AT&T

-----Original Message-----
From: "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Paul.Dobson@...>

Date: Thu, 2 Oct 2008 10:34:37
To: POI Users List<user@...>
Subject: RE: Out of Memoary Error while reading an EXCEL file


I was having the same problem.  I ended up using the event driven model
to parse the file.  Then there is no limit on file size.

-----Original Message-----
From: Awais Bajwa [mailto:abajwa@...]
Sent: Thursday, October 02, 2008 10:34 AM
To: 'POI Users List'
Subject: Out of Memoary Error while reading an EXCEL file

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement
is
to pick a specific cell from one row. But when I load the file that is
54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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


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

[Awais Bajwa-2]

Hi Mahesh,

Following are the initial heap and maximum heap sizes I am currently using:

  long heapSize = Runtime.getRuntime().totalMemory();
    System.out.println("Initial heap size  ="+heapSize );  ==> 2031616




long heapMaxSize = Runtime.getRuntime().maxMemory();
    System.out.println("Maximum heap size  ="+heapMaxSize );  ===>  66650112






-----Original Message-----
From: Mahesh Ganapathy [mailto:mganapa@...]
Sent: Thursday, October 02, 2008 1:17 PM
To: POI Users List
Subject: Re: Out of Memoary Error while reading an EXCEL file

What is the max jvm heap size? Are you running this on an app server?
Sent via BlackBerry by AT&T

-----Original Message-----
From: "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Paul.Dobson@...>

Date: Thu, 2 Oct 2008 10:34:37
To: POI Users List<user@...>
Subject: RE: Out of Memoary Error while reading an EXCEL file


I was having the same problem.  I ended up using the event driven model
to parse the file.  Then there is no limit on file size.

-----Original Message-----
From: Awais Bajwa [mailto:abajwa@...]
Sent: Thursday, October 02, 2008 10:34 AM
To: 'POI Users List'
Subject: Out of Memoary Error while reading an EXCEL file

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement
is
to pick a specific cell from one row. But when I load the file that is
54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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


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




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

[Awais Bajwa-2]

It is the part of a stand alone process for the time being for testing,
after the testing I have write a code for the web-application.


// Get current size of heap in bytes
    long heapSize = Runtime.getRuntime().totalMemory();
    System.out.println("Initial heap size  ="+heapSize );
    // Get maximum size of heap in bytes. The heap cannot grow beyond this
size.
    // Any attempt will result in an OutOfMemoryException.
    long heapMaxSize = Runtime.getRuntime().maxMemory();
    System.out.println("Maximum heap size  ="+heapMaxSize );
    // Get amount of free memory within the heap in bytes. This size will
increase
    // after garbage collection and decrease as new objects are created.
    long heapFreeSize = Runtime.getRuntime().freeMemory();
    System.out.println("Maximum heap size  ="+heapFreeSize );




Output:

Initial heap size  =2031616
Maximum heap size  =66650112
Maximum heap size  =1677312








Regards
Awais Bajwa










-----Original Message-----
From: Mahesh Ganapathy [mailto:mganapa@...]
Sent: Thursday, October 02, 2008 2:01 PM
To: Awais Bajwa
Subject: Re: Out of Memoary Error while reading an EXCEL file

Do you have poi parser as a part of standalone application or as a part of
another webapplication. If it is running as a part of web application can
you see what the maximum memory as consumed by that application before
invoking poi calls?
------Original Message------
From: Awais Bajwa
To: 'POI Users List'
To: mganapa@...
Subject: RE: Out of Memoary Error while reading an EXCEL file
Sent: Oct 2, 2008 12:58 PM

Hi Mahesh,

Following are the initial heap and maximum heap sizes I am currently using:

  long heapSize = Runtime.getRuntime().totalMemory();
    System.out.println("Initial heap size  ="+heapSize );  ==> 2031616




long heapMaxSize = Runtime.getRuntime().maxMemory();
    System.out.println("Maximum heap size  ="+heapMaxSize );  ===>  66650112






-----Original Message-----
From: Mahesh Ganapathy [mailto:mganapa@...]
Sent: Thursday, October 02, 2008 1:17 PM
To: POI Users List
Subject: Re: Out of Memoary Error while reading an EXCEL file

What is the max jvm heap size? Are you running this on an app server?
Sent via BlackBerry by AT&T

-----Original Message-----
From: "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Paul.Dobson@...>

Date: Thu, 2 Oct 2008 10:34:37
To: POI Users List<user@...>
Subject: RE: Out of Memoary Error while reading an EXCEL file


I was having the same problem.  I ended up using the event driven model
to parse the file.  Then there is no limit on file size.

-----Original Message-----
From: Awais Bajwa [mailto:abajwa@...]
Sent: Thursday, October 02, 2008 10:34 AM
To: 'POI Users List'
Subject: Out of Memoary Error while reading an EXCEL file

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement
is
to pick a specific cell from one row. But when I load the file that is
54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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


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





Sent via BlackBerry by AT&T



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

[Awais Bajwa-2]

Paul,

I searched for the event model programming in POI, but I ended up with
"POIFS" that is actually a filesystem event model to read and write. But I
need an event model to handle multiple sheets in one file. I hope you
understand what I am saying ....same like what SAX doest on multiple
elements of XML.


One more options I was thinking to increase the maximum heap size, but again
if my file size exceeds 50MB then it will create a problem. Any idea?

Regards
Awais Bajwa




-----Original Message-----
From: Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN
[mailto:Paul.Dobson@...]
Sent: Thursday, October 02, 2008 12:35 PM
To: POI Users List
Subject: RE: Out of Memoary Error while reading an EXCEL file

I was having the same problem.  I ended up using the event driven model
to parse the file.  Then there is no limit on file size.

-----Original Message-----
From: Awais Bajwa [mailto:abajwa@...]
Sent: Thursday, October 02, 2008 10:34 AM
To: 'POI Users List'
Subject: Out of Memoary Error while reading an EXCEL file

Hi Folks,

I have an excel file that has many sheets and many rows, my requirement
is
to pick a specific cell from one row. But when I load the file that is
54MB
of excel file. I get an Out of memoary Error at:

    fileSystem = new POIFSFileSystem (inputStream);



Any thoughts??? Any limitation of POI?

Regards


Awais




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


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




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

[Nick Burch]

On Thu, 2 Oct 2008, Awais Bajwa wrote:
> same like what SAX doest on multiple elements of XML.

http://poi.apache.org/hssf/how-to.html#event_api

> One more options I was thinking to increase the maximum heap size, but
> again if my file size exceeds 50MB then it will create a problem. Any
> idea?

A 50mb file is likely to need a very large amount of memory in poi, as a
file that size tends to imply a very large number of crazy records. I'd
try upping the heap size to 2gb (or more if you're on 64 bit), and
hopefully you'll then be fine with hssf usermodel

Nick

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

[Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN]

-----Original Message-----
From: Nick Burch [mailto:nick@...]
Sent: Thursday, October 02, 2008 9:45 AM
To: POI Users List
Subject: Re: All Charts removed after modifying cell data using 3.5 beta
3

On Thu, 2 Oct 2008, Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN wrote:
> I unzipped a copy of the original file and the same file after right
> after using Xssf to modify cell values (unconverted by excel).  The
> modified file was missing the Following:

I know that xssf doesn't copy over all parts of the old file by default,

it only does the bits we support, and some others we don't yet support
but know to keep. Possibly not all of your parts are currently on the
latter list?

Though, I think at least some of the ones you mention should be being
copied over, certainly with the refactorings Yegor has done recently.
Maybe Yegor will have an idea here?

Nick

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

Until I can find a better solution, I'm thinking about taking the
following steps as a workaround.
1. Open up the workbook to be changed.
2. Change the values I need to change (I am only changing exiting cell
values in existing sheets).
3. Save the new workbook as a temp file.
4. Use Java to implement the workaround
        a. open the original unmodified workbook and the new temp file
created from step 3.
        b. copy the modified xml sheets from the new workbook to the
original workbook.
        c. Save the merged zip file under the original file name.
        d. Delete the temp file created by poi.
Any ideas on how successful this approach would be before I waste time
implementing a possibly bad idea?
Thanks for any input.

Paul


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