python工作表操作汇总

本文代码及数据集来自《超简单：用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()