PeopleCode

Creating MS EXCEL Using CreateObject in PeopleSoft

Creating MS EXCEL Using CreateObject in PeopleSoft

Here are the few examples how to create EXCEL file and how to read EXCEL file using PeopleCode. This can be used in AppEngine And Online PeopleCode.

This can be used in Excel Reporting and Formatting Excel file (Like changing cell colour etc.… ).

/* Set up the Excel COM objects and open the template file */

Local object &oWorkApp, &oWorkBook;

&oWorkApp = CreateObject("COM", "Excel.Application");

&oWorkApp.DisplayAlerts = "False";

&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

&oWorkBook.Open("C:\some_path_to\template.xls");

&oWorkSheet = &oWorkApp.Worksheets("Sheet1");

&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");

&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";

&oWorkSheet.Cells(1, 1).Font.Bold = True;

&oWorkApp.ActiveWorkBook.Save();

&oWorkApp.ActiveWorkBook.Close();

&oWorkApp.DisplayAlerts = "True";

&oWorkApp.Quit();

/*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.add();

&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("E:\Personal\dummy\TestXLS.xls");

/*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("E:\Personal\dummy\TEST1.xls");

&oWorkSheet = &oWorkApp.Worksheets("Sheet1");

&oData = &oWorkSheet.Range("A1").Value;

&oWorkSheet.Range("A2").Value = &oData;

&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\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("E:\Personal\dummy\TEST1.xls");

&oWorkSheet = &oWorkApp.Worksheets("Sheet1");

&oData = &oWorkSheet.Range("A1").Value;

&oWorkSheet2 = &oWorkApp.Worksheets(2);

&oWorkSheet2.Range("A1").Value = &oData;

&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\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("E:\Personal\dummy\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("E:\Personal\dummy\TestXLS.xls");

/*Transfer the data to Excel from Rowset*/

&oWorkApp = CreateObject("COM", "Excel.Application");

ObjectSetProperty(&oWorkApp, "Visible", True);

&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

&oWorkBook.Open("E:\Personal\dummy\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("E:\Personal\dummy\TestXLS.xls");

/*Save an xls file as a CSV*/

&oWorkApp = CreateObject("COM", "Excel.Application");

ObjectSetProperty(&oWorkApp, "Visible", True);

&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");

&oWorkBook.Open("E:\Personal\dummy\TEST1.xls");

&oWorkApp.ActiveWorkbook.SaveAs("E:\Personal\dummy\TestXLS.csv", 6);