Class to read / write Excel

[devtuxtla]

Hello FiveWinners

What is the class to read and / or write Excel, without having Excel installed on my system?

Thank you...

regards

[karinha]

Mira se ayuda:

Code: Select all | Expand

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
 



[karinha]

http://forums.fivetechsupport.com/viewtopic.php?f=6&t=17030&p=122340&hilit=clase+TExcel#p122340

[Gale FORd]

I think both responses need Excel installed.
He asked how to update "without" Excel installed.
You can use ADO to access spreadsheet.
Here is an example connection info.
oCon := CreateObject("ADODB.Connection")
oCon:open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+cFileName+";Extended Properties=Excel 8.0" )

[devtuxtla]

Gale ... you're right.

I will investigate how read or write a table Excel with ADO.

If you have a more complete example I would greatly appreciate it

regards

[anserkk]

What is the class to read and / or write Excel, without having Excel installed on my system?

Hi,

Option 1
There is a Class named TExcel. This class use an Excel OCX. So I believe that this can be used in PC's without Excel installed. Search this Forum

Option 2
A class named TFileXls, this does not require Excel to be installed on your PC. Search this Forum

Option 3
XlsXml.Prg
https://github.com/harbour/core/blob/ma ... xlsxml.prg
This will help you to create XML format Excel file. Does not require Excel to be installed on your PC

Option 4
http://www.libxl.com
This company sells a DLL which can be used in Harbour/xHarbour. Does not require Excel to be installed on your PC

Regards

Anser

[Gale FORd]

i use texcel but it needs Excel.

[lucasdebeltran]

Hello,

See also methods in xbrowse and adofuncs provided by Mr. Nages.

[Gale FORd]

Here is a vbscript example you can look at.

Code: Select all | Expand

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:B65535]"
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

[gabo]

La mejor opcion sin costo

Option 3
XlsXml.Prg
https://github.com/harbour/core/blob/ma%20...%20xlsxml.prg
This will help you to create XML format Excel file. Does not require Excel to be installed on your PC