Tuesday, February 22, 2011

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);







21 comments:

Pratik said...

I am getting the error as "Invalid parameter CreateObject: Excel.Application Invalid class string for function ObjectSetProperty. (180,116) WT_VAL_UG_AE.BFUPG.GBL.default.1900-01-01.Step06.OnExecut e PCPC:175 Statement:2" in /* Set up the Excel COM objects and open the template file */ portion. Can you please find the issue????

Pawan Mundhra said...

Pratik does app server has excel installed?

Pratik said...

I asked my admin to install Excel in PSNT. Now a new kind error I am geting and i.e "Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running". Any Solutions PLZ????

Pawan Mundhra said...

I don't see this is issue with the above code.. Verify if this is issue on your application engine or with other processes also.

Dhana said...

hi Pawan - the above code works fine if the excel is placed in our machine, but then when we run from process scheduler we are not able to open the xlsb file from NT server. Any suggestions??

Pawan Mundhra said...

What is the error on this? Is your NT box has Excel installed..?

Dhana said...

@ Pawan - there is no error that pops up, but then the process stops at the .open command. And yes excel has been installed in NT box...

Pawan Mundhra said...

Is your template or data file present in the app server path? Check the folder and file path in the app server. May be this is causing the issue.

Dhana said...

yes Pawan the file is present in the app server path. we are currently trying to write an excel file and see if it is some access related issue.

pacman said...

Hi Dhana, Pawan, Since we upgraded to Peopletools 8.53.08 two weeks ago, my application engine excel reports aren't working anymore and I get the exact same error as above: Invalid parameter CreateObject: Excel.Application No such interface supported for function ObjectSetProperty. (180,116).
Were you ever able to figure out why this is happening? We have excel loaded on our servers and we've reinstalled them but don't know what to do anymore. Thanks

Srini said...

Hi,
Do you have the code to create two tabs in the same excel sheet using peoplecode ?

Pawan Mundhra said...

Hi Srini
add() method is used for creating new tab... you can program this based on your need....

Pawan Mundhra said...

@pacman, could you please check the excel installation is correct. and this has proper permissions for writing into the server.

Unknown said...

Hi
I am also facing the same above error while running AE through process scheduler. But while running through 2 tire --> Run button, .xls file is generating in temp folder or the specified path.we r using NT server.
Error I am facing:Invalid parameter CreateObject:Excel Application Invalid class string for function objectSetproperty.
Can anybody help me.



Peoplesoft Help said...
This comment has been removed by the author.
Peoplesoft Help said...

Hi Pawan,

Could you help on the same..
Process is going to "No success,posted" with the following message. I am using file attachment functionality to read excel file.
User will load the excel file and then submit the process to run.
I have verified that NT server is installed with excel and the file to be read is in app server sub directories.

4968: 1418714671: PSPAL::Abort: Unrecoverable exception received
4968: 1418714671: PSPAL::Abort: Location: E:\pt853-903-R1-retail\peopletools\src\pspal\exception_winunhandled.cpp:592: PSPAL::UnhandledExceptionFilter::UnrecoverableExceptionFilter
4968: 1418714671: PSPAL::Abort: Generating process state report to D:\XXXX\psoft\pscfg\appserv\prcs\XXXXXX\LOGS\psae.exe.4968\process_state.txt
4968: 1418714671: PSPAL::Abort: Terminating process now.
PeopleTools 8.53.12 - Application Engine
Copyright (c) 1988-2014 Oracle and/or its affiliates.
All Rights Reserved

mantra said...

Hi Pawan,

I tried the code. But i am getting the below error .
OLE Automation error in Workbooks.Open: ObjectDoMethod:
Microsoft Office Excel cannot access the file 'C:\test\testfile.xls'.

Any IDea ?

mantra said...

Hi Pawan,

I tried the code. But i am getting the below error .
OLE Automation error in Workbooks.Open: ObjectDoMethod:
Microsoft Office Excel cannot access the file 'C:\test\testfile.xls'.

Any IDea ?

Bala said...

HI Pawan / Pratik.... I am also facing the same issues like, running from 2-tier is working fine, but thru online, the process status shows - No Success and log shows 'INITIATED' or 'PROCESSING' but no longer running". Any thought?

Unknown said...

Hi mantra,
I am also getting same error, is your error resolved?

Unknown said...

Same error. It works when running in 2 tier but goes to No success while running it online.

Post a Comment