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.
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:
|create hyperlink from each table back to Contents sheet
|column to contain above (see cell B1)
|file containing logo graphic
|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 panes containing table axes (see columns B-C and rows 1-9)
|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 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 …
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.
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 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.
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 firstname.lastname@example.org.