Board index » delphi » Accessing an array of values from Excel
Richard Teller
Delphi Developer |
Richard Teller
Delphi Developer |
Accessing an array of values from Excel2004-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 |
Mariusz Celmer
Delphi Developer |
2004-12-16 07:04:31 PM
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 QuoteHelp says that using .Value on a Range of cells gives back an array of |
Deborah Pate (TeamB)
Delphi Developer |
2004-12-16 07:19:54 PM
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> .. 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 |
Richard Teller
Delphi Developer |
2004-12-17 05:50:13 AM
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: |
Richard Teller
Delphi Developer |
2004-12-17 05:53:56 AM
Re:Accessing an array of values from Excel
Many thanks, Mariusz, thats great.
"Mariusz Celmer" <XXXX@XXXXX.COM>writes QuoteHi, |