Office .XLS Guide

Catch-e supports advanced spreadsheet formatting for reports generated in Office(xls) and Office(xlsx) outputs. These tools allow you to customise both the look and behaviour of exported reports, including formatting, calculations, and layout control.

How Excel formatting works

All spreadsheets are initially generated using your organisation’s standard formatting settings.

These default settings are stored in system controls and typically define:

  • Report headers and company logo

  • Report title

  • Run date, time, and user information

  • Column headings and data row styling

You can override these defaults using custom formatting rules embedded directly into SQL or scheduler report queries.

Standard spreadsheet structure

By default, Catch-e spreadsheets include:

  • Company logo (header section)

  • Report title

  • Generation metadata (date, time, user)

  • Styled column headings

  • Formatted data rows

  • Gridlines disabled

  • Frozen header rows (for easier scrolling)

  • Adjusted column widths for readability

Large report behaviour

If an Office(xls) or Office(xlsx) report exceeds approximately 65,500 rows, it will automatically be converted to:

Text (CSV) format

This applies to:

  • Fleet / Administration / Reports

  • Reporting / Standard Reports

  • Scheduler / executeQuery jobs

Creating an Excel report (Scheduler)

To generate an Excel file from the Scheduler:

  1. Open Scheduler / Job

  2. Go to Parameters

  3. Select Attachment Format

  4. Choose:

    • Office(xls) or Office(xlsx)

Embedded formulas (Excel calculations)

You can embed Excel formulas directly into report output using SQL.

Example:

'[formula]=A#row_no# * B#row_no#'

This allows dynamic row-based calculations such as:

  • multiplying columns

  • financial calculations

  • derived values per row

The system replaces:

#row_no#

with the actual row number at runtime.

Example result:

= A6 * B6

You can also reference fixed header cells using absolute references.

Custom spreadsheet formatting

You can override default spreadsheet formatting using a special xls control block embedded inside your SQL.

This allows you to customise:

  • headers

  • column styles

  • fonts and colours

  • gridlines

  • layout behaviour

Format block structure

All formatting rules must:

  • sit inside a comment block /* ... */

  • be wrapped in --xls_controls-- ... --xls_controls--

  • end each rule with a semicolon

Example:

/*--xls_controls--
$xls_controls['worksheet']['gridlines_flag'] = 'yes';
$xls_controls['worksheet']['freeze_panes'] = 1;
--xls_controls--*/

Formatting layers

You can apply formatting at different levels:

Worksheet level

Controls overall spreadsheet behaviour:

  • gridlines

  • frozen panes

  • header visibility

  • orientation

  • page setup

Header level

Controls:

  • logo placement

  • report title

  • generated-by information

  • merged header cells

Column heading level

Controls:

  • font style

  • colours

  • alignment

  • wrapping

  • column labels

Data row level

Controls:

  • row formatting

  • numeric and date formats

  • conditional styling

  • column widths

  • text wrapping

Common formatting options

You can control:

Worksheet options

  • gridlines on/off

  • freeze panes

  • header inclusion

  • landscape/portrait mode

  • page size

  • worksheet protection

Data formatting

  • number formats (currency, decimals)

  • date formats (DD-MM-YYYY)

  • text styling (bold, italic, font family)

  • alignment (left, centre, right)

  • column widths

  • text wrap

Example formats:

  • Currency: [$$-C09]#,##0.00

  • Date: DD-MM-YYYY

Header configuration

The header section supports multiple rows:

  • Row 1: logo

  • Row 3: report title

  • Row 4: run details (date/time/user)

Placeholders can be used:

  • #reporttitle#

  • #rundate#

  • #runtime#

  • #runuser#

Column headings

Column headings can be styled globally or per column.

You can also format multi-line headings using:

Client|Name

which renders as:
Client
Name

Data row formatting

Row-level formatting can be applied per column, for example:

  • font colour

  • background colour

  • bold/italic styling

  • alignment

  • width adjustments

You only need to define what you want to change—defaults remain unchanged.

Important notes

  • Incorrect formatting rules will not trigger errors

  • The system will silently revert to default formatting if rules are invalid

  • All rules must end with a semicolon

  • Formatting must strictly follow syntax rules or it will be ignored

Library support

The XLS formatting engine extends a spreadsheet generation library, meaning:

  • advanced Excel-style features are supported

  • additional formatting options may be available beyond defaults