Xailer & OZ Software

El entorno de desarrollo definitivo en Xbase
https://forum.xailer.com/

xlsxml

https://forum.xailer.com/viewtopic.php?t=12671

Página 1 de 1

xlsxml

Publicado: Mar Feb 24, 2015 6:40 am
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

xlsxml

Publicado: Mar Feb 24, 2015 8:45 pm
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

xlsxml

Publicado: Mié Feb 25, 2015 7:41 pm
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/

xlsxml

Publicado: Jue Feb 26, 2015 3:54 am
por gabo1
Fausto
Gracias! por el excelente trabajo
Saludos

xlsxml

Publicado: Jue Feb 26, 2015 1:13 pm
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
Todos los horarios son UTC+02:00
Página 1 de 1

Desarrollado por phpBB® Forum Software © phpBB Limited

Traducción al español por phpBB España