Se podrÃa subir algún ejemplo de usar xlsxlib con un recordset con ADO en un SQL Server?
He tratado de usar la clase, y no he podido tener éxito.
Gracias.
text into cSql
SELECT
a.fecha, t.productor AS 'NroProd', prd.nombre AS 'Productor', t.consignatario AS 'NroCon',
con.nombre AS 'Consignatario', t.dicoseorigen AS 'DicoseOrigen', dpt.nombre AS 'Departamento',
a.tropaano AS 'Anio', a.tropanumero AS 'NTropa', ta.descripcion AS 'Categoria',
a.correlativo AS 'CorrFaena', ROUND( NULLIF( t.kilosenpie, 0 ) / NULLIF( t.cabezas, 0 ), 2 ) AS 'KgEnPie',
ROUND( aa.kilos + bb.kilos, 2) AS 'KgEn3ra', ROUND( a.kilos + b.kilos, 2 ) AS 'KgEn4ta',
ROUND( ( aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ), 2) AS 'KDressing',
ROUND( ( (aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ) ) * 100 / NULLIF( (a.kilos + b.kilos), 0 ), 2 ) AS 'PDressing',
ROUND(
IIF(
( (aa.kilos + bb.kilos) - (a.kilos + b.kilos) ) < (8.5 / 100 * (a.kilos + b.kilos)),
0,
( (aa.kilos + bb.kilos) - (a.kilos + b.kilos) ) - ( 8.5 / 100 * (a.kilos + b.kilos ) )
)
, 2 ) AS 'KgMach',
ROUND(
IIF(
( ( aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ) ) * 100 / ( a.kilos + b.kilos ) < 8.5,
0,
( ( (aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ) ) * 100 / ( a.kilos + b.kilos ) ) - 8.5
)
,2 ) AS 'PMach',
ROUND(
IIF(
( ( aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ) ) < ( 8.5 / 100 * ( a.kilos + b.kilos ) ),
0,
( ( ( aa.kilos + bb.kilos ) - ( a.kilos + b.kilos ) ) - ( 8.5 / 100 * ( a.kilos + b.kilos ) ) ) *
IIF(
t.moneda = 22,
ROUND( NULLIF( fh.subtotal, 0 ) / NULLIF( td.kilosen2, 0 ), 2 ),
ROUND( NULLIF( fh.subtotal, 0 ) / NULLIF( td.kilosen2, 0 ) / NULLIF( fh.cotizaciondolar, 0 ), 2 )
)
)
, 2 ) AS 'U$SMach',
ROUND( ( NULLIF(a.kilos + b.kilos, 0) / NULLIF( t.kilosenpie, 0 ) / NULLIF( t.cabezas, 0 ) ) * 100, 2 ) AS 'Rend',
b.kosher AS Ritual,
IIF(a.hilton = 0x54, 'SI', 'NO') AS hilton,
IIF(a.tipodehacienda = 0, 'NO', 'SI') AS Organico,
IIF(
t.moneda = 22,
ROUND( NULLIF( fh.subtotal, 0 ) / NULLIF( td.kilosen2, 0 ), 2 ),
ROUND( NULLIF( fh.subtotal, 0 ) / NULLIF( td.kilosen2, 0 ) / NULLIF( fh.cotizaciondolar, 0 ), 2 )
) AS 'U$S2a'
FROM C10304 a WITH (NOLOCK)
INNER JOIN C10304 b WITH (NOLOCK)
ON a.establecimiento = b.establecimiento AND a.fecha = b.fecha AND a.correlativo = b.correlativo
AND b.estado < 5 AND b.tipoderegistro = 4 AND b.mediares = 2
INNER JOIN C10304 aa WITH (NOLOCK)
ON a.establecimiento = aa.establecimiento AND a.fecha = aa.fecha AND a.correlativo = aa.correlativo
AND aa.estado < 5 AND aa.tipoderegistro = 3 AND aa.mediares = 1
INNER JOIN C10304 bb WITH (NOLOCK)
ON a.establecimiento = bb.establecimiento AND a.fecha = bb.fecha AND a.correlativo = bb.correlativo
AND bb.estado < 5 AND bb.tipoderegistro = 3 AND bb.mediares = 2
INNER JOIN C10203 t WITH (NOLOCK)
ON t.empresa = a.empresa AND t.especie = a.tropaespecie
AND t.ano = a.tropaano AND t.numero = a.tropanumero
INNER JOIN C10204 td WITH (NOLOCK)
ON td.empresa = a.empresa AND td.especie = a.tropaespecie
AND td.ano = a.tropaano AND td.numero = a.tropanumero AND td.ordendepesada = a.tropaorden
LEFT JOIN P0025 prd WITH (NOLOCK)
ON prd.establecimiento = a.establecimiento AND prd.codigo = t.productor
LEFT JOIN P0025 con WITH (NOLOCK)
ON con.establecimiento = a.establecimiento AND con.codigo = t.consignatario
LEFT JOIN P0015 dpt WITH (NOLOCK)
ON dpt.codigo = prd.departamento
LEFT JOIN P0021 ta WITH (NOLOCK)
ON ta.codigo = a.tipodeanimal
LEFT JOIN P0025 dio WITH (NOLOCK)
ON dio.establecimiento = a.establecimiento AND dio.dicose = t.dicoseorigen
OUTER APPLY (
SELECT
MIN(fc.cotizaciondolar) AS cotizaciondolar,
SUM(fd.subtotal) AS subtotal
FROM C10210 fc WITH (NOLOCK)
INNER JOIN C10211 fd WITH (NOLOCK)
ON fd.empresa = fc.empresa AND fd.pof = fc.pof
AND fd.numero = fc.numero AND fd.tropalinea = td.ordendepesada
WHERE fc.empresa = a.empresa AND fc.estado < 9 AND fc.pof = 'P'
AND fc.tropaespecie = a.tropaespecie AND fc.tropaano = a.tropaano
AND fc.tropanumero = a.tropanumero
) fh
WHERE
a.empresa = 1 AND a.estado < 5
AND a.tipoderegistro = 4 AND a.mediares = 1
AND a.fecha = wFecha
ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo;
ENDTEXT
cSql := StrTran( cSql, 'wFecha', wfecha )
oRs2 := oCn2:Execute( cSQL ) #pragma BEGINDUMP
int _streams;
#pragma ENDDUMP
UseXlsxLib( .t. )
if !XlsxLibLinked()
MsgInfo( "No es posible generar la excel, requiere XLSXLIB" )
RETURN nil
endif
IF !oRs2:Eof()
aStruct := FWAdoStruct( oRs2 )
ELSE
MsgAlert( "No se pudo conectar al RecordSet" )
Return NIL
ENDIF
cFile := 'Archivo.xlsx'
aStruct := ArrTranspose( aStruct )
cFieldsList := FW_ArrayAsList( aStruct[ 1 ] )
bRow := "{||{" + cFieldsList + "}}"
bRow := &( bRow )
if ( oXlsx := XlsxLibObj( cFile, oRs2 ) ) != nil
WITH OBJECT oXlsx
nRow := :SetStruct( aStruct,, )
DBEVAL( { || nRow := :SayRow( nRow, Eval( bRow ) ) } )
:SetFooter( aFooter )
:SetBackGround( "c:\fwh\bitmaps\backgrnd\paper2.bmp" )
:HideZero()
:Close()
END
ShellExecute( 0, "Open", cFile )
ELSE
? "No se pudo crear el Archivo XLSX"
ENDIF Error occurred at: 18/06/2025, 10:45:54
Error description: Error BASE/1081 Error de argumento: +
Args:
[ 1] = C Productor
[ 2] = N 4
Stack Calls
===========
Called from: .\source\function\fwxlsxlb.prg => (b)TWORKSHEET_SETSTRUCT( 378 )
Called from: => AEVAL( 0 )
Called from: .\source\function\fwxlsxlb.prg => TWORKSHEET:SETSTRUCT( 399 )
Called from: d:\xbase-proyectos\estadisticas\source\Estadhacienda.prg => INFORMEHACIENDA( 169 )
Called from: d:\xbase-proyectos\estadisticas\source\Estadhacienda.prg => ESTADHACIENDA( 128 )
Called from: d:\xbase-proyectos\estadisticas\source\Estadisticas.prg => (b)CREOMENU( 98 )
Called from: .\source\classes\menu.prg => TMENU:COMMAND( 1552 )
Called from: .\source\classes\window.prg => TWINDOW:COMMAND( 1188 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\window.prg => _FWH( 3729 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\window.prg => TWINDOW:ACTIVATE( 1147 )
Called from: d:\xbase-proyectos\estadisticas\source\Estadisticas.prg => MAIN( 66 )