Ejemplo para generar Excel XLSX desde un recordset via ADO

Posts: 1279

Joined: Mon Feb 06, 2006 04:28 PM

Ejemplo para generar Excel XLSX desde un recordset via ADO
Posted: Wed Jun 18, 2025 04:27 PM

Hola a los compañeros del Foro,
He tenido problemas en implementar la XLSXLIB con un Recordset via ADO sobre una consulta de MS Sql Server
Alguien podría subir un ejemplo de generar una XLSX desde un recordset via ADO?
Desde ya muchas gracias.

Aquí os cuento lo que me ha pasado intentando hacerlo.

Este es el script ( y funciona porque puedo hacer un browse del recordset sin problemas )

Code: Select all Collapse

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 )

Y este es el modo en que pretendo presentar la excel:

Code: Select all Collapse

 #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

Obtengo el siguiente error:

Code: Select all Collapse

 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 )

Cualquier ayuda será apreciada.

Saludos/Regards,

José Murugosa

"Los errores en programación, siempre están entre la silla, el teclado y la IA!!"

Otto

Posts: 6983

Joined: Fri Oct 07, 2005 07:07 PM

Re: Ejemplo para generar Excel XLSX desde un recordset via ADO
Posted: Thu Jun 19, 2025 04:12 PM

Hello,
When dealing with large SELECT statements, I found it very useful to split the query into logical parts instead of writing one long TEXT INTO cSql ... ENDTEXT block.

A complex SQL statement like this can — and in many cases should — be built step by step.
Not just for better readability, but also for easier debugging, maintenance, and reuse.
Especially in the Xbase/Harbour environment with TEXT INTO ... ENDTEXT blocks, this works very well.

Code: Select all Collapse

TEXT INTO cSqlBase
   SELECT a.fecha, t.productor AS 'NroProd', ...
   FROM C10304 a WITH (NOLOCK)
   INNER JOIN ...
   ...
ENDTEXT

TEXT INTO cSqlCalc
   , ROUND(NULLIF(t.kilosenpie, 0) / NULLIF(t.cabezas, 0), 2) AS 'KgEnPie'
   , ROUND((aa.kilos + bb.kilos), 2) AS 'KgEn3ra'
   ...
ENDTEXT

TEXT INTO cSqlApply
OUTER APPLY (
   SELECT MIN(fc.cotizaciondolar) AS cotizaciondolar, ...
   FROM C10210 fc ...
) fh
ENDTEXT

cSql := cSqlBase + CRLF + cSqlCalc + CRLF + cSqlApply + CRLF + ;
        "WHERE a.fecha = " + wfecha + CRLF + ;
        "ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo"

This makes the SQL much more readable, and far easier to debug or maintain — especially if someone else needs to work with it later on.

Regarding the Excel export error (BASE/1081 Argument error: +)
It seems there's a type mismatch when building the row expression — probably a string being added to a number.
This line could be the culprit:

Code: Select all Collapse

bRow := "{||{" + cFieldsList + "}}"  // ← possible string + number issue

A safer version would be:

Code: Select all Collapse

aStruct := ArrTranspose( FWAdoStruct( oRs2 ) )
cFieldsList := '"' + FW_ArrayAsList( aStruct[1], '","' ) + '"'
bRow := "{||{" + cFieldsList + "}}"

This forces each field name to be treated as a string and avoids accidental concatenation like "Productor" + 4.

Hope this helps — happy to share more if needed.

LG
Otto

FiveTech Support Forums

Continue the discussion