Excel formula cells overrun to other formula cells on other sheet lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- eclbnhan · 2017-02-17 21:24 I really appreciate your work on Fpspreadsheet. I am using Fpspreadsheet svn r5740. However, I am running into a problem. Please help me investigate and resolve this problem. I trying to load an Excel file with TsWorkbookTabControl, TsWorksheetGrid, and TsWorkbookSource. Attached is an Excel 2007 LoadFormulaProblem.xlsx. Here is how I load LoadFormulaProblem.xlsx: sWorkbookSource1.FileName := 'LoadFormulaProblem.xlsx';  //with option [boReadFormulas] The formulas from some cells on Sheet1 seem to overrun to the other formula cells on Sheet2 in the same workbook. The wrong/unexpected formulas can be seen with TsSpreadsheetInspector, Mode imCellValue. Or just do a CalcFormulas (sWorkbookSource1.Worksheet.CalcFormulas) then wrong formulas will show unexpected values. The suspected formula cells are are at Sheet1!E6:CG35 The cells that got (wrong) formulas overrun mostly within Sheet2!N52:T102 I guess the overrun is due to the handling of large amount of formulas. CalcFormulas is also noticeably slow. I cut down the formulas to Sheet1!E6:CG7 but still see the overrun of some cells at Sheet2!Q55:S102 in a strange "logic". I tried to use only supported built-in Excel formulas in LoadFormulaProblem.xlsx. Originally I experienced the problem with user-defined formulas. I am not sure this could be a bug or there could be the way to overcome it. Please help! Thank you! ---------------------------------------------------------------------- wp · 2017-02-18 17:46 Your file heavily makes use of shared formulas in both sheets which is an Excel-only feature. Shared formulas are stored only in a single cell, but are valid for a larger cell range; they are created for example if you drag a formula across several cells in Excel.  The xlsx reader collects shared formulas in a list and applies them to the cells when needed. I did not consider that this list begins anew with every sheet. Therefore, the shared formulas which are addressed by their list index get out of sync. After clearing the shared formula list before reading a new sheet the problem seems to be gone. Please test. There are a few other issues left. Please keep an eye on svn, I won't address every fix here. I noticed the slow speed due to the many formulas, and I think the formula engine must be redone. The way to go would probably be to remove the plain-text formulas from the cells and store formulas as parsed expression trees or rpn token arrays in a separate data structure. This should speed up calculation considerably. However, since this modification goes deeply into the heart of the spreadsheet I cannot promise that this will occur soon. ---------------------------------------------------------------------- wp · 2017-02-18 19:03 Ah, maybe it's not necessary to redo the formula engine. Your file was filled up to the limit of > 16000 columns. After the reading the true size of the grid from the "dimension" attribute these filler columns can be suppressed, and fpspreadsheet is as fast as usual... ---------------------------------------------------------------------- eclbnhan · 2017-02-20 07:42 That major overrun problem with my file is gone. You fixed it. I believe it is a challenging problem. I tested couple different excel spreadsheets including user-defined formulas with r5756 and felt confident with the results. I don't see the "overrun" problem any more. Thank you! Since you mentioned shared formulas. I think there is a problem with it. Attached is file SharedFormulasProblem.xlsx. With Excel you will see in Sheet2 these cells: B5=MAX(B1:B4)-MIN(B1:B4) C5=MAX(C1:C4)-MIN(C1:C4) D5=MAX(D1:D4)-MIN(D1:D4) E5=MAX(E1:E4)-MIN(E1:E4) F5=MAX(F1:F4)-MIN(F1:F4) G5=MAX(G1:G4)-MIN(G1:G4) H5=MAX(H1:H4)-MIN(H1:H4) However, with FPSpreadsheet r5756 and before, you will see in Sheet2, MAX(C1:C4)-MIN(C1:C4) stays the same in cell C5, D5, E5, F5, G5, and H5: B5=MAX(B1:B4)-MIN(B1:B4) C5=MAX(C1:C4)-MIN(C1:C4) D5=MAX(C1:C4)-MIN(C1:C4) E5=MAX(C1:C4)-MIN(C1:C4) F5=MAX(C1:C4)-MIN(C1:C4) G5=MAX(C1:C4)-MIN(C1:C4) H5=MAX(C1:C4)-MIN(C1:C4) On an older version than r5657, the formula in D5, E5, F5, G5, H5 somehow is ROUND(RAND()*100,0) which is the formula on the other cells B1:H4. >> Your file was filled up to the limit of > 16000 columns. After the reading the true size of the grid from the "dimension" attribute these filler columns can be suppressed What should I do to suppress  the "dimension" attribute these filler columns? (It seems you meant that there is the way to overcome the problem) Anyway I really appreciate your work on fixing the "overrun" problem and many others! ---------------------------------------------------------------------- wp · 2017-02-20 23:21 As for the shared formula try r5767. Quote from: eclbnhan on February 20, 2017, 08:42:52 am>> Your file was filled up to the limit of > 16000 columns. After the reading the true size of the grid from the "dimension" attribute these filler columns can be suppressed What should I do to suppress  the "dimension" attribute these filler columns? (It seems you meant that there is the way to overcome the problem) Normally Excel does not do that. I could trigger this effect by clicking at the "Select All" corner (fixed cell in the top/left corner) or by pressing CTRL+A and then defining a format for the entire worksheet. This results in a set of column nodes in the worksheet xml file: Code: XML  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i               Columns 1 through 8 were defined by myself, but Excel adds additional columns with indexes between 8 and 16384 - see last node. But since the true size of the sheet is written in the dimension node this situation can be safe bypassed now. So, there's nothing to do for you. ---------------------------------------------------------------------- eclbnhan · 2017-02-21 17:21 Your r5767 fixed the shared formula problem. Thank you! I am very happy with what you've done to resolve excel formula problems. It also resolved major problems with our app. Thanks a lot.