Apache OpenOffice (AOO) Bugzilla – Issue 111779
ERR:509 in cells where sheet name contains space, import of excel 2003 xml from Excel 2003
Last modified: 2013-01-29 21:44:59 UTC
when importing Excel 2003 XML files, cross-sheet references are imported wrong as Err:509 when sheet names contain a space. this is a special case that is being handled wrong where single quotes are used to handle the special case of having the space, for example, in Excel, the standard reference should be ='this sheet name has spaces'!A1 but in OOo this gets converted to ='this sheet name has spaces'A1, but in OOo a valid reference is =Restaurant.A1 or ='this sheet name has spaces'.A1 when you type in or click in an address. but in excel =Sheet1!A1 or Sheet1!R1C1 (latter is useful for XML files if you want absolute cell references) is a valid cell reference. until you get into xml files. then things get dicey. A1 type references are not valid in Excel 2003 XML files. only R C and RC references are valid in Excel 2003 XML files. and they are very messy unless you are doing absolute references. programmatically RC (for example, R1C1 or R208C31 is VERY convenient for doing file exports if you are doing absolute references - it's as easy as doing a printf with the row and column number. after entering a correct spreadsheet into OOo with simple cross-sheet references, OOo outputs as Excel 2003 XML the following incorrect format (herein called the "cross-sheet references" example"): <Row ss:Height="12.8376"> <Cell ss:StyleID="Normal" ss:Formula="of:=['this sheet name has spaces'.A1]"><Data ss:Type="Number">1</Data></Cell> <Cell ss:StyleID="Normal" ss:Formula="of:=[Sheet1.A1]"><Data ss:Type="Number">1</Data></Cell> </Row> <Row ss:Height="12.8376"><Cell ss:Index="2" ss:StyleID="Normal"/></Row> MS Excel outputs the following for the same spreadsheet: <Row> <Cell ss:Formula="='this sheet name has spaces'!RC"><Data ss:Type="Number">1</Data></Cell> <Cell ss:Formula="=Sheet1!RC[-1]"><Data ss:Type="Number">1</Data></Cell> </Row>
Created attachment 69578 [details] cross-sheet references spreadsheet in excel 2003 xml format from excel 2003
bug appears to have been fixed in 3.2.1.
It can be reproduced on AOO340m1 build9590 and AOO350m1 build9610. Reopen it.