Bug 44010 - Support for non-row and column sheet types such as ChartSheet
Summary: Support for non-row and column sheet types such as ChartSheet
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows XP
: P1 blocker with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-12-03 02:16 UTC by nlonga
Modified: 2008-12-29 23:24 UTC (History)
2 users (show)



Attachments
The source workbook (39.00 KB, application/octet-stream)
2007-12-03 02:18 UTC, nlonga
Details
the output workbook (cannot be opened with MS Excel) (42.00 KB, application/octet-stream)
2007-12-03 02:19 UTC, nlonga
Details
workbook with data and 2 charts. (Not work) (29.00 KB, application/octet-stream)
2007-12-03 03:14 UTC, nlonga
Details
the same workbook with only one chart (the output xls is correct after writing with hssf) (28.00 KB, application/octet-stream)
2007-12-03 03:16 UTC, nlonga
Details
The source file (1.41 KB, text/plain)
2007-12-03 03:17 UTC, nlonga
Details
original xls file (41.00 KB, application/octet-stream)
2008-03-06 02:10 UTC, arsen
Details
xls file saved by POI (42.00 KB, application/octet-stream)
2008-03-06 02:11 UTC, arsen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description nlonga 2007-12-03 02:16:08 UTC
I work on a xls with Excel XP and POI HSSF.
It contains 3 charts.

I only open the workbook and then save it into a new xls (without any
modifications).
After that, I can t open the new workbook with Excel 2002 (it is the same with
Excel 2003), I have an error message from Excel ...


My code is :
                // open the woorkbook
                {
                        POIFSFileSystem fs =
                                new POIFSFileSystem(new
FileInputStream(workbookFileNameSrc));
                        hssfworkbook = new HSSFWorkbook(fs);
                        //hssfworkbook = new HSSFWorkbook(fs,true);
                }

                // write the new woorkbook without modifications
                FileOutputStream fileOut = new
FileOutputStream(workbookFileNameDest);
                this.hssfworkbook.write(fileOut);
                fileOut.close();

I have try with POI 3.0.1 final and the latest beta
poi-src-3.0.2-beta1-20071204.zip.

Thanks
Comment 1 nlonga 2007-12-03 02:18:32 UTC
Created attachment 21214 [details]
The source workbook
Comment 2 nlonga 2007-12-03 02:19:33 UTC
Created attachment 21215 [details]
the output workbook (cannot be opened with MS Excel)
Comment 3 Nick Burch 2007-12-03 02:45:12 UTC
Are you able to produce a very simple file to show this? Something like a file
with only one chart in it, and only a limited set of data. Or, perhaps a file
with two simple charts that works, and then the same file with a third chart
added, which doesn't.

Tracking down what bit of a file excel has taken objection too is very tricky.
If we have two files, one which works, and one which is very similar but
doesn't, that process is made much easier.
Comment 4 nlonga 2007-12-03 03:14:19 UTC
Created attachment 21216 [details]
workbook with data and 2 charts. (Not work)

I have make a more simple test.

"Bug_44010_OK.xls" with a limited set of data and only one chart
In this case, it works !

"Bug_44010_OK.xls" with a limited set of data and two charts
In this cas, it's not works.

These xls files are simple. enough simple or not ??

Thanks
Comment 5 nlonga 2007-12-03 03:16:05 UTC
Created attachment 21217 [details]
the same workbook with only one chart (the output xls is correct after writing with hssf)

I have make a more simple test.

"Bug_44010_OK.xls" with a limited set of data and only one chart
In this case, it works !

"Bug_44010_OK.xls" with a limited set of data and two charts
In this cas, it's not works.

These xls files are simple. enough simple or not ??

Thanks
Comment 6 nlonga 2007-12-03 03:17:15 UTC
Created attachment 21218 [details]
The source file

the java source
Comment 7 nlonga 2007-12-03 03:22:25 UTC
the correct comment is :
"Bug_44010_OK.xls" with a limited set of data and only one chart
In this case, it works !

"Bug_44010_NOTOK.xls" with a limited set of data and two charts
In this cas, it's not works.

:-)

