# 1、 批量提取一个工作簿中所有工作表的特定数据
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购.xlsx')  # 打开工作簿
worksheet = workbook.sheets   # 列出工作簿中的所有工作表
data = []  # 创建一个空列表用于存放数据
for i in worksheet:
    values = i.range('A1').expand().options(pd.DataFrame).value  # 读取当前工作表的所有数据
    filtered = values[values['采购物品'] == '复印纸']  # 提取采购物为复印纸的行数据
    if not filtered.empty:    #  判断提取行数据是否为空
        data.append(filtered)  # 提取数据追加到列表中
new_workbook = xw.books.add()   # 新建工作簿
new_worksheet = new_workbook.sheets.add('复印纸')  # 新建工作簿中新增名为复印纸的工作表
new_worksheet.range('A1').value = pd.concat(data, ignore_index = False)  # 将提取的数据写入复印纸工作表中
new_workbook.save('复印纸.xlsx')
workbook.close()
app.quit()
'''
import os

# ###  concat(objs,axis=0,join='outer',join_axes=None,ignore_index=False,keys=None,levels=None,names=None,verify_integrity=False,copy=True)
# objs:要拼接的数据对象
# axis：拼接时所依据的轴，如果为0，则沿着行拼接，为1，则沿着列拼接
# join：拼接的方式，默认outer
# join_index:index对象列表
# ignore_index：默认为FALSE，如为Ture，忽略原有索引，生成新的数字序列作为新索引
# keys：序列，默认空。使用传递的键作为最外层构建层次索引，如果为多索引，应使用元组
# levels：序列列表，默认值空，用于构建唯一值
# names：列表，默认值为空，结果层次索引中的级别名称
# verify_integrity:默认FALSE，用于检查新拼接的轴是否包含重复值
# copy：默认TRUE，如果为FALSE，则不执行非必要的数据复制

# 2、批量提取一个工作簿中所有工作表的列数据
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.xlsx')
worksheet = workbook.sheets
column = ['采购日期', '采购金额']   # 指定要提取的列的标题
data = []  
for i in worksheet:
    values = i.range('A1').expand().options(pd.DataFrame, index = False).value
    filtered = values[column]  # 根据前面指定的列标题提取数据
    data.append(filtered)
new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add('提取数据')
new_worksheet.range('A1').value = pd.concat(data, ignore_index = False).set_index(column[0])
new_workbook.save('提取表.xlsx')
workbook.close()
app.quit()
'''

# 3、 在多个工作簿的指定工作表中批量追加行数据
'''
import os
import xlwings as xw
newContent = [['双肩包','64','110'],['腰包','23','58']]  # 要追加的行数据
app = xw.apps.add()
file_path = '分部信息'
file_list = os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1] == '.xlsx':
        workbook = app.books.open(file_path + '\\' + i)
        worksheet = workbook.sheets['产品分类表']  # 指定要追加行数据的工作表
        values = worksheet.range('A1').expand()  # 读取原有数据
        number = values.shape[0]  # 获取原有数据的行数
        worksheet.range(number + 1, 1).value = newContent  # 将前面指定的行数追加到原有数据的下方
        workbook.save()
        workbook.close()
app.quit()
'''
# 4、 对多个工作簿中指定工作表的数据进行分列
'''
import os
import xlwings as xw
import pandas as pd
file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):   # 判断是否有文件以~$开头
        continue   # 如果有，则跳过
    file_paths = os.path.join(file_path, i)  # 将文件夹路径和名称拼接成工作表的完整路径
    workbook = app.books.open(file_paths)  # 打开工作簿
    worksheet = workbook.sheets['规格表']  # 指定要处理的工作表
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table') # 读取指定工作表数据
    new_values = values['规格'].str.split('*', expand = True)  # 根据*拆分规格列
    values['长(mm)'] = new_values[0]
    values['宽(mm)'] = new_values[1]
    values['高(mm)'] = new_values[2]
    values.drop(columns=['规格'], inplace = True)  # 删除规格列
    worksheet['A1'].options(index = False).value = values  # 用分列后的数据替换工作表中的原有数据
    worksheet.autofit()  # 根据数据内容自动调整工作表的行高列宽
    workbook.save()
    workbook.close()
app.quit()
'''
# ### Series.str.split(pat=None,n=-1,expand=False)
# pat:指定分隔符，默认为空格；
# n：指定拆分的次数，1为在第1个分隔符拆分，2为1、2个分隔符拆分
# expand:TRUE则为DataFrame，FALSE，则为Series
# ### DataFrame.drop(labels=None,axis=0,index=None,columns=None,inplace=False)
# labels:要删除的行、列的名称
# axis：默认为0，表示删除列。如为1，则删除行
# index：指定要删除的行
# columns：指定要删除的列
# inplace：默认False，表示删除不改变原DataFrame，返回执行删除操作后的新DataFrame。如为True，直接在原DataFrame上删除，无法恢复。

