Returning Excel Array from Delphi

Question

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;

Thanks, for your answers. I re-read the doc and figured out it only took doubles. So, now I am populating SQL tables and having those on a sheet as a data connection. I use the REGISTER as this is the only thing I could find to call the dll. If there is another way, I am all ears. It all works, except I haven't figured out how to get the register to run on start up. I am no VBA expert, so haven't figured out how to get this in an auto run macro. Again, any tips greatfully accepted. — Andrew Borys
– Andrew Borys, Commented Mar 12, 2015 at 17:21

Deltics · Accepted Answer · 2015-03-11 01:38:30Z

1

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.

answered Mar 11, 2015 at 1:38

Deltics

23.1k3 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Heffernan Over a year ago

pShortString has to be wrong too. PAnsiChar surely.

Deltics Over a year ago

@David - No. The data type specification is KDD. i.e. "returns K (array) and accepts 2 D params". D is the code for a byte length prefixed string of single-byte chars. Or, in Delphi terms: a ShortString. i.e. ShortString is EXACTLY right.

Collectives™ on Stack Overflow

Returning Excel Array from Delphi

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related