CalcFormulas function not working for formula INDIRECT lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- veb86 · 2024-05-24 12:45 Hello. CalcFormulas function not working for formula INDIRECT  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    b := TsWorkbook.Create;   b.Options := [boReadFormulas];  b.ReadFromFile('d:\1.xlsx', sfOOXML);  sh := b.GetFirstWorksheet;  sh.WriteFormula(2,2,'INDIRECT("F2")');  sh.CalcFormulas;        //the bug is here  b.WriteToFile('d:\2.xlsx', sfOOXML, true);   b.Free; ---------------------------------------------------------------------- wp · 2024-05-24 14:02 Do not quote cell adresses in formulas. This makes them to a string. This works: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program Project1;uses  fpSpreadsheet, fpsTypes, xlsxOOXML;var  b: TsWorkbook;  sh: TsWorksheet;begin  b := TsWorkbook.Create;   b.Options := [boReadFormulas];   b.ReadFromFile('1.xlsx', sfOOXML);   sh := b.GetFirstWorksheet;   sh.WriteFormula(2,2,'INDIRECT(F2)');   sh.CalcFormulas;   b.WriteToFile('2.xlsx', sfOOXML, true);   b.Free;end. ---------------------------------------------------------------------- veb86 · 2024-05-24 19:59 Quote from: wp on May 24, 2024, 04:02:24 pmDo not quote cell adresses in formulas. This makes them to a string. It is not right. INDIRECT(string) - is correct. If indirect(address), then the address points to a cell that contains the text address of the cell or range. The indirect always contains the string Look at the picture, I made different options Plus I added an updated Excel Additional for reference: https://www.ablebits.com/office-addins-blog/excel-indirect-function/ INDIRECT function syntax The INDIRECT function in Excel returns a cell reference from a text string. It has two arguments, the first is required and the second is optional: INDIRECT(ref_text, [a1]) ref_text - is a cell reference, or a reference to a cell in the form of a text string, or a named range. a1 - is a logical value that specifies what type of reference is contained in the ref_text argument:     - If TRUE or omitted, ref_text is interpreted as an A1-style cell reference.     - If FALSE, ref_text is treated as a R1C1 reference. ---------------------------------------------------------------------- wp · 2024-05-24 21:23 Thank you for teaching me. Is fixed now in ccr, hopefully. Could you test also whether references to other sheets are handled correctly, and provide a test file? ---------------------------------------------------------------------- veb86 · 2024-05-27 06:31 Quote from: wp on May 24, 2024, 11:23:07 pmThank you for teaching me. Is fixed now in ccr, hopefully. Could you test also whether references to other sheets are handled correctly, and provide a test file? Works, but not completely The bug is as follows: Cell value Sheet2!D1 = E1 Excel returns Formula INDIRECT(Sheet2!D1) -> Returns a link to cell E1 of the sheet in which the INDIRECT formula is written. Returns 222 (check the file) Library: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i sh := b.Get FirstWorksheet;sh.WriteFormula(0,2,'INDIRECT(Лист2!D1)');sh.CalcFormul as; Formula INDIRECT(Sheet2!D1) -> Returns a reference to the cell Sheet2 E1. That is, it gets the value of the cell Sheet2!E1, but should have returned Sheet1!E1. Is now returning 555 (check the file) it is not right. Found another bug Лист3 - does not exist Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i sh.WriteFormula(0,2,'INDIRECT(Лист3!D1)');  \\    << returns #N/Ash.WriteFormula(1,2,'INDIRECT("Лист3!D1")'); \\  << Bug, the program freezes. It must be too #N/A ---------------------------------------------------------------------- wp · 2024-05-28 21:41 Oh man - this is twisting my head... ---------------------------------------------------------------------- veb86 · 2024-05-29 11:22 Quote from: wp on May 28, 2024, 11:41:17 pmOh man - this is twisting my head... I'll try to fix the bug myself. Plus, I will now do the INDEX formula, it is very, very complicated. Tell me how to do it: For example, the formula =INDEX((A2:D3, A5:D7), 3, 4, 2) returns the value of cell D7, which is at the intersection of the 3rd row and 4th column in the second area (A5:D7). See picture 1 How to get a collection of these values ​​in code: (A2:D3, A5:D7): val[0]=A2:D3; val[1]=A5:D7; Do I need to write an additional parser or is there a ready-made parser? I'll try to add the INDEX formula myself. But I will most likely need help. ---------------------------------------------------------------------- wp · 2024-05-29 17:14 Added the basic INDEX formula: INDEX(range, row, col) to get you started. It omits the cases where a cell range should be returned. For implementing the combination of ranges (range1, range2, ...) you probably have to introduce a new TsResultType element (rtCellRangeArray). In the expression scanner you "somehow" must detect the situation where the opening bracket is met and distinguish it from the other usages of the opening bracket. Prepare a simple worksheet with such an INDEX formula, load it into fpspreadsheet, calculate the formula and follow the steps of the scanner and parser with the debugger. ---------------------------------------------------------------------- veb86 · 2024-05-29 20:04 Quote from: wp on May 29, 2024, 07:14:40 pmAdded the basic INDEX formula: INDEX(range, row, col) to get you started. It omits the cases where a cell range should be returned. I will check the added INDEX formula tomorrow. In my project, I use only such an INDEX formula. I do not use a combination of ranges. We can temporarily leave this INDEX formula. I have another question. I will try to make do with the ready-made SUMIF formula, but ideally I need another SUMIFS formula. I was wondering, in the "OpenOffice Documentation of the Microsoft Excel File Format", section 3.11, there is no code for this formula. Study the source code of librecalс, I also did not find a special code, only 255, and this code takes several formulas there. But our iferror formula takes up this code. How is the solution planned in this case? ---------------------------------------------------------------------- wp · 2024-05-29 20:20 Quote from: veb86 on May 29, 2024, 10:04:22 pmin the "OpenOffice Documentation of the Microsoft Excel File Format", section 3.11, there is no code for this formula. Study the source code of librecalс, I also did not find a special code, only 255, and this code takes several formulas there. But our iferror formula takes up this code. How is the solution planned in this case? These codes are used only by xlsBIFF8 and older. Functions having the code INT_EXCEL_SHEET_FUNC_UNKNOWN = 255 are ignored by these old formats (I'll have to check, though, it's been a long time since I wrote this...). In xlsx and ods only the function names are needed. Of course, they must be unique, and if you want to be able to read your custom formula in Excel/Calc its name must match the corresponding Excel name. ---------------------------------------------------------------------- wp · 2024-05-29 21:00 Quote from: wp on May 28, 2024, 11:41:17 pmOh man - this is twisting my head... The following explanation refers to test file in reply #4: What really confuses me is that references here seem to be completely different from all the other parts of the formula engine. Your sheet1 contains the formula INDIRECT(sheet2!D1), and sheet2!D1 contains the string E1. The INDIRECT formula reads sheet2!D1, gets the E1 and interprets this as a reference to E1 in its own sheet, sheet1. Why not in sheet2? A "normal" formula "=E1" in sheet2 usually is interpreted to point to E1 in sheet2. I am afraid  that getting this right could require a major rework in the formula engine... What you could do is to write the references in the target cells accessed from other sheets with sheetname. In other words: In cell sheet2!D1 write Sheet1!E1 rather than E1 alone. ---------------------------------------------------------------------- veb86 · 2024-05-30 06:38 Quote from: wp on May 29, 2024, 10:20:11 pmThese codes are used only by xlsBIFF8 and older. Functions having the code INT_EXCEL_SHEET_FUNC_UNKNOWN = 255 are ignored by these old formats (I'll have to check, though, it's been a long time since I wrote this...). In xlsx and ods only the function names are needed. Of course, they must be unique, and if you want to be able to read your custom formula in Excel/Calc its name must match the corresponding Excel name. I get it, thanks. For now, I'll try to make do with what I have. ---------------------------------------------------------------------- veb86 · 2024-05-30 06:54 Quote from: wp on May 28, 2024, 11:41:17 pmWhy not in sheet2? That's how the creators of excel came up with it INDIRECT always turns text into a formula. That is: INDIRECT("sheet2!D1") the result will be the formula =sheet2!D1. INDIRECT(sheet2!D1) will take the value of the cell sheet2!D1 as a string, that is, "E1" INDIRECT(sheet2!D1) is essentially the same as INDIRECT("E1"). INDIRECT("E1") it's just =E1. There doesn't seem to be anything complicated. INDIRECT (always a string). If a set of cells is fed inside, they get the value as strings. I think you don't need to change the formula engine. ---------------------------------------------------------------------- veb86 · 2024-05-30 07:02 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i sh.WriteFormula(1,2,'INDIRECT("Sheet3!D1")'); \\  << Sheet3!D1 does not existsh.CalcFormulas; << Bug, the program freezes. It must be too #N/A  This is very bad when code execution leads to crashes in the program. I think that if a sheet is written in the formula that does not exist, then the result of executing the formula should be an error of the type ("#N/A") ---------------------------------------------------------------------- veb86 · 2024-05-30 11:59 Quote from: veb86 on May 30, 2024, 08:54:03 amI think you don't need to change the formula engine. I think I have solved the problem with INDIRECT. Code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure fpsINDIRECT(var Result: TsExpressionResult;  const Args: TsExprParameterArray);begin  Result := ErrorResult(errArgError);  if Length(Args) = 0 then    exit;  if (Args[0].ResultType = rtCell) then begin    Result := CellResult(ArgToString(Args[0]));  end else  if (Args[0].ResultType = rtString) then    Result := CellResult(Args[0].ResString);end; The first tests are excellent. Everything is working. But I don’t want to commit the solution yet, I use INDIRECT in a lot of places, I want to check how it works ---------------------------------------------------------------------- veb86 · 2024-05-30 12:00 Quote from: veb86 on May 30, 2024, 09:02:15 amCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i sh.WriteFormula(1,2,'INDIRECT("Sheet3!D1")'); \\  << Sheet3!D1 does not existsh.CalcFormulas; << Bug, the program freezes. It must be too #N/A  This is very bad when code execution leads to crashes in the program. I think that if a sheet is written in the formula that does not exist, then the result of executing the formula should be an error of the type ("#N/A") Please solve this problem ---------------------------------------------------------------------- wp · 2024-05-30 12:09 Quote from: veb86 on May 30, 2024, 01:59:04 pmI think I have solved the problem with INDIRECT. I came to a similar solution, but am setting also the Result.Worksheet which is left dangling in yours. Please test the current svn version which also catches the case of a non-existing worksheet. [EDIT] Switched to your implementation which is more logical and shorter. BTW, added also some unit tests regarding the new functions. If you have an idea about what is missing, please tell me. ---------------------------------------------------------------------- veb86 · 2024-05-30 12:44 Quote from: wp on May 29, 2024, 07:14:40 pmAdded the basic INDEX formula: INDEX(range, row, col) to get you started. It omits the cases where a cell range should be returned. Not completely correct. col - optional parameter. If the range is one-dimensional vertically or horizontally, then only the 2nd value is sufficient. They indicate which value to take from the beginning of the range. If the range is two-dimensional, then a third value is added. The second value is a rows. The third value is the column. I think we need to make an INDEX to this temporary state. ---------------------------------------------------------------------- veb86 · 2024-05-30 12:59 Quote from: veb86 on May 30, 2024, 02:44:16 pmI think we need to make an INDEX to this temporary state. INDEX. Test file shows how it works ---------------------------------------------------------------------- wp · 2024-05-30 13:28 Fixed. There is another missing option, namely row or column index specified to be zero, which returns an entire row or column: This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far. ---------------------------------------------------------------------- veb86 · 2024-05-31 05:40 Quote from: wp on May 30, 2024, 03:28:19 pm This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far. I'm very upset. These are the basic functions of the formula engine. I am now at the stage of developing a large Excel workbook for complex calculations. I'll try not to use this. How difficult is it to add this functionality? ---------------------------------------------------------------------- wp · 2024-05-31 13:13 Quote from: wp on May 30, 2024, 03:28:19 pmThere is another missing option, namely row or column index specified to be zero, which returns an entire row or column: This is not supported because FPSpreadsheet in no way can handle cell ranges as return values of formulas, so far. Added the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working. ---------------------------------------------------------------------- veb86 · 2024-05-31 19:36 Quote from: wp on May 31, 2024, 03:13:12 pmAdded the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working. Great. Very cool, thank you very much. All I have to do is add 3 formulas (SUMIFS, COUNTIFS and IFS) to get the same results as Excel through OLE control. SUMIFS, COUNTIFS and IFS differ from SUMIF, COUNTIF and IF only in multiple conditions. I saw in the code the implementation of SUMIF, COUNTIF and IF; they are all tied to the DoIF function. Please look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function? SUMIFS: https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple- criteria/ COUNTIFS: https://exceljet.net/functions/countifs-function IFS: https://exceljet.net/functions/ifs-function ---------------------------------------------------------------------- veb86 · 2024-06-02 13:39 Quote from: veb86 on May 31, 2024, 09:36:04 pmQuote from: wp on May 31, 2024, 03:13:12 pmAdded the infrastructure for cell ranges as output. Should work, at least something like =SUM(INDEX(A1:C9,3,0)) or =SUM(INDEX(A1:C9,0,3)) is working. Great. Very cool, thank you very much. All I have to do is add 3 formulas (SUMIFS, COUNTIFS and IFS) to get the same results as Excel through OLE control. SUMIFS, COUNTIFS and IFS differ from SUMIF, COUNTIF and IF only in multiple conditions. I saw in the code the implementation of SUMIF, COUNTIF and IF; they are all tied to the DoIF function. Please look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function? SUMIFS: https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple- criteria/ COUNTIFS: https://exceljet.net/functions/countifs-function IFS: https://exceljet.net/functions/ifs-function Don't waste your energy on this task. I'll add it myself. In my project I select equipment. And it would be very cool if you added the ability to copy and add validation lists. Is this really possible? https://www.powerusersoftwares.com/post/2018/07/31/how-to-create- simple-or-dynamic-drop-down-lists-in-excel-and-why-you-should I asked this a long time ago, but you didn't answer. For me, this is about creating ease of use. It would be very cool if it was. But it’s not critical if there are no validation lists ---------------------------------------------------------------------- wp · 2024-06-03 22:28 Quote from: veb86 on May 31, 2024, 09:36:04 pmPlease look at how difficult it is to connect SUMIFS, COUNTIFS and IFS to the DoIF function? I refactored the ugly DoIF function into a dedicated class for evaluation, and then was able to add SUMIFS, COUNTIFS, and AVERAGEIFS. IFS is still missing because it has a different logic in the arguments. A few tests with files created from Microsoft examples showed that the functions seem to work - mostly. There are a few limitations, though: - In string comparisons, Excel allows wildcards - this is not supported ATM. - When a cell contains a condition which is referenced by the formula, the expression parser so far does not reckognize the argument as a cell reference but assumes it is a string. Therefore, something like SUMIFS(A1:A10, B1:B10, C1) where C1 contains the string ">10" is not calculated correctly. ---------------------------------------------------------------------- veb86 · 2024-06-04 20:20 Quote from: wp on June 04, 2024, 12:28:21 am- In string comparisons, Excel allows wildcards - this is not supported ATM. This is very similar to regular expressions. In my work I do not use such opportunities. Quote from: wp on June 04, 2024, 12:28:21 am- When a cell contains a condition which is referenced by the formula, the expression parser so far does not reckognize the argument as a cell reference but assumes it is a string. Therefore, something like SUMIFS(A1:A10, B1:B10, C1) where C1 contains the string ">10" is not calculated correctly. I didn't know that this was possible. I don't use this either, but it's an interesting possibility. Quote from: wp on June 04, 2024, 12:28:21 amI refactored the ugly DoIF function into a dedicated class for evaluation, and then was able to add SUMIFS, COUNTIFS, and AVERAGEIFS You greatly changed the way formulas with conditions work, it turned out very well. I couldn't do that, I have a weak level of knowledge. I'm leaving for a few days, and when I get back I'll test it right away. Thank you!