To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=111779 Issue #|111779 Summary|ERR:509 in cells where sheet name contains space, impo |rt of excel 2003 xml from Excel 2003 Component|Spreadsheet Version|OOo 3.2 Platform|PC URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|open-import Assigned to|spreadsheet Reported by|jmichae3
------- Additional comments from [email protected] Sun May 23 04:58:18 +0000 2010 ------- 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> --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
