LibreOffice Calc: The Object Model and Using Templates

Version 4.1.6.2

I have noticed that many of the explanations I have seen written for using Styles and Templates begin with Styles. But this can be a problem if you don’t recall that styles live inside of Templates. So I prefer to cover the use of Templates first, so that when you start working with Styles you know how to store your work properly for future use.

The Object Model

As I said in the last tutorial, Templates can be understood as a container for a number of settings, most particularly Styles. This follows the object model, which is a lot like those Russian dolls inside of each other. The File for your spreadsheet is an object, and it contains individual Sheets which are objects. Each Sheet contains Cells which are objects. And each Cell contains various Characters which are objects, which can be used to represent numbers, formulas, addresses, labels, etc. Objects exhibit two features we always want to keep in mind. First, objects have properties that are particular to the kind of object. The properties of a file might include who the author is, where the file resides on the system, any access restrictions (like making the file password-protected), and so on. The properties of each sheet might include things like the name of the sheet, the orientation (landscape vs. portrait), headers and footers, etc. Then the properties of the cell might include the type of cell and how it is formatted (text, currency, general number, etc.). And finally the properties of the Character include the font family, font style, font size, and so on.

The second general characteristic in this model is inheritance, which says that any object will tend to inherit the properties of the object which contains it. In some cases that can be over-ridden, as when you have a sheet with cells that are generally set to be numbers, then over-ride on a particular cell to make it text. The reason this matters is that very often people get confused about why something is happening in their spreadsheet (or document or presentation; this is a general point) and the reason usually is that they have not thought through how properties are propagated through the object model. And if the setting is in the Template, you cannot make the change properly unless you know exactly where to look. As we address Styles we can develop this idea further.

When you create a Template, and populate it with Styles you have created, you are setting default properties for the spreadsheet file you are creating. In general, there are two kinds of Templates you can work with. Most often you would use a Default Template, which is the standard you would have every time you open the application and start a new spreadsheet. The other kind is a Special Purpose Template that is created to address a particular need. In the out-of-the-box installation of LibreOffice you start out pretty bare, but as you create Templates and install Templates from other sources, your options will increase.

Basic Template Operations

Create a Template

Open a spreadsheet file that you want to make into a Template. Add the content and Styles you want to have in it, then go to File–>Save as Template. This will open the Template Manager. If you see an icon for the My Templates folder, double-click it to open into this folder. Then click the Save button, give your Template a name, click OK, and your Template will be saved into this folder. Among the things you can put into a Template are Printer settings, single vs. double-sided printing, paper size, and of course Page Styles and Cell Styles. We will cover the specifics of these Styles in a subsequent tutorial.

Create a File from a Template

Go to File–>New–>Templates to open the Template Manager. Go to the Spreadsheet tab, and click All Templates if it is not already selected (if you don’t see it, it is already selected.) Double-click the folder that contains the Template you want to use, and double-click on the Template to create a file based on it. If you are uncertain which Template you want, note that you can select a Template and click the Properties button to bring up the Template Properties window and inspect the properties of the Template.

Edit a Template

Open the Template Manager by going to File–>New–>Templates. Select the Spreadsheets tab, click All Templates to reveal your folders, then double-click the folder that contains the Template you want to Edit. Select the Template, click the Edit button, then go to File–>Save to save your changes. Note that as with Writer, a Template you are editing looks just like a regular file. But you will know that you are in a Template by looking at the very top, to the Title bar. The name of the file is always in the top left of the screen. When you are in a regular spreadsheet file, the file name has the extension *.ods, which stands for Open Document Spreadsheet. But when you are in a Template, the extension changes to *.ots, and the “t” is there to signify that it is a Template.

Note that if you are editing a Template that you have used previously to create a file, LibreOffice remembers this fact. The next time you open the file, you will be given an opportunity to update the file to the new Template, but you only see this once since LibreOffice remembers your answer. There is a macro you can use to re-enable the updating, but that is an advanced topic so I don’t plan to go into it right now. If you need it, do a Web search, it is in the documentation.

Add a Downloaded Template with Extension Manager

As many Linux users know, in Linux you have Package Managers that let you install software packages that have been created for that purpose. There is a similar capability in LibreOffice to add Templates through the Extension Manager. In fact, the Extension Manager handles more than just Templates. You can also install Macros, graphics, and other “add-ins” through this mechanism. To see some of what is available, look at http://extensions.libreoffice.org/. What you need to look for is a file with the *.oxt extension. That indicates that it has been properly “packaged” for the Extension Manager to handle.

To install an extension (for now, a Template), find the Template you want and download it. Verify that it does have an *.oxt extension. Go to Tools–>Extension Manager to open the Extension Manager then click Add. Find the file (probably in your Downloads folder, but that is up to you how you configure your browser to download files, really) and click Open. Depending on the Template you may be asked to accept a license agreement, but if you are looking at the above site from libreoffice.org, those will all be free software. When installation is complete, the Template will be listed in the Template Manager, and in the Extension Manager.

