CODEWITHSUNDEEP
pythonexcelopenpyxl
Konstantin
Konstantin

Reputation: 421

Cannot copy openpyxl.Workbook object

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

\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')

Upvotes: 2

Views: 1799

Answers (1)

Konstantin
Konstantin

Reputation: 421

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)

Upvotes: 1

Related Questions