how to define user define function in fpshreadsheet

[Prakash]

how to define user define function in fpshreadsheet

Code: [Select]

INT_EXCEL_SHEET_FUNC_CONCATENATE = 336; 
RegisterFunction('MYCONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE,@fpsCONCATENATE);
  procedure fpsCONCATENATE(var Result: TsExpressionResult;
  const Args: TsExprParameterArray);
  // CONCATENATE( text1, text2, ... text_n )
  var
    s: string;
    i: integer;
  begin
    s := '';
    for i := 0 to Length(Args) - 1 do
    begin
      if Args[i].ResultType = rtError then
      begin
        Result := ErrorResult(Args[i].ResError);
        exit;
      end;
      s := s + ArgToString(Args[i]);
      // "ArgToString" simplifies getting the string from a TsExpressionResult as
      // a string may be contained in the ResString and in the ResCell fields.
      // There is such a function for each basic data type.
    end;
    Result := StringResult(s);
    // "StringResult" stores the string s in the ResString field of the
    // TsExpressionResult and sets the ResultType to rtString.
    // There is such a function for each basic data type.
  end;

    workbook := TsWorkbook.Create;
     worksheet := workbook.AddWorksheet('Financial');

      formula := 'MYCONCATENATE("yes","no")';
      worksheet.WriteFormula(31, 1, formula);

I have tried this example . it is not working .

My expectation this function should be available in Excel file .

[PascalDragon]

Please use [code][/code] tags to avoid the forum software interpreting your code as markup.

[Prakash]

done

[Prakash]

I have attached my code .

please tell me what is the issue in my code

[wp]

I am attaching a simplified version of your code in which I removed the unneeded financial calculation of the demo on which it is based. I also wrote the output file in several formats.

I think I only had to add the package fpspreadsheet to the requirements of the project in order to make it work. Furthermore, I moved the registration of the user functions out of the WriteFile into the main procedure; this way, WriteFile can be called multiple times (otherwise the formula engine would complain about the already-registered functions). You can see in the output written to the console after reading the file that the user-defined formula is reckognized and handled by fpspreadsheet correctly.

But you certainly noticed also that Excel cannot understand this formula in the xlsx file and writes an error code into the result cell. The following excerpt from an internal xml file of the xlsx shows how the formula is stored in the cell

Code: XML  [Select][+][-]

1. <c r="B2" s="0" t="str"><f>MYCONCATENATE(&quot; yes &quot;,&quot; no &quot;)</f><v> yes  no </v></c>

As you can see the cell contains the word "MYCONCATENATE". The problem is that the formula parser of Excel does not know this identifier, and thus cannot identify the formula, and even if it could, it would not be able to perform the calculation because the code behind MYCONCATENATE is only known to fpspreadsheet.

The old binary xls format is different. Here the formulas are identified by a number - this is the INT_EXCEL_SHEET_FUNC_* constant which you must define for your user-defined formula. The value that you select for MYCONCATENATE is the same constant as for the built-in funtion CONCATENATE. Therefore, Excel is happy with your user-defined function, no matter that it is named differently. That the result is correct, however, is pure incidence since your function simply does the same as the built-in function; if it would do a different calculation then the result of fpspreadsheet would be different from that what Excel displays.

Take your PRAKASH function as an example. You assigned function code #62 to it which is used by Excel for the "Internal rate of return" function. Since that has a complete different argument list (an array of numbers) than your function (two strings) Excel rejects to compute this formula if it is used in the fpspreadsheet project. But your PRAKASH code simply does a string concatenation again, therefore you could reuse the formula code #336 again (INT_EXCEL_SHEET_FUNC_CONCATENATE). Now Excel will calculate the formula, but since it is using its own code the result is missing the intermediate word "Demo" that your code puts in between the two string arguments.

So, what I want to say is: New user-defined formulas are pretty much useless if there is no built-in equivalent in Excel (or LibreOffice Calc). The feature to add user-defined formulas was primarily added to fpspreadsheet to give the user the opportunity to add formulas in Excel which were not implemented in fpspreadsheet.

[Prakash]

Thanks so much for detailed explanation and sample code .

I want to automate excel through lazarus . do you have any other alternative .

[wp]

I want to automate excel through lazarus

Use the built-in Excel automation. There's a fairly good page about it in the wiki. It's in German though, but you can translate it with the standard online tools: https://wiki.lazarus.freepascal.org/ExcelAutomation/de