FiveTech Software tech support forums

Code: Select all

#include "fivewin.ch"

function Main()

   local oRange, aData

   oRange   := GetExcelRange( ExePath() + "xl2array", "ArrayTest", "B3:D6" )
   aData    := ArrTranspose( oRange:Value )
   oRange:WorkSheet:Parent:Close()
   XBrowse( aData )

return nil

function ExePath()
return cFilePath( GetModuleFileName() )
 

Code: Select all

AEval( aData, { |a,i| oRange:Rows( i ):Value := a } )

Code: Select all

function ExportDbf2Excel()

   local oExcel, oBook, oSheet, oRange
   local bLine
   local n, nSecs

   USE CUSTOMER NEW ALIAS CUST SHARED

   bLine    := "{||{" + FieldName( 1 )
   for n := 2 to Fcount()
      bLine  +=  "," + FieldName( n )
   next
   bLine    += "}}"
   bLine    := &( bLine )

   oExcel   := TOleAuto():New( "Excel.Application" )
   oBook    := oExcel:WorkBooks:Add()
   oSheet   := oBook:ActiveSheet()
   oRange   := oSheet:Range( oSheet:Cells( 1, 1 ), oSheet:Cells( LastRec(), FCount() ) )
   oExcel:ScreenUpdating   := .f.

   nSecs    := Seconds()
   n        := 1
   DbEval( { || oRange:Rows( n++ ):Value := Eval( bLine ) } )
   nSecs    := Seconds() - nSecs
   MsgInfo( "Exported " + LTrim(Str(n-1)) + " rows in " + LTrim(Str(nSecs)) + " Seconds" )

   oRange:Columns:AutoFit()
   oExcel:ScreenUpdating   := .t.
   oExcel:Visible          := .t.

return nil
 

Code: Select all

#define GTI_CLIPBOARDDATA 15

FUNCTION MAIN()
   LOCAL oExcel, oSheet
   LOCAL nRow
   LOCAL nCounter, nStart, nSeconds, nSecOle, nSecClip, nSecArray
   LOCAL cMemo, cData, aData

   set century on
   set epoch to 1950

   oExcel = CREATEOBJECT( "Excel.Application" )
   oExcel:WorkBooks:Add()
   oSheet = oExcel:ActiveSheet
   nRow := 1
   oSheet:Cells( nRow, 1 ):Value = "Cell by Cell"
   nRow++
   oSheet:Cells( nRow, 1 ):Value = "Counter"
   oSheet:Cells( nRow, 2 ):Value = "Date"
   oSheet:Cells( nRow, 3 ):Value = "Row"
   nCounter := 1
   nStart := nCounter

   // ------------------ Start Cell by Cell
   nSeconds := seconds()
   DO WHILE nCounter <= 2000
      oSheet:Cells( nCounter+nRow, 1 ):Value = nCounter
      oSheet:Cells( nCounter+nRow, 2 ):Value = date()-nCounter
      oSheet:Cells( nCounter+nRow, 3 ):Value = nCounter-1
      nCounter++
   ENDDO
   nSecOle := seconds()-nSeconds
   nRow += nCounter+2
   oSheet:Cells( nRow, 1 ):Value = "Copy and Paste"
   nRow++
   oSheet:Cells( nRow, 1 ):Value = "Counter"
   oSheet:Cells( nRow, 2 ):Value = "Date"
   oSheet:Cells( nRow, 3 ):Value = "Row"

   // ------------------ Start Clipboard
   nSeconds := seconds()
   nCounter := 1
   nStart := nCounter
   cMemo := ''
   DO WHILE nCounter <= 2000

      // build record
      cMemo += ltrim( str( nCounter ) )
      cMemo += chr(9)+dtoc( date()-nCounter )
      cMemo += chr(9)+ltrim( str( nCounter+nRow-1 ) )
      cMemo += chr(10)

      // update sheet every 1000 records or eof() if using dbf
      IF mod( nCounter, 1000 ) = 0  // .or. eof()
         GTSetClipboard( cMemo )
         oSheet:Cells( nRow+nStart, 1 ):Select()
         oSheet:paste()
         nStart := nCounter
         cMemo := ''
      ENDIF
      nCounter++
   ENDDO

   nRow += nCounter+2
   oSheet:Cells( nRow, 1 ):Value = "Array"
   nRow++
   oSheet:Cells( nRow, 1 ):Value = "Counter"
   oSheet:Cells( nRow, 2 ):Value = "Date"
   oSheet:Cells( nRow, 3 ):Value = "Row"
   nCounter := 1

   // ------------------ Start Array Test
   nSeconds := seconds()
   bLine    := {||{ nCounter, date()-nCounter, nCounter-1 } }
   oRange   := oSheet:Range( oSheet:Cells( nRow+1, 1 ), oSheet:Cells( nRow+1+2000, 3 ) )
   DO WHILE nCounter <= 2000
      oRange:Rows( nCounter ):Value := Eval( bLine )
      nCounter++
   ENDDO
   nSecArray := seconds()-nSeconds

   // ------------------ Results on Screen
   nSecClip := seconds()-nSeconds
   ? '  Cell by Cell = '+ltrim(str(nSecOle))
   ? 'Copy and Paste = '+ltrim(str(nSecClip))
   ? '         Array = '+ltrim(str(nSecArray))
   wait

   // Example of getting data from spreadsheet
   // and saving to a text file so that it can be
   // appended to data file with
   // append from cFile delim with tab
   oSheet:Range("A1:C1000"):copy()
   cData := hb_gtInfo( GTI_CLIPBOARDDATA )
   memowrit( 'test2.txt', cData )

   oExcel:Visible = .T.

RETURN( nil )

function gtsetclipboard( cText )
   if cText == nil
      cText := ''
   endif
   hb_gtInfo( GTI_CLIPBOARDDATA, cText)
return nil

 

Code: Select all

function ExportRecSet2Excel()

   local cFolder    := "c:\fwh\samples\ "
   local cStr
   local oCn, oRs
   local oExcel, oBook, oSheet
   local n, nSecs

   cStr  := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cFolder + ;
            ";Extended Properties=dBASE III;User ID=Admin;Password=;"

   oCn   := TOleAuto():New( "ADODB.Connection" )
   oCn:Open( cStr )
   oRs   := TOleAuto():New( "ADODB.RecordSet" )

   WITH OBJECT oRs
      :ActiveConnection    := oCn
      :Source              := "CUSTOMER"
      :CursorLocation      := 3
      :Open()
   END

   oExcel   := TOleAuto():New( "Excel.Application" )
   oBook    := oExcel:WorkBooks:Add()
   oSheet   := oBook:ActiveSheet()
   oExcel:ScreenUpdating   := .f.

   nSecs    := Seconds()
   n        := oSheet:Cells( 1, 1 ):CopyFromRecordSet( oRs )
   nSecs    := Seconds() - nSecs
   MsgInfo( "Exported " + LTrim(Str(n)) + " rows in " + LTrim(Str(nSecs)) + " Seconds" )

   for n := 1 to oRs:Fields:Count
      oSheet:Columns( n ):AutoFit()
   next

   oRs:Close()
   oCn:Close()
   oExcel:ScreenUpdating   := .t.
   oExcel:Visible          := .t.

return nil