One approach is to read each cell value in the Range with oSheet:Cells( nRow, nCol ):Value, iterating for each row and each column in the range. This approach is felt to be slower, but has the merit of getting the values in their correct data types. I mean numbers as numbers, dates as dates, etc.
Other approach is to copy the contents of the Range to Clipboard and then extract the clipboard contents in to our program. We get a text buffer which is TAB and CRLF delimited. We see in our forums many samples based on this approach. This is felt to be much faster than the former method. Downside of this approach is all the values are of character type and we need to convert them to their native datatypes in our program ourselves, which at times may result in some bugs and consume atleast a part of the time we saved by the copy and paste method. XBrowse does a decent job in coversion of the pasted text to the native datatypes.
I have never seen any one mentioning another approach which gives the best of both the worlds. Good speed and also native datatypes.
Here it is:
For this sample, I chose a very small range of data, but you may try with very large data too.
Screenshot of Excel Sheet:
Here is the code to read the contents of the range "B3:D6" into our (x)Harbour array.
- Code: Select all Expand view
- #include "fivewin.ch"
function Main()
local oRange, aData
oRange := GetExcelRange( ExePath() + "xl2array", "ArrayTest", "B3:D6" )
aData := ArrTranspose( oRange:Value )
oRange:WorkSheet:Parent:Close()
XBrowse( aData )
return nil
function ExePath()
return cFilePath( GetModuleFileName() )
Notes: GetExcelArray( cFileXLS, cSheet, acRange ) is an FWH function from ver 10.12.
ArrTranspose( aArray ) --> aTransposedArray is function available in FWH
Just two lines of code to import, which I could have written in just a single line too.
Screenshot of Array :
