How to Export to Excel
Moderator: Rathinagiri
Re: How to Export to Excel
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
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
- 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
Thanks a lot.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
Re: How to Export to Excel
I found on internet some informations about export to excel.
When I test it, I will describe my work.
Marek
When I test it, I will describe my work.
Marek
- Alex Gustow
- Posts: 290
- Joined: Thu Dec 04, 2008 1:05 pm
- Location: Yekaterinburg, Russia
- Contact:
Re: How to Export to Excel
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):
It will work VEEERY long (if you have hundreds records)!..
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:
And we have "what we need" - but speed of process is much more
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")![Confused :?](./images/smilies/icon_e_confused.gif)
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).
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
//
![Crying or Very Sad :cry:](./images/smilies/icon_cry.gif)
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
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
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!
![Smile :)](./images/smilies/icon_e_smile.gif)
Sorry for my "not the best" English (if it really "not the best")
![Confused :?](./images/smilies/icon_e_confused.gif)
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).
- 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
Hearty welcome to the forum.
Your programming concept and logic is fantastic Alex Gustow.
Thanks for sharing this with us.
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.
South or North HMG is worth.
...the possibilities are endless.
Re: How to Export to Excel
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:
On some computers program generates error:
![Image](http://www.mol-systemy.com.pl/kern/error.jpg)
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:
and I can't find reason for this error.
Did you meet such a situation?
Best regards, Marek
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:
On some computers it works great - worksheet "Aktivitäten" is beeng selected.oExcel := CreateObject( "Excel.Application")
oExcel:Workbooks:Open( plikLBH)
oExcel:WorkSheets("Aktivitäten"):Select()
On some computers program generates error:
![Image](http://www.mol-systemy.com.pl/kern/error.jpg)
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:
but the same error has occured.oExcel:WorkSheets(2):Select()
and I can't find reason for this error.
Did you meet such a situation?
Best regards, Marek
- Alex Gustow
- Posts: 290
- Joined: Thu Dec 04, 2008 1:05 pm
- Location: Yekaterinburg, Russia
- Contact:
Re: How to Export to Excel
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):
...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:
...and someone told about my "fantastic logic"?..
Primary-school mistake! ![Confused :?](./images/smilies/icon_e_confused.gif)
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()
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
![Confused :?](./images/smilies/icon_e_confused.gif)
I forgot to write EXIT from DO...ENDDO (and we have "neverending story"
![Wink ;)](./images/smilies/icon_e_wink.gif)
Code: Select all
...
i := 0
// now we ready to next step :)
// is work ended? if "yes" - EXIT
if eof()
exit
endif
//
endif
enddo
![Smile :)](./images/smilies/icon_e_smile.gif)
![Confused :?](./images/smilies/icon_e_confused.gif)
Re: How to Export to Excel
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
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
- Alex Gustow
- Posts: 290
- Joined: Thu Dec 04, 2008 1:05 pm
- Location: Yekaterinburg, Russia
- Contact:
Re: How to Export to Excel
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?
About your 1st problem - my answer helps you?
- luisvasquezcl
- Posts: 1258
- Joined: Thu Jul 31, 2008 3:23 am
- Location: Chile
- Contact:
Re: How to Export to Excel
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.
I tried your example and export really is much faster to do it line by line
Thank you, an excellent contribution.
Regards,
Luis Vasquez.