How to Export to Excel

HMG Samples and Enhancements

Moderator: Rathinagiri

User avatar
bruno
Posts: 5
Joined: Wed Jul 30, 2008 11:40 pm

Re: How to Export to Excel

Post by bruno »

Rathingari

http://www.embalajesterra.com/misdoc/ej ... NTOv23.zip


Download from this link the openoffice version


This is a source code from Jose Miguel

http://www.embalajesterra.com/misdoc/pr ... ramas.html


Regards


Bruno
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: How to Export to Excel

Post by Rathinagiri »

Thanks a lot.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

I found on internet some informations about export to excel.
When I test it, I will describe my work.
Marek
User avatar
Alex Gustow
Posts: 290
Joined: Thu Dec 04, 2008 1:05 pm
Location: Yekaterinburg, Russia
Contact:

Re: How to Export to Excel

Post by Alex Gustow »

Hi all HMG-people!
I'm from Russia, city of Yekaterinburg (1000 miles to East from Moscow, near snowly Siberia).

Just now I'm writing a program (Harbour+MiniGUI) - export a report from DBFs (DOS charset) to Excel. I had some expirience with DBF->Excel process (I'm not expert yet, but can help someone if you need).

Advice #1: if you want to create LONG report (not a short table - but more than 200-300 lines) (for example - list of employees: Surname, First_Name, Date_of_Birth) - don't use writing to cells (something like this):

Code: Select all

sele PEOPLE
go top
i:=1
do while .not.eof()
  // line:
  oSheet:Cells(i,1):Value := PEOPLE->Surname    // "Ivanow"
  oSheet:Cells(i,2):Value := PEOPLE->Name        // "Ivan"
  oSheet:Cells(i,3):Value := PEOPLE->B_DATE     // "23.12.1967"
  skip
  i++
enddo
// 
It will work VEEERY long (if you have hundreds records)!.. :cry: We have a better way:

Let's create string variable (for example - CC); it will be our "buffer"; and add to it string representation of your data (divide cells by TAB [chr(9)] and lines by LF [chr(10)]). After SKIP look at LEN(cc); if it's length < 60K (max length for strings - 64K; but we don't want to risque) - go to next record and add it... If LEN(cc)>60K (or EOF(); or other reason to ENDDO) - write buffer to clipboard [function CopyToClipboard() ], stand to "start cell" and paste CC to Excel [oSheet:Paste() ] by "one move of finger"... Then clear CC - and go to next record. Something like this:

Code: Select all

sele PEOPLE
go top
i:=0
nRow:=1
cc:=""

do while .T.

  cc := cc + ;
        alltrim(PEOPLE->Surname) + chr(9) + ;
        alltrim(PEOPLE->Name) + chr(9) + ;
        dtoc(PEOPLE->B_DATE) + chr(10)
  skip
  i++     // how many records we have in buffer

  if eof() .or. ( len(cc) >= 60000 )

    CopyToClipboard( cc )
    oSheet:Cells( nRow, 1 ):Select()
    oSheet:Paste()
    // select all added cells
    oSel := oSheet:Range("A"+ltrim(str(nRow))+":C"+ltrim(str(nRow+i-1)))
    // autofit them
    oSel:Rows:AutoFit()
    cc := ""
    nRow := nRow + i    // next "start" row
    i := 0
    // now we ready to next step :)

  endif

enddo
And we have "what we need" - but speed of process is much more :D

Best wishes to Ricardo Lopez! (do you remember me? I began to "design" MiniGUI Help some months ago and sended you some HTML-pages; I'm sorry - I couldn't continue... work... and health... But I'll be better! :) )

Sorry for my "not the best" English (if it really "not the best") :?

P.S. Can anybody help me to translate Jose Miguel's MiTPINT's description (in HTML-file) and comments in PRG-files from Spain (I don't know it) to English? I downloaded it, it's good thing (I amazed with PDFprint! and others - good of course; print to OpenCalc - it's really what our programming group need [we plans move from MS Office to OpenOffice next year]) - and I want to use it (but don't understand all).
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: How to Export to Excel

Post by Rathinagiri »

Hearty welcome to the forum.

Your programming concept and logic is fantastic Alex Gustow.

Thanks for sharing this with us.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

That's great idea!!!
But I have a question.
I need to write format of cells.
I can do it cell by cell, but how to do it using clipboard?

I have another problem.
I used fragment of code:
oExcel := CreateObject( "Excel.Application")
oExcel:Workbooks:Open( plikLBH)
oExcel:WorkSheets("Aktivitäten"):Select()
On some computers it works great - worksheet "Aktivitäten" is beeng selected.
On some computers program generates error:

Image

When I run task manager, I can see that "EXCEL.EXE" process has started. It's problem with selecting sheet.
I tried to select this sheet by number:
oExcel:WorkSheets(2):Select()
but the same error has occured.

and I can't find reason for this error.
Did you meet such a situation?

Best regards, Marek
User avatar
Alex Gustow
Posts: 290
Joined: Thu Dec 04, 2008 1:05 pm
Location: Yekaterinburg, Russia
Contact:

Re: How to Export to Excel

Post by Alex Gustow »

rathinagiri, thanks for good words... but about my "fantastic programming concept and logic"...
If you don't know something, and I know it (because I read other Internet pages and other forums and asked some days ago - like everyone of us - many "foolish questions" to other people who knows it... and now I think [but not sure] that I know about it a liiiiittle greater than you) - why I can't answer to you when you're asking: "hey! anybody knows how I can..."?
I'm really newby in many programming things - and in Harbour+MiniGUI and DBF->Excel tasks too... But I like (as everyone "really means himself a programmer" must to like - I think) to learn new and new and new... every day.

Marek, about your 1st problem ("formatting data"):
For example, I want to "bold" data in 3rd column ("C" - Date_of_Birth). I know (after ALL data pasted - i.e. after ENNDO), what is "start row" (in my examle =1, but now let's named it "nRow1") and "end row" (in example - nRow-1, but now let's named it "nRow2")... I'll do something like this (after ENDDO):

Code: Select all

// select all cells (what I need to "bold")
oSel := oSheet:Range("C"+ltrim(str(nRow1))+":C"+ltrim(str(nRow2)))
// bold selected RANGE -
// don't work with EVERY cell for that (if you have loooong list)
// it's save your time for coffee :)
oSel:Font:Bold := .T.
// you can add next line too
// (for to be sure that "bold" data placed correctly in sheet view)
oSel:Cols():AutoFit()
...and that's all folks ("one move" - remember?)!
It's main concept in working with OLE-objects: "do as much as you can with ranges - not with each word, cell etc..." (such work is veeeery "lazy")

About your 2nd problem - I'll think how to do it everywhere...

Guys, I found one mistake in my example in previous post... Sorry :?
I forgot to write EXIT from DO...ENDDO (and we have "neverending story" ;) ). Correctly it must be like this:

Code: Select all

...
    i := 0
    // now we ready to next step :)

    // is work ended? if "yes" - EXIT
    if eof()
      exit
    endif
    //

  endif

enddo
...and someone told about my "fantastic logic"?.. :) Primary-school mistake! :?
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

It seems that on some excel version, functions:

oExcel:WorkSheets("Aktivitäten"):Select() - selecting sheet by name
oExcel:WorkSheets(2):Select() - selecting by number
and oExcel:Sheets(2):Select() works like oExcel:WorkSheets(2):Select()

works good

and on another version (mostly Excel 2003, different compilations, but I tried on 3 computers with excel 2007)
it doesn't work at all.

I think, it isn't caused by HMG, but I don't know where to search solution of this problem...

Best regards, Marek
User avatar
Alex Gustow
Posts: 290
Joined: Thu Dec 04, 2008 1:05 pm
Location: Yekaterinburg, Russia
Contact:

Re: How to Export to Excel

Post by Alex Gustow »

Marek, sorry for delay... but I haven't Office 2003 (or 2007) at home or at work - so next week I'll do some experiments at my friend's comp (he has 2007). I understand what "trick" you need - it's interesting for me too...

About your 1st problem - my answer helps you?
User avatar
luisvasquezcl
Posts: 1258
Joined: Thu Jul 31, 2008 3:23 am
Location: Chile
Contact:

Re: How to Export to Excel

Post by luisvasquezcl »

Hi Alex,
I tried your example and export really is much faster to do it line by line
Thank you, an excellent contribution.
Regards,
Luis Vasquez.
Post Reply