Indirect/Address
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
gii · 2020-11-09 18:32

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
fpsADDRESS(var Result: TsExpressionResult; const Args:
TsExprParameterArray);begin  Result := StringResult( NumToAlpha(
ArgToInt( Args[1] ) - 1 ) + IntToStr( ArgToInt( Args[0] ) ) );end; 
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][+][-]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  with ( Result.Worksheet as TsWorksheet )
do    Result := StringResult( ReadAsText( GetCell( ArgToString(
Args[0] ) ) ) );end; 
Does anyone have any suggestions? Thanks!

----------------------------------------------------------------------
gii · 2020-11-09 18:54

Looking at the examples, I modified the function to:

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  with ( Args[0].Worksheet as TsWorksheet )
do    Result := StringResult( ReadAsText( GetCell( ArgToString(
Args[0] ) ) ) );end;
This time, the error occurs in:

TsWorksheet.GetCell ()

----------------------------------------------------------------------
gii · 2020-11-09 19:33

INDIRECT () works now.

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

=INDIRECT(ADDRESS(1;1))

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
RegisterFunction('ADDRESS', 'C', 'II', 219, @fpsADDRESS
);RegisterFunction('INDIRECT', 'S', 'C', 148, @fpsINDIRECT
); procedure fpsADDRESS(var Result: TsExpressionResult; const Args:
TsExprParameterArray);begin  Result := CellResult( ArgToInt( Args[0]
), ArgToInt( Args[1] ) );end; procedure fpsINDIRECT(var Result:
TsExpressionResult; const Args: TsExprParameterArray);begin  with (
Args[0].Worksheet as TsWorksheet ) do    Result := StringResult(
ReadAsText( ArgToCell( Args[0] ) ) );end;

----------------------------------------------------------------------
wp · 2020-11-10 22:29

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
fpsADDRESS(var Result: TsExpressionResult;  const ARgs:
TsExprParameterArray);{ ADDRESS(row, column, [ref_type], [ref_style],
[sheet_name] )  Returns a text representation of a cell address. 
"row" and "col": row and column indices, 1-based.  "ref_type" is the
type of reference to use: 1=absolute, 2=rel col, abs row,    3= abs
col, rel row, 4=relative; if omitted, 1 (absolute) is assumed. 
"ref_style" if true (default) means: address in A1 dialect, otherwise
in R1C1.  "sheet_name": name of the worksheet. Note, when sheet_name
is used the    address is presented in Excel dialects only. }var  c,
r: Integer;  flags: TsRelFlags;  sheet: String;  resStr: String; 
A1Dialect: Boolean;begin  Result := ErrorResult(errArgError);  if
Length(Args) < 2 then    exit;  r := ArgToInt(Args[0]) - 1;  c :=
ArgToInt(Args[1]) - 1;   flags := [];  if Length(Args) > 2 then   
case ArgToInt(Args[2]) of      1: ;      2: flags := [rfRelCol];     
3: flags := [rfRelRow];      4: flags := [rfRelCol, rfRelRow];   
end;   A1Dialect := true;  if Length(Args) > 3 then    A1Dialect :=
ArgToBoolean(Args[3]);   sheet := '';  if Length(Args) > 4 then   
sheet := ArgToString(Args[4]);   if A1Dialect then    resStr :=
GetCellString(r, c, flags)  else    resStr := GetCellString_R1C1(r, c,
flags);   if sheet <> '' then resStr := sheet + '!' + resStr;   Result
:= StringResult(resStr);end;                           procedure
fpsINDIRECT(var Result: TsExpressionResult;  const Args:
TsExprParameterArray);{ INDIRECT(string_reference, [ref_style]) 
returns the reference to a cell based on its string representation 
"string_reference": textual representation of a cell reference. 
"ref_style": TRUE (default) indicates that string_reference will be
interpreted  as an A1-style reference. FALSE indicates that
string_reference will be  interpreted as an R1C1-style reference.  
NOTE: ref_style and mixing of A1 and R1C1 notation is not supported.
}begin  Result := ErrorResult(errArgError);  if Length(Args) = 0 then 
  exit;  Result := Args[0];end;Add these two lines at the end ("Lookup
/ reference functions") of procedure RegisterStdBuiltIns in the same
unit: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    
AddFunction(cat, 'ADDRESS',   'S',
'IIibs',INT_EXCEL_SHEET_FUNC_ADDRESS,    @fpsADDRESS);   
AddFunction(cat, 'INDIRECT',  'C', 'Sb',  
INT_EXCEL_SHEET_FUNC_INDIRECT,   @fpsINDIRECT);In unit xlsconst.pas,
add these declarations to the others: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  
INT_EXCEL_SHEET_FUNC_INDIRECT   = 148;  INT_EXCEL_SHEET_FUNC_ADDRESS 
 = 219;  // not available in BIFF2 
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][+][-]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,
fpsopendocument; var  book: TsWorkbook;  sheet1: TsWorksheet;  sheet2:
TsWorksheet; begin  book := TsWorkbook.Create;  try    book.Options :=
book.Options + [boAutoCalc];    sheet1 :=
book.AddWorksheet('Sheet1');    sheet1.WriteNumber(1, 1, 3.121592);  
// in B2    sheet1.WriteNumber (1, 2, 2);         // in C2     sheet2
:= book.AddWorksheet('Sheet2');    sheet2.WriteText(0, 0, 'Hallo');  
 sheet1.WriteText(2, 1, 'B2');         // in B3    sheet1.WriteText(2,
2, 'C2');         // in C3    sheet1.WriteText(2, 3, 'Sheet2!A1');  //
in D3    sheet1.WriteFormula(3, 1, 'INDIRECT(B3)');  // in D2   
sheet1.WriteFormula(3, 2, 'INDIRECT(C3)');  // in D3   
sheet1.WriteFormula(3, 3, 'INDIRECT(D3)');    sheet1.WriteFormula(4,
1, 'INDIRECT(B3) + INDIRECT(C3)');    sheet1.WriteFormula(5, 1,
'INDIRECT(ADDRESS(2, 2))');    sheet1.WriteFormula(5, 2,
'INDIRECT(ADDRESS(1, 1, , , "Sheet2"))');   
book.WriteToFile('test.xlsx', true);    book.WriteToFile('test.ods',
true);  finally    book.Free;  end;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 · 2020-11-11 11:35

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 · 2020-11-11 23:44

Please try the new trunk revision.

----------------------------------------------------------------------
gii · 2020-11-12 11:27

Worked perfectly.

Thank you!

And again congratulations on the work!