# 5、批量合并多个工作簿中指定工作表中的列数据
'''
import os
import xlwings as xw
import pandas as pd
file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    worksheet = workbook.sheets['规格表']
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value
    # 合并列数据
    values['规格'] = values['长(mm)'].astype('str') + values['宽(mm)'].astype('str') + '*' + values['高(mm)'].astype('str')
    values.drop(columns=['长(mm)'], inplace= True)  # 删除标题为长（mm）的列
    values.drop(columns=['宽(mm)'], inplace= True)
    values.drop(columns=['高(mm)'], inplace= True)
    worksheet.clear()  # 清楚工作表规格表中原有数据
    worksheet['A1'].options(index = False).value = values  # 将处理好的数据写入工作表
    worksheet.autofit()
    workbook.save()
    workbook.close()
app.quit()
'''

# 6、多个工作簿中指定工作表的列数据拆分成多行
'''
import os
import xlwings as xw
import pandas as pd
file_path = '产品记录表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path,i)
    workbook = app.books.open(file_paths)
    worksheet = workbook.sheets['规格表']
    values = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value
    new_values = values['规格'].str.split('*', expand = True)
    values['长(mm)'] = new_values[0]
    values['宽(mm)'] = new_values[1]
    values['高(mm)'] = new_values[2]
    values.drop(columns=['规格'], inplace= True)
    values = values.T  # 转换数据的行列
    values.columns = values.iloc[0]
    values.index.name = values.iloc[0].index.name
    values.drop(values.iloc[0].index.name, inplace= True)
    worksheet.clear()
    worksheet['A1'].value = values
    worksheet.autofit()
    workbook.save()
    workbook.close()
app.quit()
'''

# 7、批量提取一个工作簿中所有工作表的唯一值
### 将这6个工作表中的书名提取出来，但是不能有重复的书名
'''
import xlwings as xw
app = xw.App(visible= True, add_book = False)
workbook = app.books.open('上半年销售统计表.xlsx')
data = []   #创建一个空列存放书名数据
for i, worksheet in enumerate(workbook.sheets):   #  遍历工作簿中的工作表
    values = worksheet['A2'].expand('down').value  #  提取当前工作表中的书名数据
    data = data + values
data = list(set(data))   # 对列表中的书名数据进行去重操作
data.insert(0, '书名')  # 在去重后的书名数据前添加列标题“书名”
new_workbook = xw.books.add()   # 新建工作簿
new_worksheet = new_workbook.sheets.add('书名')   # 新工作簿中新增名为“书名”的工作表
new_worksheet['A1'].options(transpose = True).value = data   #  将处理好的书名数据写入新工作表
new_worksheet.autofit()
new_workbook.save('书名.xlsx')
workbook.close()
app.quit()
'''
###  insert(index,obj)
# index:要插入元素的位置
# obj：要插入的元素

# 8、 批量提取一个工作簿中所有工作表的唯一值并汇总
'''
import xlwings as xw
app = xw.App(visible = True, add_book = False)
wb = app.books.open('销售统计表.xlsx')
data = list()  # 创建空列表用于存放书名和销售明细
for i, sht in enumerate(wb.sheets):
    values = sht['A2'].expand('table').value
    data = data + values
sales = dict()    # 创建空字典存放书名和销量汇总
for i in range(len(data)):   #遍历书名和销量明细
    name = data[i][0]   # 获取书名
    sale = data[i][1]   # 获取销量
    if name not in sales:
        sales[name] = sale   # 如果不存在，字典中添加此书名的销量记录
    else:
        sales[name] += sale  #  如果存在，计算书名累加销量
dictlist = list()
for key, value in sales.items():
    temp = [key, value]  # 列出书名和累积销量
    dictlist.append(temp)
dictlist.insert(0, ['书名','销量'])  # 获取的数据前添加列标题书名和销量
new_workbook = xw.books.app()
new_worksheet = new_workbook.sheets.add('销量统计')
new_worksheet['A1'].value = dictlist
new_worksheet.autofit()
new_workbook.save('销售统计.xlsx')
wb.close
app.quit()
'''

