Bug 45061 - Error while editing a xls file generated using the POI APIs
Summary: Error while editing a xls file generated using the POI APIs
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-05-22 05:10 UTC by jagannadham
Modified: 2010-12-22 07:51 UTC (History)
1 user (show)



Attachments
zip contains excel file which crashes when tries to edit and save it and screenshot to show error (69.99 KB, application/x-zip-compressed)
2008-05-22 05:10 UTC, jagannadham
Details
The attached zip has two java files which are self explanatory (2.27 KB, application/x-zip-compressed)
2008-05-26 04:13 UTC, jagannadham
Details
code that generates a xls sheet using POI (661 bytes, application/x-zip-compressed)
2008-06-02 00:34 UTC, jagannadham
Details
Output of JobEmail from svn trunk, and repaired file (2.10 KB, application/zip)
2008-06-05 08:25 UTC, Nick Burch
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jagannadham 2008-05-22 05:10:49 UTC
Created attachment 21988 [details]
zip contains excel file which crashes when tries to edit and save it and screenshot to show error

We are creating excel sheet using HSSF work book.

When we tried to edit and save we are getting an error popup saying this microsoft excel has encountered a problem and needs to close.Generated Excel sheet has only 2 rows in the the sheet.we are using Microsoft Excel 2003 version.we are facing this problem with both poi3.0.1 and poi3.0.2 versions.i have attached the zip which contains the excel sheet and also screenshot which shows the error condition.
Comment 1 Josh Micich 2008-05-23 01:06:21 UTC
I am having trouble reproducing this error.  I have Excel 2007.  The version may be important.

The attached file opens in excel OK for me.  I can directly save it in Excel. I can manually edit it in Excel and save it OK.  With POI, I read the file and re-wrote it back, then did all those manual operations again in Excel.  I did this with POI versions 3.0, 3.0.2 and svn trunk.  No errors occurred.


You need to be more clear about:
 - What exact action in excel causes the error. (does the error occur when opening the spreadsheet, when saving immediately, or saving after making a manual change)
 - Is the attached spreadsheet input to or output from POI?
 - How was the attached spreadsheet was produced? If it was by POI, please attach the exact source code that produced it.
Comment 2 jagannadham 2008-05-26 04:13:12 UTC
Created attachment 22002 [details]
The attached zip has two java files which are self explanatory
Comment 3 jagannadham 2008-05-26 04:19:08 UTC
(In reply to comment #2)
> Created an attachment (id=22002) [details]
> The attached zip has two java files which are self explanatory

(In reply to comment #1)
> I am having trouble reproducing this error.  I have Excel 2007.  The version
> may be important.
> The attached file opens in excel OK for me.  I can directly save it in Excel. I
> can manually edit it in Excel and save it OK.  With POI, I read the file and
> re-wrote it back, then did all those manual operations again in Excel.  I did
> this with POI versions 3.0, 3.0.2 and svn trunk.  No errors occurred.
> You need to be more clear about:
>  - What exact action in excel causes the error. (does the error occur when
> opening the spreadsheet, when saving immediately, or saving after making a
> manual change)
>  - Is the attached spreadsheet input to or output from POI?
>  - How was the attached spreadsheet was produced? If it was by POI, please
> attach the exact source code that produced it.

Sorry for the clarity on the issue. Here are the exact details as requested.

1) The exact version is excel 2003
2) The attached spread sheet is the output of POI.
3) The spread sheet is generated using the POI (3.0.1 and 3.0.2) (The problem persists with both the versions. The POI zip attached herewith has the source code.
4) The problem occurs when the spread sheet generated using the POI is either saved with or without editing
Comment 4 jagannadham 2008-05-30 05:32:44 UTC
Could you ple(In reply to comment #3)
> (In reply to comment #2)
> > Created an attachment (id=22002) [details] [details]
> > The attached zip has two java files which are self explanatory
> (In reply to comment #1)
> > I am having trouble reproducing this error.  I have Excel 2007.  The version
> > may be important.
> > The attached file opens in excel OK for me.  I can directly save it in Excel. I
> > can manually edit it in Excel and save it OK.  With POI, I read the file and
> > re-wrote it back, then did all those manual operations again in Excel.  I did
> > this with POI versions 3.0, 3.0.2 and svn trunk.  No errors occurred.
> > You need to be more clear about:
> >  - What exact action in excel causes the error. (does the error occur when
> > opening the spreadsheet, when saving immediately, or saving after making a
> > manual change)
> >  - Is the attached spreadsheet input to or output from POI?
> >  - How was the attached spreadsheet was produced? If it was by POI, please
> > attach the exact source code that produced it.
> Sorry for the clarity on the issue. Here are the exact details as requested.
> 1) The exact version is excel 2003
> 2) The attached spread sheet is the output of POI.
> 3) The spread sheet is generated using the POI (3.0.1 and 3.0.2) (The problem
> persists with both the versions. The POI zip attached herewith has the source
> code.
> 4) The problem occurs when the spread sheet generated using the POI is either
> saved with or without editing

