Copy whole worksheet with openpyxl

Question

Please can someone give me an example, how to copy a whole worksheet with styles (from rows and columns) to a second worksheet in the same workbook ? (in a new workbook would also be possible)

Thank you.

P.S.: I tried to do a deepcopy, but that failed on saving changed data cells.
Purpose is: I try to fill some worksheets with my data and the first worksheet is my template.

I was successful in copying the values but only some styles. I am using the latest version of openpyxl, so please no 1.x methods.

score 28 · Accepted Answer · edited Jun 29 '16 at 18:43

28

Version 2.4 will allow you to do this: copy_worksheet

>>> source = wb.active
>>> target = wb.copy_worksheet(source)

For older ones you can probably copy the source code from here

UPDATE: You can't simply graft this code into older versions of the library

edited Jun 29 '16 at 18:43

Charlie Clark

18,477
4
49
55

answered Jun 21 '16 at 15:29

psychok7

5,373
9
63
101

1

Worth pointing out that the version you refer to has not yet had a final release. – Charlie Clark Jun 22 '16 at 10:57
4

Another point is that this "copy_worksheet" function seems to be only restricted to the same workbook. (Source: http://openpyxl.readthedocs.io/en/default/api/openpyxl.worksheet.copier.html?highlight=copy_worksheet "...from one worksheet to another within the same workbook.") I wanted to copy the worksheet from a "template" workbook to another output workbook. – Jason Aug 30 '17 at 09:42
Specifying a target isn't working for me. See here: https://stackoverflow.com/questions/51128029/copying-to-a-specific-sheet-openpyxl-destination-sheet-ignored-when-using-cop – virtualxtc Jul 02 '18 at 04:14

score 2 · Answer 2 · answered Nov 24 '14 at 09:43

2

You can't do this easily. The best approach is probably the one described in bug 171

answered Nov 24 '14 at 09:43

Charlie Clark

18,477
4
49
55

thanks for cheering me up. finally i found python-relatorio to be more helpful. was difficult to find... – ralf.w. Nov 25 '14 at 20:37

score 0 · Answer 3 · answered Sep 09 '15 at 18:54

0

I had the same problem. I solved using copy instead deepcopy. I found the solution on this site

I hope this works for you!

answered Sep 09 '15 at 18:54

Sam

313
1
7
15

The reason to use deepcopy is creating a new object as copy of another, and not just a reference – Sam Jul 29 '16 at 13:36

score 0 · Answer 4 · answered Mar 22 '22 at 11:20

Regarding the links for the Openpyxl repository on @charlie-clark and @sam answers, these are not working anymore since the repo was moved from Bitbucket to Gitlab.

The updated link for issue 171 is https://foss.heptapod.net/openpyxl/openpyxl/-/issues/171

score 0 · Answer 5 · answered Jan 11 '23 at 12:05

Notice that the copy_worksheet method does not copy data validations. You can do this manually using openpyxl's add_data_validation. A full working example:

# Copy worksheet
workbook = openpyxl.load_workbook(source_excel)
sheet_to_copy = workbook[sheet_name]
new_sheet = workbook.copy_worksheet(sheet_to_copy)
new_sheet.title = new_sheet_name

# Copy data validations
for data_validation in sheet_to_copy.data_validations.dataValidation:
    new_sheet.add_data_validation(data_validation)

# Save result
workbook.save(source_excel)

Copy whole worksheet with openpyxl

5 Answers5

Linked