Aporte: Sentencia SQL directa a Excel class tDboDBCDirect

[armando.lagunas]

Amigos del foro:


un pequeño aporte para las personas que utilizan la clase TdboDBCDirect

por una necesidad puntual, he incorporado un nuevo metodo a esta clase, lo cual me permite llevar una consulta SQL en forma directa a excel, sin pasar por una tabla DBF temporal, un xBrowse y un oBrw:toExcel() , aumentando la velocidad en un 300% y lograr que cualquier consulta, vista o procedimiento almacenado, se vacíe en una hoja de excel.

un ejemplo de utilización

Code: Select all  Expand view

   oSql := Todbc():New("Nombre ODBC","Usuario","Password")
...
...
          REDEFINE  SBUTTON  ID 42  ACTION Exportar_Sql_Excel("Stock Fruta a Proceso")  OF xDlg
...
...

oSql:End()

STATIC FUNCTION Exportar_Sql_Excel( cTitulo )
LOCAL oDbf

   oDbf:= oSql:Query("SELECT * FROM RECEPCIONES")
   oDbf:SQLtoExcel( cTitulo )
   oDbf:End()

RETURN .T.

 

con esto sale esto:



en la clase hay que hacer lo siguiente :

Code: Select all  Expand view

CLASS TDbOdbcDirect
...
...
METHOD  SQLtoExcel()
...
...
//----------------------------------------------------------------------------//

METHOD SQLtoExcel( cTitle, nRecs ) CLASS TDbOdbcDirect
LOCAL aBuffer, n, nLen, lAll
LOCAL oExcel, oBook, oSheet, uData, oRange, cRange, cCell, cLet, nColHead, bError, cText, oClip, nStart, aRepl
LOCAL nLine  := 1  , nCount := 0 , aCol := { 26, 52, 78, 104, 130, 156 } , aLet := { "", "A", "B", "C", "D", "E" }     , xWin

