0

I'm using openpyxl (unfortunately I don't know how to find out my version number, installed it about a month ago) on Windows with python 2.7 and want to copy a worksheet that I generated using a template.xlsx file to a new workbook. The template has a single worksheet that I alter. I want to load it n times and copy each version as a new worksheet to another workbook. Could also be the same workbook ifneedbe.

I found some hints here which took me here. The example doesn't work as it seems the add_sheet() method has been removed.

primary.add_sheet(copy.deepcopy(ws),ido+1)
AttributeError: 'Workbook' object has no attribute 'add_sheet'

Also couldn't find anything helpful in the API.

Community
  • 1
  • 1
Dominic
  • 383
  • 1
  • 3
  • 16
  • Possible duplicate of [Copy whole worksheet with openpyxl](http://stackoverflow.com/questions/27101024/copy-whole-worksheet-with-openpyxl) – John Y Jan 20 '16 at 19:05

2 Answers2

0

I'm afraid copying worksheets is not supported because it is far from easy to do.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • That's unfortunate... do you probably know about another package that could do it? Could you point me to a place where it's described why it is so difficult to copy a worksheet as I can easily create an empy one? – Dominic Jan 15 '16 at 10:49
  • There s no easy way. See https://bitbucket.org/openpyxl/openpyxl/issues/171/copy-worksheet-function for some more information. – Charlie Clark Jan 15 '16 at 11:42
0

I was struggling with it as you. But, I could find out the way to solve. The best way I think to copy Excel worksheets using Openpyxl and Python:

from openpyxl import Workbook, load_workbook

# workbook source = wb1 and workbook destination = wb2

wb1 = load_workbook('file.xlsx')
wb2 = Workbook()
ws1 = wbs.active
ws2 = wbd.active

for r in plan1.iter_rows():
    for c in r:
        ws2[c.coordinate] = c.value

wb2.save('file2.xlsx')

The FOR loop with iter_rows() creates a named list with existing filled cells. And the 2nd FOR iterates in those cells ('A1','A2','B1' etc). The method .coordinate can be applied to the cell(c) and extract the Column,Row like 'A1' as a string. If we add it as an index of the worksheet, we can set it as a variable. Then just get the value of the cell(c), the magic is done. We can do something with data during the loop and after save it to the file.

Milton C Dias Jr
  • 11
  • 2