Pasting OpenOffice Calc Spreadsheets Into Writer, as Tables

Alexander Kjerulf (http://www.positivesharing.com ) wrote to me with an excellent question.

How do you get a spreadsheet into a Writer document?

The question is interesting, since it's not really that it's hard—there are just so many ways and many possible results. It's kind of like asking “How do you make a really great vinaigrette?” or “What is the best episode of Buffy the Vampire Slayer?” There is no one right answer

Here are some answers, then. My birthday is coming up, so let's say that I'm planning my ideal birthday weekend and I did it in a spreadsheet first.

However, let's say that I've had the brilliant thought that I should leave it lying around where my boyfriend is sure to see it, so that he can help facilitate some of these items. In this case, I would like it to be in a regular document, with a nice heading across the top.

In short, I want to take some content from a spreadsheet, and turn it into a table.  I'm going to paste.  But which of the many pasting approaches is best?

Just Pasting the Spreadsheet Normally

Copy the cells from the spreadsheet that you want, switch to a Writer document, and paste.

You get a thingamajig that is kind of a table. Note the table icons in the toolbar. However, you can't really use them except to do things like wrap text around the table or apply other properties to the table object.

Pasting normally gives you kind of a table object with a table look

Double-click in any of the cells so that you can select the text and the cells and basically do normal things to it. But when you do this, iit magiliciously turns into a spreadsheet, with spreadsheet icons. You don't have the table icons anymore.

Double-clicking a pasted spreadsheet gives you spreadsheet tools and a temporary spreadsheet look

This could be quite useful if the spreadsheet were chock full of numbers. However, since it's just full of Wonderful Birthday Ideas, I don't really need it to act like a spreadsheet.

The quest for the perfect way to paste this into a Writer document continues.

Exploring the Paste Special Options

When you choose Edit > Paste Special, or click and hold down on the Paste icon in the main toolbar, you get extra pasting options.

You probably won't use the metafile or bitmap that much, since they create graphics. And the calc8 option just does exactly the same thing as normal pasting. However, the rest are quite useful.

Using the HTML (HyperText Markup Language) Paste Special Option

As you might guess, using this option pastes an HTML table version of your spreadsheet. This is pretty slick if you are, for instance, creating a web page. And it's a table, so of course you get the table tools. Not just when you click on the table but when you click in the cells or select the text.

Using the DDE Link Paste Special Option

This is a very slick pasting option—it's one of the two I recommend. You get a Writer table version of the spreadsheet content, but it's linked to the original spreadsheet. When the original spreadsheet changes, this copy of it changes, too. Of course, you have full use of the table icons.

Consider this option even if you don't want the link because you retain any border formatting or background shading.

This is also nice since, as you might have noticed, the alignment was corrected. In the spreadsheet the alignment by default was at the bottom of the cell. Now, it's at the top. If this isn't what you want then it's a disadvantage, but it's what I want in this instance.

Using the Unformatted Text Paste Special Option

When it says unformatted, it means unformatted.

However, what you can do with this, if you want, is to re-convert it to a table using the instructions here.

http://openoffice.blogs.com/openoffice/2006/06/converting_text.html

It's a bit of extra work since you can also use the DDE Link or Formatted Text options to get a regular table.

Using the Formatted Text (RTF) Paste Special Option

This is the other one I recommend. You get a plain old table, in normal Writer format, that's not linked to the original spreadsheet. With the table icons. However, you do lose any formatting associated with the borders or background shading that you might have had in the spreadsheet.

---

Comments

[Gustavo Chaves]

This is a very useful article, thank you. In fact, I maintain a FAQ for the OOo users where I work and this is one of the questions in it. I'm going to link to your article in the answer.

There is a related issue for which I can't find a good solution. Suppose I don't want to create a new table from scratch with the spreadsheet data, but that I already have a big table and just want to substitute the spreadsheet data for a few of the table's cells. Is there a way to do it?

Perhaps this is very specific, but I have already been asked about it twice.

[Solveig]

Hi Gustavo

Hmm, interesting question. I've tried a few ways and the only way, kind of a hack, seems to be this:
- Go to the spreadsheet
- Copy the cells you want
- Go to the Writer document with the table and click somewhere in the normal text flow outside the table. (Or go to any Writer document.)
- Choose Edit > Paste Special and select Formatted Text.
- Copy the resulting data, now in a table
- Click in the upper left cell of the area in the table where you want to paste the cells
- Paste normally

So there's an extra step. It seems that pasting acts differently when the cells are pasted into a table rather than being pasted into plain text.

[accessdenied]

Hi
I'd like to know how one can dynamically link a OOOCalc cell's content to a OOOWriter document, and make sure that whenever the cell's content is changed in Calc, the related correspondent word in the Writer document is automatically updated ? I don't find any option to do this in OOO's "Paste Special" menu, whereas the equivalent "Paste Special" menu of M$'s WORD has an option named "Paste Special With Link" which does just what I described.

Thanks for replies.

P.S. Supressing the * in my mail adress will allow human senders to mail-contact me. This is done to prevent bots from spamming me. Cheers

[Joe "Floid" Kanowitz]

Much obliged for this article, especially since the book's been delayed. I do have to cry out into the wilderness and observe that the default behavior is kind of horrible for legal users creating financial affidavits.

Legal documents can be subject to all sorts of specific margin and formatting requirements, require a text caption at top, and usually a signature or certification block at the bottom -- things best done in a word processor (go look at the top of a PDF from Groklaw, then try to lay that out in Calc fields without losing hair).

Unfortunately, as far as I can tell, getting an OLE spreadsheet object to obey page margins and break across pages just isn't happening. These workarounds are much better than nothing, but it's still an annoyance.

(Of course, the office I'm working in did just do these as text documents with a desk calculator, or within word processor tables, before I came along and decided to try to learn to use software properly.)

[e-mailers, remove the -no-spam-.]

[Solveig Haugland]

Hi Accessdenied,

Sorry for the delay, my notification is broken. You can Edit > Paste Special from a spreadsheet into a Writer document, choose DDE Link, and then when you update the spreadsheet, the Writer doc is updated too. This is a great way of putting spreadsheets into master docs since master docs can only handle writer docs.

Solveig

[Solveig Haugland]

Hi Joe,

And let's not even start talking about pleading papers, eh? :(

You might try creating what you need with a graphic and putting the graphic in the page style, using the Background tab. Frames might work too. (Insert > Frame.)

You can link a section or a frame to a text fle so you might want to insert a frame at the top that sucks in the content from a particular file with the canned text you need.

HTH,

Solveig

[mike]

quite often i would like to paste a range of cells from an HTML table in a browser into a spreadsheet, maintaining the tabular layout. this always fails (everything gets pasted onto one spreadsheet row).

is there a way to do this? ironically it's one of the most frequent uses i have for a spreadsheet.

[Solveig Haugland]

Hi Mike,

That's odd; pasting from tables into a spreadsheet works fine for me. Do you click in just one cell in the spreadsheet when you paste?

There's another approach. Go to the spreadsheet, choose Insert > Link to External Data, and select the path to the HTML file. (I tried it with a URL to a web site but it didn't seem to work.) Then in the area below, select the table you want to insert. It comes into the spreadsheet nicely.

I'll have to blog on this; it definitely goes with the other info about transferring tables into text into spreadsheets, etc.

Solveig

[Joe "Floid" Kanowitz]

I just got back to this problem today, and ended up pasting the sheet into Writer as an HTML table since, oddly enough, that preserved the most formatting. (Or at least obeyed the formatting in the document, rather than popping everything into some Default style of Times New Roman. DDE can't handle merged cells, OLE doesn't break across pages, life is fun, etc...)

Regarding:
"You can link a section or a frame to a text fle so you might want to insert a frame at the top that sucks in the content from a particular file with the canned text you need."

... I might be braindead today, but in Calc I only see an "Insert -> Floating Frame" item, and that's decidedly nondeterministic in behavior, especially when Page Previewed. (This with whatever stale version of OO.o Ubuntu 6.06 LTS provides.)

In theory the image thing could work, or I could just figure out exactly how to size cells to hold and wrap the text, but it's certainly easier to preview and conform to margin requirements in Writer.

[I'm really just posting this as a record of one user's struggle, for any developers scraping across it.]

[Henry]

Hi, I found that there is a bug for using the DDE link method.
I am using the Chinese WinXP OS. If the source file (the spreadsheet document) is placed at the folder which is named with some Chinese characters, it can't link the spreadsheet's content to the Writer's document correctly.

[AXE]

Thanks for this info but I'm not able to delete a DDE linked table from my text file. How can we do that?

[Justin]

DDE Link doesn't apparently carry over formatting such as colors, text attributes, etc. Any solution for this?

I read your stuff quite a bit by the way - good stuff.

[Janne]

Hi. To delete a DDE linked table go to "edit -> links". Then select the link that is used on your table and break it. Then I used "table -> delete" from the menu.

Good luck with your writings!

[sagar]

how can set margins while print preview in openoffice
spreadsheet ???? plsssssssssss help me ?????

[sagar]

how can set margins while print preview in openoffice
spreadsheet ???? plsssssssssss help me ?????

[Solveig]

Hi Sagar,

In Print Preview, there's a Format Page button. Click that, set margins in the Page tab, then click OK and you'll be back in Preview with the changes you made having taken effect.

Solveig

[pofufo]

Hi, I have a question about DDE links.

I am trying to link some cells from a calc spreadsheet to a writer document, both in the same directory. These documents are templates which I'd like to use in many projects, so it would be great that when I change the location of the files (keeping them in the same directory) the links keep on working. I'm trying to use DDE links with relative paths, but I don't know how. Is there any way to achieve this?.

[Solveig]

Hi,

Sorry, not that I know of. Maybe have the calc doc stationary and the writer doc move around?

Solveig

[pofufo]

Thanks Solveig for your quick answer. In the meantime I've found that this is filed as an issue (issue 49362), and that there seems to be a workaround. I copy from openoffice.org:
"First of all, relative paths do still work, but the documentation doesn't cover
it well. I hope it will be updated soon!

If you want to use relative paths, in the DDE link field write this:
"file:linkedfile.ods" if the 'linkedfile.ods' is in the same directory
"file:subdir/linkedfile.ods" if the 'linkedfile.ods' is in a subdir called 'subdir'
"file:../linkedfile.ods" if the 'linkedfile.ods' is in the root directory

Please note: this works on both, writer (in the Links window) and calc (as the
=DDE() argument). I've tested this on Win XP, but the fact it uses slashes
instead of backslashes makes me think this works on linux too. But I can't
confirm it.

But be careful! If you update the links with the "file:" path (Modify->Links),
when you try to "Modify->Paste Special->DDE Link", writer will crash! You can
still add DDE links using "Ctrl+F2"."

[Solveig]

Nice! Thanks, pofufo.

Solveig