10. MERLIN Tables in Excel

MERLIN can generate tables in “xlsx” files so you can open them in Microsoft Excel – whether you simply want to view them, or maybe create charts. MERLIN’s creation of Excel tables is completely independent of Excel, and may be done without Excel being installed. There are numerous options controlling the output and appearance of these tables, which are introduced in this blog.

Tables and worksheets

Output of tables to Excel is controlled by Run Control Parameter (RCP) XLSTAB, which has three possible settings:

XLSTAB=1       all tables in one Excel worksheet called “Tables”, one after another
XLSTAB=2       each table in a separate Excel file named after the MERLIN table ID
XLSTAB=3       each table in a separate Excel worksheet named after the MERLIN table ID

Settings 1 and 3 also generate a worksheet called “Contents” containing a Table of Contents (TOC) with hyperlinks to the tables themselves. No TOC is generated by setting 2.

All settings automatically suppress output of tables in other formats, except you may specify RCP TABSML if you also want tables in TABSML format.

Plain Excel output

By default, MERLIN produces “plain Excel output” like this:

You will notice that the banner labels (including the header “Class”) are left justified, since that is Excel’s default treatment of text, and zero cells are shown as “0” instead of the usual “-”, since the latter would also be treated as text. This plain output is suitable if you just want to view the figures, or maybe create charts, and the appearance of the tables themselves isn’t important – but it is usually preferable (and very easy) to produce enhanced output.

Enhanced Excel output

By setting RCP XLSID, we can produce “enhanced Excel output” like this:

XLSID causes an “identifier flag” to be placed in column A (hidden by default) which enables MERLIN to format specific rows differently to others. So, by default, a different font is used for the job header, table title and banner labels – and the latter are also boxed and shaded. This is all specified in a “styles” file called “xlstab_std_styles.txt”, which is supplied with the software and used by default – but you can create your own styles files as explained in the next section below.

If you always want to use XLSID with Excel tables, simply set it in your global RCP settings file (MERLIN allows users to define default settings that will be used on every project you run).

Creating your own styles

To create your own style, copy the default styles file from the software installation folder to a folder of your choice, and give it a suitable name, e.g. “my_styles_1.txt”. You can then edit this file, following the explanatory comments to change or insert simple commands relating to the various parts of the table, e.g. there is a line that reads “freeze=off” but, if you change it to “freeze=on”, the Excel “freeze panes” option will be applied so both axes of the table remain visible when you are scrolling. Similarly, you can set “toclink=on” so each table contains a hyperlink back to the TOC.

Cells and fonts can be changed to standard colours, e.g. the banner cells to yellow, the raw numbers font to red, and the vertical percentages font to blue – or you can specify any colour using RGB or hexadecimal values – plus there are many other options, fully described in the MERLIN Help File > USER FILES > “txt” Excel styles file.

To apply your own styles file, use RCP XLSTYLES, e.g.

XLSSTYLES= my_styles_1.txt

Here is a table produced using a styles file, with the changes described above:

MERLIN formats

MERLIN itself has hundreds of formats and label controls affecting tabular output – but not all of these are relevant to Excel tables, and some settings will be ignored. These include all that control page breaks, text spacing, indentation and justification – since these are irrelevant, or can be specified in the styles file. See the MERLIN Help File chapters about FORMATS and LABEL CONTROLS for full details of which settings are applicable or not. There are, however, some formats that specifically relate to Excel tables:

SCV     creates an extra column for score values, as opposed to including them in the row labels column
BLC      includes blank rows in Excel tables (as specified by the relevant MERLIN formats, or by using * in text) – by default all blank rows are suppressed
BTL      causes significance flag rows (e.g. generated by format TTS) to be output when there are no flags – giving the table a more uniform appearance.

There is also a label control <PX> which is used only when outputting tables to Excel, and which forces a banner to be split on that item. Excel tables usually use as many columns and rows as are needed to display the entire table, i.e. there are no “page breaks” – but it may sometimes be desirable to split the banner into two or more sections.

Any questions? Email support@merlinco.co.uk.