Page 1 of 4

DBF To Excel

Posted: Thu Mar 26, 2009 12:49 pm
by sudip

I was looking for an application which can transfer data from grid to excel. MOL showed me an example (very well written) directly copied from his application. Thank you MOL. :)

I already used excel in my xHarbour apps. I modified slightly to make it HMG compatible. I got CopyToClipboard() function from this forum. Finally I made this application to share with you. :)

There is a file "". Yes, I uploaded it for you in :)

Code: Select all

#include ""
#include ""

Function Main()
   Local cFile := GetFile({{'DBF File','*.dbf'}}, 'DBF File')
   Local a_fields , cAlias

   if empty(cFile)
      Return Nil
   If ! File( cFile )
      MSGSTOP("File I/O error, cannot proceed")
      Return Nil

   cAlias :=ALLTRIM(substr(cFile,Rat('\',cFile)+1))
   cAlias :=substr(cAlias,1,len(cAlias)-4)
   use &cFile alias &cAlias
   a_fields := {}
   for n:=1 to fcount()
      aadd( a_fields , fieldname( n ) )

   Define Window winMain ;
      at 0, 0 ;
      width 470 ;
      height 350 ;
      title cFile ;
      main ;
   	@ 10, 10 button cmdShowexcel caption "Show in Excel" ;
   		action ShowExl(cAlias, a_fields, a_fields, cFile)
   end window
 	return nil

function ShowExl(cAlias, aFldnm, aPrompt, mHeading)
private oExcel, nRow, nStartRow, mPrevRow, cMemo, mesg, i, mTemp

   oExcel = CREATEOBJECT( "Excel.Application" )
   oSheet = oExcel:ActiveSheet
   //oExcel:ActiveWindow:DisplayGridlines = .f.
   nRow := 1

   with object oSheet
      for i = 1 to len(aPrompt)
         :Cells(nRow, i):Value = aPrompt[i]

   select &cAlias
   nStartRow = nRow
   cMemo = ""

   do while inkey() != 27 .and. !eof()

      FOR i = 1 TO len(aFldnm)
        mTemp = eval(fieldblock(aFldnm[i]))
         do case
            case valtype(mTemp) $ "CM"
               cMemo += alltrim(mTemp)
            case valtype(mTemp) = "D"
               cMemo += ExcelDt(mTemp)
            case valtype(mTemp) = "N"
               cMemo += ltrim(str(mTemp, 12, 2))
            case valtype(mTemp) = "L"
               cMemo += iif(mTemp, "Yes", "No")
         cMemo += chr(9)
      cMemo += chr(10)

      mPrevrow = nRow
      if (inkey() != 27 .and. !eof()) .or. mod(nRow, 1000) = 0
         CopyToClipboard( cMemo )
         oSheet:Cells( nStartRow, 1 ):Select()
         nStartRow := nRow
         cMemo := ''

      select &cAlias

   oSheet:Columns( "A:"+chr(asc("A")+len(aFldnm))):AutoFit()
   if mHeading <> NIL
      oSheet:Cells( 1, 1 ):Value = mHeading

   oExcel:Visible = .T.
   return nil

function CopyToClipboard
	param cTekst
	hb_gtInfo( HB_GTI_CLIPBOARDDATA, cTekst )

function ExcelDt(mDt)
   return (ltrim(str(day(mDt)))+"-"+left(cmonth(mDt), 3)+"-"+str(year(mDt), 4))

function ExcelBorder(oSheet, col1, row1, col2, row2)
   local mRange
   mRange = col1+ltrim(str(row1))+":"+col2+ltrim(str(row2))
   oSheet:Range(mRange):Borders(xlEdgeTop):LineStyle = xlContinuous
   oSheet:Range(mRange):Borders(xlEdgeLeft):LineStyle = xlContinuous
   oSheet:Range(mRange):Borders(xlEdgeBottom):LineStyle = xlContinuous
   oSheet:Range(mRange):Borders(xlEdgeRight):LineStyle = xlContinuous
Please download, compile and run.
(10.32 KiB) Downloaded 763 times
And please advise me. I really need it for learning HMG :)

With best regards.


Re: DBF To Excel

Posted: Thu Mar 26, 2009 1:26 pm
by Rathinagiri
Thanks a lot Sudip. It would be definitely useful for me. Thanks again.

Re: DBF To Excel

Posted: Thu Mar 26, 2009 1:31 pm
by sudip
Hello Rathi,
It would be definitely useful for me.
You don't know how many times I used your dbu (and shall use your grid2print in future)!
So, I am very much happy to create something, which will be useful to you :)

With best regards.


Re: DBF To Excel

Posted: Thu Mar 26, 2009 1:55 pm
by luisvasquezcl
Great Job,
Luis Vasquez

Re: DBF To Excel

Posted: Fri Mar 27, 2009 5:21 am
by dhaine_adp
Hi Sudip,

Thank you for sharing your code. Keep up the good work. I can see that the HMG community is still growing.



Re: DBF To Excel

Posted: Fri Mar 27, 2009 5:29 am
by swapan
Hi Sudip,

I downloaded ur attached file....compiled it but the exe is giving error at the time of porting data to excel - after pressing the button 'Show to Excel'

Any1 tried?

Plz. check....and revert.



Re: DBF To Excel

Posted: Fri Mar 27, 2009 7:12 am
by mol
I tried your program and got error, too:

but I cut line 103:
//oSheet:Columns( "A:"+chr(asc("A")+len(aFldnm))):AutoFit()
and now it works...

But, I want to say, that I've got unknown error exporting to Excel on some computers with my program, which works OK on my 3 computers...
It's only from Microsoft... :lol:

Re: DBF To Excel

Posted: Fri Mar 27, 2009 9:46 am
by esgici
Hi Sudip

I had tried and non encountered any error like our friends noticed.

Thanks a lot to sharing.

Also I am happy by seeing realized a big step in one of my wish in older posts :
I hope that this will be a good example and starting point for developing a generic and versatile .dbf to .xls conversion routine/module.
Thanks again.




Re: DBF To Excel

Posted: Fri Mar 27, 2009 2:30 pm
by sudip
Hi Marec,

Thank you for your checking. It really will help me to improve my software. :)

I also got same type of errors about 2 years back in one of my client's machine (a parish church!!!), but funny thing is that I was programming with one Microsoft Software Tool ;)

I got autofit() .. etc functions by exploring excel macro (by recording and then seeing the codes). There is a problem. Microsoft changes their own codes from version to version (may be to increase the sale of their product ;) , but that's a different issue). Now, we have be cautious about those functions, which we should avoid from our codes. If you find any more problem (from your own codes also), please let me know.

Thank you again for this report.

With best regards.


Re: DBF To Excel

Posted: Fri Mar 27, 2009 2:33 pm
by sudip
Dear Swapan,

Can you please tell me exactly what's the error message (like MOL sent), so that I shall rectify the code.

Thank you for testing!!! :)