You can now get XLSX format files from report templates, as well as CSV. This also allows you to say how the column should be formatted.
Contents
Background
A report is just a template which, instead of merging data into an email or a letter, merges into cells of a spreadsheet.
Originally report templates used a table in the content content and merged to produce a CSV file suitable for opening in Excel or Google Sheets. Then in 2023, we updated reports to have their own editor for column definitions. Now you can also download the report as a native XLSX file.
XLSX files can also include column formats (so you can say a column is text, a date, a number etc). That avoids Excel’s habit of over-enthusiastic guessing column formats, particularly seeing numbers as dates when they aren’t. By saying a column is text, it stays that way. A CSV file has nowhere to store this information. An XLSX file is also less vulnerable to confusion over accented characters, currency symbols, emojis and other more unusual characters.
Report
To make a report:
- start a new template
- choose Report as the template type
- edit what should go in the header and data rows of the spreadsheet.
- merge, putting the result in either to a browser window, the file store or a file download
See article about the report editor for details.
Instead of two buttons to save to file store or download, we now have four: to save or download as CSV or XLSX (Fig 1: 1). If you want a XLSX file, you just use one of the XLSX buttons.
Formatting
The spreadsheet definition in the report template editor has a new column, labelled Show in
XLSX as (Fig 1: 2). For each column, choose the format (how Excel should treat the column contents):
- text
- para[graph]
- link
- date
- currency
- number
Even if you leave everything as text, this avoids Excel trying to incorrectly second-guess an unassigned column type.