TFileXLS again

[Kleyber]

Hi all,

I've been using TOleAuto to work with Excel, but I've noticed that is slow, comparing with TFileXLS. The class TFileXLS I already have, but there is a situation that I didn't see: How to open an Excel file with TFileXLS which is protected by a password? Using TOleAuto I do this way:

Code: Select all  Expand view

oExcel:Workbooks:Open( cDirXLS+cArquivoX,OleDefaultArg() ,OleDefaultArg() ,OleDefaultArg() ,cSenha )
 

How to do this with TFileXLS? Has anybody an example?

TIA,

[Kleyber]

Now I see that TFileXLS only creates an Excel file... it does not read. So, do you have any suggestions to read en Excel file fastly than TOleAuto?

[driessen]

Hello,

I use this code to open any kind of file (XLS, BMP, JPG, ...) :

Code: Select all  Expand view

ShellExecute(nil,"Open",cFileName,,,3)

It works very well.

Good luck.

[Gale FORd]

You will have to modify the open method or inherit the class.
Here is an untested modification to the open method.

Code: Select all  Expand view

METHOD Open( cFilexls, cPassword )  CLASS TExcelScript
   ::cFile  := cFilexls
   // oWorkbook.Open(FileName, UpdateLinks, ReadOnly, Format, ;
   //     Password, WriteResPassword, IgnoreReadOnlyRecommended, ;
   //     Origin, Delimiter, Editable, Notify, Converter, ;
   //     AddToMru, Local, CorruptLoad)
   ::oExcel:WorkBooks:Open( ::cFile,,, cPassword )
   ::oBook  := ::oExcel:Get( "ActiveWorkBook")
   ::oSheet := ::oExcel:Get( "ActiveSheet" )
   ::oPageSetup := ::oSheet:Get( "PageSetup" )
   ::cFont := "Arial"
   ::nSize := 10
   ::lBold := .F.
   ::lItalic := .F.
   ::lUnderLine := .F.
   ::nAlign := 1
   ::nBordStyle := 1
   ::nBordWeight := 2

RETURN( Nil )
 

[Gale FORd]

Sorry, that was for a different class.

[Kleyber]

Thanks to everybody for answering.

Driessen: maybe i was not so clear, but what i nedd is open an Excel file and read all data and write them into my database. Thanks anyway

Gale Ford: Thanks. No problem.

[Richard Chidiak]

Kleyber

to read an excel file try something like this

Code: Select all  Expand view

TRY
   oExcel := CREATEOBJECT( "Excel.Application" )
CATCH
   MSGSTOP("xxxxx error no ecxeL !" )
   return nil
END

oExcel:WorkBooks:Open( CFILE )  // your file
oSheet = oExcel:Get( "ActiveSheet" )

nRows  := oSheet:UsedRange:Rows:Count()
nCols  := oSheet:UsedRange:Columns:Count()

FOR I := 1 TO nRows  // number of lines
    FIELD1 :=  oSheet:Cells(I,1):value  // if you know the columns otherwise retreive them
    FIELD2 :=  oSheet:Cells(I,2):value

// write your dbf file
next

 

Hth

Richard

[Gale FORd]

I found updating a lot of cells with OLE is real slow so I use windows clipboard to store the data and paste it into spreadsheet. This is very fast. I am sure you can do the reverse.

Select the cell range or sheet and copy them to windows clipboard.
Read clipboard into a (x)Harbour variable.

Here is a sample program I did some time ago to demonstrate how much faster copy and paste is.
Towards the end I added a couple of lines to show how to copy from excel to clipboard
and save it to a file that you can use "append from cFile delim with tab".

Code: Select all  Expand view

#define GTI_CLIPBOARDDATA 15

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

   oExcel = CREATEOBJECT( "Excel.Application" )
   oExcel:WorkBooks:Add()
   oSheet = oExcel:ActiveSheet
   nRow := 1
   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 = "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)

      nCounter++
      // 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
   ENDDO

   // ------------------ Results on Screen
   nSecClip := seconds()-nSeconds
   ? 'Ole = '+ltrim(str(nSecOle))
   ? 'Clip = '+ltrim(str(nSecClip))
   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
 

[Kleyber]

Richard Chidiak, that's the way I use today. Thanks anyway.

Gale Ford, That's an interesting approach. I'll take a good look at this.

Thank you

[nageswaragunupudi]

Mr. Klyber

Please see this posting, if this helps you to achieve better speeds for importing data reliably.

viewtopic.php?f=3&t=20763

[Kleyber]

Hi Nagesh,

Thank you very much for this clue. i don't know if I answer you here or in the other post, but, in my case I need to import some cells from each line and write them into a MySQL table. And besides, the excel files does not have the same size. I have to do some tests using your approach.

Thanks a lot,