Page 2 of 4

Re: How to Export to Excel

Posted: Sun Nov 30, 2008 1:51 am
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

Re: How to Export to Excel

Posted: Mon Dec 01, 2008 7:42 am
by Rathinagiri
Thanks a lot.

Re: How to Export to Excel

Posted: Tue Dec 02, 2008 9:46 am
by mol
I found on internet some informations about export to excel.
When I test it, I will describe my work.
Marek

Re: How to Export to Excel

Posted: Thu Dec 04, 2008 3:00 pm
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).

Re: How to Export to Excel

Posted: Thu Dec 04, 2008 5:12 pm
by Rathinagiri
Hearty welcome to the forum.

Your programming concept and logic is fantastic Alex Gustow.

Thanks for sharing this with us.

Re: How to Export to Excel

Posted: Thu Dec 04, 2008 6:52 pm
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

Re: How to Export to Excel

Posted: Fri Dec 05, 2008 8:42 am
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! :?

Re: How to Export to Excel

Posted: Fri Dec 05, 2008 8:57 am
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

Re: How to Export to Excel

Posted: Sat Dec 06, 2008 11:26 pm
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?

Re: How to Export to Excel

Posted: Wed Dec 10, 2008 12:48 pm
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.