Writing Excel Addons - Reading Cell Values
(Page 6 of 8 )
Having been passed a cell reference (xltypeSref), you want to get at the values contained in the cells. To do this you must "coerce" the cell reference xloper. This coerce (an Excel function) forces Excel to construct a vector of xlopers, with exactly (#Rows * #Cols) xlopers. I.e., for a 2 x 3 array of cells, you get six xlopers. The target xloper becomes a header with the xltype field = 64 (xltypearray). The val._array member contains the number of rows and columns and a pointer to the body xloper.
These xlopers are arranged in row order so the six cells in two rows by 3 columns is stored as
1 2 3
4 5 6
Each will have the fundamental type (xltype num or Str) and the value.
Example of Coercion
Desttype.xltype := xltypeInt;
Desttype.val.w := xltypeMulti;
Eresult := Excel4v(xlcoerce,@Xval,2,[Values,@desttype]);
asm pop sink; end; // Never Remove
The xlopers/lpxlopers used here are:
- Desttype - An xloper that specifies the coercion destination type (xltypemulti- Excels name for an array of xlopers).
- Values - The passed in cell reference- an lpxloper.
- Xval - The target xloper. After a successful coerce, this is the header of an array. With the 2 x 3 array, there will be six xlopers, each holding a cell value.
Excel has allocated memory on your behalf and this has to be dealt with, but more on that later.
Example of using the values
if xval.val._array.rows<>1 then
begin
Error('Should only have one row');
goto myexit;
end;
xlar := xval.val._array.lparray;
index:=0;
for col:= 1 to xr.NumCols do
begin
if xlar^[index].xltype <> xltypenum then
begin
Error('Element '+inttostr(row)+' Not a number');
break;
end;
Value[col] := xlar^[index].val.num;
inc(index);
end;
xlar is a pointer to an array of xlopers. In this example this checks that there is one row of non-numeric data. It then copies the values into the Value[] array.
Next: Putting values into Excel Cells >>
More Windows Scripting Articles
More By David Bolton
/ 7 
