CODEWITHSUNDEEP
arraysexceldelphidelphi-7
Andrew Borys
Andrew Borys

Reputation: 1

Returning Excel Array from Delphi

I'm trying to call a Delphi DLL from Excel and return a column of variant data values. I have the dll returning a single shortstring, and that appears in a cell ok. Now I am trying to return a column of variable values. It gets into my code ok, but the array in excel is all 0.

Any ideas greatly appreciated.

Here is the macro registration in Excel: =REGISTER("c:\projects\test\delphixl.dll","GetPolicyData","KDD","GetPolicyData","Co,Pol",1,"Delphi")

I'm not sure what the 1 is after the parameters; I cant find full documentation anywhere.

the range in Excel has: {=GetPolicyData(C1,D1)}

Below is the code in D7. According to the excel doc for the register functions, this appears to be OK.

K Data Type

The K data type uses a pointer to a variable-size FP structure. You must define this structure in the DLL or code resource as follows:

typedef struct _FP
{
    unsigned short int rows;
    unsigned short int columns;
    double array[1];        /* Actually, array[rows][columns] */
} FP;


Type    
  pPolicyData = ^tPolicyData;
  tPolicyData = Record
    Rows: word;
    Cols: word;
    data: variant;
  End;

var
 pd: tPolicyData;

Function GetPolicyData(co: pShortString; pol: pShortString): pPolicyData; Stdcall;
Var
  polc: tpolcmst;
Begin
  lpro := tlifepro.create;
  lpro.opendatabases;
  polc := tpolcmst.create(lpro);
  Try
    polc.read(co^, pol^);
    pd.Rows := 2;
    pd.Cols := 1;
    pd.data := VarArrayCreate([0, 1], varVariant);
    pd.data[0] := datetostr(polc.issuedate);
    pd.data[1] := format('%.2f', [polc.modepremium]);
    result := addr(pd);
  Finally
    lpro.closedatabases;
    freeit(lpro);
  End;
End;

Upvotes: 0

Views: 632

Answers (1)

Deltics
Deltics

Reputation: 23056

You have declared the data member of the FP record (TPolicyData) as a Variant. It should be declared Double, and it should be an array of the required dimensions.

In this case, with Rows = 2 and Cols = 1 :

TPolicyData = record
  Rows: word;
  Cols: word;
  data: array[0..1] of Double;
End;

If the Rows and Cols are dynamic then you will need to calculate the memory required for the FP record and allocate this dynamically. The required memory will be:

   2 bytes for "Rows" word
+  2 bytes for "Cols" word
+  Rows x Cols x 8 bytes for "data" array of Doubles

With Delphi 7 you will need to use pointer offsets into the data memory to assign the values to the individual Double elements in the "array".

However, since you have not mentioned the need to address variable rows and columns, I shall not go into further detail on that as it may be superfluous.

Upvotes: 1

Related Questions