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: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

DBF To Excel

Post by sudip » Thu Mar 26, 2009 12:49 pm

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

With best regards.

Sudip
With best regards,
Sudip

User avatar
Rathinagiri
Posts: 5263
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Thu Mar 26, 2009 1:26 pm

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: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Thu Mar 26, 2009 1:31 pm

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: 1142
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Has thanked: 14 times
Been thanked: 14 times
Contact:

Post by luisvasquezcl » Thu Mar 26, 2009 1:55 pm

Great Job,
regards
Luis Vasquez

User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines
Been thanked: 2 times

Post by dhaine_adp » Fri Mar 27, 2009 5:21 am

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:

Post by swapan » Fri Mar 27, 2009 5:29 am

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: 3226
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 224 times
Been thanked: 126 times
Contact:

Post by mol » Fri Mar 27, 2009 7:12 am

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: 4516
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Has thanked: 389 times
Been thanked: 111 times
Contact:

Post by esgici » Fri Mar 27, 2009 9:46 am

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: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Fri Mar 27, 2009 2:30 pm

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: 1446
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 5 times
Been thanked: 1 time

Post by sudip » Fri Mar 27, 2009 2:33 pm

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