Apache OpenOffice (AOO) Bugzilla – Issue 95256
Wrong cell references when loading Excel 2003 spreadsheet containing shared formulas that would wrap at column 256.
Last modified: 2013-08-07 15:14:30 UTC
The failing spreadsheet contains references to cells in the same sheet. The original spreadsheet was created in MS EXCEL 2003 SP2 and works fine for a couple of moths already. Loading this spreadsheet into SCALC results in having some cell references to be pointing to a completely differrent cell. Worksheets Q1 2008 tot Q4 2008 all show the same behaviour. See Cell FM5 which contains a formula that references a number of cells including E5. Opening this sheet iin SCALC FM5 holds the same formula but the reference to E5 is changed to JA5. Also if the sheet in transformed to an ODF file, and this is used to crete a new xls file, opening this XLS will show the same errors.
Created attachment 57352 [details] A 7 sheet MS EXCEL 2003 file with incorrect cell references in SCALC
When I open the file in 2.4.1 the reference in cell FM5 is correct. When I open the file in OOo3.0 I see the wrong reference as described. I work on WinXP.
@dr: Looks like the Excel binary filter does a modulo MAXCOLCOUNT instead of 256, JA-IV==E ...
seems to happen with relative offsets in reference tokens, e.g. shared formulas, defined names. Does not happen with simple cell formulas. Started.
This cannot be fixed :-( The Excel file uses a relative offset in the mentioned shared formulas. Instead of directly pointing to the cell E5, the formula contains the distance of formula position and referred cell. By doing this, Excel uses the fact that these relative references wrap at the sheet borders. In our example, the formula does not point to "164 columns left" (negative offset -164) but points to "92 columns right" (positive offset 92). If that formula is located in cell FM5 (column FM has 0-based index 168), Excel calculates the target column 168+92=260 and wraps that at last column (255), which results in column index 4 meaning column E. Calc did and does the same, but now, with changed column count, no wrapping occurs anymore. The same happened in the past, when loading a BIFF5 file (Excel 5 or Excel 95, 16384 rows) in Excel 97 or later, and the file contains shared formulas or defined names pointing a few rows below. Seems, they have been aware of such problems while extending Excel 2007 to more rows/columns and thus added a "compatibility mode" with 65536 rows/256 columns. This mode is enabled when loading old files in Excel 2007 to prevent these problems. A risky and non-trivial filter-only fix is to resolve shared formulas for every cell and do the wrapping manually. But this would result in more memory consumption when loading big Excel files, and increased import time. Additionally, this is only possible for shared formulas, if you use defined names, the problem is still present. Example: In Excel 2003, cell A1, define the name "test" pointing to "Sheet1!B1" (relative reference). This creates a name pointing to 1 column to the right. If this name is used in cell A1, it points to cell B1. If this name is used in cell IV1, it points to cell A1 (one column to right + wrap at sheet border). After importing to Calc 3, it will point to cell IW1. There is no way to fix this in the filter, without breaking any of the formulas. The "real" fix would be to add a compatibility mode that restricts the sheet size to 256 columns.
Too bad :-( This would be a bit more effort than just some fix. Certainly not doable for OOo3.0.1, not even for OOo3.1, at least not squeezable into my schedule. Retargeting to OOo3.2 at the moment. If someone wants to jump in, please do so, setting 'needhelp' keyword for this.
@dr: So, other attempt to fix this: break up shared formulas during import if they would wrap with 256 columns. Back to you ;-)
accepted
retargeted due to 3.1 deadline
*** Issue 98856 has been marked as a duplicate of this issue. ***
*** Issue 99376 has been marked as a duplicate of this issue. ***
*** Issue 103677 has been marked as a duplicate of this issue. ***
double to issue 103861 which contains a patch *** This issue has been marked as a duplicate of 103861 ***
duplicate -> reboot