Do you have any update on this issue. Thanks in advance for your help.
Comment 5 Josh Micich 2008-05-30 09:21:08 UTC
Thanks for the clarification.  So the steps to reproduce are: generate with POI, open in Excel, save.

I ran the attached code against 3.0.2 and svn trunk(3.1-beta2+). No change in observations.  BTW the attached code had 2 compiler errors for which I guessed a fix.  I hope that this did not affect the problem.

There doesn't seem to be anything unusual in your code.  It looks like very normal usage of POI.  Without being able to reproduce the bug, there is not much I can do besides blame it on differences in Excel versions.  Perhaps someone else with Excel 2003 could try this code out.

On your side, you could help by cutting down the example code to the simplest/smallest version that will reproduce the bug for you.  Re-attach that code (without compiler errors, please).  Also specify which of the remaining lines of code you have observed to be critical (i.e. can't be removed) for creating the problem. 
Comment 6 jagannadham 2008-06-02 00:34:30 UTC
Created attachment 22050 [details]
code that generates a xls sheet using POI

Code that generates a xls sheet using POI (3.0.1 or 3.0.2)
Comment 7 jagannadham 2008-06-02 00:35:20 UTC
(In reply to comment #5)
> Thanks for the clarification.  So the steps to reproduce are: generate with
> POI, open in Excel, save.
> I ran the attached code against 3.0.2 and svn trunk(3.1-beta2+). No change in
> observations.  BTW the attached code had 2 compiler errors for which I guessed
> a fix.  I hope that this did not affect the problem.
> There doesn't seem to be anything unusual in your code.  It looks like very
> normal usage of POI.  Without being able to reproduce the bug, there is not
> much I can do besides blame it on differences in Excel versions.  Perhaps
> someone else with Excel 2003 could try this code out.
> On your side, you could help by cutting down the example code to the
> simplest/smallest version that will reproduce the bug for you.  Re-attach that
> code (without compiler errors, please).  Also specify which of the remaining
> lines of code you have observed to be critical (i.e. can't be removed) for
> creating the problem. 

Please find attached the one single java file which generates the xls sheet using the POI. Execute the attached java file by including the poi.jar in the classpath and open the xls sheet (generated in the same directory from where the java program is executed) using the excel 2003. Open it and save it. You would get the error. The reported problem does not persist if excel 2007 is used instead.
The attached code does not have any compilation errors now.
Could you please confirm this problem with excel 2003 using POI (3.0.1 or 3.0.2) and a probable solution as we are not intending for upgrading to excel 2007
Comment 8 Josh Micich 2008-06-02 00:47:23 UTC
(In reply to comment #7)
Thanks for the new sample code.  That's way simpler.  No change on my side (Excel 2007 open+save OK).  Someone with Excel 2003 might need to look at this one.


You haven't mentioned which lines of this new code are critical to re-creating the bug.  It will be useful to know which line/lines (when removed) make the bug go away.
Comment 9 jagannadham 2008-06-02 01:57:20 UTC
(In reply to comment #8)
> (In reply to comment #7)
> Thanks for the new sample code.  That's way simpler.  No change on my side
> (Excel 2007 open+save OK).  Someone with Excel 2003 might need to look at this
> one.
> You haven't mentioned which lines of this new code are critical to re-creating
> the bug.  It will be useful to know which line/lines (when removed) make the
> bug go away.

Thanks for your quick response. We are not sure as to which lines are critical. The whole idea is to generate a xls sheet using the POI api. When the so generated xls sheet is opened using 2003, the problem occurs.The java file attached is very small as such. Could you please help us in identifying the problem in the code as you are now aware as to what the problem is.
Comment 10 Josh Micich 2008-06-02 08:49:56 UTC
(In reply to comment #9)
> ... as you are now aware as to what the problem is.
I'm sorry if I gave the impression that I know what the problem is.  To be clear, the problem has not been reproduced yet, and until someone else does, the only lead we have is *your* observation of the failure.  

> ... We are not sure as to which lines are critical. ...
This sounds ominous.  What steps did you take to reduce the first example java code you supplied (attachment id=22002) down to this latest (attachment id=22050)?  To determine critical lines, all you have to do is pick a line at random and remove it.  It the same problem occurs, the line was not critical for this bug.  If the problem goes away, the line was critical.  For example, this line:
		str.applyFont(HSSFFont.BOLDWEIGHT_BOLD);
What happens if you remove it.  Does the bug still occur.


Comment 11 jagannadham 2008-06-03 00:30:55 UTC
(In reply to comment #10)
> (In reply to comment #9)
> > ... as you are now aware as to what the problem is.
> I'm sorry if I gave the impression that I know what the problem is.  To be
> clear, the problem has not been reproduced yet, and until someone else does,
> the only lead we have is *your* observation of the failure.  
> > ... We are not sure as to which lines are critical. ...
> This sounds ominous.  What steps did you take to reduce the first example java
> code you supplied (attachment id=22002) down to this latest (attachment
> id=22050)?  To determine critical lines, all you have to do is pick a line at
> random and remove it.  It the same problem occurs, the line was not critical
> for this bug.  If the problem goes away, the line was critical.  For example,
> this line:
>                 str.applyFont(HSSFFont.BOLDWEIGHT_BOLD);
> What happens if you remove it.  Does the bug still occur.

Hi

Thanks for your qucik response. When we remove the line as mentioned in your comment above, the problem does not persist. But what is the alternate way of making the font bold. We need to make it bold. 
With the above line bolding does not work even with excel 2007.

Please clarify.
Comment 12 Nick Burch 2008-06-05 08:25:30 UTC
Created attachment 22082 [details]
Output of JobEmail from svn trunk, and repaired file

Have tried with svn trunk and excel 2003

Excel still crashes for me having made a small edit. Attached is the file output by poi, and the edited version as repaired by excel
Comment 13 Josh Micich 2008-06-05 15:59:26 UTC
(In reply to comment #12)
> Have tried with svn trunk and excel 2003...


I noticed a small difference while investigating with BiffViewer.  The dbcells values in the INDEX records were being set wrong.  I opened a separate bugzilla (bug 45145) for this because it seems unrelated to the str.applyFont() call that is critical to this bug.  Bug 45145 was fixed in svn r663765.  I'm guessing that did not fix this bug, but someone with 2003 will have to confirm.


There are many diffs I see in the BiffViewer output between the file written with and without the str.applyFont() call.  Perhaps too many to consider individually.

It would be helpful to simplify the sample code even more (or find other critical lines).  For example if the call to createUserRow() is skipped, does the bug still occur?  What happens if the titlesArray has fewer elements?  What about the line: "dataCell.setCellType(HSSFCell.CELL_TYPE_STRING);" ?

Comment 14 Nick Burch 2008-06-06 02:44:11 UTC
I can confirm that with r663765, the problem still remains - the text in A1 is not bold, and attempting to change the file and save crashes excel 2003

If I comment out lines 31-37 (userRow and dataCell), the problem remains. Text in A1 is not bold, and change+save crashes excel 2003

Commenting out
  titleCell.setCellType(HSSFCell.CELL_TYPE_STRING);
makes no difference either

If I change titlesArray to be "JobJobJobJob", there is no change. If I change it to be just "J", still no change
Comment 15 awm 2010-12-22 07:51:06 UTC
Tried with 3.7 (release) and Excel 2010, resaved as .xls (binary) - no issues.