Here's a cool use of David Vandiver's Excel XML libraries when mashed up with an the PeopleCode Query API, iScript, and XML Link.
Imagine launching Excel, hitting File > Open, and entering a URL with your query name and prompt values embedded in it, and your query opens in Excel. No code, nVision plug in, or PeopleSoft ODBC driver required.
Here's how it looks to an end-user:
Launch Excel.
Then go to File > Open.
Enter this URL into the Filename text box: http://laptop.ok.cox.net:6600/xmllink/hdmo89/ExecQuery?userid=BRENT&pwd=BRENT &runservice=yes&qryname=PT_OBJSEC_USER&type=public&OPRID=BRENT
You'll notice that your PeopleSoft username and password are parameters in the URL, along with the query name (qryname=PT_OBJSEC_USER), the query type (type=public or type=private), and any bind parameters (oprid=BRENT).
Then hit open and your query will show up in Excel. Look mom - No code on the client! Nice if you hate PeopleSoft's ODBC driver as much as I do.
In addition to David Vandiver's Excel XML libraries for PeopleCode, here's the PeopleCode that makes it possible:
Function IScript_ExecQuery() Returns boolean;
rem Local string &sOutFile;
Local string &QryName;
Local string &RunCntlID;
Local Record &rcdQryRunParms;
Local Record &rcdQryPrompts;
Local SQL &sqlSelectQryParms;
Local number &cnt, &i, &Result;
Local ApiObject &aRunQry, &QryRunTimePrompts;
Local boolean &bPublic;
Local number &nOrigPSMessagesMode;
Local Rowset &aRowSet;
Local XmlDoc &inXMLDoc;
Local boolean &ret;
rem Open the query the user specifies with the QRYNAME parameter on the URL;
&aRunQry = %Session.GetQuery();
&QryName = %Request.GetParameter("QryName");
&bPublic = True;
If %Request.GetParameter("TYPE") = "PRIVATE" Then
&bPublic = False;
End-If;
If &aRunQry.Open(&QryName, &bPublic, False) = 0 Then
rem Get the query prompts, and assign them to parameter values specified in the URL;
&rcdQryPrompts = &aRunQry.PromptRecord;
If &rcdQryPrompts <> Null Then
&QryRunTimePrompts = &aRunQry.RunTimePrompts;
For &i = 1 To &rcdQryPrompts.FieldCount
&rcdQryPrompts.GetField(&i).Value = %Request.GetParameter(&rcdQryPrompts.GetField(&i).Name);
End-For;
End-If;
Else
rem Query didn't open cleanly, return from the function without doing anything;
Return True;
End-If;
rem Run the query to a rowset;
&aRowSet = &aRunQry.RunToRowset(&rcdQryPrompts, 0);
rem Now parse the rowset and create valid XML tags;
If &aRowSet <> Null Then
rem Looks like a good query -- Initialize the Excel worksheet;
xml_init_file(0, 0, 0, 0);
Rem now initialize title, header, and row styles;
xml_init_style(0);
xml_style_font("Arial", 14, 1);
xml_style_alignment("Left", "Bottom", 0, 0);
xml_init_style(1);
xml_style_font("Arial", 12, 1);
xml_style_alignment("Left", "Bottom", 0, 0);
xml_style_border("Bottom", "Continuous", 1);
xml_init_style(2);
xml_style_font("Left", 10, 0);
xml_finalize_style();
xml_init_worksheet(&QryName, 0, 0);
xml_init_col(1, 0, 50, 0, 0);
xml_init_col(2, 0, 125, 0, 0);
xml_init_col(3, 0, 135, 0, 0);
xml_init_col(4, 0, 50, 0, 0);
xml_init_col(5, 0, 110, 0, 0);
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char(&aRunQry.Description, 0, 0);
&aRec = &aRowSet(1).GetRecord(1);
&QrySelOutputFldCol = &aRunQry.QuerySelect.QueryOutputFields;
If &aRowSet.RowCount > 0 Then
rem Rows are being returned. Write them to an Excel sheet;
rem First, write the header row;
xml_init_row(1, 0, 0, 0, 1);
For &j = 1 To &QrySelOutputFldCol.Count
xml_cell_char(&QrySelOutputFldCol.Item(&j).HeadingText, 0, 0);
End-For;
For &i = 1 To &aRowSet.ActiveRowCount
&aRow = &aRowSet(&i);
xml_init_row(1, 0, 0, 0, 2);
For &j = 1 To &aRow.GetRecord(1).FieldCount
If Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "CHAR" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 9) = "LONG CHAR" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 5) = "IMAGE" Then
xml_cell_char(&aRow.GetRecord(1).GetField(&j).Value, 0, 0);
End-If;
If Left(&QrySelOutputFldCol.Item(&j).Format, 6) = "NUMBER" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 6) = "SIGNED" Then
xml_cell_number(&aRow.GetRecord(1).GetField(&j).Value, "99999999999.99", 0, 0);
End-If;
If Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "DATE" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 4) = "TIME" Or
Left(&QrySelOutputFldCol.Item(&j).Format, 9) = "DATE/TIME" Then
xml_cell_datetime(&aRow.GetRecord(1).GetField(&j).Value, 0, 0);
End-If;
End-For;
End-For;
Else
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char("No records retrieved", 0, 0);
End-If;
Else
xml_init_row(0, 0, 25, 0, 0);
xml_cell_char("Failed to retrieve result set", 0, 0);
End-If;
xml_finalize_worksheet_pagesetup("Landscape", "", 0.5, 0.23, 0.5, 0.26, "", "");
xml_finalize_worksheet_print(1, 1000, 0, 75);
xml_finalize_workbook();
&aRunQry.Close();
%Session.PSMessagesMode = &nOrigPSMessagesMode;
If %Session.PSmessages.Count = 0 Then
Return True;
Else
Return False;
End-If;
End-Function; /* ExecQuery */
Once you get the PeopleCode in place, make sure you add it to the web libraries on a permission list, and register it as an XML Link function.
Comments () |
 |
|
|
|
 |
