In order for this site to work correctly we need to store a small file (called a cookie) on your computer. Most every site in the world does this, however since the 25th of May 2011, by law we have to get your permission first. Please abandon the forum if you disagree.

Para que este foro funcione correctamente es necesario guardar un pequeño fichero (llamado cookie) en su ordenador. La mayoría de los sitios de Internet lo hacen, no obstante desde el 25 de Marzo de 2011 y por ley, necesitamos de su permiso con antelación. Abandone este foro si no está conforme.

xlsxml

Foro público de Xailer en español
Responder
Avatar de Usuario
gabo1
Mensajes: 127
Registrado: Lun Oct 13, 2014 9:42 am

xlsxml

Mensaje por gabo1 »

Hola a todos
Les comparto un codigo para generar archivos xml para excel
usa la lib xlsxml de <extras> de harbour. Bastante rapido formato de celdas de manera facil. columnas combinadas sin complicaciones. bueno me ha parecido una lib bastante interesante. SAludos

//------------------------------------------------------------------------------
STATIC FUNCTION CrearHojaExcel( aProducs, aIgrediens, dFechaUno, dFechaDos )
//------------------------------------------------------------------------------
LOCAL oXML, oSheet, nDias, aDias, nColMerge, lMesas, nLineas, cEmpresa, aItems
LOCAL cFile, lPrimero, hDatos, nLenArray, cProducto, nn

WaitOn("generando hoja de excel" )

cFile:= GetFolderPersonal() + "ReporteVentasDel.xml"

aDias:= { "LUNES", "MARTES", "MIERCOLES", "JUEVES", "VIERNES", "SABADO", "DOMINGO" }

oXML:= ExcelWriterXML():New(cFile)
oXML:setOverwriteFile(.t.)
oXML:showErrorSheet( .T. )

oSheet:= oXML:addSheet('Ventas por Productos')
nLineas:= 0
WITH OBJECT oXML:addStyle( 'numberCan' )
:alignHorizontal( "Right" )
:alignVertical( "Center" )
:setNumberFormat( "#,##0.0" )
:setFontName('Calibri')
:setFontSize(8)
END WITH
WITH OBJECT oXML:addStyle( 'numberRight' )
:alignHorizontal( "Right" )
:alignVertical( "Center" )
:setNumberFormat( "#,##0.00" )
:setFontName('Calibri')
:setFontSize(8)
END WITH
WITH OBJECT oXML:addStyle('dias')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#AEAAAA')
END WITH
WITH OBJECT oXML:addStyle('sucursal')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(12)
:setFontBold()
:bgColor('#E7E6E6')
END WITH
WITH OBJECT oXML:addStyle('mesas')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#E7E6E6')
END WITH
WITH OBJECT oXML:addStyle('pedidos')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#D6DCE4')
END WITH
oSheet:writeString(++nLineas,1,"REPORTE DE VENTAS")
oSheet:writeString(++nLineas,1,"POR FECHA Y SUCURSALES DEL:")
oSheet:writeString(++nLineas,1,"SUCURSAL DE PRUEBA")
oSheet:writeString(++nLineas,1,DToC( Date() ) )

oSheet:columnWidth( 1, 150 )

FOR nDias:= 1 TO 28
oSheet:columnWidth( 1+nDias, 42 )
NEXT
++nLineas
oSheet:writeString(++nLineas,1,"PRODUCTOS", 'pedidos' )
nColMerge:= 2
FOR nDias:= 1 TO 7
oSheet:writeString( nLineas, nColMerge, aDias[nDias], 'dias' )
nColMerge+=4
NEXT

oSheet:cellMerge( nLineas, 1, 0, 1 )
oSheet:cellMerge( nLineas, 2, 3, 0 )
oSheet:cellMerge( nLineas, 6, 3, 0 )
oSheet:cellMerge( nLineas,10, 3, 0 )
oSheet:cellMerge( nLineas,14, 3, 0 )
oSheet:cellMerge( nLineas,18, 3, 0 )
oSheet:cellMerge( nLineas,22, 3, 0 )
oSheet:cellMerge( nLineas,26, 3, 0 )

lMesas:= .T.
++nLineas
nColMerge:= 2
FOR nDias:= 1 TO 14
IF lMesas
oSheet:writeString( nLineas,nColMerge,"MESAS",'mesas')
ELSE
oSheet:writeString( nLineas,nColMerge,"DOMICILIO",'pedidos')
ENDIF
lMesas:= !lMesas
nColMerge+=2
NEXT

oSheet:cellMerge( nLineas, 2, 1, 0 )
oSheet:cellMerge( nLineas, 4, 1, 0 )
oSheet:cellMerge( nLineas, 6, 1, 0 )
oSheet:cellMerge( nLineas, 8, 1, 0 )
oSheet:cellMerge( nLineas,10, 1, 0 )
oSheet:cellMerge( nLineas,12, 1, 0 )
oSheet:cellMerge( nLineas,14, 1, 0 )
oSheet:cellMerge( nLineas,16, 1, 0 )
oSheet:cellMerge( nLineas,18, 1, 0 )
oSheet:cellMerge( nLineas,20, 1, 0 )
oSheet:cellMerge( nLineas,22, 1, 0 )
oSheet:cellMerge( nLineas,24, 1, 0 )
oSheet:cellMerge( nLineas,26, 1, 0 )
oSheet:cellMerge( nLineas,28, 1, 0 )
++nLineas
lMesas:= .T.
nColMerge:= 2
FOR nDias:= 1 TO 28
IF lMesas
oSheet:writeString( nLineas,nColMerge,"CANTIDAD",'mesas')
ELSE
oSheet:writeString( nLineas,nColMerge,"TOTAL",'mesas')
ENDIF
lMesas:= !lMesas
++nColMerge
NEXT

