Recent

Author Topic: Indirect/Address  (Read 748 times)

gii

  • New Member
  • *
  • Posts: 45
Indirect/Address
« on: November 09, 2020, 07:32:48 pm »
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

  • New Member
  • *
  • Posts: 45
Re: Indirect/Address
« Reply #1 on: November 09, 2020, 07:54:07 pm »
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

  • New Member
  • *
  • Posts: 45
Re: Indirect/Address
« Reply #2 on: November 09, 2020, 08:33:32 pm »
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

  • Hero Member
  • *****
  • Posts: 7964
Re: Indirect/Address
« Reply #3 on: November 10, 2020, 11:29:02 pm »
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).
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

gii

  • New Member
  • *
  • Posts: 45
Re: Indirect/Address
« Reply #4 on: November 11, 2020, 12:35:31 pm »
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

  • Hero Member
  • *****
  • Posts: 7964
Re: Indirect/Address
« Reply #5 on: November 12, 2020, 12:44:17 am »
Please try the new trunk revision.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

gii

  • New Member
  • *
  • Posts: 45
Re: Indirect/Address
« Reply #6 on: November 12, 2020, 12:27:53 pm »
Worked perfectly.

Thank you!

And again congratulations on the work!

 

TinyPortal © 2005-2018