11. Creating styles for MERLIN tables in Excel

In blog #10, we described how to generate MERLIN tables in “xlsx” files, and how they can be enhanced by creating your own styles file. In this blog we will look in more detail at possible enhancements, using this example (which was generated directly by MERLIN without any post-processing).

For general information about styles files, refer to blog #10, and for more detailed information about the commands, see the MERLIN Help file > User files > “txt” Excel styles file.

Global commands

A styles file commences with global commands which control things like the width of the row label and data columns, and whether commas are shown in thousands (e.g. 2,385). In our example, we have changed the following global commands from their default settings:

TOCLINK=ON	create hyperlink from each table back to Contents sheet
TOCLINK COLUMN=B	column to contain above (see cell B1)
LOGO=MERLINCO.PNG	file containing logo graphic
LOGO COLUMN=L	column to contain logo in Tables (see cell L1)
LOGO TOC COLUMN=D	column to contain logo in Contents sheet
BANNER COLOUR ROTATION=1	header level at which colour rotation operates (see row 6)
FREEZE=ON	freeze panes containing table axes (see columns B-C and rows 1-9)
GRIDLINES=OFF	do not show Excel cell gridlines
CHART TYPE=HORIZONTAL BAR	generate horizontal bar chart (see rows 23+)

Style definition hierarchy

The global commands are followed by style definitions, which control things such as the font, cell colours and borders, text wrapping or shrinking. The style definitions form a hierarchy as shown in the following chart (information in brackets relates to our example):

So, BASALL defines the style for the entire table but this can be overwritten by BASTOP for titles at the top, then by the individual components in that section such as LBJ (lines before job header) and JH (job header). See MERLIN Help file for a full list of the individual components.

Each style definition starts with its name in square brackets, e.g. [BASALL], followed by style definition commands.

Style definition commands

The following commands may be used within each style definition (but it is only necessary to specify them if non-default):

FONT FACE=fontname

FONT SIZE=n

FONT COLOUR=colour

FONT BOLD=OFF / ON

FONT ITALIC=OFF / ON

FONT UNDERLINE=OFF / ON

TEXT SHRINK=OFF / ON

TEXT WRAP=OFF / ON

CELL BORDER STYLE=NONE / DASHED / DOTTED / THICK / MEDIUM / THIN

CELL BORDER COLOUR=colour

CELL COLOUR=colour / colour / colour …

CELL COLOUR ALTERNATIVE= colour / colour / colour …

JUSTIFICATION=CENTRE

FIGURES=LABEL

The CELL COLOUR ALTERNATIVE command may only be used in BASROW and BASFIG and their individual components.

The JUSTIFICATION=CENTRE command may only be used in BASTLC, BASBAN, BASROW and BASFIG and their individual components. Otherwise, all text is left justified.

The FIGURES=LABEL command may only be used in BASFIG and its individual components, signifying that the style definition is to be copied from the corresponding style in BASROW, e.g. if used in UNR_FIG, the definition will be copied from UNR.

Specifying colours

Colours may be specified using three different methods which may be mixed freely:

• Standard colours may be specified using one of these words: AUTOMATIC, BLACK, WHITE, RED, GREEN, BLUE, YELLOW, MAGENTA, CYAN, GREY

• Any colour may be specified with a hexadecimal value, e.g. #C4F8C8

• Any colour be specified with an RGB value, e.g. RGB(196,248,200)

Hexadecimal and RGB values may be found on the Microsoft Word Custom Colours form.

In our example, we have used FONT COLOUR=BLUE in the VT style to show the Vertical Title in blue (row 4).

The CELL COLOUR command (only) may contain two or more colours (using any of the above methods) separated by forward slashes, e.g. CELL COLOUR=RED / GREEN / CYAN / GREY. Multiple cell colours are only relevant in BASROW, BASBAN and BASFIG and their individual components (they will not actually be used in any BASROW styles, but will be picked up if FIGURES=LABEL is used in BASFIG styles). Multiple colours will be used in rotation across the banner as specified by the global command BANNER COLOUR ROTATION. A setting of 0 means it will change on every column, 1 on every header, 2 on every overheader, etc. In our example, the colour changes on every header (row 6) and this effect is continued for all rows beneath (rows 7-20).

The CELL COLOUR ALTERNATIVE command allows you to use different colours on alternate rows of styles BASROW and BASFIG and their individual components. The alternation works within the individual components, so in our example we have included these commands in the RTV style definition…

CELL COLOUR=#5FEB69

CELL COLOUR ALTERNATIVE=#C4F8C8

… to produce the two shades of green, and these commands in RTV_FIG…

CELL COLOUR=#71E4F3 / #FFFF69 / #FFB343 / #E99BB9

CELL COLOUR ALTERNATIVE=#BDF2F9 / #FFFFCD / #FFDDAB / #F9E3EB

… to produce the two shades of blue / yellow / brown / purple (used in rotation as defined by BANNER COLOUR ROTATION=1).

We have also used CELL COLOUR commands to specify the colours of each of the blank rows, and the Mean Score row. No ALTERNATIVE is needed, since these rows only occur once within each table.MERLIN formats PSV and SCV

If MERLIN formats PSV (Print Score Values) and SCV (Separate Cell for score Values) are used, the score values will appear in column C, and the figures in columns D+.

When this is the case, column C is treated as belonging to BASTLC and BASROW and their component styles, so it will have all the same characteristics as column B. The global FREEZE command will also be adjusted to include column D.

Charts

Only one chart type is currently available (HORIZONTAL BAR). If a table contains any column percentages, and you are using RCP XLSTAB=3 (one table per worksheet), this chart will be generated below the table using the figures from column C (or column D if formats PSV/SCV are used). This will usually be the total column but not always – so you may need to delete or change charts that are inappropriate.

We will add other chart options if there is sufficient client demand.

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