Page 1 of 4

DBF To Excel

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

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 "excel.ch". Yes, I uploaded it for you in ShowExcel.zip. :)

Code: Select all

#include "minigui.ch"
#include "excel.ch"

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

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

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

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

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

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

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

   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")
         endcase
         cMemo += chr(9)
      NEXT
      
      cMemo += chr(10)

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

      select &cAlias
   enddo

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

   oExcel:Visible = .T.
   return nil

   
   
function CopyToClipboard
	param cTekst
	#define HB_GTI_CLIPBOARDDATA 15
	hb_gtInfo( HB_GTI_CLIPBOARDDATA, cTekst )
	return

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
   return   
Please download, compile and run.
ShowExcel.zip
(10.32 KiB) Downloaded 763 times
And please advise me. I really need it for learning HMG :)

With best regards.

Sudip

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.

Sudip

Re: DBF To Excel

Posted: Thu Mar 26, 2009 1:55 pm
by luisvasquezcl
Great Job,
regards
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.

Shukria,

Danny

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.

Regards,

Swapan

Re: DBF To Excel

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

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.

Regards

--

Esgici

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.

Sudip

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!!! :)

Regards.

Sudip