CalcFormulas function not working for formula INDIRECT

[veb86]

Hello.
CalcFormulas function not working for formula INDIRECT

 

Code: Pascal  [Select][+][-]

1.  
2.   b := TsWorkbook.Create;
3.   b.Options := [boReadFormulas];
4.   b.ReadFromFile('d:\1.xlsx', sfOOXML);
5.   sh := b.GetFirstWorksheet;
6.   sh.WriteFormula(2,2,'INDIRECT("F2")');
7.   sh.CalcFormulas;        //the bug is here
8.   b.WriteToFile('d:\2.xlsx', sfOOXML, true);
9.   b.Free;  
10.  

[wp]

Do not quote cell adresses in formulas. This makes them to a string.

This works:

Code: Pascal  [Select][+][-]

1. program Project1;
2. uses
3.   fpSpreadsheet, fpsTypes, xlsxOOXML;
4. var
5.   b: TsWorkbook;
6.   sh: TsWorksheet;
7. begin
8.   b := TsWorkbook.Create;
9.    b.Options := [boReadFormulas];
10.    b.ReadFromFile('1.xlsx', sfOOXML);
11.    sh := b.GetFirstWorksheet;
12.    sh.WriteFormula(2,2,'INDIRECT(F2)');
13.    sh.CalcFormulas;
14.    b.WriteToFile('2.xlsx', sfOOXML, true);
15.    b.Free;
16. end.

[veb86]

Do 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]

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]

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?

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][+][-]

1. sh := b.GetFirstWorksheet;
2. sh.WriteFormula(0,2,'INDIRECT(Лист2!D1)');
3. sh.CalcFormulas;
4.  

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][+][-]

1. sh.WriteFormula(0,2,'INDIRECT(Лист3!D1)');  \\    << returns #N/A
2. sh.WriteFormula(1,2,'INDIRECT("Лист3!D1")'); \\  << Bug, the program freezes. It must be too #N/A
3.  

[wp]

Oh man - this is twisting my head...

[veb86]

Oh 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]

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]

Added 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]

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?

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]

Oh 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]

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.

I get it, thanks. For now, I'll try to make do with what I have.

[veb86]

Why 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]

Code: Pascal  [Select][+][-]

1. sh.WriteFormula(1,2,'INDIRECT("Sheet3!D1")'); \\  << Sheet3!D1 does not exist
2. sh.CalcFormulas; << Bug, the program freezes. It must be too #N/A
3.  

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]

I think you don't need to change the formula engine.

I think I have solved the problem with INDIRECT.
Code:

Code: Pascal  [Select][+][-]

1. procedure fpsINDIRECT(var Result: TsExpressionResult;
2.   const Args: TsExprParameterArray);
3. begin
4.   Result := ErrorResult(errArgError);
5.   if Length(Args) = 0 then
6.     exit;
7.   if (Args[0].ResultType = rtCell) then begin
8.     Result := CellResult(ArgToString(Args[0]));
9.   end else
10.   if (Args[0].ResultType = rtString) then
11.     Result := CellResult(Args[0].ResString);
12. end;
13.  

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