FiveTech Software tech support forums

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.

 

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

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

 

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
 

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.
 

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

 

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

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

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

nageswaragunupudi wrote:

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.