# 9、调整多个工作簿的行高列宽
'''
import os
import xlwings as xw
file_path = 'e:\\table\销售表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path, i)
    for j in workbook.sheets:
        value = j.range('A1').expand('table')  # 在工作表选择要调整行高列宽的区域
        value.column_width = 12  #  列宽调整为12个字符宽度
        value.row_height = 20  # 将行高设置为20
    workbook.save()
    workbook.close()
app.quit()
'''

# 10、一个工作簿中所有表的行高列宽
'''
import xlwings as xw
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('e:\\table\*.xlsx')
for i in workbook.sheets:
    value = i.range('A1').expand('table')
    value.column_width = 12
    value.row_height = 20
workbook.save()
app.quit()
'''

# 11、批量更改多个工作簿的数据格式
'''
import os
import xlwings as xw
file_path = '采购'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    for j in workbook.sheets:
        row_num = j['A1'].current_region.last_cell.row  # 获取工作表数据区域最后一行的行号
        j['A2:A{}'.format(row_num)].number_format = 'm/d'  # A列的采购如期全部更改为月/日格式
        j['D2:D{}'.format(row_num)].number_format = '￥#，##0.00'  # D列的采购金额更改为货币符号和两位小数格式
    workbook.save()
    workbook.close()
app.quit()
'''

# 12、批量更改多个工作簿的外观格式
'''
import os
import xlwings as xw
file_path = '销售表'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    for j in workbook.sheets:
        j['A1:H1'].api.Font.Name = '宋体'  # 标题行为宋体
        j['A1:H1'].api.Font.Size = 10
        j['A1:H1'].api.Font.Bold = True  # 字体加粗
        j['A1:H1'].api.Font.Color = xw.utils.rgb_to_int((255,255,255))  # 字体颜色为白色
        j['A1:H1'].color = xw.utils.rgb_to_int((0,0,0))  # 单元格填充颜色为黑色
        j['A1:H1'].api.HorizontalAlignment = xw.constants.VAlign.xlHAlignCenter  # 工作表标题行的水平对齐方式居中
        j['A1:H1'].api.VerticalAlignment = xw.contants.VAlign.xlVAlignCenter # 工作表标题行垂直对齐方式居中
        j['A2'].expand('table').api.Font.Name = '宋体'
        j['A2'].expand('table').api.Font.Size = 10
        j['A2'].expand('table').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft  # 设置正文水平对齐方式靠左
        j['A2'].expand('table').api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter  # 设置正文垂直对齐方式居中
        for cell in j['A1'].expand('table'):
            for b in range(7,12):
                cell.api.Borders(b).LineStyle =1   # 边框线型为1
                cell.api.Borders(b).Weight = 2     # 单元格边框粗细2
    workbook.save()
    workbook.close()
app.quit()
'''

# 13、批量替换多个工作簿的行数据
'''
import os
import xlwings as xw
file_path = '分部信息'
file_list = os.listdir(file_path)
app = xw.App(visible = False, add_book = False)
for i in file_list:
    if i.startswith('~$'):
        continue
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)
    for j in workbook.sheets:
        value = j['A2'].expand('table').value
        for index, val in enumerate(value):    # 按行遍历工作表数据
            if val == ['背包', 16, 65]:
                value[index] = ['双肩包', 36, 46]
            j['A2'].expand('table').value = value
    workbook.save()
    workbook.close()
app.quit()
'''

