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 , 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
- Create a Rowset object with the record used to extract the desired data.
Local Rowset &rs = CreateRowset(Record.RECORDNAME);
- Now get the Query object and create a file to store the information.
Local objecNot &qryObject = %Session.GetQuery();
&exportFile = GetFile(“filename”, “W”, “UTF8”);
- 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);
- Generate iScript url and navigate to it.
&sURL = GenerateScriptRelativeURL(%Portal, %Node, Record.WEBLIB_GEN_XLS, Field.ISCRIPT1, “FieldFormula”, “IScript_ToExcel”);
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.SetHeader(“content-disposition”, “attachment;filename=” | &file_name);
If &fExcel.IsOpen Then
If you need help or clarification with any of these points, do not hesitate to contact me.
For more information related to the APIs: