DBF To Excel

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

DBF To Excel

Post 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 904 times
And please advise me. I really need it for learning HMG :)

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: DBF To Excel

Post by Rathinagiri »

Thanks a lot Sudip. It would be definitely useful for me. Thanks again.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: DBF To Excel

Post 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
With best regards,
Sudip
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: DBF To Excel

Post by luisvasquezcl »

Great Job,
regards
Luis Vasquez
User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines

Re: DBF To Excel

Post 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
Regards,

Danny
Manila, Philippines
User avatar
swapan
Posts: 242
Joined: Mon Mar 16, 2009 4:23 am
Location: Kolkata, India
Contact:

Re: DBF To Excel

Post 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
Thanks & Regards,
Swapan Das

http://www.swapandas.com/
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: DBF To Excel

Post 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:
User avatar
esgici
Posts: 4543
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Contact:

Re: DBF To Excel

Post 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
Viva INTERNATIONAL HMG :D
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: DBF To Excel

Post 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
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: DBF To Excel

Post 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
With best regards,
Sudip
Post Reply