python 对 excel sheet 的插入、复制、删除、重命名、设置标签颜色操作

0、import

import openpyxl
from openpyxl.styles import colors

 

1、插入 sheet

wb = openpyxl.Workbook()
wb.sheetnames

wb.create_sheet(title='my_sheet',
                index=0
               )
wb.sheetnames

 

2、复制 sheet

wb.sheetnames, wb.worksheets

for ws in wb.worksheets:
    wb.copy_worksheet(from_worksheet=ws)    # from_worksheet 参数需是 worksheet 对象
    
wb.sheetnames

 

 按：(1)、wb.copy_worksheet() 目前只能在同 workbook 上进行；

　　 (2)、wb.copy_worksheet() 没有 index 参数，复制到最后。

 

3、删除 sheet

wb.worksheets

wb.remove_sheet(wb.worksheets[-1])
wb.sheetnames

wb.remove_sheet(worksheet=wb.get_sheet_by_name('Sheet'))
wb.worksheets

 

 

4、重命名 sheet

wb.sheetnames

for i, ws in enumerate(wb.worksheets):
    ws.title = f'Sheet{i+1}'
    
wb.sheetnames

 

5、设置 sheet 的标签颜色

wb.worksheets, [ws.sheet_properties.tabColor for ws in wb.worksheets]

for ws in wb.worksheets:
    ws.sheet_properties.tabColor = colors.RED
    
wb.save('test.xlsx')

excel: