Generate Excel file using PeopleCode/Application engine

To generate Excel file using PeopleCode make sure that any Microsoft excel template file exists in the excel folder. For example in the below PeopleCode I am using “template.xlt” Microsoft excel template file, if you are not using any formula in the Excel sheet then in the template file data type of all the cell should be TEXT to avoide eliminating leading zero from any number which should be treated as string. And one more thing while generating excel file make sure that code is being executed in the server where Microsoft excel is installed. For example if you are generating excel file using application engine then make sure process should be executed in PSNT process scheduler server where Microsoft excel has been already installed as in Unix server it will not work and process error out. Similarly if using normal PeopleCode then make sure your Application server is in PSNT and Microsoft excel is already installed.

----------------SAMPLE CODE----------------------

Local object &oWorkApp, &oWorkBook;

Local PostReport &RPTINFO;

Local number &RPTINSTANCE;

&PrcsInstace = TEST_TREEDT_AET.PROCESS_INSTANCE;
&SetID = TEST_TREEDT_AET.SETID;
&TreeName = TEST_TREEDT_AET.TREE_NAME;
&EffDt = String(TEST_TREEDT_AET.EFFDT);
&Field = TEST_TREEDT_AET.FIELDNAME;


SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist WHERE prcsinstance = :1", &PrcsInstace, &Server);


&find = 0;
&find = Find("NT", &Server);

If &find <> 0 Then
&reportPath = GetEnv("PS_HOME") |"\excel\";
&reportOutPutPath = GetEnv("PS_SERVDIR") |"\log_output\AE_TEST_TREEDTL_" |&PrcsInstace |"\" |&SetID |"-" |&TreeName|"-" |&EffDt |"-" |&PrcsInstace |".xls";
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open(&reportPath |"template.xlt");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs(&reportOutPutPath);


&oWorkSheet.Cells(1, 1).Value = "Set ID";
&oWorkSheet.Cells(1, 1).Font.Bold = True;
&oWorkSheet.Cells(1, 2).Value = &SetID;
&oWorkSheet.Cells(1, 2).Font.Bold = True;


&oWorkSheet.Cells(2, 1).Value = "Tree Name";
&oWorkSheet.Cells(2, 1).Font.Bold = True;
&oWorkSheet.Cells(2, 2).Value = &TreeName;
&oWorkSheet.Cells(2, 2).Font.Bold = True;

&oWorkSheet.Cells(3, 1).Value = "Effective Date";
&oWorkSheet.Cells(3, 1).Font.Bold = True;
&oWorkSheet.Cells(3, 2).Value = &EffDt;
&oWorkSheet.Cells(3, 2).Font.Bold = True;



&SQL1 = CreateSQL("SELECT DISTINCT DESCR254||'>' , CHARTFIELD_VALUE ,DESCR,Length(DESCR254)-Length(replace(DESCR254,'>',null))+1 FROM %TABLE(TEST_TRDT_TMP) WHERE PROCESS_INSTANCE = :1 ORDER BY 1,2", &PrcsInstace);

SQLExec("SELECT DISTINCT B.LONGNAME FROM PSDBFLDLABL A, PSDBFLDLABLLANG B WHERE A.FIELDNAME = B.FIELDNAME AND A.LABEL_ID = B.LABEL_ID AND A.DEFAULT_LABEL = '1' AND A.FIELDNAME = :1", &Field, &FieldLabel);

SQLExec("SELECT Max(Length(DESCR254)-Length(replace(DESCR254,'>',null)))+1 FROM %TABLE(TEST_TRDT_TMP) WHERE PROCESS_INSTANCE = :1 ", &PrcsInstace, &MaxNodeLevel);

If None(&FieldLabel) Then
     &FieldLabel = "Tree leaf";
End-If;


For &j = 1 To &MaxNodeLevel
   &oWorkSheet.Cells(5, &j).Value = "Node Number " |String(&j);
   &oWorkSheet.Cells(5, &j).Font.Bold = True;
End-For;

&oWorkSheet.Cells(5, &MaxNodeLevel + 1).Value = &FieldLabel;
&oWorkSheet.Cells(5, &MaxNodeLevel + 1).Font.Bold = True;
&oWorkSheet.Cells(5, &MaxNodeLevel + 2).Value = "Description";
&oWorkSheet.Cells(5, &MaxNodeLevel + 2).Font.Bold = True;

/*Write detail information*/
&i = 6;
While &SQL1.Fetch(&Path, &ChartField, &Decr, &CurrNodeLevel)

For &j = 1 To &CurrNodeLevel
&PathLength = Len(&Path);
&find = Find(">", &Path);
&NodeLevel = Left(&Path, &find - 1);
&Path = Right(&Path, &PathLength - &find);
&oWorkSheet.Cells(&i, &j).Value = &NodeLevel;
End-For;

&oWorkSheet.Cells(&i, &MaxNodeLevel + 1).VALUE = &ChartField;
&oWorkSheet.Cells(&i, &MaxNodeLevel + 2).Value = &Decr;
&i = &i + 1;

End-While;

/* Save Excel file and quit */

&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.Quit();

Else

MessageBox(0, "", 0, 0, "Process is not being executed in NT Server");

End-If;

----------------------------------------------------------------------------------------