Getting the Excel output using html format using people code.
Local Record &Rec;
Local Rowset &rowSet;
Local File &file1, &file2;
Local SQL &SQL1, &SQL2, &SQL3;
Local string &PresidentsHornor;
&CUR_DATE = DateTimeToLocalizedString(%Datetime, "MMddyyyy");
&OPRID = %UserId;
&PI = XXX_PRSDNT_AET.PROCESS_INSTANCE.Value;
/* Output File Processing */
&FILE_NAME = "President_List_Success_" | &CUR_DATE | "_" | &PI | ".xls";
&ERROR_FILE = "President_List_Error_" | &CUR_DATE | "_" | &PI | ".xls";
&file1 = GetFile(&FILE_NAME, "W", %FilePath_Relative);
&file2 = GetFile(&ERROR_FILE, "W", %FilePath_Relative);
&PresidentsHornor = "President’s Honors";
&HTML_Success = "<html> <font face = 'Trebuchet MS'> <table border = 1> <tr style='font-size:10pt' ><th bgcolor = "#99CCFF" >EMPLID</th><th bgcolor = "#99CCFF" >HOME CAMPUS</th><th bgcolor = "#99CCFF" >DT_RECVD</th><th bgcolor = "#99CCFF" >STRM</th><th bgcolor = "#99CCFF"> AWARDCODE</th><th bgcolor = "#99CCFF" >DESCRFORMAL</th></bgcolor> ";
&HTML_Error = "<html> <font face = 'Trebuchet MS'> <table border = 1> <tr style='font-size:10pt' ><th bgcolor = "#99CCFF" >EMPLID</th><th bgcolor = "#99CCFF" >HOME CAMPUS</th><th bgcolor = "#99CCFF" >DT_RECVD</th><th bgcolor = "#99CCFF" >STRM</th><th bgcolor = "#99CCFF"> AWARDCODE</th><th bgcolor = "#99CCFF" >DESCRFORMAL</th><th bgcolor = "#99CCFF"> ERROR MESSAGE</th></bgcolor> ";
If &file1.IsOpen Then
&rowSet = CreateRowset(Record.XXX_PRSDNT_TBL);
&NUM_READ = &rowSet.Fill("WHERE PROCESS_INSTANCE= :1", XXX_PRSDNT_AET.PROCESS_INSTANCE.Value);
&file1.WriteLine(&HTML_Success);
&SQL1 = CreateSQL("SELECT EMPLID,CAMPUS,DATE_RECEIVED,XXX_RUNTERM,AWARD_CODE FROM %Table(XXX_PRSDNT_TBL) WHERE PROCESS_INSTANCE = :1 and TO_CHAR(DESCRLONG) = 'UPD'", &PI);
While &SQL1.Fetch(&Emplid, &Campus, &DtRecvd, &Runterm, &AwardCode)
&Success_Data = "<html> <font face = 'Trebuchet MS'><table border = 1> <tr style='font-size:10pt'><td>" | &Emplid | " </td><td>" | &Campus | " </td><td>" | DateTimeToLocalizedString(&DtRecvd, "dd-MMM-yy") | " </td><td>" | &Runterm | " </td><td>" | "PRES" | " </td><td>" | &PresidentsHornor | " </td> ";
&file1.WriteLine(&Success_Data);
End-While;
End-If;
If &file2.IsOpen Then
&rowSet = CreateRowset(Record.XXX_PRSDNT_TBL);
&NUM_READ = &rowSet.Fill("WHERE PROCESS_INSTANCE= :1", XXX_PRSDNT_AET.PROCESS_INSTANCE.Value);
&file2.WriteLine("EMPLID" | Char(9) | "HOME CAMPUS" | Char(9) | "DT_RECVD" | Char(9) | "STRM" | Char(9) | "AWARD CODE" | Char(9) | "DESCRFORMAL" | Char(9) | "Error Message");
&file2.WriteLine(&HTML_Error);
&SQL2 = CreateSQL("SELECT EMPLID,CAMPUS,DATE_RECEIVED,XXX_RUNTERM,AWARD_CODE,DESCRLONG FROM %Table(XXX_PRSDNT_TBL) WHERE PROCESS_INSTANCE = :1 and TO_CHAR(DESCRLONG) <> %TEXTIN(:2) and TO_CHAR(DESCRLONG) <> ' '", &PI, "UPD");
While &SQL2.Fetch(&Emplid, &Campus, &DtRecvd, &Runterm, &AwardCode, &ErrorMesge)
&Error_Data = "<html> <font face = 'Trebuchet MS'><table border = 1> <tr style='font-size:10pt'><td>" | &Emplid | " </td><td>" | &Campus | " </td><td>" | DateTimeToLocalizedString(&DtRecvd, "dd-MMM-yy") | " </td><td>" | &Runterm | " </td><td>" | "PRES" | " </td><td>" | &PresidentsHornor | " </td><td>" | &ErrorMesge | " </td> ";
&file2.WriteLine(&Error_Data);
End-While;
End-If;
&file1.Close();
&file2.Close();
The output file should dispaly as below.
file1:
file2:
Comments
Post a Comment