Working with XLS files in Sui

A XLS file is a spread sheet file understood by Spread sheet tools such as Microsoft Excel and Open Office Calc.

Sui is capable of creating a native XLS file from a SQL result-set. This can be achieved in two ways:

1. Execute the query and directly create a XLS file

2. Export an existing result-set from the Report window to an XLS file

If you are running on Windows and have MS-Excel installed you can choose to directly launch Excel when the XLS file is created.

To set up Sui to automatically invoke Excel, select File->Preferences and the Export tab. Set the parameters as described in the graphics.

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_46730d8f.jpg$

Note: If you are running versions of Windows prior to XP, it might be necessary to set the launch command to: cmd /c start Excel

To execute a query and present the result set directly in Excel right click in the query window and select Exec->XLS from the menu (or select the fast path ctrl-t).

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_44e0b371.jpg$

The result is presented as a spread sheet with two sheets.

1. Sui-Export, contains the query result-set.

2. Sui-Meta, contains information about the query, a sample is shown below:

If you have selected to launch the XLS file is launched with the created spreasheet.

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_m3e58863c.png$

Meta Data:

Spreadsheet created by Sui 0.64B

Created at Sat Nov 12 17:34:58 CET 2006

Driver COM.ibm.db2.jdbc.app.DB2Driver

URL jdbc:db2:sample

Userid Db2Admin

SQL-Stmt SELECT * FROM STAFF

Result set status full result set

Report rows 39

The name of the spreadsheet is based on the time when it was created and it's stored in the Sui data path (File -> Sui preferences-> Misc). You can of course use Save As in Excel to save the file with a name of your choice.

Exporting to an XLS file from the Query report

You can also create a XLS spreadsheet by exporting data from the query report window.

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_7dd184e6.jpg$

Using Open Office

It's very easy to customize Sui to launch “Open Office Calc” rather than Excel, if your preferences are set up as described in the graphic below, Sui will launch the program you have associated to XLS files.

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_46730d8f.jpg$

If you are using Windows versions prior to XP you might need to Change XLS file launch CMD to the path and program name of “Open Office Calc” (for example "C:\Program\OpenOffice.org 2.0\program\scalc").

A Sui result-set in OpenOffice:

$Beskrivning: Beskrivning: C:\Users\kjell\Dropbox\SuiSource\SuiSite\TipWeek_html_m31eca621.jpg$