pandas: best way split excel to list of sheets by 'Sheet' and index?

The problem is:

I have file with many Sheets ('Sheet1',Sheet2...) and need to create one new file with some of the Sheets (for example: 'Sheet16', 'Sheet23') or by index of Sheet for example 0(for first), 15, 22, -1 (for last).
Can help me?

As Suraj has already mentioned, you can use xlsxwriter to achieve the desired objective. Here's a slightly simpler and more interactive approach to your problem, using pandas.

To extract individual sheets from an Excel file, you can use the ExcelFile attribute of pandas. Here's how you can extract the sheets as dataframes:

xls = pd.ExcelFile(filename)
target_sheets = ['Sheet16', 'Sheet23', 'Sheet32']

or you can set target_sheets as a list of indices, say [16, 23, 32]

for sheet in target_sheets:
    if isinstance(sheet, int):
        sheet = xls.sheet_names[sheet]
    df = pd.read_excel(xls, sheet)
    print(df)

This will give you the individual sheets as pandas dataframes that you can view/modify as required. If you want to create a separate Excel file combining the listed number of sheets, you can use pd.ExcelWriter. Here's an example:

writer = pd.ExcelWriter(outfile)

for sheet in target_sheets:
    if isinstance(sheet, int):
        sheet = xls.sheet_names[sheet]
    df = pd.read_excel(xls, sheet)
    print(df)
    df.to_excel(writer, sheet_name=sheet, index=False)
    
writer.save()

Feel free to tweak with the variables and make sure to include import pandas as pd.

As BigBen mentioned in the comments you should use xl specific libraries.

One way would be by using xlsxwriter, a code like this might work for your use case:

import xlsxwriter

# Open org workbook
wb1 = xlsxwriter.Workbook('workbook.xlsx')
# Open the destination workbook
wb2 = xlsxwriter.Workbook('dest.xlsx')

# define the sheetnames that need to be copied
sheets = ["Sheet1", "Sheet2"]
for sheet in sheets:
   ws1 = wb1.get_sheet_by_name(sheet)
   # Create a new worksheet
   ws2 = wb2.add_worksheet(sheet)
   # Copy data
   ws2.copy_worksheet(ws1)

# Close the workbooks
wb_source.close()
wb_dest.close()

openpyxl can also be used with a similar code structure as the above example