Generating Spreadsheets from Peoplesoft Application Engines

In my last post I shared the File Conversion Utility, and inside the project are a couple of nice Easter eggs.  But the one I am going to blog about today is how the utility converts a rowset into a XML Spreadsheet 2003. Most of the credit should go to David Vandiver, whose SQR2XLS project inspired me to not just generate spreadsheets in Application Engines, SQR's and WebLibs!  But also inspired me to realize I could write and application engine that could parse an XML Spreadsheet and develop a really cool utility that would make my job easier.  So back to generating Spreadsheets from application engines.

If you have downloaded the project and installed it in a development environment then check out the Report Step under the Main Section.  This is the step that generates the errors in a spreadsheet.  Below is the code:


Declare Function GetFileName PeopleCode ZZ_INTFC_FUNCLB.FILENAME FieldFormula;
Declare Function xml_init_file PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_init_style PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_init_worksheet PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_init_col PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_init_row PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_cell_char PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_format_header_or_footer PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_finalize_worksheet_pagesetup PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_finalize_worksheet_print PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_finalize_worksheet_split PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_finalize_worksheet_allowoptions PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function xml_finalize_workbook PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function BUILD_XML_STYLES PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Declare Function ROWSET_TO_CELLS PeopleCode ZZ_XMLLINK.XMLFILE FieldFormula;
Global string &HTMLstring;
Global File &Z_XML_XLS;
Local Record &TAO, &PER_DATA;
Global Rowset &cnv_stg;
Local Rowset &hide_flds, &rpt_flds;
Local SQL &getdata;

Function build_header()
  
   Local number &COL;
   &COL = 0;
   For &flds = 1 To &rpt_flds.ActiveRowCount
      xml_init_col((&COL = &COL + 1), 0, 78.25, 0, 0);
   End-For;
  
  
   xml_init_row(1, 0, 50, 0, "S1");
   For &flds = 1 To &rpt_flds.ActiveRowCount
      xml_cell_char(&rpt_flds(&flds).ZZ_RPT_FIELDS.FIELDNAME.Value, 0, 0);
   End-For;
  
End-Function;
Function build_Report
   &FileName = "ERRORS.XLS";
   &FileName = "\TEMP\" | &FileName;
   &Z_XML_XLS = GetFile(GetFileName("ZZ_XML_CNV", ZZ_CNV_AET.PROCESS_INSTANCE.Value, "errors", "xls", ""), "w", "a", %FilePath_Absolute);
  
   xml_init_file(0, 0, 0, 0, 0, 0, 0, 0);
   BUILD_XML_STYLES("ZZ_XML_CNV");  
   xml_init_worksheet("Results", 0, 0);
   build_header();
  
   ROWSET_TO_CELLS("ZZ_XML_CNV", &cnv_stg, 4);  
   xml_finalize_worksheet_split(1, 0, 1, "");
   xml_finalize_worksheet_pagesetup("Landscape", "", 0.75, 0.25, 0.75, 0.25, "", "");
   xml_finalize_worksheet_print(1, 1000, 0, 75, 0);
   xml_finalize_workbook();
End-Function;


&hide_flds = CreateRowset(Record.ZZ_HIDE_FIELDS);
&hide_flds.Fill("WHERE TEMPLATE_ID = :1", ZZ_CNV_AET.TEMPLATE_ID);
&rpt_flds = CreateRowset(Record.ZZ_RPT_FIELDS);
&rpt_flds.Fill("WHERE TEMPLATE_ID = :1", ZZ_CNV_AET.TEMPLATE_ID);
&rpt_flds.Sort(ZZ_RPT_FIELDS.FIELDNUM, "A");

For &D = &cnv_stg.ActiveRowCount To 1 Step - 1
   If &cnv_stg(&D).GetRecord(1).ZZ_PROCESS_IND.Value = "C" Then
      &cnv_stg.DeleteRow(&D);
   Else
      For &flds = 1 To &hide_flds.ActiveRowCount
         &FieldName = "Field." | &hide_flds(&flds).ZZ_HIDE_FIELDS.FIELDNAME.Value;
         &cnv_stg(&D).GetRecord(1).GetField(@&FieldName).Visible = False;
      End-For;
   End-If;
End-For;

build_Report();





To utilize this code you first need define your work record that you will be using to generate the rowset in the application engine.  The only requirement for this work record is that it contain the fields, ZZ_ROW_STYLE and HEIGHT, I usually include these two fields at front of the record as the ROWSET_TO_CELLS function has a Start with parameter that accounts for these two work fields to being in Column 1 and 2.  Also, if you want to include other work fields within your rowset, then include them to the front of the record and adjust the start parameter to not include these fields on your spreadsheet.  The example code above will use the Long Name from the work record as column headers.

Example Work Record:

 

 After you have identified what columns are included in your spreadsheet you need to create excel styles and associate them to the ZZ_ROW_STYLE within your work record.  This allows the process to associate what styles are used for Character, Date and number fields within your rowset.

First build the excel styles used by your spreadsheet, the excel styles component allows you to define styles and reuse them in multiple spreadsheets and can be found in the File Conversion utility project.

Excel Styles

Use this Componet to create your Excel Styles.


Excel Style



Process Styles Tab 1

Use this component to associate your styles to your Report you are generating.  All styles used by your report must be added to the report process name.

Row Styles

Use this page in the Process Styles to associate the styles to a row style and then the field types to an excel style.  This will associate all date fields within the row to the DT1 style.  You can associate multiple row styles to one Process Name, since you might have a scenario where you are representing one style on one row, but on the next you need a different style for that same row.  For instance, if you were generating a spreadsheet that had outstanding balances by Vendor for AP and you wanted to include a Total Row where all the totals were summed and shown in Bold, Then you would need another row style for this total row where your numbers were bold.   The Override Style allows you to select one to many fields within your rowset and override the style by qualifying the FIELDNAME:STYLE.  Then you can add multiple overrides as long as you separate them by a semicolon. 



Once you have gotten to this point you are ready to call the code in the beginning of this post to generate spreadsheet reports with multiple tabs and functions!  Also, if you want to generate a report with multiple tabs and the data in almost the same as the other tabs. Then only create one work record and hide the fields on the rowset that you don't want to print to the report for the current worksheet you are generating.  If you use this technique you will need to loop through the rowset hiding every instance of this field so it won't print.


And this is how I generate xls in PeopleSoft.

Enjoy!