本文代码及数据集来自《超简单:用Python让Excel飞起来(实战150例)》
# 提取一个工作簿中所有工作表的名称(方法一)
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets # 获取工作簿中的所有工作表
lists = []
for i in worksheet:
sheet_name = i.name
lists.append(sheet_name)
print(lists)
workbook.close()
app.quit()
# 提取一个工作簿中所有工作表的名称(方法二)
import pandas as pd
file_path = 'F:\\python\\第3章\\新能源汽车备案信息.xlsx'
data = pd.read_excel(file_path, sheet_name=None) # 字典的键为工作表的名称,字典的值为对应的工作表数据
worksheet_name = list(data.keys())
print(worksheet_name)
# 在一个工作簿中新增一个工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets
new_sheet_name = '产品信息表'
lists = []
for i in worksheet:
sheet_name = i.name
lists.append(sheet_name)
if new_sheet_name not in lists: # 判断工作簿中是否不存在名为“产品信息表”的工作表
worksheet.add(name=new_sheet_name)
# new_sheet = worksheet.add(before=worksheet[1])表示在第2个工作表之前新增一个工作表
# new_sheet.name = '产品信息表'表示将新增工作表命名为'产品信息表'
workbook.save()
workbook.close()
app.quit()
# 在一个工作簿中删除一个工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets
del_sheet_name = '汽车备案信息'
for i in worksheet:
sheet_name = i.name
if sheet_name == del_sheet_name:
i.delete()
break
workbook.save()
workbook.close()
app.quit()
# 在多个工作簿中批量新增工作表
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第3章\\上半年销售统计\\')
file_list = folder_path.glob('*.xls*')
new_sheet_name = '产品信息表'
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
lists = []
for j in worksheet:
sheet_name = j.name
lists.append(sheet_name)
if new_sheet_name not in lists:
worksheet.add(name=new_sheet_name)
workbook.save()
workbook.close()
app.quit()
# 在多个工作簿中批量删除工作表
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第3章\\汽车信息\\')
file_list = folder_path.glob('*.xls*')
del_sheet_name = 'Sheet1'
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
sheet_name = j.name
if sheet_name == del_sheet_name:
j.delete()
break
workbook.save()
workbook.close()
app.quit()
# 重命名一个工作簿中的一个工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽车备案信息':
i.name = '汽车信息'
break
workbook.save()
workbook.close()
app.quit()
# 重命名一个工作簿中的所有工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\上半年销售统计.xlsx')
worksheet = workbook.sheets
for i in worksheet:
i.name = i.name.replace('销售表', '')
workbook.save()
workbook.close()
app.quit()
# 重命名多个工作簿中的同名工作表
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第3章\\销售统计\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name == '产品信息':
j.name = '配件信息'
break
workbook.save()
workbook.close()
app.quit()
# 将一个工作表复制到另一个工作簿
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open('F:\\python\\第3章\\产品信息表.xlsx')
workbook2 = app.books.open('F:\\python\\第3章\\1月销售表.xlsx')
worksheet1 = workbook1.sheets['配件信息']
worksheet2 = workbook2.sheets[0]
worksheet1.copy(before=worksheet2)
workbook2.save()
app.quit()
# 将一个工作表批量复制到多个工作簿
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook1 = app.books.open('F:\\python\\第3章\\产品信息表.xlsx')
worksheet1 = workbook1.sheets['配件信息']
folder_path = Path('F:\\python\\第3章\\上半年销售统计\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook2 = app.books.open(i)
worksheet2 = workbook2.sheets[0]
worksheet1.copy(before=worksheet2)
workbook2.save()
app.quit()
# 按条件将一个工作表拆分为多个工作簿
import pandas as pd
file_path = 'F:\\python\\第3章\\销售表.xlsx'
data = pd.read_excel(file_path, sheet_name='总表')
pro_data = data.groupby('产品名称') # 按“产品名称”列分组
for i, j in pro_data:
new_file_path = 'F:\\python\\第3章\\拆分\\' + i + '.xlsx' # 构造以产品名称命名的工作簿的文件路径
j.to_excel(new_file_path, sheet_name=i, index=False) # 将拆分后的产品数据写入工作簿
# 按条件将一个工作表拆分为多个工作表
import pandas as pd
file_path = 'F:\\python\\第3章\\销售表.xlsx'
data = pd.read_excel(file_path, sheet_name='总表')
pro_data = data.groupby('产品名称')
with pd.ExcelWriter('F:\\python\\第3章\\各产品销售表.xlsx') as workbook:
for i, j in pro_data:
j.to_excel(workbook, sheet_name=i, index=False)
# 将一个工作表横向拆分为多个工作表
import pandas as pd
file_path = 'F:\\python\\第3章\\销售数量统计.xlsx'
data = pd.read_excel(file_path, sheet_name='总表')
head_col = list(data.columns) # 获取工作表“总表”中各列的列名
same_col = data[['配件编号', '配件名称']] # 获取拆分后每个工作表中都要有的固定列的数据
with pd.ExcelWriter('F:\\python\\第3章\\各产品销售表1.xlsx') as workbook: # 新建工作簿
for i in head_col[2:]: # 遍历要拆分的月份列的列名
dif_col = data[i] # 根据列名获取单个月份的数据
sheet_data = pd.concat([same_col, dif_col], axis=1) # 将固定列与单个月份的列数据横向拼接起来
sheet_data.to_excel(workbook, sheet_name=i, index=False)
# 纵向合并多个工作表为一个工作表
import pandas as pd
file_path = 'F:\\python\\第3章\\上半年销售统计.xlsx'
data = pd.read_excel(file_path, sheet_name=None) # 读取工作簿中所有工作表的数据
all_data = pd.concat(data, ignore_index=True) # 将所有工作表的数据纵向拼接为一个DataFrame
new_file_path = 'F:\\python\\第3章\\销售统计.xlsx'
all_data.to_excel(new_file_path, sheet_name='总表', index=False)
# 横向合并多个工作表为一个工作表
import pandas as pd
file_path = 'F:\\python\\第3章\\产品各月销售数量表.xlsx'
data = pd.read_excel(file_path, sheet_name=None)
all_data = data['1月'][['配件编号', '配件名称']] # 从工作表“1月”中获取“配件编号”和“配件名称”这两个固定列的数据
for i in data:
col = data[i].iloc[:, [2]] # 获取工作表第三列的数据
all_data = pd.concat([all_data, col], axis=1) # 将两个固定列与各个工作表的第3列横向拼接起来
new_file_path = 'F:\\python\\第3章\\合并表.xlsx'
all_data.to_excel(new_file_path, sheet_name='总表', index=False)
# 设置工作表的标签颜色
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽车备案信息':
i.api.Tab.Color = 255 # 将工作表“汽车备案信息”的标签颜色设置为红色
# Color属性整数值=R+G×256+B×256×256,例如,红色的RGB值为(255,0,0)
workbook.save()
workbook.close()
app.quit()
# 隐藏一个工作簿中的一个工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\新能源汽车备案信息.xlsx')
worksheet = workbook.sheets
for i in worksheet:
if i.name == '汽车备案信息':
i.visible = False
workbook.save()
workbook.close()
app.quit()
# 隐藏多个工作簿中的一个同名工作表
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第3章\\区域销售统计\\')
file_list = folder_path.glob('*.xls*')
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name == '供应商信息':
j.visible = False
workbook.save()
workbook.close()
app.quit()
# 隐藏多个工作簿中的多个同名工作表
from pathlib import Path
import xlwings as xw
app = xw.App(visible=False, add_book=False)
folder_path = Path('F:\\python\\第3章\\区域销售统计\\')
file_list = folder_path.glob('*.xls*')
lists = ['配件信息', '供应商信息'] # 列表的元素为要隐藏的工作表的名称
for i in file_list:
workbook = app.books.open(i)
worksheet = workbook.sheets
for j in worksheet:
if j.name in lists:
j.visible = False
workbook.save()
workbook.close()
app.quit()
# 保护一个工作表
import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\python\\第3章\\产品信息表.xlsx')
worksheet = workbook.sheets['配件信息']
worksheet.api.Protect(Password='123', Contents=True)
workbook.save()
workbook.close()
app.quit()