Reading data from an excel-file

[driessen]

Hello,

Does anyone have an example of how to read data from an excel-file?

Thanks a lot in advance.

[dagiayunus]

Dear Mr. driessen

Code: Select all

 
function main()
  local cValue
   myfile:=HB_CURDRIVE()+":\"+CURDIR()+"\"+"FileName.xls"
   oExcel := CreateObject( "Excel.Application")
   oWorkBook:=oExcel:WorkBooks:Open( MYFILE )
   xl_Sht:=oWorkBook:WorkSheets(1):Name
   oSheet :=oExcel:Sheets(xl_sht)
   nRows := oSheet:UsedRange:Rows:Count()
   nCols := oSheet:UsedRange:Columns:Count()
   //oExcel:Visible:=.T.  
   oExcel:Application:DisplayAlerts:=.f.
   col:=1
   for r=1 to 1
       for c=1 to nCols
           cValue:=oSheet:Cells(R,c):Value
    next c
   next r   
   oExcel:Quit()   
   release oExcel  
   release oSheet
Return nil 

Regards

[driessen]

Mr. Dagiaynus,

Thank you very much for your help.

I'll try it out.

[nageswaragunupudi]

We suugest:

oRange := GetExcelRange( [ cExcelFileName ], [ cSheetName ], [ acRange ], @lOpened )
aData := ArrTranspose( oRange:Value )

Code: Select all

local oRange, lOpened := .f.
local aData

oRange  := GetExcelRange( [ cExcelFileName ], [ cSheetName ], [ acRange ], @lOpened )
if oRange == nil
   ? "Failure"
else
   aData := ArrTranspose( oRange:Value )
   if lOpened
      oRange:WorkSheet:Parent:Close()
   endif
   xbrowser aData
endif
 

Function GetExcelRange(...) is provided by FWH.

3rd Optional Parameter: acRange: This can be specified in the format "A2:F6" or { nTop, nLeft, nBottom, nRight } or name of a Range (eg. "Sales") or can be nil/omitted. If nil or ommitted, the function accesses the UsedRange.

2nd Optional Parameter: Sheet Name: If provided, attempts to access the range from the specified page. If nil/omitted then uses the ActiveSheet.

1st Optional Parameter:
(a) If specified, this should be excel file name with fullpath. Extension is not necessary. If extension is provided, it must be .xls or .xlsx and if omitted the function opens the available file.
(b) Before opening, the function checks if the user has already opened the excel file (either independantly or through another program). If the file is already opened, the function does not open the file again to avoid problems in opening the same file second time, but uses the file that is already opened.
If the function itself opened the file, the 4th parameter lOpened it set to .T., indicating that the Programmer calling the function should close the file. If lOpened is set to .F. it means the file is already opened by the usr for some other purpose and the programmer should NOT close the file.
(c) If this parameter is omitted, i.e., if no excel file name is specified, the function checks to see if any excel file is opened and uses the active book.

On success the function returns the Excel Range object. Return value of NIL indicates failure.

GetExcelRange() returns UsedRange from the ActiveSheet of the ActiveWorkBook if the user has already opened any.
GetExcelRange( cExcelFile ) returns UsedRange of ActiveSheet of <cExcelFile>
GetExcelRange( cExcelFile, cSheetName ) returns UsedRange of cSheetName of cExcelFile
GetExcelRange( cExcelFile, cSheetName, acRange ) returns Specified range of specified sheet of cExcelFile.
GetExcelRange( cExcelFile, cSheetName, acRange, @lOpened ) returns as above and also indicates if the file is opened by this function now or was already opened. If lOpened is .T., the WorkBook has to be closed by the programmer.

Reading Excel Range values into an Array:

aData := ArrTranspose( oRange:Value ) is the FASTEST way.
Reading values of cell by cell in a loop is the SLOWEST way.

Special Case:
If we want only specific columns of the total range, but not all the columns.
For example, the range is "A1:F1000" and we require only columns A,C,E,F.

aData := oRange:Value. // aData has 6 rows A,B,C,D,E,F
ADel( aData, 2, .t. ) // aData now has 5 rows A,C,D,E,F
ADel( aData, 3, .t. ) // aData now has 4 rows A,C,E,F

aData := ArrTranspose( aData ) // Data with 4 columns A,C,E,F and 1000 rows.

[joseluisysturiz]

Nages, EXCELENTE...algun archivo de cabecera para el uso de GetExcelRange o es asi de sencillo como lo muestras.? saludos...

[nageswaragunupudi]

Nages, EXCELENTE...algun archivo de cabecera para el uso de GetExcelRange o es asi de sencillo como lo muestras.? saludos...

I do not understand clearly.
It is simple as suggested in the sample above. Does not require any header file.
I am using it in my own applications.

[nageswaragunupudi]

Some may be interested in the functions FW_DbfToExcel() and FW_ExcelToDbf() for exchange of data between Excel and DBF.

FW_DbfToExcel( [cFieldList], [bFor], [bWhile], [nNext], [nRec], [lRest] )

Exports data from DBF to Excel. Optional parameters bFor, bWhile, nNext, nRec, lRest have the same purpose as in the standard DBEVAL() function.

If cFieldList ( eg: "FIRST,CITY,SALARY" ) is specified only the specified field are exported.

FW_ExcelToDbf( oRange, cFieldList, lHasHeaders, bProgress )

oRange: Excel Range Object. Can be obtained from GetExcelRange() function.

The imported data is appened to DBF at the end of the file.

By default, the columns of the excel sheet are copied into the fields of the DBF in the same order as present in the excel sheet. If the Range has more columns they are ignored and if less than the number of fields only the first fields are filled.

Optionally we can specify a field list:
FW_ExcelToDbf( oRange, "FIRST,CITY,SALARY" ) copies first 3 columns of the excel range into fields FIRST, CITY and SALARY of the DBF.

In case the first row of the ExcelRange contains Headers (field names)
FW_ExcelToDBF( oRange, nil, .t. )
Then the function treats the first row of the Range as FieldNames and copies data from 2nd row onwards in to the DBF to the field names contained in the first row

[driessen]

Mr. Rao,

Thanks a lot for sharing all the information.