CODEWITHSUNDEEP
pythonpython-3.xexcelopenpyxl
Anastasia
Anastasia

Reputation: 27

How to copy a worksheet from another workbook using Python (openpyxl)?

I need to copy one worksheet to another one. I have a workbook wb1, which has many sheets. Firstly, I activate it and then create a new sheet at the beginning as I need to put it there:

import openpyxl

wb1= openpyxl.load_workbook(path_full_file)

sheet='current_date'

wb1.create_sheet(sheet, 0)

It works fine, the sheet 'current date' is created at the beginning. Then I need to copy to this sheet information from wb2, which has only one sheet:

\open wb2

wb2= openpyxl.load_workbook(path_to ).active

\trying to copy sheet from wb2 to wb1

wb1[sheet]=wb2

The last row does not work as I get the error: "AttributeError: setitem".

Could you advise what to do?

Many thanks in advance!

Upvotes: 0

Views: 698

Answers (1)

moken
moken

Reputation: 6555

In the following Xlwings code samples;

Example 1;
will open the workbook and copy the worksheet 'Sheet3' and place the copy before the first Sheet in the workbook i.e. the copied sheet is now the first sheet, and names the Sheet 'NewSheet'.
Then saves the workbook.
Example 2;
will copy the same sheet from workbook 1, 'excel_file.xlsx' to another workbook 2, 'new_excel_file.xlsx' as the first sheet and names the Sheet 'NewSheet'.
Takes around a second to run the code.

Example 1 copy sheet within the same workbook

import xlwings as xw


excel_file = 'excel_file.xlsx'

with xw.App(visible=False) as app:
    wb1 = xw.Book(excel_file)
    ws1 = wb1.sheets['Sheet3']

    ws1.copy(before=wb1.sheets(1), name="NewSheet")

    wb1.save(excel_file)

Example 2 copy sheet to another workbook

import xlwings as xw


excel_file1 = 'excel_file.xlsx'
excel_file2 = 'new_excel_file.xlsx'


with xw.App(visible=False) as app:
    wb1 = xw.Book(excel_file1)
    wb2 = xw.Book(excel_file2)
    ws1 = wb1.sheets['Sheet3']

    ws1.copy(before=wb2.sheets(1), name="NewSheet")

    wb2.save(excel_file2)

Upvotes: 2

Related Questions