Import and Export to Excel

[nageswaragunupudi]

Talking about importing data from Excel ranges, there are basically two well known approaches, we keep seeing in these forums.

One approach is to read each cell value in the Range with oSheet:Cells( nRow, nCol ):Value, iterating for each row and each column in the range. This approach is felt to be slower, but has the merit of getting the values in their correct data types. I mean numbers as numbers, dates as dates, etc.

Other approach is to copy the contents of the Range to Clipboard and then extract the clipboard contents in to our program. We get a text buffer which is TAB and CRLF delimited. We see in our forums many samples based on this approach. This is felt to be much faster than the former method. Downside of this approach is all the values are of character type and we need to convert them to their native datatypes in our program ourselves, which at times may result in some bugs and consume atleast a part of the time we saved by the copy and paste method. XBrowse does a decent job in coversion of the pasted text to the native datatypes.

I have never seen any one mentioning another approach which gives the best of both the worlds. Good speed and also native datatypes.

Here it is:
For this sample, I chose a very small range of data, but you may try with very large data too.

Screenshot of Excel Sheet:



Here is the code to read the contents of the range "B3:D6" into our (x)Harbour array.

Code: Select all  Expand view  RUN

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

Notes: GetExcelArray( cFileXLS, cSheet, acRange ) is an FWH function from ver 10.12.
ArrTranspose( aArray ) --> aTransposedArray is function available in FWH

Just two lines of code to import, which I could have written in just a single line too.

Screenshot of Array :

[Armando]

nageswaragunupudi:

Thanks a lot for your excelent clue.

regards

[RAMESHBABU]

Mr.Rao,

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

I am not able to trace the above functions either in FWH or in xHarbour Libraries.

Please tell me which libs are to be linked.

Regards,

- Ramesh Babu P

[nageswaragunupudi]

GetExcelRange( cBook, cSheet, cRange ) is a new function in olefuncs.prg, available from 10.12 onwards. In any case this function's functionality is nothing but:

1. oExcel := TOleObject():New( "Excel.Application" )
2. oBook := oExcel:WorkBooks:Open( cBook )
3. oSheet := oBook:WorkSheets( cSheet )
4 Return oSheet:Range( cRange )

ArrTranspose() is also a recent function which transposes rows and columns of an array. I am sure you can write it yourself.

[nageswaragunupudi]

A few points about exchange of data between Excel Ranges and Arrays may be interesting. VB programmers who deal with Office OLE automation must be aware that contents or Excel range and Array are mutually exchangeable.

Array = oRange.Value tranfers the contents of the range to array and oRange.Value = Array transfers the values in the array to the Range. This facility is very convinient and quite fast.

Though (x)Harbour's TOleAuto is very powerful, I encountered some inadequacies while dealing with multi-dimensional arrays in situations similar to the above.

While in VB, Array = oRange.Value, works perfectly, for us aArray := oRange:Value returns the values in multi-dimensional array no doubt, but exchanges and rows and columns. We need to Transpose ( matrix inversion ) the array to get the rows as rows and columns as columns.

Again in VB, oRange.Value = Array, transfers the values from array to the excel range instantaneously, but it does NOT work with our TOleAuto, if the Array is multi-dimensional. However a single dimensional array can be assigned to a Range with one row.

Therefore, if we want to transfer the contents of a multi-dimensional array to Excel range, we need to follow this work around.

Code: Select all  Expand view  RUN

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

But even this is quite fast and even faster where number of columns is large.

Hope the (x)Harbour pundits soon enhance the functionality of TOleAuto() to handle multi-dimensional arrays also perfectly.

Till then we can use the above logic to achieve faster export of data from our data souces like DBFs and arrays.

[nageswaragunupudi]

Here is a sample function to Export DBF to Excel using the above approach.

Code: Select all  Expand view  RUN

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
 

I personally feel this approach is faster than the other two well known methods.

[Gale FORd]

I added your method to my little test program and I am happy to report it is just as fast as the copy and paste method.
For each test I used 2000 rows with 3 columns of data and I generally received the following.
Ole Cell by Cell = 3.86 seconds
Clipboard = 1.88 seconds
Array = 1.88 seconds

I like your approach better but I found one thing I had to do.
For the ole cell by cell and and the copy and paste method, Excel automatically formatted the dates.
For the array approach Excel did not automatically format the date type so the date shows up as a number instead of the date. You would have to apply a format to the cells containing dates.
The problem with copy and paste is that everything has to be converted to character type first, even though excel makes a guess at the data type and formats the cell accordingly.

Here is my updated test

Code: Select all  Expand view  RUN

#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

 

[nageswaragunupudi]

Yet another way to export data to Excel. Transfer of data from ADO RecordSet to Excel is the fastest. Using this approach, here is a sample to export contents of a DBF to Excel.

Code: Select all  Expand view  RUN

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
 

[Kleyber]

Nagesh,

I've tried export from Excel to MYSQL with your solution and everything worked as expected and gaining time (instead of 50 seconds using OleAuto, now is 21 seconds). Thank you for your tips.

Best Regards,

[nageswaragunupudi]

Dear Mr. Klyber

Are you trying to speed up Excel to MySql?
First part is reading from Excel. And second part is export to MySql. I have not worked with MySql. My experience is limited to Oracle and MSSql. Exporting to any of the RDMSs is the fastest when we use their custom upload method from text files. May be importing excel range to text file directly as CSV and upload to MySql may be the fastest way, particularly when the data is large.

Another way is to save as xml and use builtin methods of the RDMS to upload xml ( I do not know about MySql )

[Kleyber]

Nagesh,

You are right. The first step was reading the coompleted range to an array, according to your example. Then I used this array to export all data to MySQL and this was fastest than the method I was using before (using Oleauto directly to export to MySQL).

Thanks again

[nageswaragunupudi]

Mr Klyber

You may be writing to MySql column by column and row by row. Whether you use ADO or TMySql or similar library, it will be slower than bulk upload from text file.

I still feel, saving excel range to CSV and bulk uploading from the CSV text file could be very fast.

[CARLOS ATUNCAR]

existe alguna forma de combinar filas en xbrowse como se hace con las cabeceras :SetGroupHeader( 'Americas', 2, 3, oBold ) necesito poner esto en xbrowse

https://onedrive.live.com/redir?resid=4 ... hoto%2cjpg

[nageswaragunupudi]

existe alguna forma de combinar filas en xbrowse como se hace con las cabeceras :SetGroupHeader( 'Americas', 2, 3, oBold ) necesito poner esto en xbrowse

https://onedrive.live.com/redir?resid=4 ... hoto%2cjpg

Yes. Very simple

oCol:lMergeVert := .t.

Please see sample \fwh\samples\testmerg.prg

[CARLOS ATUNCAR]

Another query, you can create a header like this?

| LUN-VIE |
| Turno | Refrigerios |
Ingreso|Salidas|Ingreso|Salidas|

thank you very much