Page 1 of 1
Class to read / write Excel
Posted: Wed Sep 28, 2016 5:30 pm
by devtuxtla
Hello FiveWinners
What is the class to read and / or write Excel, without having Excel installed on my system?
Thank you...
regards
Re: Class to read / write Excel
Posted: Wed Sep 28, 2016 7:02 pm
by karinha
Mira se ayuda:
Code: Select all
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
Re: Class to read / write Excel
Posted: Wed Sep 28, 2016 7:13 pm
by karinha
Re: Class to read / write Excel
Posted: Wed Sep 28, 2016 8:37 pm
by 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" )
Re: Class to read / write Excel
Posted: Wed Sep 28, 2016 11:42 pm
by 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
Re: Class to read / write Excel
Posted: Thu Sep 29, 2016 4:11 am
by anserkk
devtuxtla wrote: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
Re: Class to read / write Excel
Posted: Thu Sep 29, 2016 12:20 pm
by Gale FORd
i use texcel but it needs Excel.
Re: Class to read / write Excel
Posted: Thu Sep 29, 2016 12:22 pm
by lucasdebeltran
Hello,
See also methods in xbrowse and adofuncs provided by Mr. Nages.
Re: Class to read / write Excel
Posted: Thu Sep 29, 2016 2:21 pm
by Gale FORd
Here is a vbscript example you can look at.
Code: Select all
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
Re: Class to read / write Excel
Posted: Thu Sep 29, 2016 3:39 pm
by gabo
La mejor opcion sin costo
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