One of the powerful features of SEQUEL is its ability to extract data from the System i and create PC-compatible output files in a wide array of formats. You can save SEQUEL output files (to your PC or network drives) or e-mail them automatically (using the version of Help/Systems’ ESEND e-mail file and report distribution software that comes with SEQUEL.). This article focuses on saving results in Excel (XLS) format from a Web browser.

Let’s look at an example from the SEQUELEX library shipped with SEQUEL. The DDREGON view creates results that can be displayed in ViewPoint or the Web interface.

Remote users need Excel format
SEQUEL users often ask, “Can remote users running SEQUEL Web Interface save their results in Excel format?”

One way to accomplish this is to display the results in the browser with the SEQUEL Web Interface, then use the File > Save As option to save the HTML file to a local drive. When Excel opens the file, it converts HTML to its own format. The problem with this method is that you must review the file to remove unwanted headings, footers, and other formatting from the spreadsheet. In addition, you may need to address character/numeric data conversion issues.

SEQUEL scripting provides a better way
SEQUEL scripting lets you save multiple commands within a script object. Scripting gives you much of the capability of CL programming without requiring technical programming knowledge.

To continue with our example, let’s create a SEQUEL script that allows remote users to request results in native Excel format within a browser window. Enter the following commands in the SEQUEL script editor, replacing directory/file references with your own:

10 MONMSG

20 RMVLNK OBJLNK(’/tmp/ddxls*.xls’)

Where MONMSG is a standard i5/OS command that monitors system escape messages and RMVLINK removes previous output files created with this script. Continue with:

30 EXECUTE VIEW(SEQUELEX/DDREGON) PCFMT(*XLS) TOSTMF(’/tmp/ddxls&&jobnbr.xls’) REPLACE(*YES)

The SEQUEL EXECUTE command creates output files from the System i database. In this example, the DDREGON view extracts the data and saves the result in XLS format. The TOSTMF parameter puts the file in an IFS location.

Note: This script is designed to be flexible so that multiple users can run it at the same time. The flexibility comes from starting the file name with the ddxls prefix and indicating a variable value with double ampersands (&&). In this case, the variable is the system job number. Click on the Variables tab within the Script editor to define the variable.

Next, enter the following command:

40 SWIOPEN NEW(*YES) TITLE(’Customer Summary’) ROWSET((100)) COLSET((1 100 ‘http://servername/tmp/ddxls&&jobnbr.xls’))

The SEQUEL Web Interface command SWIOPEN opens a new browser window to display a URL or file (be sure “Allow pop-ups” is selected when you run this example). In this case, the full http: address of the Excel file placed in the IFS in the previous step is specified.

Finally, enter the following command:

50 SWIOPEN NEW(*NO) TITLE(’SEQUEL Web Interface’) ROWSET((100)) COLSET((1 100 ‘http://servername/SEQUEL’))

This second SWIOPEN command returns the original browser window to the SEQUEL Web Interface prompt page.

This script displays results in a browser in native Excel format, and you can save the file to your local PC or a network drive.

This entry was posted on Wednesday, February 21st, 2007 at 10:10 am and is filed under Excel, SEQUEL Web Interface. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.