# 14、批量升序工作簿中所有工作表
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('销售表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    values = i.range('A1').expand('table').options(pd.DataFrame).value  # 读取当前工作表数据，并转换为DataFrame格式
    result = values.sort_values(by = '销售利润')  # 对销售利润进行升序排列
    i.range('A1').value = result   # 将排序结果写入当前工作表，替换原有数据
workbook.save()
workbook.close()
app.quit()
'''
###  sort_values(by='##',axis=0,ascending=True,inplace=False,na_position='last')
    # by:要排序的列名或索引值
    # axis：省略或者0或者index，则按照by指定的列中的数据排序；如为1或columns，则按照by指定的索引的数据排序
    # ascending：排序方式。省略或者TRUE，为升序；如果为false，则做降序排列
    # inplace：省略或者false，不替换；如果TRUE，则用排序后的数据替换原来数据
    # na_pasition：空值的显示位置，如果为first，将空值放在列的首位；如果为last，则放在列的末尾
'''
result = values.sort_values(by = '销售利润', ascending=False)  # 对销售利润列进行降序排序
'''

# 15、批量排序多个工作簿中的数据
'''
import xlwings as xw
import pandas as pd
import os
app = xw.App(visible = False, add_book = False)
file_path = '产品销售表'
file_list = os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1] == 'xlsx':
        workbook = app.books.open(file_path + '\\' + i)
        worksheet = workbook.sheets
        for j in worksheet:
            values = j.range('A1').expand('table').options(pd.DataFrame).value
            result = values.sort_values(by = '销售利润')
            j.range('A1').value = result
        workbook.save()
        workbook.close()
app.quit()
'''

# 16、筛选一个工作簿中所有工作表数据
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.xlsx')
worksheet = workbook.sheets
table = pd.DataFrame()  # 创建一个空DataFrame
for i,j in enumerate(worksheet):
    # 读取当前工作表的数据
    values = j.range('A1').options(pd.DataFrame, header = 1, index= False, expand= 'table').value
    # 调整列的顺序
    data = values.reindex(columns = ['采购物品', '采购日期', '采购数量', '采购金额'])
    # 将调整列顺序后的数据合并到前面创建的DataFrame中
    table = table.append(data, ignore_index = True)
table = table.groupby('采购物品')  # 根据采购物品列筛选数据
new_workbook = xw.books.add()   # 新建一个工作簿
for idx, group in table:
    new_worksheet = new_workbook.sheets.add(idx)  # 在新工作簿中新增工作表，以物品名称作为工作表名
    new_worksheet['A1'].options(index = False).value = group  # 新工作表中写入当前物品的所有明细
    last_cell = new_worksheet['A1'].expand('table').last_cell  # 获取当前工作表数据区域右下角的单元格
    last_row = last_cell.row
    last_column = last_cell.column
    last_column_letter = chr(64 + last_column)  # 将数据区域最后一列的数字转换为改列的列标字母
    sum_cell_name = '{}{}'.format(last_column_letter, last_row+1)  # 获取数据区域右下角单元格下方的单元格位置
    sum_last_row_name = '{}{}'.format(last_column_letter, last_row)  # 获取数据区域右下角单元格位置
    formula = ' =SUM({}2:{})'.format(last_column_letter, sum_last_row_name) # 根据前面获取单元格位置构造Excel公式，对求购金额求和
    new_worksheet[sum_cell_name].formula = formula  # 将求和公式写入数据区域右下角单元格下方单元格中
    new_worksheet.autofit()   # 自动调整工作表的行高和列宽
new_worksheet.save('采购分类表.xlsx')
workbook.close()
app.quit()
'''
# reindex(index = **, columns = **, fill_value=0)
# index：要改变位置的行，**为列表
# columns：要改变位置的列，**为列表
# fill_value：可选参数，前面两个不存在时，可用该参数定义如何填充缺失值

# chr(64+列号) 可以将列号转换为列表

# 17、在一个工作簿中筛选单一类别数据
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.xlsx')
worksheet = workbook.sheets
table = pd.DataFrame()
for i,j in enumerate(worksheet):
    values = j.range('A1').options(pd.DataFrame, header=1, index = False, expand = 'table').value
    data = values.reindex(columns = ['采购物品','采购日期','采购数量','采购金额'])
    table = table.append(data, ignore_index = True)  # 将多个工作表数据合并到一个DataFrame中
product = table[table['采购物品'] == '保险箱']  # 筛选采购物品是保险箱的数据
new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add('保险箱')
new_worksheet['A1'].options(index = False).value = product  # 将筛选出数据写入工作表（index=False为删除索引列）
new_worksheet.autofit()
new_workbook.save('保险箱.xlsx')
new_workbook.close()
app.quit()
'''

# 18、对多个工作簿的工作表进行分类汇总
'''
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = '销售表'
file_list = os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1] == 'xlsx':
        workbook = app.books.open(file_path + '\\' + i)
        worksheet = workbook.sheets
        for j in worksheet:
            values = j.range('A1').expand('table').options(pd.DataFrame).value  # 读取当前工作表的数据
            values['销售利润'] = values['销售利润'].astype('float')  # 转换销售利润数据类型
            result = values.groupby('销售区域').sum()  # 根据销售区域列对数据进行分类汇总，求和
            j.range('J1').value = result['销售利润']   # 将各个销售区域销售利润汇总结果写入当前工作表
        workbook.save()
        workbook.close()
