Exportar datos desde SQL Server a Excel

[jparada]

Para exportar informes de datos a Excel utilizo la librería libxlsxwriter que comparado con exportar datos con OLE/COM es mucho más rápido, el tema es que la con libxlsxwriter no está disponible la manipulación de las tablas dinámicas de Excel, así que para un proyecto nuevo que estoy implementando en la empresa no me queda otra opción que utilizar OLE para implementar el proyecto...

Como ya sabemos COM es muy lento para la exportación de datos, así que he buscado algunas opciones que ayuden a incrementar la velocidad y según he leído hay otras técnicas, ya sea exportar datos desde ADO RecordSet a Excel o exportar datos desde un Array

He realizado éste ejercicio el cual utiliza la primera técnica (ADO RecordSet)

Y funciona bastante bien, de hecho bastante rápido comparado con COM

Code: Select all

#define fileFormat 51

/*
* main
*/
Function main  
  Local oError  
  Local oExcel
  Local oSheet
  Local oRs
  Local sqlcommand
  Local nSecs
  
  Public connectionString

  conexion()
    
  If ( oExcel := win_oleCreateObject( 'Excel.Application' ) ) == Nil
    ? 'Excel no disponible, ', win_OleErrorText()
    Return Nil
  Endif

  *-- catch any errors
  BEGIN SEQUENCE WITH ErrorBlock( { | oError | Break( oError ) } )
    oExcel:Visible := .F.
    oExcel:DisplayAlerts :=.F.

    *-- open new book
    oExcel:WorkBooks:Add()
  
    *-- set first sheet as current
    oSheet := oExcel:ActiveSheet()

    oRs := win_OleCreateObject( "ADODB.Recordset" )
  
    *-- This query returns approximately 10K records
    sqlcommand2 := 'Select * From Ventas'    
    
    oRs:Open( sqlcommand, connectionString )

    If oRs:Eof()
      ? hb_Utf8ToStr( 'No hay información' )
      oRs:Close()
      //Return
    Endif

    nSecs   := Seconds()
    n       := oSheet:Cells( 1, 1 ):CopyFromRecordSet( rs )
    nSecs   := Seconds() - nSecs
    ? 'Se exportaron ' +  hb_NToS( n )  + ' filas a Excel en ' + hb_NToS( nSecs ) + ' segundos'
     
    *-- block to save file
    BEGIN SEQUENCE WITH ErrorBlock( { | oError | Break( oError ) } )
      *-- if the file already exists and it's not open, it's overwritten without asking
      oSheet:SaveAs( hb_dirSepAdd( hb_dirBase() ) + 'test_excel', fileFormat )
      
      ? hb_Utf8ToStr( 'Archivo Excel se generó en ' ) + hb_dirSepAdd( hb_dirBase() )

    RECOVER USING oError
      *-- if oSheet:SaveAs() fails, show the error
      ? 'Error al grabar el archivo', hb_Utf8ToStr( oError:Description )
      
    END SEQUENCE

  RECOVER USING oError
    ? oError:Description, 'Error Excel' 
  END SEQUENCE

  oExcel:DisplayAlerts := .T.
  *-- close and remove the copy of Excel.exe from memory
  *-- close the excel workbook
  oExcel:Workbooks:Close()
  *-- close Microsoft Excel
  oExcel:Quit()
  *-- free the COM Excel Object from memory
  Release oExcel

  oSheet  := Nil
  oExcel  := Nil

  oRs:Close()
  connectionString:Close()
  
  rs := NIL
  connectionString := NIL

  ? 'Fin!'
  
Return Nil
/*********/

No he tenido éxito para encontrar un ejemplo para utilizar la técnica de exportar los datos mediante el uso de array, he visto los ejemplos del foro, en especial los ejemplos de Jimmy, pero aún no lo entiendo y no he podido realizar las pruebas.

Agradezco si pueden por favor proporcionarme algún ejemplo para exportar datos a Excel desde array

Saludos,
Javier

[AUGE_OHR]

hi,

you are using Excel COM and (!) ADO COM in your Sample with is NOT need

if you have Excel you can use RANGE Option to "select" DataArea in Excel Sheed (A1:Z9)
now you can "import/export" Data from/to Array with "same Size"

Sample show how to "export" into Array

