LibreOffice Impress: OLE Objects, Spreadsheets, and Charts

Version 4.2.8.2

The next topic we want to cover involves something called OLE, which stands for Object Linking and Embedding. This was developed by Microsoft, but has spread to the free software world as well. What it means is that you can use data from two different programs together, and changes made in place are automatically reflected in the other place. A great example comes with spreadsheets, since you create them in a spreadsheet program like Calc, but you might want to take a table created there and put it into a slide to display. So the first level of OLE utility is that if the object is in a slide but is recognized as a spreadsheet object, you can edit it on the slide using the Calc editing capabilities, and get it to do spreadsheet functions. So instead of just being a static object it is alive in the slide. You could, for instance, have a group of cells and use them to calculate a result. If what you had on the slide was nothing more than a static picture of the spreadsheet table, you could not edit it. But with OLE it retains its nature as a spreadsheet, so you could change on of the numbers on the slide and have the calculated result change, just as if you did it in Calc!

Now, that is definitely cool, but we can go further. If you do it right, you can link the slide to the original spreadsheet file so that any change to the spreadsheet would automatically update the slide! These examples illustrate the power of OLE, and make it an extremely useful feature of modern office suites. But as wonderful as this is, there is something you need to keep in mind. The link between the spreadsheet and the Impress slide can be easily broken, particularly if you then save the slide show and send it to someone else. If the spreadsheet is on your machine, and the slideshow is now on the other person’s machine, you have broken the link. Of course within a corporate environment you might be able to get around that by using a shared network resource that both of you have access to, but keep in mind that if you send this slideshow outside of your corporate network, or to someone who does not access the same resources in the same way, the link will be broken. The object in the slide show will then display whatever data it had last received, but would not update again until the link was restored.

Finally, OLE applies to more than just adding spreadsheets to slides, though that is very popular. You could add spreadsheets to documents, or add Charts to either slides or documents, or add Draw objects to documents, spreadsheets, or slides, and so on. For the purposes of Impress, here are the things you can add as OLE objects:

  • LibreOffice spreadsheets
  • LibreOffice charts
  • LibreOffice drawings
  • LibreOffice formulas
  • LibreOffice text

But I think the largest use of this is to bring spreadsheets and charts from Calc. Presentations are a major tool of business, and things like financial and sales data just lend themselves naturally to this technique.

Unlinked OLE Objects

You don’t need to create a separate Calc spreadsheet to use the Calc tools. You can create a brand new object, which will let you use all of Calc’s tools. The limitation is that it only exists in your presentation, and can only be edited inside of Impress. But it will call on the Calc tools to do all of the editing, and will behave like a spreadsheet. To do this, go to or add the slide where you want this object, then go to Insert–>Object–>OLE Object. You will get this window:

LibreOffice Impress Insert OLE Object

LibreOffice Impress Insert OLE Object

 

 

 

 

 

 

 

As you can see, it has the same 5 options we saw abovc. If we want to add a spreadsheet object, make that selection, and then click OK. This will insert the spreadsheet object like this:

LibreOffice Impress Spreadsheet OLE Object

LibreOffice Impress Spreadsheet OLE Object

 

 

 

 

 

And what you can see is that it is a complete spreadsheet object. It has all of the Calc controls. You can move around, you can add a sheet if you want, and you can make it larger to display more rows or columns as needed. You can even split the window if you want, as this shows:

LibreOffice Impress Spreadsheet OLE Object Showing Split Window

LibreOffice Impress Spreadsheet OLE Object Showing Split Window

 

 

 

 

 

 

And you can add or delete columns or rows as needed. It is really very impressive. And if you look above the slide, you will see the Calc Formula bar displayed. Now for any future changes, like updating the numbers, you would need to open up Impress, go to this slide, and edit the spreadsheet. You can do calculations within the spreadsheet, however, and click-and-drag to fill the cells as usual. And with multiple sheets, you can have data on a background sheet that feeds my calculations.

