Cannot copy openpyxl.Workbook object

Question

I have .xlsx template file which is used for creating a openpyxl.Workbook object via load_workbook(file).

I want to use the imported Workbook to generate two new Workbook objects wb1, wb2 and perform different transformations to each of them and at the end store them in a workbooks dictionary.

I can't copy the objects from the dictionary without errors when writing back to file

Setting each workbook equal to the template, creates only reference pointers and not different objects
Using shallow copy() creates different objects but some of the properties are still referenced
Using deepcopy() creates different objects with different properties. However, exporting the workbooks back to excel using BytesIO(save_virtual_workbook(wb1)) gives errors of the type:

\stylesheet.py", line 232, in write_stylesheet xf.alignment = wb._alignments[style.alignmentId] IndexError: list index out of range

I think the deepcopy is messing some of the other properties of the object.

from openpyxl import load_workbook
from openpyxl.writer.excel import save_virtual_workbook
from io import BytesIO
import copy
import pysftp

conn = pysftp.Connection()
with conn.open() as f:
    wb_template = load_workbook(file)

wb1 = copy.deepcopy(wb_template)
wb2 = copy.deepcopy(wb_template)

wb1['Sheet'].cell(1,1).value = 'wb1'
wb2['Sheet'].cell(1,1).value = 'wb2'

wbs = {}
wbs['wb1'] = wb1 
wbs['wb2'] = wb2 

# Copying with deepcopy works but exporting to file gives errors!
print(wbs['wb1']['Sheet'].cell(1,1).value)
print(wbs['wb2']['Sheet'].cell(1,1).value)

# Writing to file deep copied workbook gives error
conn.putfo(BytesIO(save_virtual_workbook(wbs['wb1'])), 'file.xlsx')

Is the deep copy a requirement? Otherwise, you can just load the workbooks twice with load_workbook. ```wb1 = load_workbook(file)``` ```wb2 = load_workbook(file)``` — ConSod, Oct 01 '19 at 16:23
Possible duplicate of [Copy whole worksheet with openpyxl](https://stackoverflow.com/questions/27101024/copy-whole-worksheet-with-openpyxl) — stovfl, Oct 01 '19 at 18:04
It is not a duplicate. I ask for copy of workbook object not of its sheets. — Konstantin, Oct 01 '19 at 18:05

score 1 · Answer 1 · answered Oct 02 '19 at 08:59

The openpyxl.Workbook object doesn't support deepcopy.

To avoid importing the .xlsx template file several times, I first read it from the connection as bytes and then execute load_workbook several times to create different workbook objects:

connection.open() as f:
    template_file = BytesIO(f.read())

wb1 = load_workbook(template_file)
wb2 = load_workbook(template_file)

Cannot copy openpyxl.Workbook object

1 Answers1