DEFAULT cTitle := "Datos de la consulta realizada", nRecs := 1

   IF ::hStmt == 0
      RETURN .f.
   ENDIF

   ::aFields  := ::CursorFields( ::hStmt )
   lAll       := .T. // (nRecs == 0)
   nLen       := LEN( ::aFields )
   ::aBuffer  := Array(nLen)
   ::aIsNull  := Array(nLen)
   aBuffer    := ::aBuffer
   aRepl      := {}
   cLet       := aLet[ ASCAN( aCol, {|e| nLen  <= e } ) ]
   IF !EMPTY( cLet )              ;   n        := ASCAN( aLet, cLet ) - 1  ;    cLet += CHR( 64 + nLen - aCol[ MAX( 1, n ) ] )
   ELSE                           ;   cLet     := CHR( 64 + nLen )
   ENDIF                          ;   bError   := ErrorBlock( { | x | Break( x ) } )

   BEGIN SEQUENCE
                                           oExcel := TOleAuto():New("Excel.Application")
   RECOVER
                                           ErrorBlock( bError )
                                           RETURN Nil
   END SEQUENCE
   ErrorBlock( bError )

   nCount    -= 15
   oExcel:ScreenUpdating := .F.
   oExcel:WorkBooks:Add()
   oBook     := oExcel:Get( "ActiveWorkBook")
   oSheet    := oExcel:Get( "ActiveSheet" )
   nCount    -= 15
   cText     := ""
   oSheet:Cells( nLine++, 1 ):Value := cTitle
   oSheet:Range( "A1:" + cLet + "1" ):Set( "HorizontalAlignment", 7 )
   ++nLine
   nStart    := nLine
   nColHead  := 0

   FOR n     := 1 TO nLen
       uData := ChkSp( ::aFields[ n ][ SQLNAME ])
       uData := STRTRAN( uData, CRLF, Chr( 10 ) )
       nColHead ++
       oSheet:Cells( nLine, nColHead ):Value := uData
       nCount ++
   NEXT
   nStart     := ++nLine

   DO WHILE (lAll .or. nRecs > 0)  .AND. ::hStmt != 0
      IF ::Fetch()
         For n := 1 To nLen
             uData  := aBuffer[n]
              IF VALTYPE( uData ) == "C" .AND. AT( CRLF, uData ) > 0
                 uData  := STRTRAN( uData, CRLF, "&&" )
                 IF ASCAN( aRepl, n ) == 0
                     AADD( aRepl, n )
                 ENDIF
              ENDIF
              uData  :=  IIF( VALTYPE( uData )=="D", DTOC( uData ), ;
                         IIF( VALTYPE( uData )=="N", TRANSFORM( uData, "@E 99,999,999,999.99" ) , ;
                         IIF( VALTYPE( uData )=="L", IIF( uData ,".T." ,".F." ), cValToChar( uData ) ) ) )
              cText  += TRIM( uData ) + Chr( 9 )
              nCount ++
          NEXT
          cText += CHR( 13 )
          ++nLine
      ENDIF
      nRecs --
   ENDDO

   oSheet:Rows( 1 ):Font:Bold   := .T.
   IF LEN( cText ) > 0      ;           oClip := TClipBoard():New()                      ;    oClip:Clear()
      oClip:SetText(cText)  ;           cCell := "A" + Alltrim( Str( nStart ) )          ;    oRange := oSheet:Range( cCell )
      oRange:Select()       ;           oSheet:Paste()                                   ;    oClip:End()
      cText := ""
   ENDIF                    ;           nLine := If( ! Empty( cTitle ), 3, 1 )           ;    cRange := "A" + LTrim( Str( nLine ) ) + ":" + cLet + Alltrim( Str( oSheet:UsedRange:Rows:Count() ) )
                                        oRange := oSheet:Range( cRange )                 ;    oRange:Font:Name := "Consolas"
   oRange:Font:Size := 11   ;           oRange:Font:Bold := .F.
   IF ! EMPTY( aRepl )
        FOR n := 1 TO LEN( aRepl )
            oSheet:Columns( CHR( 64 + aRepl[ n ] ) ):REPLACE( "&&", CHR( 10 ) )
        NEXT
   ENDIF
   oSheet:Rows( 1 ):Font:Size       := 14
   oSheet:Rows( 1 ):Font:Bold       := .T.
   oSheet:Rows( 1 ):RowHeight       := 30
   oSheet:Rows( 1 ):Font:ColorIndex := 25

   oSheet:Rows( 3 ):Font:Bold       := .T.
   oSheet:Rows( 3 ):Font:ColorIndex := 20
   oSheet:Rows( 3 ):RowHeight       := 25
   oRange:Borders():LineStyle       := 1
   oRange:Columns:AutoFit()
   IF ! Empty( aRepl )
         FOR n := 1 TO LEN( aRepl )
            oSheet:Columns( CHR( 64 + aRepl[ n ] ) ):WrapText := .T.
         NEXT
   ENDIF
   oSheet:Range( "A3:"+cLet+"3" ):Interior:ColorIndex := 49
   oSheet:Range( "A3:"+cLet+"3" ):Borders:ColorIndex  :=  2
   oSheet:Range( "A4" ):Select()
   xWin                    := oExcel:ActiveWindow
   xWin:SplitRow           := 3
   xWin:FreezePanes        := .t.
   oExcel:ScreenUpdating   := .t.
   oExcel:Visible          := .T.
   ShowWindow( oExcel:hWnd, 3 )
   BringWindowToTop( oExcel:hWnd )

RETURN Self

//----------------------------------------------------------------------------//

 

con eso me funciona muy bien.

espero que a alguien le ayude tanto como ami.

Saludos.

[cnavarro]

Armando, gracias

[nageswaragunupudi]

Fasted way to export to excel

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, oRs

   oCn   := FW_OpenAdoConnection( "c:\fwh\samples\xbrtest.mdb" ) // MS Access
