Problem solve Get help with specific problems with your technologies, process and projects.

OpenOffice 2.0: Master page hacks make better spreadsheets

Don't waste time manually figuring out page numbers in OpenOffice. Use this master page hack to combine your spreadsheets, images and more into a readable, polished report.

I learn a lot when I teach. I learn a lot about what people have to do and what they need to accomplish, and I realize that there's not always a real strong correspondence between that and software.

More OpenOffice tips:
Combining text documents using master files

Using styles in

I took a look at some of the big honkin' reports that the City of Largo puts together. The answer to combining documents in a single publication is usually master documents, which work by organizing Writer text documents.

But guess what? The information that people at the City of Largo, and pretty much everyone else, use isn't contained only in text documents. There's a healthy helping of spreadsheets. There are drawings. There are presentations, sometimes. So the implied question on everyone's lips, of course, is, "What now, Miss Smarty-Pants OpenOffice Instructor?"

The best answer, of course, is for master pages to be able to handle all the document types. That would be great.

Until then, however, what do you do if you have 17 Writer documents, a query from one of your databases, 12 spreadsheets, five drawings and a presentation that you'd like to combine into one publication with a unified table of contents? And you'd like this publication with its unified table of contents to automatically update when anyone changes the source documents? (Otherwise you're in Copy-and-Paste Hell, somewhere right around level six.)

To figure it out, I put on my Nancy Drew outfit, stared piercingly at the screen for a while after class, and decided that it was possible....ugly, but possible.

Here's what you do.

First, get to know master documents and how they work normally.

Then follow the appropriate hacks in this article for bringing other types of documents into a master document.

It's not really simple, but it does work. And it's better than manually figuring out the page numbers, printing it all out manually and collating it together at the end.

The fundamental principle of including other document types in master documents

You have to get the other document's content into a Writer document. Then insert that Writer document into the master document.

How you do that depends on the document type.

  • With spreadsheets, you can do a Paste > Special that will link the spreadsheet to the Writer subdocument.
  • With drawings, you can insert the drawing so that it too is linked, though editing is a little funky. (You might be better just exporting the drawing to a JPG or PNG and inserting that graphic in the Writer subdocument. There's no link, but if your drawing doesn't get updated that often, then that's fine.)
  • With presentations, there's no link, but it is at least possible to paste in all the text into a Writer subdocument and reformat it by applying styles. The only real benefit here is that you can copy and paste the text content. It's essentially nothing but hard work and elbow grease.

Bringing a spreadsheet into a Writer subdocument

Just copy and paste the spreadsheet into a Writer document. You need to make sure you're pasting in roughly the right width of data, since spreadsheets are generally a lot wider than eight inches.

Pasting a spreadsheet
1. Create a new Writer document and import any styles you're going to need. Press Return a few times to give yourself some room for headings later.

Note: You don't need to create a new Writer document every time; if you want several spreadsheets to be consecutive or a spreadsheet followed by a relevant drawing, then just paste or insert your content into an existing Writer subdocument.

2. Select the number of columns that will fit into a standard width Writer document.

3. Copy them.

4. Go to the Writer document. Press Return a couple times to leave some blank lines at the top; you'll want these later for headings.

5. Choose Edit > Paste Special and in the window that appears, make the appropriate choice. Click OK.

  • Select DDE Link if you need the master document to be updated when you change something in the original spreadsheet.
  • Select Formatted Text if you don't need the master document to be updated when you change something in the original spreadsheet or if you specifically don't want the updating to happen.
  • Select Unformatted Text if you don't want it linked and you want the text to be comma-delimited instead of in a table.

Here's what the window looks like.

6. If you paste as DDE Link or Formatted Text, it will look like this. It's in a table, with invisible borders.

It breaks nicely over the pages, so there are as many pages as needed for the data. And whenever you update the original spreadsheet, this Writer subdocument will reflect those changes.

7. Select the text and make it a narrower font or smaller font size if you need to skoosh more content into the width of the page.

Inserting a spreadsheet using the OLE Object feature
You can also insert the spreadsheet by choosing Insert > Object > OLE Object; see more detail in the section titled Inserting a linked drawing into a Writer subdocument.

But this only works with spreadsheets that can visually fit well inside the page. Here's what it looks like with a very small spreadsheet that fits easily.

Here's what a spreadsheet with several hundred rows looks like.

Bringing data from a database into a Writer subdocument

What if you've got a dandy query or some really important data that you need to include in your big report? No big deal; it's easy to bring it into a table.

1. Create a new empty Writer document and import any necessary styles. Press Return a few times to give yourself some room for headings later.

2. Use the instructions in this article [link to the article in getting data out of a database] to bring the data into a table.

Bringing a drawing into a Writer subdocument

If you're constantly changing your drawing and you need the master document to get those updates, then choose the first option. If you really don't care, go with the second option.

Inserting a linked drawing into a Writer subdocument
1. Create a new empty Writer document and import any necessary styles. Press Return a few times to give yourself some room for headings later.

2. Choose Insert > Object > OLE Object.

3. Select Create From File, and select Link File.

4. Click Search and find the drawing file. Click Open.

5. Click OK. The drawing will appear in your Writer subdocument and any updates to the drawing will be reflected.

Exporting a drawing to a graphic file and inserting the file in a Writer subdocument
1. Open your drawing.

2. Select everything that you want your exported graphic to contain.

3. Choose File > Export.

4. Specify the location where you want the graphic, name it, and select a format: JPG, GIF, and PNG are commonly used.

5. Click Save.

6. If you're prompted by a window, specify additional options for that format. Click OK.

7. Create a new empty Writer document and import any necessary styles. Press Return a few times to give yourself some room for headings later.

8. Choose Insert > Picture > From File.

9. In the window that appears, select the file you just exported, and also select the Link option.

10. Click Open.

11. The graphic will appear in the Writer subdocument. Resize it as necessary by holding down the Shift key, moving your mouse over a corner handle, then dragging the corner in toward the middle of the drawing.

It's not linked to the original drawing. If you export to the same location with the same graphic type and file name when you change the drawing, then the Writer subdocument will be updated.

An Inconvenient Truth: There is no good way to bring a presentation into a master document

There's a bad way. But no good way. All you can do without horrifyingly slow page-by-page exporting to graphics is to copy the text content, paste it into a Writer document and reapply styles and bulleting to the whole thing.

I thought it was going to work fine. Presentations have styles, after all. So you would think that you could just copy and paste the text content, then redefine the style definition. Brilliant! And it should work. But it doesn't. You can modify the style definition, but the text with that style applied don't change to reflect the new definition. So you just need to apply new styles.

Needless to say, there's no way to link the pasted presentation text in the Writer subdocument back to the original presentation file. On the bright side, you're probably not going to need to include presentations as often as you are spreadsheets are drawings.

Here's how the bad way works.

1. Open your presentation.

2. Click the Outline tab.

3. Select all.

4. Create a new empty Writer document and import any necessary styles. Press Return a few times to give yourself some room for headings later.

5. Paste.

6. The text will be huge, so you might want to select all and apply a 12-point font.

7. Choose Format > Styles and Formatting, and apply the appropriate styles to the text. Remember, to select non-consecutive text, you can select one chunk, then hold down Ctrl and select additional chunks. Then you can just double-click the style and apply it to all the selected pieces of text at once.

Final polishing

Add each of the Writer documents to your master document, using the instructions in my article on master documents. The article includes a fair amount of post-combination tweaking that you might need to do, depending on what formatting you want. For instance, if you have lists, you'll need to restart them at 1; if you want each top-level heading to start at the top of a new page, you might need to redefine that heading style to do so.

Your content is now in the master document, but you'll probably want to add, say, some headings. Above the inserted content, in the Writer subdocument itself, type a heading like 2006 Financial Report at the top of the Writer document, to make sure it's well labeled and will show up in the table of contents.

Be sure to apply the appropriate heading style, too, like Heading1. Choose Format > Styles and Formatting, select the heading and double-click Heading1 or another heading style in the Styles and Formatting window.

It's a hack, but it works

It's a hassle, isn't it? It works, but the master documents really should be able to handle the other file types directly. On the plus side, for spreadsheets, at least, once you set up the links you shouldn't have to set them up again for a while. And there's always job security as an angle—if you're the one person in your organization who knows how to do this, there's no way they can fire or lay you off. Multi-format master page-based publications are your ticket to comin' in late, leavin' early and three-martini lunches.

Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.

Did you find this tip useful? Email us and let us know.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.