Export specific Sheet, and save different file openpyxl

Question

I'm trying to export a specific sheet from an excel file, but without a result. I want to export a specific sheet of paper to a completely new file What I have written is:

import openpyxl
book = openpyxl.load_workbook('C:\Python\test.xlsx')
a = (book.get_sheet_names())
sheet1 = book[a[5]]
sheet1.save('C:\Python\sheet2.xlsx')

Also, another thing I can't do,and look for a certain sheet if I have its name.

I apologize if the questions are simple, but it's been a few days since I started with python :)

Answer 1

Well, openpyxl does provide copy_worksheet() but it cannot be used between different workbooks. You can copy your sheet cell-by-cell or you can modify your starting workbook in memory and then you can save it with a different file name. Here is the code

import openpyxl

# your starting wb with 2 Sheets: Sheet1 and Sheet2
wb = openpyxl.load_workbook('test.xlsx')

sheets = wb.sheetnames # ['Sheet1', 'Sheet2']

for s in sheets:

    if s != 'Sheet2':
        sheet_name = wb.get_sheet_by_name(s)
        wb.remove_sheet(sheet_name)

# your final wb with just Sheet1
wb.save('test_with_just_sheet2.xlsx')