// oCn   := FW_OpenAdoConnection( "c:\fwh\samples\" ) // DBase III
// oCn   := FW_OpenAdoConnection( "MYSQL,localhost,FWH,root,password" ) // MySql DataBase FWH: Replace your details

   oRs   := FW_OpenRecordSet( oCn, "SELECT * FROM CUSTOMER" ) // Use your sql statement

   AdoToExcel( ors )

   oRs:Close()
   oCn:Close()

return nil

function AdoToExcel( oRs )

   local oExcel, oBook, oSheet
   local aHead, oHead, nFlds, nRow
   local nSecs

   if ( oExcel := ExcelObj() ) == nil
      MsgAlert( "Excel Not Installed" )
      return nil
   endif
   oBook    := oExcel:WorkBooks:Add()
   oSheet   := oBook:ActiveSheet()
   oExcel:ScreenUpdating := .f.

   nSecs    := Seconds()

   // Export Header
   nFlds    := oRs:Fields:Count
   aHead    := Array( nFlds )
   AEval( aHead, { |u,i| aHead[ i ] := oRs:Fields( i - 1 ):Name } )
   oHead    := oSheet:Range( oSheet:Cells( 1, 1 ), oSheet:Cells( 1, nFlds ) )
   oHead:Value       := aHead
   oHead:Font:Bold   := .t.

   // Export All Cell Values
   oRs:MoveFirst()
   nRow   := oSheet:Cells( 2, 1 ):CopyFromRecordSet( oRs )
   oRs:MoveFirst()
   oSheet:Range( oSheet:Columns( 1 ), oSheet:Columns( nFlds ) ):AutoFit()

   // Display Excel
   oExcel:ScreenUpdating := .t.
   oExcel:visible := .t.

   nSecs    := Seconds() - nSecs
   MsgInfo( "Time taken " + cValToChar( nSecs ) + " seconds" )

return nil
 

[nageswaragunupudi]

If we want to export from TDbOdbcDirect, this loop can be tried

Code: Select all  Expand view

  if ( oExcel := ExcelObj() ) == nil
      MsgAlert( "Excel Not Installed" )
      return nil
   endif
   oBook    := oExcel:WorkBooks:Add()
   oSheet   := oBook:ActiveSheet()
   oExcel:ScreenUpdating := .f.

   nLen     := Len( ::aFields )
   AEval( ::aFields, { |a,i| oSheet:Cells( 1, 1 ):Value := a[ SQLNAME ] } )

   oRange   := oSheet:Range( oSheet:Columns( 1 ), oSheet:Columns( nLen ) )
   nRow     := 2  // after header

   // MAIN EXPORT LOOP
   do while ::Fetch()  // add other conditions
      oRange:Rows( nRow ):Value := ::aBuffer
      nRow++
   enddo
   // MAIN EXPORT LOOP ENDS
   
   oRange:AutoFit()
   oRange:VerticalAlignment := -4160

   // Display Excel
   oExcel:ScreenUpdating := .t.
   oExcel:visible := .t.
 

[jose_murugosa]

Mr. Rao,

It is interesting the study of FWH ADO functions, I start to study them because of this post, and I'd like to make you some questions.

1)Is there a limit of rows for a recordset using this functions or a limit to export to dbf files, because I tried to export 700.000 records from a mysql table to a dbf file .and. it returns this error

Called from: => TOLEAUTO:GETROWS( 0 )
Called from: .\source\function\ADOFUNCS.PRG => FW_ADOEXPORTTODBF( 1219 )
Called from: exportadbf.prg => ADOTODBF( 28 )
Called from: exportadbf.prg => MAIN( 17 )

