Leer Excel XLSX grande a un array o DBF

[nageswaragunupudi]

Amigo puedes hacerlo directamente desde Excel, esta función convierte el Excel a un array de tipo hash

Code: Select all | Expand

Function recupera(cRtaXls)
Local hLinea := {=>}
Local aDatos := {}
Local oExcel,oBook,oHoja
Local nTotRowCount := 0
Local nTotColCount := 0
Local nContador := 1

oExcel:= TOleAuto():New("Excel.Application")
oBook := oExcel:Workbooks:Open(cRtaXls)
oHoja := oExcel:Get( "ActiveSheet" )

nTotRowCount:= oHoja:UsedRange:Rows:Count()
nTotColCount:= oHoja:UsedRange:Columns:Count()

//oMeter:nTotal = nTotRowCount
//oMeter:Set( 0 )

FOR Q=1 TO nTotRowCount

    FOR b:=1 TO nTotColCount
        cNmcol := "COL"+cvaltochar(b)   
        hLinea[cNmcol] := oHoja:Cells( Q, b ):Value
    NEXT    
    AADD(aDatos,hLinea)
    hLinea := {=>}

    nContador++ 
    //oMeter:Set( nContador )   

NEXT
oExcel:WorkBooks:Close()
oExcel:Application:Quit()
oExcel:Quit()
oExcel := NIL

xbrowse(aDatos)

Return nil 
 

This can also be written as:

Code: Select all | Expand

oRange := GetExcelRange( cExelFileWithFullPath )
aData := xlRangeValue( oRange )
XBROWSER aData

[Jimmy]

hi,

if you use FWH 64 Bit it is no Problem to use 1.000.000 Row of Excel Sheet with ADO without Array

Code: Select all | Expand

FUNCTION ADOsheet( cPathcFile )
LOCAL objRS, oBrw
LOCAL cSheet, cRange, lHeaders := .T.

   objRS := FW_OpenADOExcelSheet( cPathcFile, cSheet, cRange, lHeaders )
 
   @ nBhight,  2 XBROWSE oBrw SIZE nWidth - 20, nHeight - 90 PIXEL OF oWnd ;
              RECORDSET objRS FASTEDIT ;
              AUTOCOLS ;
              CELL LINES NOBORDER UPDATE ;
              FONT oFontDefault COLOR BFcolor, BGcolor

this CODE work also under 32 Bit but over 500.000 Row it might crash when MEMORY is low of 32 Bit App

---

instead of XBROWSE you can use Record-Set to create a DBF or SQL-Table

[Enrrique Vertiz]

Hi Mr. Rao

Unresolved external _HB_FUN_FW_GetExcelRange
Unresolved external _HB_FUN_xlValue

I use FWH 23.07, Thanks

[karinha]

https://forums.fivetechsupport.com/view ... =3&t=40844

https://github.com/manuelcalerosolis/Ge ... /Comun.prg

Regards, saludos.

[nageswaragunupudi]

Hi Mr. Rao

Unresolved external _HB_FUN_FW_GetExcelRange
Unresolved external _HB_FUN_xlValue

I use FWH 23.07, Thanks

Very sorry, I corrected the mistakes.
Please see my edited posting again.
the functions are:
GetExcelRange .. not FW_GetExcelRange
and
xlRangeValue ... not xlValue

Code: Select all | Expand

oRange := GetExcelRange( cExelFileWithFullPath )
aData := xlRangeValue( oRange )
XBROWSER aData

[Enrrique Vertiz]

GRACIAS Karinha, Rao !!!