Code: Select all

   oExcel := CreateObject( "Excel.Application" )
   oExcel:visible := .T.                   // visible
   // If there is a problem, don't let excel pop up messages
   oExcel:Application:DisplayAlerts := .F.
   // full Path
   oExcel:Application:Workbooks:open(cPATH+cFILE)
   // Make the first one active
   oWorkBook := oExcel:activeWorkBook
   oExcel:Application:Worksheets(1):activate()
   // Speed things up by creating an object containing the cells
   oSheet := oExcel:Worksheets(1):cells
   // "select" used Range
   oWorkBook:workSheets(1):usedRange:Select

   numRows    := oWorkBook:workSheets(1):usedRange:Rows:Count
   numColumns := oWorkBook:workSheets(1):usedRange:Columns:Count
   // build Array with same Size
   FOR i := 1 TO numRows
       AADD(aExcel,ARRAY(numColumns))
   NEXT
   // convert Numeric to "A1"
   cEnde := ZAHL2CHR(numColumns)

   // now get "select" Range into Array
   aExcel := oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value

if you want to use ADO you do not need Excel !
you need to use a "Connection-String" like ODBC

Code: Select all

   strHeader := "HDR=YES;"
   oConnect := CreateObject( "ADODB.Connection" )
   oConnect:ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;' + ;
           'Data Source=' + myXlsFile + ';' + ;
           'Extended Properties="Excel 12.0 Xml;' + strHeader + 'IMEX=1' + '";'

   bError := ERRORBLOCK( { | oErr | BREAK( oErr ) } )
   BEGIN SEQUENCE
      oConnect:Open()

if you have more Question feel free to ask

[jparada]

Hi Jimmy, I appreciate your help, but it is the same snippet where I can't yet understand where you put the code to export the content of the
recordset to Excel (remember I'm trying to export from Sql -> Excel), I don't understand the first example with OLE and much less understand
your second example with ADO, Jimmy I'm sorry to bother you, but if you have some free time can you please provide a complete example

I appreciate your help

Regards,
Javier

[AUGE_OHR]

hi,

ok, i have "oversee" that you come from SQL

you still can use a Array "between" SQL -> Excel
but i have "see" Sample using ADO direct write a Excel Sheet from "Result-Set" of SQL

but you need IMHO "ConnectString" to SQL-Server which depend on your SQL-Server ( MySQL, PostgreSQL, M$-SQL ...)

so please search for : ADO "your SQL" Excel ConnectString
if you find a Sample ( please tell us later about it )

[jparada]

Hi,
Again I don't understand your answer, I have no problems with the connection string to Sql or with obtaining the sql data, which in my case is Microsoft SQL Server, I have solved this using the Ado class of David Field, so my connection string is something like this:

Code: Select all

/*
* Conexión
*/
Function conexion
  Local server := 'SERVER\INSTANCE'
  Local database := 'BD'
  Local user := 'user'
  Local pass := 'pass'
  Local engine := 'MSSQL'

  connectionString := AdoConn( server, database, user, pass, engine )

Return connectionString
/*********/


/*
* AdoConn
*/
Function AdoConn( Server, Database, User, Pass, Engine, Port )
  Local oC, sConection, nOption

  oC := win_oleCreateObject( "ADODB.Connection" )
	oC:CursorLocation := adUseClient
	oC:ConnectionTimeout := 10
        oC:CommandTimeOut := 15 //30

  DO CASE	
	  
  CASE Engine == "MSSQL"
        sconection := "Provider=SQLNCLI11;" + ;
        "server=" + server + if(Port <> NIL, ","+Port ,"") +;
        ";database=" + if(EMPTY(database), "master", database) + ;
        iif(EMPTY(User), ";Integrated Security=SSPI;",";uid=" + User + ";pwd=" + Pass )	
  ENDCASE

 oC:ConnectionString := sconection
  .
  .
  .

Any other tip?

Regards,
Javier

[AUGE_OHR]

hi,

Microsoft SQL Server

so your next is to open SQL-Table and send a Query, here PostgreSQL.

Code: Select all

   cQuery := "SELECT " + cDBSfield + ", " + cOrder + "," + cDBSfield + ") FROM " + cTable + " ORDER BY " + cDBSfield   
   oPG2:Exec( cQuery )
   oResult := oPG2:result
   iMax := oResult:rows

i do "store" Result in a Array.

next Step is to open ADO Connection for Excel
you can "Append" ROW and "write" with FIELDPUT()

hope it is clear now