Did you ever need to document a spreadsheet structure? Were you once given maintenance on a spreadsheet you had no idea how it was designed and by whom? Did you spend endless hours collecting sparse information to fill some audit documentation or just to understand how that spreadsheet was actually working? Did you curse that painstaking and error prone work?
If you did recognize yourself, then TheCAT is for you.

TheCAT is an auditing tool for LibreOffice Calc spreadsheets. With its help, you get a full report in a single document, with many pieces of information about the document being examined.

TheCAT :

• Documents the document general settings: description, custom properties, security options.

• Documents the spreadsheet properties: security, display options, custom number formats, styles, used fonts, named ranges and database ranges, sheets list with their relations, dynamic tables, external links, validity list, macros and events.

• Gives details about each individual sheet: name, data zone, links with other sheets, settings, zones (print zone, repeated columns and rows), hidden columns and rows, ranges, dynamic tables, external links, conditional formats, annotations, scenarios, OLE items, diagrams, graphics, controls and events.

• The report last chapter is a set of hints, from the points met when processing the document (no metadata, no creation date, unused user-defined styles, unused custom number formats, fonts only used in conditional formattings, screen freeze with abnormal values, invalid dynamic tables, unexisting styles referenced by conditional formattings, unnamed OLE objects),

• Can create reports under LibreOffice Writer, from customizable templates.

Moreover:

• You may customize the report template and add a logo, a company name, or for localization purposes.

• You may select the template to use and manage your own templates list. This list is kept between sessions.

• The tool may be localized using PO files.

  The PO format (portable object) and use are described in the gettext pages from the GNU Translation Project, here : https://www.gnu.org/software/gettext/manual/gettext.html

TheCAT icons come from the Flaticon web site (https://www.flaticon.com): the extension icon and toolbuttons icons are by freepik (https://www.freepik.com/). These icons are free to use.

The main dialogue | Report details | Creating a custom report template | Localizing TheCAT | Credits | Author | License

The main dialogue

Using this dialogue, you’ll select the spreadsheet to audit, choose the report template to use and run the report generation:

Report details

The report contents are quite clear. Below are a few points that seemed necessary to detail.

Incomplete Reports

If you run a report for some unsaved document (Untitled N), the generated report is incomplete. In this situation, some pieces of information are lacking (ex: password protection flag) that are only added at save time.

A message warns you when you ask for a report.

Cell styles (Spreadsheet / Cell Styles)

The Used column shows:

• Y if the style is actually used for cell formatting.

• (parent of Xxxx) if the style is itself not used but is the parent of a used style which Xxxx name is specified.

• (CF) if the style is only used for a conditional formatting. You get more details in the corresponding sheet section of the report.

• N if the style is not used, neither directly nor indirectly.

Used fonts ( Spreadsheet / Used Fonts)

Origin column:

• Style: the font is used in a style definition.

• Style (CF): the font is only used in a conditional formatting definition.

• Format: the font is used for some manual formatting.

Dynamic tables (Sheet XXX / Dynamic Tables)

These tables information is reported in two rows. The first row shows the output range (on the current sheet), row fields and page fields. The second row shows the input range (possibly on some other sheet), column fields and data fields.

Conditional formatting (Sheet XXX / Conditional Formatting)

Whenever a style is referenced in a conditional formatting but doesn’t exist, the style name is shown with an asterisk *.

OLE objects (Sheet XXX / OLE Objects)

A user-named object is shown in the Name column.

Whenever an object has not been given an explicit name, it bears an internal name which is shown in brackets [ ].

Report templates

Presentation

TheCAT uses a report template to generate the final document. Changing the report aspect or contents thus relies upon the existence of an adjusted template.

The report template is designed for easy updates. Beforehand, here are some important details to know about the template contents and formatting.

• For a better control before working on a report template, you’re strongly advised to use the Formatting marks display mode of the word processor.

Report templates contents

The template text content can be directly changed. Just replace the existing test and you’re set. You may also add your own text content, provided you read what follows.

Three points are worth mentioning though, about tables and other free content.

Sheets information

The template has a dedicated section for sheets (template section).

Do not remove nor rename that section. Otherwise, the sheets information wouldn’t appear.

Tables

Tables are the place where the report information is inserted.

• This is why you must not delete them, change their structure or rename them!

You may freely change their columns or rows titles and even retructure them. Make sure to insert the corresponding identifiers so that the data are correctly inserted.

Caution: when modifying table contents, make sure there is no collision between labels and identifiers.

Adding notes or comments within chapters

You may freely add comments to the document, spreadsheet and sheet detail chapters.

• Make sure, though, that you don’t insert text at the end of the document.

Formatting templates

All formatting heavily use styles, including tables. Changing a format must then be done by an action on the underlying style.

Creating a custom template

This operation allow to add a logo, a company name, modify some contents, follow some graphic guidelines, etc.

1. Start from an existing template: select it, then Save. The .ott file is now ready to update.

2. When the update is completed, import the template using a new name, by Add.

• Before proceeding, please read the chapter above.

Localizing TheCAT

TheCAT is being developed in English. The user interface, though, is designed so that its easy to adjust to new languages according to the local LibreOffice environment. At initial release time, two “locales” are supported : English as the default language and French which is the author’s mother language.

Localizing TheCAT is made possible through the use of PO files https://www.gnu.org/software/gettext/manual/html_node/PO-Files.html) (for the UI), a report template (at least) and an html help file. Thus, supporting new languages is easy.

The PO files are stored withing the extension po subdir. Custom report templates are stored in the templates/custom subdir. And HTML help files are found I the help subdir.

TheCAT automatically selects the localized files that fit the locale used by the LibreOffice installation. Whenever a wanted localized file is not found, English files are used as a backup language.

Creating a new PO file

1. Open the provided .pot (en.pot file).

2. Edit it, using any text editor you like and fill the msgstr strings (do not change the msgid strings!).

3. Save this file as xx.po, where xx is the language code, as a two-characters value (es: spanish, de: german, etc.)

4. Copy the resulting file in the extension po subdir and that’s it. At next start, you’ll find that the GUI is translated using your strings.

Don’t forget to send a copy of the po file to the extension author. You’ll get credit for that in this help file.

Creating a new help file

1. Open the English version of the HTML help file (TheCAT_help_en.html).

2. Translated it to your language of choice.

3. Name it accordingly: TheCAT_help_xx.html, where xx is the language code, as a two-characters value (es: spanish, it: italian, etc.)

4. Copy it in the the extension help subdir and that’s it. At next start, the help text should be the one you translated.

Don’t forget to send a copy of the html file to the extension author. You’ll get credit for that in this help file.

Localizing a report template

In this situation, you have to update the document contents so it fits your preferred language.

More details at the chapter.

Credits

Author

TheCAT is being developed by Jean-Francois Nifenecker, Bordeaux (France)

jean-francois.nifenecker@laposte.net

Many thanks to the testers, especially to Michel Rudelle for is very thorough checks and numerous suggestions.

Icons

The TheCAT extension icon and toolbutton icon are by freepik (https://www.freepik.com/). They were found on https://www.flaticon.com/ and are freely reusable.

Translations

(none yet)

History

License