3

All I want to do is copy a worksheet from an excel workbook to another excel workbook in Python.

I want to maintain all formatting (coloured cells, tables etc.)

I have a number of excel files and I want to copy the first sheet from all of them into one workbook. I also want to be able to update the main workbook if changes are made to any of the individual workbooks.

It's a code block that will run every few hours and update the master spreadsheet.

I've tried pandas, but it doesn't maintain formatting and tables.

I've tried openpyxl to no avail

I thought xlwings code below would work:

import xlwings as xw

wb = xw.Book('individual_files\\file1.xlsx')
sht = wb.sheets[0]
new_wb = xw.Book('Master Spreadsheet.xlsx')
new_wb.sheets["Sheet1"] = sht

But I just get the error:

----> 4 new_wb.sheets["Sheet1"] = sht

AttributeError: __setitem__

"file1.xlsx" above is an example first excel file.

"Master Spreadsheet.xlsx" is my master spreadsheet with all individual files.

Pythonner12
  • 123
  • 1
  • 1
  • 8
  • What you're doing here is *assigning* a sheet in the current book to *equal* a sheet in another book. I would expect this to raise an error an error because that's not how a sheet gets copied. Does this answer help? https://stackoverflow.com/questions/45948255/copying-a-worksheet-with-xlwings-and-python – David Zemens Jun 20 '19 at 14:04
  • Is `xlwings` your only choice? – Chillie Jun 20 '19 at 14:46
  • @Chillie no it is not, infact, I would prefer to not use it! – Pythonner12 Jun 20 '19 at 14:57
  • You may want to look at [this (solution 2)](https://stackoverflow.com/a/44596301/4570170). You can get the [pywin32 package](https://github.com/mhammond/pywin32) with pip. Note however, that if you get exceptions, you may need to kill excel processes with task manager. – Chillie Jun 20 '19 at 15:14

3 Answers3

1

In the end I did this:

def copyExcelSheet(sheetName):

read_from = load_workbook(item)
#open(destination, 'wb').write(open(source, 'rb').read())
read_sheet = read_from.active
write_to = load_workbook("Master file.xlsx")
write_sheet = write_to[sheetName]

for row in read_sheet.rows:
    for cell in row:
        new_cell = write_sheet.cell(row=cell.row, column=cell.column,
                value= cell.value)
        write_sheet.column_dimensions[get_column_letter(cell.column)].width = read_sheet.column_dimensions[get_column_letter(cell.column)].width
        if cell.has_style:
            new_cell.font = copy(cell.font)
            new_cell.border = copy(cell.border)
            new_cell.fill = copy(cell.fill)
            new_cell.number_format = copy(cell.number_format)
            new_cell.protection = copy(cell.protection)
            new_cell.alignment = copy(cell.alignment)

write_sheet.merge_cells('C8:G8')
write_sheet.merge_cells('K8:P8')
write_sheet.merge_cells('R8:S8')

write_sheet.add_table(newTable("table1","C10:G76","TableStyleLight8"))
write_sheet.add_table(newTable("table2","K10:P59","TableStyleLight9"))

write_to.save('Master file.xlsx')
read_from.close

With this to check if the sheet already exists:

#checks if sheet already exists and updates sheet if it does.
def checkExists(sheetName):
    book = load_workbook("Master file.xlsx")   # open an Excel file and return a workbook

    if sheetName in book.sheetnames:
        print ("Removing sheet",sheetName)
        del book[sheetName]
    else:
        print ("No sheet ",sheetName," found, will create sheet")

    book.create_sheet(sheetName)
    book.save('Master file.xlsx')

with this to create new tables:

def newTable(tableName,ref,styleName):
    tableName = tableName + ''.join(random.choices(string.ascii_uppercase + string.digits + string.ascii_lowercase, k=15))
    tab = Table(displayName=tableName, ref=ref)
    # Add a default style with striped rows and banded columns
    tab.tableStyleInfo = TableStyleInfo(name=styleName, showFirstColumn=False,showLastColumn=False, showRowStripes=True, showColumnStripes=True)
    return tab
Pythonner12
  • 123
  • 1
  • 1
  • 8
  • This worked thanks! I had remove the `column_dimension` line, don't know where the `get_column_letter` is defined. Had to `import copy from copy` too. – sudo_dudo Feb 10 '21 at 00:31
0

Adapted from this solution, but note that in my (limited) testing (and as observed in the other Q&A), this does not support the After parameter of the Copy method, only Before. If you try to use After, it creates a new workbook instead.

import xlwings as xw

wb = xw.Book('individual_files\\file1.xlsx')
sht = wb.sheets[0]
new_wb = xw.Book('Master Spreadsheet.xlsx')
# copy this sheet into the new_wb *before* Sheet1:
sht.api.Copy(Before=new_wb.sheets['Sheet1'].api)
# now, remove Sheet1 from new_wb
new_wb.sheets['Sheet1'].delete()
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 1
    The `After` parameter is implemented, it's just a little different to write. You just have to use `None` before the `After` parameter. For example, `sht.api.Copy(None, After=wb.sheets[-1].api)`. See [here](https://stackoverflow.com/questions/56687602/copy-excel-sheet-from-one-worksheet-to-another-in-python). – mouwsy Dec 23 '20 at 21:25
0

This can be done using pywin32 directly. The Before or After parameter needs to be provided (see the api docs), and the parameter needs to be a worksheet <object>, not simply a worksheet Name or index value. So, for example, to add it to the end of an existing workbook:

def copy_sheet_within_excel_file(excel_filename, sheet_name_or_number_to_copy):
    excel_app = win32com_client.gencache.EnsureDispatch('Excel.Application')
    wb = excel_app.Workbooks.Open(excel_filename)
    wb.Worksheets[sheet_name_or_number_to_copy].Copy(After=wb.Worksheets[wb.Worksheets.Count])
    new_ws = wb.ActiveSheet
    return new_ws

As most of my code runs on end-user machines, I don't like to make assumptions whether Excel is open or not so my code determines if Excel is already open (see GetActiveObject), as in:

    try:
        excel_app = win32com_client.GetActiveObject('Excel.Application')
    except com_error:
        excel_app = win32com_client.gencache.EnsureDispatch('Excel.Application')

And then I also check to see if the workbook is already loaded (see Workbook.FullName). Iterate through the Application.Workbooks testing the FullName to see if the file is already open. If so, grab that wb as your wb handle.

You might find this helpful for digging around the available Excel APIs directly from pywin32:

def show_python_interface_modules():
        os.startfile(os.path.dirname(win32com_client.gencache.GetModuleForProgID('Excel.Application').__file__))
Stephen Genusa
  • 1