Emulating Download To Excel grid option in PeopleSoft

In the past few weeks I had the opportunity to work on a page that displays the information related to client’s financial documents, depending on the criteria selected by the user. The resulting grid was often too large to handle so the requirement was to simulate the download to excel grid button Grid to Excel, only when the row count was greater to an X value and, at the same time, avoid filling the results grid with the content just queried.

A simple way to achieve this is through the combination of two APIs:  Query and iScript.

Part One – Query Classes

  1. Create a Rowset object with the record used to extract the desired data.

    Local Rowset &rs = CreateRowset(Record.RECORDNAME);
    &rs.Fill(“Where clause”);

  2. Now get the Query object and create a file to store the information.

    Local objecNot &qryObject = %Session.GetQuery();
    &exportFile = GetFile(“filename”, “W”, “UTF8”);

  3. With the file open, use the FormatResultString to apply excel formatting to the rowset content and add it to the exportFile.

    &strExcel = &qryObject.FormatResultString(&rs, %Query_XLS, 1, &rs.RowCount);
    &exportFile.WriteLine(&strExcel);
    &exportFile.Close();

  4. Generate iScript url and navigate to it.

    &sURL = GenerateScriptRelativeURL(%Portal, %Node, Record.WEBLIB_GEN_XLS, Field.ISCRIPT1, “FieldFormula”, “IScript_ToExcel”);
    ViewContentURL(&sURL);

Part Two – iScript Classes

Generate an iScript function on FieldFormula event and inside it, get the file previously populated in order to return its string content on the Response.Write method.

%Response.SetContentType(“application/vnd.ms-excel;”);
%Response.SetHeader(“content-disposition”, “attachment;filename=” | &file_name);
If &fExcel.IsOpen Then
%Response.Write(&fExcel.GetString());
End-If;

If you need help or clarification with any of these points, do not hesitate to contact me.

For more information related to the APIs:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s