Board index » delphi » Accessing an array of values from Excel

Accessing an array of values from Excel


2004-12-16 04:01:29 PM
delphi29
Help says that using .Value on a Range of cells gives back an array of
values.
I can not seem to get my head around assigning that array to a variable so I
can access the values
Can anyone give me the syntax for that.
Something like....
MyArray := FThisWorksheet.Range['A1', 'C3'].Value; ????
Many thanks
Richard Teller
 
 

Re:Accessing an array of values from Excel

Hi,
take a look at my example, hth:
XLApplication := CreateOleObject('Excel.Application');
XLApplication.Visible := True;
LWorkbook := XLApplication.Workbooks.Add(xlWBATWorksheet);
LSheet := LWorkbook.Sheets.Item[1];
LSheet.Range['A1:C1'].Value := VarArrayOf([1,2,3]);
LSheet.Range['A2:C2'].Value := VarArrayOf([4,5,6]);
LValueArr := LSheet.Range['A1:C2'].Value;
for i := VarArrayLowBound(LValueArr, 1) to VarArrayHighBound(LValueArr, 1)
do
for j := VarArrayLowBound(LValueArr, 2) to VarArrayHighBound(LValueArr,
2) do
begin
LValueArrCoord[1] := i;
LValueArrCoord[2] := j;
LSheet.Cells.Item[i + 3, j].Value := VarArrayGet(LValueArr,
LValueArrCoord);
end;
where:
i, j: Integer;
XLApplication: OLEVariant;
LWorkbook, LSheet: OLEVariant;
LValueArr: variant;
LValueArrCoord: array [1..2] of longint;
One more thing, of course first you have to check if your range.value is an
array. There is a function VarArrayDimCount [in example above
VarArrayDimCount(LValueArr) = 2]. Returned 0 mean it is not an array so
there is no bounds etc.. code crash.
Example: LValueArr := LSheet.Range['A1'].Value; // its VarArrayDimCount =
0
"Richard Teller" <XXXX@XXXXX.COM>writes
Quote
Help says that using .Value on a Range of cells gives back an array of
values.
I can not seem to get my head around assigning that array to a variable so I
can access the values
Can anyone give me the syntax for that.

Something like....
MyArray := FThisWorksheet.Range['A1', 'C3'].Value; ????

Many thanks
Richard Teller


 

Re:Accessing an array of values from Excel

<<Richard Teller:
Help says that using .Value on a Range of cells gives back
an array of values.
Quote
>
var
ArrV: OleVariant;
..
Sum := 0;
ArrV := WS.Range['B2', 'D3'].Value2;
for Row := 1 to 2 do
for Col := 1 to 3 do
Sum := Sum + ArrV[Row, Col];
ShowMessage('Sum is ' + FloatToStr(Sum));
--
Deborah Pate (TeamB) delphi-jedi.org
TeamB don't see posts sent via Google or ISPs
Use the real Borland server: newsgroups.borland.com
www.borland.com/newsgroups/genl_faqs.html
 

Re:Accessing an array of values from Excel

Many thanks, Deborah.
Do you know of a way to show the values retrieved *exactly* as they appear
in the Excel sheet i.e. respecting all cell formatting, number formats etc.
I'm hoping there's a function like .Value I could use rather than work hard
reading the formatting of every cell, interpreting it and then and
re-applying it in my code.
"Deborah Pate (TeamB)" <XXXX@XXXXX.COM>wrote in
message news:XXXX@XXXXX.COM...
Quote
<<Richard Teller:
Help says that using .Value on a Range of cells gives back
an array of values.
>>

var
ArrV: OleVariant;
..
Sum := 0;
ArrV := WS.Range['B2', 'D3'].Value2;
for Row := 1 to 2 do
for Col := 1 to 3 do
Sum := Sum + ArrV[Row, Col];
ShowMessage('Sum is ' + FloatToStr(Sum));

--
Deborah Pate (TeamB) delphi-jedi.org

TeamB don't see posts sent via Google or ISPs
Use the real Borland server: newsgroups.borland.com
www.borland.com/newsgroups/genl_faqs.html


 

Re:Accessing an array of values from Excel

Many thanks, Mariusz, thats great.
"Mariusz Celmer" <XXXX@XXXXX.COM>writes
Quote
Hi,

take a look at my example, hth:

XLApplication := CreateOleObject('Excel.Application');
XLApplication.Visible := True;
LWorkbook := XLApplication.Workbooks.Add(xlWBATWorksheet);
LSheet := LWorkbook.Sheets.Item[1];

LSheet.Range['A1:C1'].Value := VarArrayOf([1,2,3]);
LSheet.Range['A2:C2'].Value := VarArrayOf([4,5,6]);
LValueArr := LSheet.Range['A1:C2'].Value;
for i := VarArrayLowBound(LValueArr, 1) to VarArrayHighBound(LValueArr,
1)
do
for j := VarArrayLowBound(LValueArr, 2) to VarArrayHighBound(LValueArr,
2) do
begin
LValueArrCoord[1] := i;
LValueArrCoord[2] := j;
LSheet.Cells.Item[i + 3, j].Value := VarArrayGet(LValueArr,
LValueArrCoord);
end;

where:
i, j: Integer;
XLApplication: OLEVariant;
LWorkbook, LSheet: OLEVariant;
LValueArr: variant;
LValueArrCoord: array [1..2] of longint;

One more thing, of course first you have to check if your range.value is
an
array. There is a function VarArrayDimCount [in example above
VarArrayDimCount(LValueArr) = 2]. Returned 0 mean it is not an array so
there is no bounds etc.. code crash.

Example: LValueArr := LSheet.Range['A1'].Value; // its VarArrayDimCount
=
0



"Richard Teller" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>Help says that using .Value on a Range of cells gives back an array of
>values.
>I can not seem to get my head around assigning that array to a variable so
>I
>can access the values
>Can anyone give me the syntax for that.
>
>Something like....
>MyArray := FThisWorksheet.Range['A1', 'C3'].Value; ????
>
>Many thanks
>Richard Teller
>
>