一安装

安装：pip install openpyxl

备注：

如果某一天库不好使了，解决办法如下：

1）卸载出问题的库：
pip uninstall pandas
pip uninstall openpyxl
2)重新安装
python -m pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple
python -m pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

二工作簿的新建保存和打开：

1创建工作簿（新建-保存）

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.Workbook(path) #新建一个工作簿
workbook.save(path) #保存工作簿

2打开工作簿（读取工作簿-指定工作表）

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #读取工作簿
worksheet = workbook['1月']

三工作表的创建删除与复制：

1显示工作簿中所有工作表的名称：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
show_worksheets = workbook.worksheets #获取所有工作表对象
for i in show_worksheets:
    print(i.title) #遍历出每个工作表的名称

2删除指定的工作表:

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #打开工作簿
worksheet = workbook['3月'] #指定工作表
workbook.remove(worksheet) #删除指定工作表
workbook.save(path) #在原表保存
备注：如果要另存为新文件，指定一个新的path即可！

3新建指定名称的工作表：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #打开工作簿
workbook.create_sheet('3月') #创建工作表3月
workbook.save(path) #保存工作簿

4复制指定名称的工作表并给表重命名：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #读取工作簿
copysheet = workbook.copy_worksheet(workbook['3月']) #复制工作表
copysheet.title = '4月' #修改工作表名
workbook.save(path) #保存工作簿

四练习：

1如何批量新建工作表

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test1223.xlsx'
workbook = vb.Workbook(path)
for i in range(1,32):
    workbook.create_sheet('12月' + str(i) + '日')
workbook.save(path)

2如何批量修改工作表的名字：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test1223.xlsx'
workbook = vb.load_workbook(path)
show_sheets = workbook.worksheets
for i in show_sheets:
    i.title = '北京' + '_' + i.title
workbook.save(path)

3保留含有上海两个字的表，其余表全部删除：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test1223.xlsx'
workbook = vb.load_workbook(path)
show_sheets = workbook.worksheets
for i in show_sheets:
    if i.title.split('_')[0] != '上海':
        worksheet = workbook[i.title]
        workbook.remove(worksheet)
workbook.save(path)

4复制30张表然后把模板删除：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test1223.xlsx'
workbook = vb.load_workbook(path)
for i in range(1,31):
    copysheet = workbook.copy_worksheet(workbook['模板']) #复制模板这张表
    copysheet.title = '12月' + str(i) + '日'
workbook.remove(workbook['模板']) #删除模板表
workbook.save(path)