Apache OpenOffice (AOO) Bugzilla – Issue 9895
Saving Calc spreadsheet as .xls loses totals for Excel
Last modified: 2013-08-07 15:14:58 UTC
I created a spreadsheet in OpenOffice Calc. It has no complicated formulas, but has over 700 rows of data with totals, and grand totals. If I save it in .xls format (Microsoft Excel 97/2000/XP), and open the file in Excel viewer (or even 602Tab), all the totals lines contain zeros. Opening it in OpenOffice shows the totals correctly. Thanks.
Created attachment 3928 [details] Spreadsheet. Totals (bold) show as zeros in Excel viewer
I created a spreadsheet in OpenOffice Calc. It has no complicated formulas, but has over 700 rows of data with totals, and grand totals. If I save it in .xls format (Microsoft Excel 97/2000/XP), and open the file in Excel viewer (or even 602Tab), all the totals lines contain zeros. Opening it in OpenOffice shows the totals correctly.
In Excel formula cells contain the formula itself and the calculated result. On export, Calc does not write this result, but sets an internal flag "recalculate on load". This works perfectly in Excel. Seems that the Excel viewer only shows the (nonexistent) cached results.
In Excel formula cells contain the formula itself and the result. On export, Calc does not write this result, but sets an internal flag "recalculate on load". Seems that the viewer only shows the (nonexistent) cached results.
started
Thanks for your prompt responses, people! Here's a not-so-prompt response. I've done some testing, and your comments about the Excel Viewer working differently to Excel, seem to be quite correct. I also had an Excel user create a spreadsheet in Excel 97, and I viewed it with the viewer, and the calculated values were present, which again confirms what you say. So, I guess our options are: 1. Leave it as is. (Easy but leaves a slight problem). 2. Change Calc to store calculated values in .xls files. (This will make them larger, which may not be considered very efficient). 3. Ask Bill to fix his viewer. (Could be fun, as he's unlikely to be interested in helping his competition to compete with his product). Not very good options, really. How about: 4. Add a non-default option (tick box) to Calc, to allow the values to be stored in .xls files? What do you think? Tel
Hi Tel, I have started the issue, so it will be fixed :-) The files will not become larger. The formula result field has to be written always, but is currently only filled with zeros (therefore you see zeros in the viewer).
I have agreed with Daniel to implement his analysis. I have a couple of queries. Daniel, is this solution about writing away the value and always resetting the "calculate on open" bit on export to excel. Is there any other implications if this is a shared formula? I presume that we will store the result always i.e. if the result is not a numeric value but a string, boolean or error code?
Discussion forwarded to sc-dev
changed owner
Marking this for OOo 1.1 BETA
Created attachment 4084 [details] Proof of Concept Patch
Daniel, I have some issues that I would like your input on: 1. I see that we don't appear to map all the possible Excell Formula Errors based on looking at excform.cxx : ExcelToSc::SetCurVal() i.e. we don't support the a. ##### - columns not wide enough , invalid dates b. #N/A - we use errNoValue here as in #VALUE c. #NUM is also mapped to our errIllegalFPOperation as is #DIV/0 2. I used a static function to map between the Calc and Excel Errors. But perhaps I should a new method to ExcFormula() 3. I notice that in Excel the Boolean and Errors Formula values are centered while we right jusify them. Is this minor issue significant? 4. I have only looked at Biff8 for string handling but presumably I should handle earlier versions. Also, my string record handling helped me understand it but presumably there is an easier way to code this. 5. Do I need to be concerned if GetFormatType() returns a string and the string value is NULL. 6.My understanding is that all Formula result errors are returned as GetFormatType() NUMBER.
Created attachment 4093 [details] Real Proof of Concept Patch - ignore earlier patch
Created attachment 4153 [details] Ensure patch compatability with OOo 1.1 Beta
There is still one issue with this patch. I get a "File error: data may have lost" message when I open an exported file in Excel. This is caused when I do not write away a blank string e.g. IF((SUM(C2:C4) > 20), "", "NOT BLANK"). If I force the writing away of this blank string, then I do not get a data loss message. I thought the right idea would be to skip writing away the STRING record if the string returned from the formula result cell is empty?
A further comment on this. The other option is to not write away the String Formula record and the following String Record, but that will bring us back to where we started and an empty string will appear as a 0 in an Excel Viewer.
OK. Daniel has explained that I need to use a 0x03 when inserting an empty cell for a formula result. I will update the patch.
Created attachment 4222 [details] patch now ready for review
Created attachment 4266 [details] correct string handling for non Biff8 formats
Created attachment 4283 [details] Do not specify EXC_STR_8BITLENGTH in AssignByte(), need 16bit
I spent a little time testing this patch today. The only compatability issues that I have found relate to error handling between Excel and Calc. Here are the details: 1. Calc does not support the #VALUE! Error Code e.g. attempts to sum a cell containing a string will result in a #VALUE! error in Excel. Calc will simply interpret treat the string as a zero. 2. Calc does not support the displaying #DIV0! Instead "Err:503" is used. 3. We map both the Excel errors #N/A and #VALUE to our NOVALUE value. I presume if these minor differences are an issue, I can create a new issue.
John, Because this issue is only related to the Excel Viewer, I don't think that we need to fix the slightly different behaviour with error values.
Created attachment 4307 [details] Test Case
Checked in this patch today. Will mark this as fixed.
*** Issue 10790 has been marked as a duplicate of this issue. ***
*** Issue 10015 has been marked as a duplicate of this issue. ***
Re-opening issue as there is a regression when text formula results are written before Shared Formula records.
Created attachment 4715 [details] Proposed patch to fix this
Created attachment 4735 [details] Changes following Code Review: Use the new XclExpRecord in the manner it was deigned for
Created attachment 4740 [details] Move writing of Formula String Result after the Table Operations Record
Checked in this latest patch today.
Thanks for all your work on this, John! Excuse my ignorance, but I've tried searching the Help at this site and on my OO installation, and haven't yet found anything about how I can use the patch you've created, or do I have to wait until the target release? Can you point me in the right direction, please. Thanks...Tel
Tel, yes, you will have to wait for the target release for this fix. It will definitely appear in the OOo 1.1Beta which is the next release.
re-open to assign to QA
re-assigning to QA
restoring the "fixed" state
This problem doesn't appear to be fixed in 1.0.2b. Should it be? I know you said 1.1Beta, but you also said "the next release". Was 1.0.2b not the next release?
1.0.2 was available on 20th January. The code fix went in on 14th Feb. OOo1.1Beta is available since March 25th.
Verified in OOo 1.1 Beta2.
closed ...
Thanks for the good work, John! At long last I've tried this in 1.1Beta2, and it seems to be fixed, with the following possibly related issues: 1. A new spreadsheet containing a date in cell A1 (eg: 01/01/04), and say =A1+1 in cell A2, and say =A1 in cell A3, shows 0 in cells A2 and A3, when saved as .xls and opened with Excel viewer 97. 2. Spreadsheets created in versions prior to the fix, opened in 1.1Beta2, and saved as .xls, still manifest the problem in Excel viewer 97. Have you any recommended solution or workaround for me, short of re-entering all the formulas in the spreadsheet? If any of the above should be opened as separate issues, please let me know. Thanks. Tel.
Hi Tel, thanks for the feedback. Yes, I can confirm that I am seeing 00/01/00 in A2 and A3 from your example. You should create a new issue and you can reference this issue in it and attach your failing example. I have not had time to investigate this more closely as yet to suggest a solution or workaround, but I will get back to it.
I am going to reset this to fix/closed. The original problem reported by the sample doc is fixed. Formula results of type number, text and boolean are now saved. But Formula results of category type Date, Time, Percent, Scientific etc. are still saved as zero. This problem has been reported as issue 17918. I will fix it there.
reset to closed
Thanks John. And thanks for appending 17918 with my problem and for opening 17601 on my behalf. I was going to do it (honest!), but just hadn't made time yet. Tel.
Hi, This seems like a really old issue that was fixed a long time ago, but I think I am seeing the same thing in OpenOffice 3.2. When I save a spreadsheet in .xls format and open it in the Microsoft Excel Viewer, cells containing formulae appear as zero. It doesn't do this every time, but I haven't figured out what triggers it. However if I open it in Microsoft Excel or OOCalc the cells always display correctly. Any help would be much appreciated! Our specific problem is not actually with the Excel viewer; we have our own parsers which read the .xls file to create other files based on its content, and it is this which is breaking when the formulae results are not saved. Thanks, Emma