Exporting to Excel

[bohrmann]

Hello,
Is there any way to export data from a character based Progress application to Excel (along with formatting cells)? Originally I took the xml code of the destination Excel file, and created a program which generates the same xml code. At the end I only had to rename the xml file to xls and it can be opened by Excel. But the newest version of Excel (2007) gives a warning message when opening that xls, and it's not acceptable for the customer. (Though the file is opened after just clicking on the message.) I know that Excel structure has been changed, maybe I should add some other files as well to the xml? Or I could generate an xls (xlsx) file in a simple way?

Thanks in advance.

[ss_kiran]

Hello,
Is there any way to export data from a character based Progress application to Excel (along with formatting cells)? Originally I took the xml code of the destination Excel file, and created a program which generates the same xml code. At the end I only had to rename the xml file to xls and it can be opened by Excel. But the newest version of Excel (2007) gives a warning message when opening that xls, and it's not acceptable for the customer. (Though the file is opened after just clicking on the message.) I know that Excel structure has been changed, maybe I should add some other files as well to the xml? Or I could generate an xls (xlsx) file in a simple way?

Thanks in advance.

Probably something like this?? The below code exports some fields from customer table in the sportsdatabase.

DEF VAR vchExcel AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkBook AS COM-HANDLE NO-UNDO.
DEF VAR vchWorkSheet AS COM-HANDLE NO-UNDO.
DEF VAR vRow AS INT NO-UNDO.
CREATE "Excel.Application":U vchExcel.

ASSIGN
vchExcel:VISIBLE = false
vchWorkBook = vchExcel:WorkBooks:ADD()
vchWorkSheet = vchExcel:Sheets:ITEM(1).

FOR EACH Customer Where Customer.CustNum < 100 NO-LOCK:
ASSIGN
vRow = vRow + 1
vchWorkSheet:Range("A":U + STRING(vRow)):VALUE = Customer.CustNum
vchWorkSheet:Range("B":U + STRING(vRow)):VALUE = Customer.Name
vchWorkSheet:Range("C":U + STRING(vRow)):VALUE = Customer.CreditLimit
vchWorkSheet:Range("D":U + STRING(vRow)):VALUE = Customer.Balance.
END.
vchWorkBook:SaveAs("c:\temp\sample.xlsx",,,,,,,).

/* RELEASE OBJECT vchWorkSheet. */
/* RELEASE OBJECT vchWorkBook. */
/* RELEASE OBJECT vchExcel. */

vchWorkBook:CLOSE.
RELEASE OBJECT vchWorkSheet.
RELEASE OBJECT vchWorkBook.
vchExcel:QUIT.
RELEASE OBJECT vchExcel.

[bohrmann]

Thanks for the info, something similar is that I expected, just I'm not convinced that it's possible to format the output file (colors, borders, font size etc.). Am I wrong?

[lord_icon]

Indeed you ARE wrong, yes it CAN be done from the ABL.
Sorry I can not give you any sample syntax at tis time, I am NOT at my workstation. However it IS possible from the ABL !!

[ss_kiran]

Thanks for the info, something similar is that I expected, just I'm not convinced that it's possible to format the output file (colors, borders, font size etc.). Am I wrong?

Try something like this
vchWorkSheet :range("a1:d1"):FONT:bold = TRUE. /* Make first row BOLD */
vchWorkSheet :range("a1:d1"):FONT:ColorIndex = 22. /* Change Font color*/

These URLs contain more examples,
http://herohog.com/Progress/Excel2.p
http://herohog.com/Progress/Excel3.p
http://herohog.com/Progress/Excel4.p

HTH

[joey.jeremiah]

you might want to look at this

http://www.oehive.org/project/libxlsx