cEmpresa := "@"
cProducto:= "@"
lPrimero:= .T.
FOR EACH aItems IN aProducs
IF aItems[1] != cEmpresa
++nLineas
IF !lPrimero
++nLineas
ENDIF
oSheet:writeString( nLineas,1, aItems[1],'sucursal' )
cEmpresa:= aItems[1]
lPrimero:= .F.
ENDIF
IF aItems[3] != cProducto
++nLineas
oSheet:writeString( nLineas,1, aItems[4] )
cProducto:= aItems[3]
ENDIF
DO CASE
CASE DoW( aItems[2] ) == 2 // "Lunes" // 2
oSheet:writeNumber( nLineas,2, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,3, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,4, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,5, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 3 // "Martes" // 3
oSheet:writeNumber( nLineas,6, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,7, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,8, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,9, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 4 // "Miércoles" // 4
oSheet:writeNumber( nLineas,10, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,11, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,12, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,13, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 5 // "Jueves" // 5
oSheet:writeNumber( nLineas,14, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,15, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,16, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,17, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 6 // "Viernes" // 6
oSheet:writeNumber( nLineas,18, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,19, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,20, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,21, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 7 // "Sábado" // 7
oSheet:writeNumber( nLineas,22, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,23, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,24, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,25, aItems[8], 'numberRight' )

CASE DoW( aItems[2] ) == 1 // "Domingo" // 1
oSheet:writeNumber( nLineas,26, aItems[5], 'numberCan' )
oSheet:writeNumber( nLineas,27, aItems[6], 'numberRight' )
oSheet:writeNumber( nLineas,28, aItems[7], 'numberCan' )
oSheet:writeNumber( nLineas,29, aItems[8], 'numberRight' )
END CASE
NEXT

oXML:writeData( cFile )
WaitOff()

wapi_ShellExecute( 0, 'open', cFile, , 0, 0 )

RETURN NIL
Cassiano de Oliveira
Mensajes: 475
Registrado: Mar Jul 24, 2012 10:21 pm

xlsxml

Mensaje por Cassiano de Oliveira »

Gracias
"Gabriel Ornelas" wrote in message news:54ec0ebe$[email=1@svctag-j7w3v3j....]1@svctag-j7w3v3j....[/email]
Hola a todos
Les comparto un codigo para generar archivos xml para excel
usa la lib xlsxml de <extras> de harbour. Bastante rapido
formato de celdas de manera facil. columnas combinadas sin
complicaciones. bueno me ha parecido una lib bastante
interesante. SAludos
//---------------------------------------------------------- --------------------
STATIC FUNCTION CrearHojaExcel( aProducs, aIgrediens,
dFechaUno, dFechaDos )
//---------------------------------------------------------- --------------------
LOCAL oXML, oSheet, nDias, aDias, nColMerge, lMesas,
nLineas, cEmpresa, aItems
LOCAL cFile, lPrimero, hDatos, nLenArray, cProducto, nn
WaitOn("generando hoja de excel" )
cFile:= GetFolderPersonal() + "ReporteVentasDel.xml"
aDias:= { "LUNES", "MARTES", "MIERCOLES", "JUEVES",
"VIERNES", "SABADO", "DOMINGO" }
oXML:= ExcelWriterXML():New(cFile)
oXML:setOverwriteFile(.t.)
oXML:showErrorSheet( .T. )
oSheet:= oXML:addSheet('Ventas por Productos')
nLineas:= 0
WITH OBJECT oXML:addStyle( 'numberCan' )
:alignHorizontal( "Right" )
:alignVertical( "Center" )
:setNumberFormat( "#,##0.0" )
:setFontName('Calibri')
:setFontSize(8)
END WITH
WITH OBJECT oXML:addStyle( 'numberRight' )
:alignHorizontal( "Right" )
:alignVertical( "Center" )
:setNumberFormat( "#,##0.00" )
:setFontName('Calibri')
:setFontSize(8)
END WITH
WITH OBJECT oXML:addStyle('dias')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#AEAAAA')
END WITH
WITH OBJECT oXML:addStyle('sucursal')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(12)
:setFontBold()
:bgColor('#E7E6E6')
END WITH
WITH OBJECT oXML:addStyle('mesas')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#E7E6E6')
END WITH
WITH OBJECT oXML:addStyle('pedidos')
:alignHorizontal('Center')
:alignVertical('Center')
:setFontName('Calibri')
:setFontSize(8)
:bgColor('#D6DCE4')
END WITH
oSheet:writeString(++nLineas,1,"REPORTE DE VENTAS")
oSheet:writeString(++nLineas,1,"POR FECHA Y SUCURSALES
DEL:")
oSheet:writeString(++nLineas,1,"SUCURSAL DE PRUEBA")
oSheet:writeString(++nLineas,1,DToC( Date() ) )
oSheet:columnWidth( 1, 150 )
FOR nDias:= 1 TO 28
oSheet:columnWidth( 1+nDias, 42 )
NEXT
++nLineas
oSheet:writeString(++nLineas,1,"PRODUCTOS", 'pedidos' )
nColMerge:= 2
FOR nDias:= 1 TO 7
oSheet:writeString( nLineas, nColMerge, aDias[nDias],
'dias' )
nColMerge+=4
NEXT
oSheet:cellMerge( nLineas, 1, 0, 1 )
oSheet:cellMerge( nLineas, 2, 3, 0 )
oSheet:cellMerge( nLineas, 6, 3, 0 )
oSheet:cellMerge( nLineas,10, 3, 0 )
oSheet:cellMerge( nLineas,14, 3, 0 )
oSheet:cellMerge( nLineas,18, 3, 0 )
oSheet:cellMerge( nLineas,22, 3, 0 )
oSheet:cellMerge( nLineas,26, 3, 0 )
lMesas:= .T.
++nLineas
nColMerge:= 2
FOR nDias:= 1 TO 14
IF lMesas
oSheet:writeString(
nLineas,nColMerge,"MESAS",'mesas')
ELSE
oSheet:writeString(
nLineas,nColMerge,"DOMICILIO",'pedidos')
ENDIF
lMesas:= !lMesas
nColMerge+=2
NEXT
oSheet:cellMerge( nLineas, 2, 1, 0 )
oSheet:cellMerge( nLineas, 4, 1, 0 )
oSheet:cellMerge( nLineas, 6, 1, 0 )
oSheet:cellMerge( nLineas, 8, 1, 0 )
oSheet:cellMerge( nLineas,10, 1, 0 )
oSheet:cellMerge( nLineas,12, 1, 0 )
oSheet:cellMerge( nLineas,14, 1, 0 )
oSheet:cellMerge( nLineas,16, 1, 0 )
oSheet:cellMerge( nLineas,18, 1, 0 )
oSheet:cellMerge( nLineas,20, 1, 0 )
oSheet:cellMerge( nLineas,22, 1, 0 )
oSheet:cellMerge( nLineas,24, 1, 0 )
oSheet:cellMerge( nLineas,26, 1, 0 )
oSheet:cellMerge( nLineas,28, 1, 0 )
++nLineas
lMesas:= .T.
nColMerge:= 2
FOR nDias:= 1 TO 28
IF lMesas
oSheet:writeString(
nLineas,nColMerge,"CANTIDAD",'mesas')
ELSE
oSheet:writeString(
nLineas,nColMerge,"TOTAL",'mesas')
ENDIF
lMesas:= !lMesas
++nColMerge
NEXT
cEmpresa := "@"
cProducto:= "@"
lPrimero:= .T.
FOR EACH aItems IN aProducs
IF aItems[1] != cEmpresa
++nLineas
IF !lPrimero
++nLineas
ENDIF
oSheet:writeString( nLineas,1, aItems[1],'sucursal'
)
cEmpresa:= aItems[1]
lPrimero:= .F.
ENDIF
IF aItems[3] != cProducto
++nLineas
oSheet:writeString( nLineas,1, aItems[4] )
cProducto:= aItems[3]
ENDIF
DO CASE
CASE DoW( aItems[2] ) == 2 // "Lunes" // 2
oSheet:writeNumber( nLineas,2, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,3, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,4, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,5, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 3 // "Martes" // 3
oSheet:writeNumber( nLineas,6, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,7, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,8, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,9, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 4 // "Miércoles" // 4
oSheet:writeNumber( nLineas,10, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,11, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,12, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,13, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 5 // "Jueves" // 5
oSheet:writeNumber( nLineas,14, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,15, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,16, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,17, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 6 // "Viernes" // 6
oSheet:writeNumber( nLineas,18, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,19, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,20, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,21, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 7 // "Sábado" // 7
oSheet:writeNumber( nLineas,22, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,23, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,24, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,25, aItems[8],
'numberRight' )
CASE DoW( aItems[2] ) == 1 // "Domingo" // 1
oSheet:writeNumber( nLineas,26, aItems[5],
'numberCan' )
oSheet:writeNumber( nLineas,27, aItems[6],
'numberRight' )
oSheet:writeNumber( nLineas,28, aItems[7],
'numberCan' )
oSheet:writeNumber( nLineas,29, aItems[8],
'numberRight' )
END CASE
NEXT
oXML:writeData( cFile )
WaitOff()
wapi_ShellExecute( 0, 'open', cFile, , 0, 0 )
RETURN NIL
---
Este email foi escaneado pelo Avast antiví­rus.
http://www.avast.com
ftwein
Mensajes: 50
Registrado: Lun Oct 22, 2007 5:17 pm

xlsxml

Mensaje por ftwein »

Eu precisava gerar planilhas a partir do linux e encontrei essa classe em php e converti para harbour, uso muito até hoje, mais rápido que OLE no windows.
At.te,
Fausto Di Creddo Trautwein
/*
* Harbour Project source code:
*
* Copyright 2011 Fausto Di Creddo Trautwein, ftwein@yahoo.com.br
* www - http://harbour-project.org
*
* Thanks to Robert F Greer, PHP original version
* http://sourceforge.net/projects/excelwriterxml/
Avatar de Usuario
gabo1
Mensajes: 127
Registrado: Lun Oct 13, 2014 9:42 am

xlsxml

Mensaje por gabo1 »

Fausto
Gracias! por el excelente trabajo
Saludos
Cassiano de Oliveira
Mensajes: 475
Registrado: Mar Jul 24, 2012 10:21 pm

xlsxml

Mensaje por Cassiano de Oliveira »

parabens
"Fausto Di Creddo Trautwein" wrote in message
news:54ee176a$[email=1@svctag-j7w3v3j....]1@svctag-j7w3v3j....[/email]
Eu precisava gerar planilhas a partir do linux e encontrei
essa classe em php e converti para harbour, uso muito até
hoje, mais rápido que OLE no windows.
At.te,
Fausto Di Creddo Trautwein
/*
* Harbour Project source code:
*
* Copyright 2011 Fausto Di Creddo Trautwein,
mailto:ftwein@yahoo.com.br
* www - http://harbour-project.org
*
* Thanks to Robert F Greer, PHP original version
* http://sourceforge.net/projects/excelwriterxml/
---
Este email foi escaneado pelo Avast antiví­rus.
http://www.avast.com
Responder