3

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 :)

Razel
  • 41
  • 1
  • 3

1 Answers1

7

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')
floatingpurr
  • 7,749
  • 9
  • 46
  • 106
  • 1
    and automatically with the wb. remove_sheet command, remove all the sheets that I don't need? – Razel Sep 15 '17 at 12:14
  • Yes, it removes all the sheets except for the one named 'Sheet2' and it saves the `wb` in new file with just the sheet you need – floatingpurr Sep 15 '17 at 12:57
  • Last request, if I have un nome del foglio "hello world1, hello world2, etc", and I have to create the file that only has the last part of the page name, so world1, world2, etc. How do I do it? with[0:5] correct? As a result, the file name that I save is changed according to the name. – Razel Sep 15 '17 at 15:32
  • @Razel for example you could iterate over the remaining sheets and change their name like shown [here](https://stackoverflow.com/questions/39540789/how-to-rename-the-sheet-name-in-the-spread-sheet-using-python) – floatingpurr Sep 15 '17 at 15:37
  • Can i modify the same file usig Optimized mode i.e. write_only? – user2549538 Jul 06 '20 at 15:10
  • `write_only` is used for writing large files. It depends on what you want to do. Please, read warnings in this page https://openpyxl.readthedocs.io/en/stable/optimized.html in order to understand if this fits your needs. – floatingpurr Jul 15 '20 at 07:32