Indirect/Address

[gii]

I have a spreadsheet that uses the Indirect and Address functions. Unfortunately, the component does not natively support these functions.

The function ADDRESS():

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

1. procedure fpsADDRESS(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. begin
3.   Result := StringResult( NumToAlpha( ArgToInt( Args[1] ) - 1 ) + IntToStr( ArgToInt( Args[0] ) ) );
4. end;
5.  

However, I don't know how to retrieve the value of another cell in the Indirect function.

I tried something like:

But it does not work.

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

1. procedure fpsINDIRECT(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. begin
3.   with ( Result.Worksheet as TsWorksheet ) do
4.     Result := StringResult( ReadAsText( GetCell( ArgToString( Args[0] ) ) ) );
5. end;
6.  

Does anyone have any suggestions? Thanks!

[gii]

Looking at the examples, I modified the function to:

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

1. procedure fpsINDIRECT(var Result: TsExpressionResult; const Args: TsExprParameterArray);
2. begin
3.   with ( Args[0].Worksheet as TsWorksheet ) do
4.     Result := StringResult( ReadAsText( GetCell( ArgToString( Args[0] ) ) ) );
5. end;

This time, the error occurs in:

TsWorksheet.GetCell ()

[gii]

INDIRECT () works now.

But it doesn't work if ADDRESS () is your parameter.

=INDIRECT(ADDRESS(1;1))

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

1. RegisterFunction('ADDRESS', 'C', 'II', 219, @fpsADDRESS );
2. RegisterFunction('INDIRECT', 'S', 'C', 148, @fpsINDIRECT );
3.  
4. procedure fpsADDRESS(var Result: TsExpressionResult; const Args: TsExprParameterArray);
5. begin
6.   Result := CellResult( ArgToInt( Args[0] ), ArgToInt( Args[1] ) );
7. end;
8.  
9. procedure fpsINDIRECT(var Result: TsExpressionResult; const Args: TsExprParameterArray);
10. begin
11.   with ( Args[0].Worksheet as TsWorksheet ) do
12.     Result := StringResult( ReadAsText( ArgToCell( Args[0] ) ) );
13. end;
14.  

[wp]

You are confusing the result types: ADDRESS() should return a string (e.g. 'A1', when the arguments are both 0), and INDIRECT() should return a cell.

I added the two formulas to fpspreadsheet trunk, please test. If you work with the release version you must apply these changes:

• Open fpsfunc.pas and add these two functions:

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

1. procedure fpsADDRESS(var Result: TsExpressionResult;
2.   const ARgs: TsExprParameterArray);
3. { ADDRESS(row, column, [ref_type], [ref_style], [sheet_name] )
4.   Returns a text representation of a cell address.
5.   "row" and "col": row and column indices, 1-based.
6.   "ref_type" is the type of reference to use: 1=absolute, 2=rel col, abs row,
7.     3= abs col, rel row, 4=relative; if omitted, 1 (absolute) is assumed.
8.   "ref_style" if true (default) means: address in A1 dialect, otherwise in R1C1.
9.   "sheet_name": name of the worksheet. Note, when sheet_name is used the
10.     address is presented in Excel dialects only. }
11. var
12.   c, r: Integer;
13.   flags: TsRelFlags;
14.   sheet: String;
15.   resStr: String;
16.   A1Dialect: Boolean;
17. begin
18.   Result := ErrorResult(errArgError);
19.   if Length(Args) < 2 then
20.     exit;
21.   r := ArgToInt(Args[0]) - 1;
22.   c := ArgToInt(Args[1]) - 1;
23.  
24.   flags := [];
25.   if Length(Args) > 2 then
26.     case ArgToInt(Args[2]) of
27.       1: ;
28.       2: flags := [rfRelCol];
29.       3: flags := [rfRelRow];
30.       4: flags := [rfRelCol, rfRelRow];
31.     end;
32.  
33.   A1Dialect := true;
34.   if Length(Args) > 3 then
35.     A1Dialect := ArgToBoolean(Args[3]);
36.  
37.   sheet := '';
38.   if Length(Args) > 4 then
39.     sheet := ArgToString(Args[4]);
40.  
41.   if A1Dialect then
42.     resStr := GetCellString(r, c, flags)
43.   else
44.     resStr := GetCellString_R1C1(r, c, flags);
45.  
46.   if sheet <> '' then resStr := sheet + '!' + resStr;
47.  
48.   Result := StringResult(resStr);
49. end;                          
50.  
51. procedure fpsINDIRECT(var Result: TsExpressionResult;
52.   const Args: TsExprParameterArray);
53. { INDIRECT(string_reference, [ref_style])
54.   returns the reference to a cell based on its string representation
55.   "string_reference": textual representation of a cell reference.
56.   "ref_style": TRUE (default) indicates that string_reference will be interpreted
57.   as an A1-style reference. FALSE indicates that string_reference will be
58.   interpreted as an R1C1-style reference.
59.  
60.   NOTE: ref_style and mixing of A1 and R1C1 notation is not supported. }
61. begin
62.   Result := ErrorResult(errArgError);
63.   if Length(Args) = 0 then
64.     exit;
65.   Result := Args[0];
66. end;

• Add these two lines at the end ("Lookup / reference functions") of procedure RegisterStdBuiltIns in the same unit:

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

1.     AddFunction(cat, 'ADDRESS',   'S', 'IIibs',INT_EXCEL_SHEET_FUNC_ADDRESS,    @fpsADDRESS);
2.     AddFunction(cat, 'INDIRECT',  'C', 'Sb',   INT_EXCEL_SHEET_FUNC_INDIRECT,   @fpsINDIRECT);

• In unit xlsconst.pas, add these declarations to the others:

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

1.   INT_EXCEL_SHEET_FUNC_INDIRECT   = 148;
2.   INT_EXCEL_SHEET_FUNC_ADDRESS    = 219;  // not available in BIFF2
3.  

With these modification, the following code creates valid Excel and ODS Calc files which behave correctly (in my opinion -- I never used these functions before...):

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

1. program project1;
2.  
3. uses
4.   fpspreadsheet, fpsTypes, xlsxooxml, fpsopendocument;
5.  
6. var
7.   book: TsWorkbook;
8.   sheet1: TsWorksheet;
9.   sheet2: TsWorksheet;
10.  
11. begin
12.   book := TsWorkbook.Create;
13.   try
14.     book.Options := book.Options + [boAutoCalc];
15.     sheet1 := book.AddWorksheet('Sheet1');
16.     sheet1.WriteNumber(1, 1, 3.121592);   // in B2
17.     sheet1.WriteNumber (1, 2, 2);         // in C2
18.  
19.     sheet2 := book.AddWorksheet('Sheet2');
20.     sheet2.WriteText(0, 0, 'Hallo');
21.  
22.     sheet1.WriteText(2, 1, 'B2');         // in B3
23.     sheet1.WriteText(2, 2, 'C2');         // in C3
24.     sheet1.WriteText(2, 3, 'Sheet2!A1');  // in D3
25.     sheet1.WriteFormula(3, 1, 'INDIRECT(B3)');  // in D2
26.     sheet1.WriteFormula(3, 2, 'INDIRECT(C3)');  // in D3
27.     sheet1.WriteFormula(3, 3, 'INDIRECT(D3)');
28.     sheet1.WriteFormula(4, 1, 'INDIRECT(B3) + INDIRECT(C3)');
29.     sheet1.WriteFormula(5, 1, 'INDIRECT(ADDRESS(2, 2))');
30.     sheet1.WriteFormula(5, 2, 'INDIRECT(ADDRESS(1, 1, , , "Sheet2"))');
31.     book.WriteToFile('test.xlsx', true);
32.     book.WriteToFile('test.ods', true);
33.   finally
34.     book.Free;
35.   end;
36. end.

Note that cell references are not fully supported. Mixing of A1 and R1C1 notation as suggested by the optional parameters of these formulas will crash the fpspreadsheet formula parser. Moreover I see problems when 3D formulas (containing sheet names) will work when loaded into LibreOffice Calc because they are written in the Excel notation (but I did not test it).

[gii]

I upgraded to the trunk version.

When I use the functions individually, they work. But together, it does not have the expected result.

Thank you very much and congratulations on your work.

[wp]

Please try the new trunk revision.

[gii]

Worked perfectly.

Thank you!

And again congratulations on the work!