app.quit()
'''
# groupby()函数后接的sum()函数用于进行求和汇总，还可以使用其他函数完成其他类型的汇总运算。常用的有：用mean()函数求平均值，
# 用count()函数统计个数，用max()函数求最大值，用min()函数求最小值。

# 19、批量分类汇总多个工作簿中的指定工作表
'''
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = '销售表'
file_list = os.listdir(file_path)
for i in file_list:
    if os.path.splitext(i)[1] == 'xlsx':
        workbook = app.books.open(file_path + '\\' + i)
        worksheet = workbook.sheets['销售记录表']  # 指定要分类汇总的工作表
        values = worksheet.range('A1').expand('table').options(pd.DataFrame).value
        values['销售利润'] = values['销售利润'].astype('float')
        result = values.groupby('销售区域').sum()
        worksheet.range('J1').value = result['销售利润']
        workbook.save()
        workbook.close()
app.quit()
'''

# 20/ 将多个工作簿数据分类汇总到一个工作簿
'''
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = '销售表'
file_list = os.listdir(file_path)
collection = []
for i in file_list:
    if os.path.splitext(i)[1] == 'xlsx':
        workbook = app.books.open(file_path + '\\' + i)
        worksheet = workbook.sheets['销售记录表']
        values = worksheet.range('A1').expand('table').options(pd.DataFrame).value
        filtered = values[['销售区域', '销售利润']]    # 只保留两列数据
        collection.append(filtered)
        workbook.close()
new_values = pd.concat(collection, ignore_index = False).set_index('销售区域')
new_values['销售利润'] = new_values['销售利润'].astype('float')
result = new_values.groupby('销售区域').sum()
new_workbook = app.books.add()
sheet = new_workbook.sheets(0)
sheet.range('A1').value = result
new_workbook.save('汇总.xslx')
app.quit()
'''

# 21、对一个工作簿中所有表分别求和
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    values = i.range('A1').expand('table')
    data = values.options(pd.DataFrame).value
    sums = data['采购金额'].sum()  # 创建的DataFrame中对采购金额进行求和
    column = values.valuep[0].index('采购金额') + 1  # 获取采购金额列的序号
    row  = values.shape[0]  # 获取数据区域最后一行行号
    i.range(row+1, column).value = sums  # 将求和结果写入采购金额列最后一个单元格下方的单元格
workbook.save()
workbook.close()
app.quit()
'''
# index(obj,start,end)
# obj:要查找的元素
# start:查找的起始位置
# end：可选，查找的结束位置

# 22、将所有求和结果写入固定单元格
'''
import xlwings as xw
import pandas as pd
app = xw.App(visible =False, add_book = False)
workbook = app.books.open('采购表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    values = i.range('A1').expand('table').options(pd.DataFrame)
    sums = values['采购金额'].sum()
    i.range('F1').value = sums   # 将当前工作表中的数据求和结果写入当前工作表的单元件中
workbook.save()
workbook.close()
app.quit()
'''

# 23、批量统计工作表的最大值最小值
'''
file_path = '产品销售统计表'
file_list = os.listdir(file_path)
for j in file_list:
    if os.path.splitext(j)[1] == '.xslx':
        workbook = app.books.open(file_path + '\\' + j)
        worksheet = workbook.sheets
        for i in worksheet:
            values = i.range('A1').expand('table').options(pd.DataFrame).value
            max = values['销售利润'].max()
            min = values['销售利润'].min()
            i.range('I1').value = '最大销售利润'
            i.range('J1').value = max
            i.range('I2').value = '最小销售利润'
            i.range('J2').value = min
    workbook.save()
    workbook.close()
app.quit()
'''

# 24、批量统计工作簿中所有表的最大最小值
'''
workbook = xw.Book('产品销售统计表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
    values = i.range('A1').expand('table').options(pd.DataFrame).value
    max = values['销售利润'].max()
    min = values['销售利润'].min()
    i.range('I1').value = '最大利润'
    i.range('J1').value = max
    i.range('I2').value = '最小利润'
    i.range('J2').value = min
workbook.save()
workbook.close()
app.quit()
'''

出处：https://www.cnblogs.com/chengfo/p/17284335.html