HMGforum.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
/*********/

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

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()

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
  .
  .
  .

Code: Select all

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