FiveTech Support Forums

FUNCTION ReadFromExcel( cFile, xColumnas, nHojaExcel, nFilasVacias, lConvertNulls )

LOCAL oExcel, oPage
LOCAL aDatos := {}, aLinea := {}, nLine := 0, nCol := 0, lVacio := .F., nContador:=0
LOcal nFilas:=0, nColumnas:=0

DEFAULT xColumnas TO 256
DEFAULT nHojaExcel TO 1
DEFAULT nFilasVacias TO 20
DEFAULT lConvertNulls TO .T.


IF !File( cFile )
MsgStop( "No existe el documento Excel " + cFile)
RETURN {}
ENDIF

TRY
oExcel := GetActiveObject( "Excel.Application" )
CATCH
TRY
oExcel := CreateObject( "Excel.Application" )
CATCH
MsgInfo( "Excel no disponible. [" + Ole2TxtError() + "]" )
RETURN .F.
END
END

oExcel:WorkBooks:Open( cFile )
oPage := oExcel:WorkSheets( nHojaExcel )

If ValType(xColumnas)="A"
nFilas:=xColumnas[1]
nColumnas:=xColumnas[2]
Else
nColumnas:=xColumnas
Endif

MsgWait( "Leyendo datos de Hoja Excel",,"Excel" )

FOR nLine := 1 TO 65536
lVacio := .T.
aLinea := {}
FOR nCol := 1 TO nColumnas
AAdd( aLinea, IF(lConvertNulls .And. oPage:Cells( nLine, nCol ):Value=Nil,'',oPage:Cells( nLine, nCol ):Value ) )
IF lVacio .AND. !Vacio( aLinea[ Len( aLinea ) ] )
lVacio := .F.
nContador := nFilasVacias
ENDIF
NEXT

//Limite de filas si se ha requerido
If nFilas<>0 .And. nLine>nFilas
Exit
Endif

IF lVacio
nContador--
IF nContador = 0
EXIT
ENDIF
ELSE

AAdd( aDatos, aLinea )
ENDIF
NEXT

oExcel:Quit()

RETURN aDatos

Const adOpenStatic = 3
Const adLockOptimistic = 3

filename = "Test.xls"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & _
        ";Extended Properties=Excel 8.0"

query = "Select * from [Sheet1$A1]"
Set rs = CreateObject("ADODB.Recordset")
rs.Open query, cn, adOpenStatic, adLockOptimistic

rs.AddNew
rs("First") = "George"
rs("Last") = "Washington"
rs.Update

rs.MoveFirst
Do Until rs.EOF
  WScript.Echo rs.Fields("First") & " " & rs.Fields("Last")
  rs.MoveNext
Loop