openpyxl: copy_worksheet drops the page setup and insert_rows leaves merged cells behind
Symptom
The job was the usual bureaucratic one: take an official .xlsx form, duplicate one sheet per item, and
fill a table on each copy by inserting as many rows as the item needed. The script ran clean. No traceback,
no warning, no stderr at all. Every value landed in the cell I expected, and when I opened the result the
grid looked exactly like the template.
Then I printed it, and two things were wrong at once:
- The duplicated sheets broke across two pages wide. The rightmost columns — a signature block and a date field — sat alone on page two. Sheet 1, the original, printed on a single page. Same column widths, same content, same printer, both in LibreOffice and in Excel.
- On the sheets where I had inserted rows, a merged header cell no longer sat above its table. The merge covered the rows it had covered before the insert, so it now spanned the tail of the previous section and the first line of the new one. The dropdown validation had the same problem: it stayed on the old row numbers while the data moved down.
The error message here is that there is no error message. Both failures are silent, both survive a
round-trip through load_workbook(), and both are invisible if you verify by reading cells back.
What it is not
The first guess is always the renderer, and it is wrong. “LibreOffice scales differently from Excel” is a real phenomenon, but it does not apply when the same renderer produces one page for sheet 1 and two pages for its copy from the same file. That difference is in the file, not in the tool. Open the original and the copy side by side in a single application and compare; if they disagree, stop blaming the renderer.
The second guess is the template — that somebody saved it with a broken print area. Also wrong, and cheap to rule out: copy the untouched template, open it, print-preview it. If the pristine file is fine and the script’s output is not, the script did it.
The third guess, the one that cost me the most time, is that the column widths did not come across. They
did. copy_worksheet() copies column_dimensions faithfully; I checked the widths cell by cell and they
matched the source to the decimal. The geometry was identical. What differed was how the sheet asked to be
scaled onto paper, which is a different part of the file entirely.
What I measured
Two measurements, one in the object model and one on the rendered artefact.
In the object model, compare the copy against its source directly:
from openpyxl import load_workbook
wb = load_workbook("out.xlsx")
for ws in wb.worksheets:
print(ws.title,
ws.sheet_properties.pageSetUpPr,
ws.page_setup.fitToWidth,
ws.page_setup.fitToHeight,
ws.page_setup.orientation)
The original carries a pageSetUpPr with fitToPage enabled and a fitToWidth of 1. Every sheet produced
by wb.copy_worksheet(src) carries the defaults instead. copy_worksheet() copies cell values, styles,
merged_cells and row/column dimensions — it does not copy page_setup. Print scaling, fit-to-page and
orientation are reset on the copy, quietly.
For the merges, print the ranges after the insert and compare them with where the data actually is:
print([str(r) for r in ws.merged_cells.ranges])
print([dv.sqref for dv in ws.data_validations.dataValidation])
ws.insert_rows(idx, amount) shifts cells, styles and formula references. It does not shift merged ranges
and it does not shift data validation references. Both keep pointing at the coordinates they had before the
rows appeared.
The measurement that catches all of this without knowing what to look for is rendering. Convert the workbook and count pages:
soffice --headless --convert-to pdf out.xlsx
pdfinfo out.pdf | grep Pages
Then rasterise and diff against a render of the untouched original:
pdftoppm -r 100 -png out.pdf out-page
pdftoppm -r 100 -png reference.pdf ref-page
compare -metric AE ref-page-1.png out-page-1.png /dev/null
Even eyeballing the two PNGs is enough — a column that fell off the right margin is not subtle. The point is that the check happens on the artefact a human will hold, not on the object graph that produced it.
The fix
Page setup, re-applied after every copy. Both halves are required:
from openpyxl.worksheet.properties import PageSetupProperties
ws = wb.copy_worksheet(src)
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0 # as many pages tall as needed
ws.page_setup.orientation = src.page_setup.orientation
fitToPage on the sheet properties switches the sheet from “scale by percentage” to “fit to N pages”;
fitToWidth says how many pages wide that is. Setting only one of them changes nothing you can see. Set
fitToPage alone and the fit values are ignored because the mode is on but unconfigured in the direction
you care about; set fitToWidth alone and the value sits in the file while the sheet is still in percentage
mode. Use fitToHeight = 0 for tables of unknown length — zero means “unconstrained”, not “zero pages”.
Merges, re-applied after every insert. openpyxl will not do it, so do it explicitly:
from openpyxl.worksheet.cell_range import CellRange
def insert_rows_keeping_merges(ws, idx, amount):
affected = [str(r) for r in ws.merged_cells.ranges] # snapshot first
ws.insert_rows(idx, amount)
for ref in affected:
rng = CellRange(ref)
if rng.min_row >= idx:
ws.unmerge_cells(ref)
rng.shift(row_shift=amount)
ws.merge_cells(str(rng))
The snapshot on the first line is not defensive style, it is required. ws.merged_cells.ranges is the live
collection; unmerge_cells() removes from it, so iterating it directly while unmerging mutates the sequence
you are walking and you will skip ranges. Materialise the refs as plain strings before you touch anything.
Ranges that straddle idx — start above the insertion point and end below it — are a policy decision,
not a bug to fix generically. The condition above leaves them alone, which is right for a header merge above
a growing table and wrong if you meant the merge to grow with it. Decide per template; do not pretend there
is a universal answer.
Data validations need the same treatment: read dv.sqref, shift the rows, write it back. If your form has
only a handful of validated columns, it is usually cleaner to drop and re-add them over the final row range
once the sheet has its real size.
Why it happens and what to remember
openpyxl models the parts of OOXML it models. A worksheet’s print configuration lives in <pageSetup> and
<sheetPr><pageSetUpPr>, merges live in <mergeCells>, validations in <dataValidations> — all siblings of
the cell data, none of them derived from it. copy_worksheet() and insert_rows() operate on the parts they
know about and leave the rest untouched. Nothing is wrong from the library’s point of view, so nothing is
raised. The missing piece is simply absent from your output.
Treat both operations as shallow. “Copy” means copy the cells and their formatting, not clone the sheet. “Insert” means renumber the cells, not open a gap in every structure that references a row number. Once you hold that model, both bugs are predictable rather than surprising.
This compounds with the other openpyxl trap in the same file:
a workbook loaded with load_workbook() that contains images can be saved exactly once, because the second
save() raises ValueError: I/O operation on closed file and leaves a truncated archive behind. So a
multi-pass fill has to be one load_workbook() per save() — which means every pass re-enters a fresh
object graph, and every pass that copies a sheet must re-apply the page setup again. There is no “set it
once at the end”; the fix belongs next to the copy, inside the same pass.
The transferable rule is not about spreadsheets. Any pipeline that generates something a human will print or
sign needs a render-and-compare step, because the object model is not the deliverable. Layout regressions are
invisible where you are looking and obvious one conversion away. soffice --headless --convert-to pdf
followed by a page count and an image diff costs a couple of seconds per file and catches the entire class.