This is the code, you will see that it is based on yours in this post.

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

    local oCn, oRs
    REQUEST DBFCDX
    RddSetDefault( "DBFCDX")

    //oCn   := FW_OpenAdoConnection( "c:\fwh\samples\xbrtest.mdb" ) // MS Access
    //oCn   := FW_OpenAdoConnection( "c:\fwh\samples\" ) // DBase III El directorio es la base de datos
    //oCn   := FW_OpenAdoConnection( "MYSQL,localhost,FWH,root,password" ) // MySql DataBase FWH: Replace your details
    oCn   := FW_OpenAdoConnection( "MYSQL,192.168.123.161,stock2,root,1234" ) // MySql DataBase FWH: Replace your details

    oRs   := FW_OpenRecordSet( oCn, "SELECT * FROM cajas_dbf" )
    //oRs   := FW_OpenRecordSet( oCn, "SELECT * FROM producto_dbf" ) // Use your sql statement  (aca se selecciona la tabla)

    AdotoDbf(oRs)

    oRs:Close()
    oCn:Close()

return nil

function AdoToDbf( oRs )

    local nSecs
   
    FW_AdoExportToDBF( oRs, "cajas.dbf", .t. )
   
    nSecs    := Seconds()

    nSecs    := Seconds() - nSecs
    //MsgInfo( "Time taken " + cValToChar( nSecs ) + " seconds" )

return nil

 

2) In recordsets the first column is column 0 (zero)?

3) Is there a way to obtain better information of this functions that the information we find in wiki of fivetech? because this functions are not in hlp files of FWH

Thanks in advance for your help, and for your contributions in this forum.

[nageswaragunupudi]

1)Is there a limit of rows for a recordset using this functions or a limit to export to dbf files, because I tried to export 700.000 records from a mysql table to a dbf file .and. it returns this error

There is no such limit in the documentation. I can not answer unless I test myself. I'll test this some time later and come back to you.

Are you able to successfully export smaller tables using this function?

2) In recordsets the first column is column 0 (zero)?

Yes.

3) Is there a way to obtain better information of this functions that the information we find in wiki of fivetech? because this functions are not in hlp files of FWH

For now, the best way is to study the source code in the adofuncs.prg.
I'll soon try to update the Wiki. Spending more time on development, not finding enough time for the documentation.

[jose_murugosa]

Yes I did'nt have problems with smaller tables, I will study adofuncs.prg and make other tests

Thanks a lot for your answer Mr. Rao.

[nageswaragunupudi]

Yes I had success with smaller tables, I will be making some tests too and share results.

It is okay if you are testing the functions for academic interest.

But if you have an immediate professional need to import large tables from MySql to DBF or other formats, ADO may not be the way. Mostly these are one-time jobs for us. Please examine options like mysqldump or mysqldbexport.

[jose_murugosa]

Yes I had success with smaller tables, I will be making some tests too and share results.

It is okay if you are testing the functions for academic interest.
But if you have an immediate professional need to import large tables from MySql to DBF or other formats, ADO may not be the way. Mostly these are one-time jobs for us.
Please examine options like mysqldump or mysqldbexport.

My systems are developed using sqlrdd of xharbour, and works with mysql, mariadb and sqlserver but backups tables to dbf files zipped so If server crash it is posible to work with dbf tables in other PC while repairing it and then charge the updated data to mysql or other databases again.

In order to change to harbour, I have to live sqlrdd and use adordd and make this backups with ado functions, I will see your sugestion and the way I can use it for my purposes, it is not urgent, but is my goal to change to harbour this year and convert my programs to it.

I will study this options you mention, thanks again for your help.

[nageswaragunupudi]

My personal advice is to use ADO straight away to make the applications robust, fast and to take advantage of all features of ADO directly. Using RDD may seem to be easier way of migration, but better invest time on using ADO classes directly. It is worth the effort. This is only my personal opinion.

I can anticipate your next question. How can you make your applications work with both DBF and also ADO simultaneously. Right?

[jose_murugosa]

Thanks for your suggestion and interest, I'm thinking on it...

Exact!!!!

You anticipate correctly my next question , I heard that ado with dbfs doesn´t work very well, I don´t know if that is correct.