GNOME Bugzilla – Bug 130803
Problem with the use of ValueFormat attribute in numeric cells imported from XLS
Last modified: 2009-08-15 18:40:50 UTC
Attached to this bug report is an MS Excel (2002) spreadsheet, and a Gnumeric spreadsheet that illustrates a problem importing formatted numeric fields. Cells A1..D2 of the Gnumeric sheet are the result of importing the XLS sheet. The cells A4..D5 were entered manually into Gnumeric. As one can see by clicking on the cells, the values in A1..D1 appear to be text, not numeric. Looking at the XML, there is a clear contrast between, e.g.. B1 and B4: <gmr:Cell Col="1" Row="0" ValueType="40" ValueFormat="#,##0.00">-100000</gmr:Cell> <gmr:Cell Col="1" Row="3" ValueType="30">-100000</gmr:Cell> Deleting the ValueFormat attributes from the A1..D1 cells appears to correct the situation. This problem occurs with both the gnumeric-1.2.1-1 shipped with Fedora Core 1, and the 20040107 head of the 1.2.x branch.
Created attachment 23081 [details] MS Excel spreadsheet that triggers the import bug.
Created attachment 23082 [details] Gnumeric spreadsheet created from import-bug.xls
I'm unable to replicate the problem (gnome-1-2 branch or head), although I can guess what it might have been. When I save that xls file as a .gnumeric nothing seems to convert to string <gmr:Cell Col="0" Row="0" ValueType="40" ValueFormat="0.00">-100000</gmr:Cell> <gmr:Cell Col="1" Row="0" ValueType="40" ValueFormat="#,##0.00">-100000</gmr:Cell> <gmr:Cell Col="2" Row="0" ValueType="40" ValueFormat="_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)">-100000</gmr:Cell> <gmr:Cell Col="3" Row="0" ValueType="40" ValueFormat="#,##0.00_);[Red](#,##0.00)">-100000</gmr:Cell> Can you give me more detail ?
Thanks for looking at this. I just built the head of the Gnumeric 1.2 branch, and when I import import-bug.xls into gnumeric, I see the following in the expression widget as I click through various cells: A1: -100000.00 B1: -100,000.00 C1: [space](100,000.00) D1: (100,000.00) When I enter cells manually, and then format them, e.g., cell D4 with parentheses, commas, and red, the expression widget simply says D4: -100000 However, since updating to CVS head, I can now sum(A1:D1) and I get sensible results. I am closing this bug.