(In reply to comment #4)
> Created an attachment (id=21216) [edit]
> workbook with data and 2 charts. (Not work)
> 
> I have make a more simple test.
> 
> "Bug_44010_OK.xls" with a limited set of data and only one chart
> In this case, it works !
> 
> "Bug_44010_OK.xls" with a limited set of data and two charts
> In this cas, it's not works.
> 
> These xls files are simple. enough simple or not ??
> 
> Thanks

Comment 8 ms 2008-02-14 00:45:45 UTC
I have the same problem... A template with one chart works nice to read and then
write with poi, but if you add another worksheet with a chart to the template
and then read and write with poi, excel can't open the file.
Comment 9 nlonga 2008-02-14 01:23:04 UTC
Same problem for a another user ...
Comment 10 arsen 2008-03-06 02:08:29 UTC
I have the same problem... xls file was prepared in MSOffice2007 and saved in MS0ffice2003 Excel (Compatibility Mode).

I only open the workbook and then save it into a new xls (without any
modifications).

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
FileOutputStream fos = new FileOutputStream(filePath + "_error1");
wb.write(fos);

POI: poi-bin-3.0.2-FINAL-20080204.zip
xls files att.

Thanks
Comment 11 arsen 2008-03-06 02:10:03 UTC
Created attachment 21636 [details]
original xls file
Comment 12 arsen 2008-03-06 02:11:42 UTC
Created attachment 21637 [details]
xls file saved by POI
Comment 13 arsen 2008-03-10 05:00:43 UTC
xls file was saved successfully after deleting old 3.0 version of poi from classpath.
Sorry
Comment 14 Nick Burch 2008-03-10 05:26:24 UTC
Does everything now work properly for you? If so, please do mark the bug as resolved :)
Comment 15 nlonga 2008-03-10 07:34:24 UTC
hi,
this bug is not resolved.
With my example, you can reproduce the problem. (?)
Thanks.
Comment 16 Michael Zalewski 2008-03-14 12:42:49 UTC
There are two problems here. The first one is Bug_44010_NOTOK.xls, which has chartsheets. 

The second problem is new.xls, which is *way* more complicated than it looks. This workbook seems to have been created in Excel 2003 and contains many of that version's newer features such as format extensions, enhanced protection (of data from EXTERNSHEETs) and style extensions. The sheet also contains Office Drawings (though nothing is visible on the sheet).

An Excel workbook can actually contain 4 types of sheets

1) A Worksheet is the most common. It contains rows and columns of data
2) A Macrosheet is very similar to a worksheet, but the data represents macros to be executed on a worksheet. This is a holdover from Excel v2, and is no longer used as far as I know.
3) A Chartsheet is a sheet that contains a chart. It has now rows or columns, only a chart
4) A Dialogsheet is a sheet that contains a form. It also has no rows or columns.

Most Excel workbooks contain only worksheets, and POI works quite well on these types of workbooks.

The Excel file Bug_44010_NOTOK.xls which does not work contains a ChartSheet. I suspect that HSSF has put some elements of a Worksheet (such as a ROW or COLUMN record) that does not belong on a ChartSheet. It is possible to place a Chart object onto a Worksheet (instead of using a ChartSheet).If youy did this, I bet you would be able to process the spreadsheet with POI.

BTW, the Excel file which does work also contains a ChartSheet (but only one ChartSheet). I was actually surprised to see that POI works with this.

This bug could be split into two.

1) Support for non-row and column sheet types such as ChartSheet and DialogSheet (Bug_44010_NOT_OK.xls)

2) Support for Excel 2003 extended features (new.xls) I'm not sure what the problem is in this case. I know there are several record types new in Excel 2003 that are present in this worksheet and not recognized by HSSF. Such record types are blindly copied by HSSF, which might be incorrect if the record contains internal pointers, or may result in an improperordering of the BIFF records in the file output by HSSF.
Comment 17 Berend 2008-04-23 11:52:28 UTC
Have the same problem as described. Found out that the end of the file has additional data while the original has not. Only thing that has been done on the file using POI, is set a document property.
Comment 18 nlonga 2008-06-06 03:38:49 UTC
hello,
i have change the summary for this bug.
Comment 19 Patrick Cheng 2008-11-11 20:59:08 UTC
I tried the latest trunk with both Bug_44010_NOTOK.xls and new.xls, and they both open and save fine in both POI and Excel 2007, as far as I can tell.
Comment 20 Yegor Kozlov 2008-12-29 23:24:39 UTC
The reported problem is not reproducible with the latest trunk.
Please try the latest 3.5-beta4 or download daily builds from
http://encore.torchbox.com/poi-svn-build/

Yegor