Creating Word Document Using App Engine!

Now, who's up for some ice cream? Yes, you've heard me.. for me Word Document is like an ice cream at least compare to text (.txt) document. Did you know that you can create a formatted word document from PeopleSoft using App Engine/PeopleCode? Just like creating Excel Document (discuss in my previous post Creating MS EXCEL Using CreateObject in PeopleSoft) I used the CreateObject PeopleCode Function to create Word Document. Note: I only tested this code thru NT Server.

&oWORD = CreateObject("COM", "Word.Application");
ObjectSetProperty(&oWORD, "Visible", True);

&oWORD.Documents.Add();
&oPara1 = &oWORD.Selection;
&oPara1.Style = "Heading 1";
&oPara1.TypeText("Hello World ");
&oPara1.Font.Bold = True;
&oPara1.TypeText("Bold ");
&oPara1.Font.Bold = False;
&oPara1.Font.Italic = True;
&oPara1.TypeText("Italic ");
&oPara1.Font.Italic = False;
&oPara1.Font.Underline = True;
&oPara1.TypeText("Underline ");
&oPara1.Font.Underline = False;
&oPara1.Font.Name = "Arial";
&oPara1.TypeText("Arial ");
&oPara1.Font.Name = "Times Roman";
&oPara1.Font.Size = "16";
&oPara1.TypeText("Times Roman ");
&oPara1.Font.Size = "12";
&oPara1.TypeText("Example ");
&oPara1.Start = "50";
&oPara1.End = "59";
&oPara1.Font.Name = "Tahoma";
&oWORD.ActiveDocument.SaveAs("C:\temp\Format.doc");
&oWORD.Quit();

Creating MS EXCEL Using CreateObject in PeopleSoft

Application Engine is great for batch processing, but it is not a reporting tool. Recently, our users requires every single AE to have a formatted output report, so my teammate came up with the solution that writes .txt file using writeline function. Its very simple, you just need to code it manually. Getting bored of the text file that comes out on my report, I look for some other ways to create a more decent output. Of course, XML Publisher would have been nice but I dont want to go thru that hassle creating a report definition, data source, etc. I browse thru PeopleBooks and found this PeopleCode function CreateObject! It has an example using Excel.Application, a few trial and error, and voala! I created an Excel Report! I only tested it on the NT Server though.

/*How to read data from one cell and writes to another*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*How to read data from one cell and writes to different sheet*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkBook = &oWorkApp.Workbooks.Add();
&rs_Awards = CreateRowset(Record.PERTBL);
&rs_Awards.Fill("WHERE FILL.YEAR = '2008' AND FILL.STATUS = 'C'");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
For &ie = 1 To &rs_Awards.activerowcount
&oWorkSheet.Cells(&ie, 1).Value = &rs_Awards.getrow(&ie).PERTBL.ID.Value;
&oWorkSheet.Cells(&ie, 2).Value = &rs_Awards.getrow(&ie).PERTBL.COMP.Value;
End-For;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Save an xls file as a CSV*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.csv", 6);