The spreadsheet view looks like this when you are in editing mode in Impress and the object is selected. Once you click away, however, and it is not selected any longer, it looks like this:

LibreOffice Impress Spreadsheet OLE Object as displayed

LibreOffice Impress Spreadsheet OLE Object as displayed

 

 

 

 

If you want to do some further editing, select this table and double-click so that it re-appears as a spreadsheet, then click on a row marker to select a row, right-click to bring up the menu of actions, and select Format Cells. This will let you change the font, the font size, make it bold, and so on. And if you want to add cell dividers, use the Borders button up top to put them in, and it will look like this:

LibreOffice Impress Spreadsheet OLE Object formatted

LibreOffice Impress Spreadsheet OLE Object formatted

 

 

 

 

Linking a file

But if you really want the best from your OLE, you want to link a file. And that means going to Insert–>Object–>OLE Object, but this time you want to insert from a file. So select that option in the window that opens, and it will change to this:

LibreOffice Impress Spreadsheet OLE Object Insert from File

LibreOffice Impress Spreadsheet OLE Object Insert from File

 

 

 

 

 

 

Go to the Search button, click it, and locate your spreadsheet file. And if you want it to update automatically, make sure you put a checkmark in the Link to file box. If you don’t check this, it will insert the spreadsheet, but without a link, and why would you want to do that? Click OK, and your spreadsheet will be inserted into the slide, but here is the thing: Any change you make to the spreadsheet will automatically reflect in the slide. To do this, go to the spreadsheet and make a change. In my case, I decided to add a row with a new region. Here are the steps needed to update:

  1. Make the change in the spreadsheet
  2. Save the spreadsheet. The link is to the file on the disk, not to what you have open.
  3. Go to the slide that has the spreadsheet
  4. Double-click on the spreadsheet to put it in Calc editing mode
  5. You should now see the change(s) you made reflected in the slide
  6. Save the presentation to save the new data

Here is my updated spreadsheet with the added row:

LibreOffice Impress Spreadsheet OLE Object updated via linked spreadsheet file

LibreOffice Impress Spreadsheet OLE Object updated via linked spreadsheet file

 

 

 

 

Charts

The other thing you will often want to do is bring in a chart or graph from Calc. In fact, I would imagine that this is even more common than spreadsheets, since they are graphical and Impress is at heart a graphical program. But Calc is where all of the Chart and Graph tools reside, so that is where you would want to go. Using the same data as above, I can create a simple bar chart. That can be done in much the same way, but charts and graphs always start with the data. If I wanted to create it directly in Impress as an unlinked Chart it gets a little more complicated, but as an OLE linked file it is not too hard. I would create the Chart in Calc as usual, save the Calc file, go to Impress, double-click on the OLE object, and it will update to include the Chart. Now this is not entirely perfect since both the chart and the cells are all on the same sheet, and it can take a little work to get it to display properly. But here is the trick to showing the Chart alone in a reasonable manner. Cut the Chart from Sheet 1 in the Calc file, and place it on Sheet 2. Save the Calc file, then go to Impress, double-click the spreadsheet object to update it in Edit mode, and then if it is not already selected make sure you select Sheet 2. This time when you click away all that is left will be chart in all of its glory:

LibreOffice Impress Spreadsheet OLE Object Chart

LibreOffice Impress Spreadsheet OLE Object Chart

 

 

 

 

 

 

 

And if you update the underlying data in the Calc spreadsheet, the chart will update, and then your Presentation will update. That is what OLE is all about.

A final note: Calc spreadsheets are not exactly the same things as tables, and the documentation for Impress warns about this:

You may be tempted to use spreadsheets in Impress for creating complex tables or presenting data in a tabular format. However, the Table Design feature in Impress is often more suitable and faster, depending on the complexity of your data.

And that is a topic we will get to, but first I want to explore Charts created directly in Impress since that is a tricky business in itself.

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