2

I have a LibreOffice Calc spreadsheet that uses some conditional formatting of cells. I would like to copy it into Writer as a table. The colours/formats of the cells should remain as they were due to the conditional formatting in Calc. Unfortunately when I do that, the formatting vanishes.

How can I copy it keeping the formatting?

Of course the Writer version no longer has to be conditional, but I need to keep current colours.

My work is done so eventually I can do the trick in Calc first (abandon the "conditional" part, and just preserve the formatting as - is). However due to amount of data I would prefer not to do it manually.

Is macro the only way to do that?

Community
  • 1
  • 1
Ister
  • 73
  • 1
  • 2
  • 7

2 Answers2

2

Use Insert -> Object -> OLE Object Choose Create from file Pick the right .ods file.

If you want to modify further (in my case - I need to create many tables from one spreadsheet as the original file is humongous - up to CL column) - do not tick "Link to the file" option.

After pressing OK, the spreadsheet is inserted as is (cloned and embedded), with the conditional formatting. Can be further modified (e.g. rows/cols can be deleted, hidden or whatever is needed). The conditional formatting remains active.

Ister
  • 73
  • 1
  • 2
  • 7
2

I personally prefer to copy as an image. This ensures the format is always exactly as it was in the spreadsheet and that no weird OLE/DDE links go wrong.

However, you specifically ask for a table. For that there are three (or 2.5) options:

  • Insert the entire as spreadsheet as an object. In Windows that can be done as Ister describes in his answer. This will be editable as an inline mini-sheet (Writer will invoke Calc for any editing actions).
  • Insert a part of the sheet as an object: Select what you want in the document, copy to the clipboard, go to Writer and select Edit->Paste Special. Then select the OLE option, or if on Linux, select "calc8". This will be editable as an inline mini-sheet.
  • Insert as HTML. This creates a standalone table. Formatting will not be 100% as in the sheet, as fonts, etc, will be reset by Writer, but it is a native Writer table that you can manipulate in Writer without invoking Calc. Colors, etc, are preserved.

If you use any of the object embedding options, you'll notice that formulae are kept intact (when not referring the data outside the pasted sheet or region). If you want all the data to be verbatim, then you need an intermediate step:

  1. Select the data in your original sheet that you wish
  2. Copy to the clipboard
  3. Create a new sheet and place the cursor in the same spot as the first cell of the copied data (e.g. if your copied region is B4:X99, then place the cursor in B4 of the new sheet)
  4. Select Edit->Paste Special
  5. In the Paste Special window, check only the following options and click OK:
    • Text
    • Numbers
    • Date & Time
    • Formats
dovetalk
  • 1,995
  • 1
  • 13
  • 21