One thing you want to watch out for in downloading extensions is the version they are compatible with. These things may be backwards compatible, meaning if it worked once, it will continue to work, but not always. Each Template is listed with a “latest release compatible” note, which tells you when it was last tested. You may be OK with a Template that is close to the version you are running, but the farther away the less likely this is. Right now, for instance, I am running 4.1.6.2 (I noted this at the top of this page). If I saw a Template that said “4.0” I would give it a shot, but if it says “3.4” I would at least be cautious. It probably won’t do anything worse than not work, but be advised. If you see something that looks like it might be useful, click on the name of the Template, and a page will open with more information. I looked at one that said there is not a stable release yet, so this may be a work-in-progress. Also look for for if it is being maintained. Another I clicked on had a warning banner that it had not had a release in a year and might not be in active development. Is that a problem? Well, given that the underlying software keeps changing, I would say yes. When I started this series, I was on LibreOffice 3.4. Now I am on 4.1, I know 4.2 has been released, and 4.3 is hitting Release Candidate stage. So look for Templates that are current and/or actively in development for the best results, and don’t be surprised if a Template you have used for a while starts to show problems.

Another thing you should bear in mind is that Apache OpenOffice.org and LibreOffice are mostly the same, and Templates developed for one can be used in the other. Here are some you might want to look at that are Apache OpenOffice.org. (And thanks to Andrew Pitonyak for these suggestions.)

  • Professional Template Pack – English – This collecton of Templates for Apache OpenOffice.org has Templates for Writer, Calc, and Impress which are very good. The Calc Templates include a Class Schedule Template, and several Templates for managing Tournaments.
  • Professional Template Pack II – English – Another Apache OpenOffice.org collection like the one above. This one adds Templates for Calc that are business-related, such as a Marketing Budget Template, a Start-up Capital Estimate Template, and a Project Planning Template.

So if you are searching for possible Templates (and other things), remember to check Apache OpenOffice.org as well as LibreOffice. You can find some good stuff there.

Downloading a Template directly as an *.ots File

If you have not found a Template that is packaged as an extension there are alternatives. Many Templates are available as *.ots files. To add this to your Template selections, just open the Template Manager by going to File–>New–>Templates, open your My Templates folder if it is not already opened, then look for the Import button. This opens a standard file manager window. Go to where your downloaded *.ots file is, select it, and click “Open”.

So, with that in mind, let’s take a look at a candidate you might want to download. It is called Account ID manager, and it is used to maintain your list of accounts and passwords. Of course, one of the things you should be concerned with in this case is the security of your data, and this Template page explains how you can password-encrypt the file using AES-256 encryption. It says it was tested with LibreOffice 4.0, so it looks like a good candidate. And it says it was “updated” for LibreOffice 4.0 which to me suggests that it is being maintained. So I click on the “Get Account ID manager for All platforms” link to download.

This places the file in my Downloads directory. I look at the file, and it has an *.ots extension, so I know I need to use the Import function. I follow the procedure outlined above, go to my downloads folder, and soon I have a new Template in my My Templates folder called “account_id_manager_3-0”

Making a Template from an *.ods File

I have also seen files that are offered as Templates, but which are actually just spreadsheet files. One that I found that looked interesting to me because my day job is project management is called Gantt Chart Template. I checked and it was released March 27, 2014, and the latest release was validated against LibreOffice 4.2 so it was extremely current. It might cause a problem if it relies on features only found in 4.2 since this machine is still on 4.1, but I thought it was worth a shot, so I downloaded it. In this case, it was an *.ods file. This can be added using the Import function in the Template Manager in most cases, but an alternate solution here is to open it as a spreadsheet, then save it as a Template by going to File–>Save As Template.

Setting the Default Template

Of course, as we saw with Writer, once you know how to set up a Template to do what you need, you will probably want to create your own Default Template. Remember that this is the Template that will be used if you simply click File–>New–>Spreadsheet (or click the New icon, or press Ctrl+N) without specifying a Template. The Default that comes with LibreOffice Calc is featureless, and when you understand how to customize you will want to add your own touches. To do this, create a Template with all of the features you want and save it to your My Templates folder (or whichever folder you like), open the Template Manager, highlight the Template to select it, then click the “Set as default” button. If you later decide this was a mistake, and want to go back to the plain vanilla Default that comes built-in, go to the Template Manager and look for the icon of a gear in the upper-right. Click on it, and select Reset Default Template–>Spreadsheet, and you will have it back as the Default.

Organizing Your Templates

If you really get into using Templates you may find that putting all of them into the My Templates folder is not satisfactory. And Template Manager lets you do a little more organizing if you wish. Open to your My Templates folder, select a Template, and click “Move to folder”. If you already have a folder to move it to, just select it, but of course the first time you will want to use the New Folder option that appears in the drop-down menu.

Exporting Templates

Exporting is something you might want to do for two reasons. First, if you are creating a Template for use by a group of people, you need to export it so that you can share it. Second, if want to use it on multiple machines (which I do frequently) this lets you copy the Template to as many machines as you like. You can even export it as a file that you place in Dropbox for easy moving and sharing.

To export a Template, open the Template Manager, then open the folder containing the Template, and select the Template. Click the Export button above the list of Templates. This will open the standard File Manager, and you can choose where to save the file.

Closing thought

This pretty much wraps up the topic of working with Templates, and it sets us up for the next topic, which is Styles. The point is that Styles must be saved within a Template, and that is why I covered Templates first. whether you are creating a Template from scratch, adapting one that you already have created, or are working with one you downloaded, you need to know these things. My suggestion is that you prepare for the next few tutorials by creating your own Default Template and saving it right now. Then as we go through the Styles you can Edit the Template to add your refinements. As I did with Writer, I created mine as KOB default, and when I am done I will share it to my other machines.

So next time, Page Styles.

Listen to the audio version of this post on Hacker Public Radio!

CC BY-SA 4.0 LibreOffice Calc: The Object Model and Using Templates by Kevin O'Brien is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.