Reading data from an excel-file

Post Reply
User avatar
driessen
Posts: 1239
Joined: Mon Oct 10, 2005 11:26 am
Location: Genk, Belgium

Reading data from an excel-file

Post by driessen »

Hello,

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

Thanks a lot in advance.
Regards,

Michel D.
Genk (Belgium)
_____________________________________________________________________________________________
I use : FiveWin for (x)Harbour v. 21.01 - Harbour 3.2.0 (October 2020) - xHarbour Builder (January 2020) - Bcc7
dagiayunus
Posts: 69
Joined: Wed Nov 19, 2014 1:04 pm
Contact:

Re: Reading data from an excel-file

Post by 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
Dagia Yunus.
Rajkot, India

FWH 17.04
User avatar
driessen
Posts: 1239
Joined: Mon Oct 10, 2005 11:26 am
Location: Genk, Belgium

Re: Reading data from an excel-file

Post by driessen »

Mr. Dagiaynus,

Thank you very much for your help.

I'll try it out.
Regards,

Michel D.
Genk (Belgium)
_____________________________________________________________________________________________
I use : FiveWin for (x)Harbour v. 21.01 - Harbour 3.2.0 (October 2020) - xHarbour Builder (January 2020) - Bcc7
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Reading data from an excel-file

Post by 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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
joseluisysturiz
Posts: 2024
Joined: Fri Jan 06, 2006 9:28 pm
Location: Guatire - Caracas - Venezuela
Contact:

Re: Reading data from an excel-file

Post by joseluisysturiz »

Nages, EXCELENTE...algun archivo de cabecera para el uso de GetExcelRange o es asi de sencillo como lo muestras.? saludos... :shock:
Dios no está muerto...

Gracias a mi Dios ante todo!
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Reading data from an excel-file

Post by nageswaragunupudi »

joseluisysturiz wrote:Nages, EXCELENTE...algun archivo de cabecera para el uso de GetExcelRange o es asi de sencillo como lo muestras.? saludos... :shock:
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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: Reading data from an excel-file

Post by 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
Regards

G. N. Rao.
Hyderabad, India
User avatar
driessen
Posts: 1239
Joined: Mon Oct 10, 2005 11:26 am
Location: Genk, Belgium

Re: Reading data from an excel-file

Post by driessen »

Mr. Rao,

Thanks a lot for sharing all the information.
Regards,

Michel D.
Genk (Belgium)
_____________________________________________________________________________________________
I use : FiveWin for (x)Harbour v. 21.01 - Harbour 3.2.0 (October 2020) - xHarbour Builder (January 2020) - Bcc7
Post Reply