How to insert large Calc spreadsheet into Writer document using OLE without rows disappearing?

English
, , ,
#1

In LibreOffice 24.2.7.2 on Ubuntu 24.04.4 LTS, when I insert a big spreadsheet into a Writer document, Writer shows it with the bottom two rows cut off (i.e. hidden; cropped; not visible; disappeared).

To reproduce:

  1. Create a Calc spreadsheet with text content in cells A1:Z15, and save it as a .ods file
  2. In Writer, click Insert > OLE Object > OLE Object…
  3. In the resulting Insert OLE Object dialogue box:
    • Select Create from file, then
    • Select Link to file, then
    • Click the Search… button, and select the Calc spreadsheet from earlier.

Expected result:

  • All cells of the inserted spreadsheet, as viewed in Writer, are visible.

Actual result:

  • The inserted spreadsheet, as viewed in Writer, appears to be missing its bottom two rows.

Solutions I have tried that were unsuccessful:

  • Click the inserted spreadsheet and drag the bottom handle down. This just scales the view of the spreadsheet vertically, distorting the aspect ratio.
  • Right-click the inserted spreadsheet and click Edit. Then drag the bottom handle of the embedded edit window down. This lets me see the bottom rows are definitely present in the spreadsheet - but as soon as I click outside the embedded edit window, Writer pops up a dialogue box saying, “You have changed the data in the inserted object which will be overwritten by updating the [file name]. Do you still want to overwrite this data?” (I don’t know why it does this, since I haven’t actually changed the spreadsheet.) The options are Overwrite or Cancel. If I press Overwrite, Writer goes back to showing me the same view of the spreadsheet as it did originally, with the bottom rows hidden.
  • In step 1 above, click Format > Print Ranges > Edit… and set the Print Range field to $A$1:$Z$15. This seems to have no effect.
  • In step 1 above, as well as setting the print range to $A$1:$Z$15, go to Format > Page Style… > Sheet and set the Scaling mode to Shrink print range(s) on number of pages and Number of pages to 1. Again, this does not solve the problem.

Unfortunately, the Writer and Calc documents exhibiting this issue contain private information so I can’t upload them here for scrutiny or testing. If I manage to create some test documents that exhibit the same issue, I will upload them here.

#2

The problem comes from the fact that OLE objects are considered by Writer as “monolithic”, the same as an image. Writer does not try to analyse the “structure” of the object to see if it has “reflowable” components like rows which could be laid out on separate pages. Instead, the whole block is constrained to be displayed on a single page. If it can’t, it is clipped at bottom.

There is nothing you can do, except make sure the size of the OLE object fits within a page.

You mention that your Calc spreadsheet contains “Lorem ipsum”, i.e. text. If this is the main contents of your spreadsheet, use a Writer table. Every cell in a Writer table is a full-fledged sub-document which is handled like the main flow regarding page limits (provided you allow the table and its rows to be split at page boundaries). If you have “simple” numeric data (let’s say limited to addition, subtraction, multiplication and division, without “complex” functions), Writer can do simple arithmetic operations without the need for sophisticated Calc features.

#3

Thanks, but that is not the problem I am encountering.

My spreadsheet does fit entirely on a single Writer page. The spreadsheet only has 15 rows, after all, and the spreadsheet font is only 10 points in size.

(The spreadsheet is wider than the Writer page unless I use a Landscape page in Writer, but that is easily solved by either:

  • using a Landcape page in Writer, or
  • right-clicking the inserted spreadsheet and clicking Properties… > Position and Size, ensuring Keep ratio is on, and then setting Width to 100% relative to entire paragraph area.

Either of those approaches ensures the entire spreadsheet fits on the Writer page with plenty of whitespace below - the bottom half of the Writer page is empty, in fact - but neither of them fixes the issue of the bottom two rows being cropped from Writer’s view of the spreadsheet.)

#4

You are right that I mentioned “Lorem ipsum” in an early draft of my post, when I was trying to think of how other people might be able to reproduce the issue, but then I removed mention of it from the final edit because it wasn’t really relevant.

Unfortunately, although my spreadsheet is mainly text, it is heavily driven by formulae and so a Writer table would not be suitable.

#5

Weirdly, the two “missing” rows are now showing when viewed in Writer. I don’t know why. I edited some other parts of the Writer document and when I scrolled back to the page with the embedded spreadsheet, all its rows were visible.
Maybe there is a rendering bug, and the inserted spreadsheet got redrawn after one of the edits I made?
I will leave this question “unsolved” for now, until the cause of the issue becomes clearer.

#6

If you can reproduce the mishap, attach the faulty document (probably with the spreadsheet so that the context is the same) for analysis.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.