释放双眼，带上耳机，听听看~！
- 代码文件：筛选一个工作簿中的所有工作表数据.py – 数据文件：采购表.xlsx 下图所示是按月份存放在不同工作表中的物品采购明细数据，如果要更改为按物品名称存放在不同工作表中，你会怎么做呢？
思路：先合并所有表，再按采购物品名称进行分表：
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'<a href="http://viplao.com/index.php/2023/05/03/%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%b7%a5%e4%bd%9c%e7%b0%bf%e4%b8%8e%e5%b7%a5%e4%bd%9c%e8%a1%a8/d:">http://viplao.com/index.php/2023/05/03/%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%b7%a5%e4%bd%9c%e7%b0%bf%e4%b8%8e%e5%b7%a5%e4%bd%9c%e8%a1%a8/d:</a>\22\采购表.xlsx')
#合并原工作簿中各工作表的数据
table=pd.DataFrame() #创建一个空的DataFrame
for i,j in enumerate(workbook.sheets) :
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)
#新建表，并写入数据
table=table.groupby('采购物品')
new_workbook=app.books.add()
for idx,group in table: #遍历筛选好的数据,其中idx对应物品名称，group对应物品的明细数据
new_worksheet=new_workbook.sheets.add(idx)
new_worksheet['A1'].options(index=False).value=group
#对分表进行求和，放在右下角最后一个位置
last_cell= new_worksheet['A1'].expand().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)
new_worksheet[sum_cell_name]. formula= formula
new_worksheet.autofit()
new_workbook.save(r'C:\Users\MLoong\Desktop\22\采购分类表.xlsx')
new_workbook.close()
workbook.close()
app.quit()
举一反三 在一个工作簿中筛选单一类别数据
- 代码文件：在一个工作簿中筛选单一类别数据.py – 数据文件：采购表.xlsx
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'http://viplao.com/index.php/2023/05/03/%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%b7%a5%e4%bd%9c%e7%b0%bf%e4%b8%8e%e5%b7%a5%e4%bd%9c%e8%a1%a8/d:\22\采购表.xlsx')
table=pd.DataFrame() #创建一个新的DataFrame
for i,j in enumerate(workbook.sheets):
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) #ignore_index=True是序号进行累加的意思
product=table[table['采购物品']=='保险箱'] #筛选"采购物品"是"保险箱"的数据
new_workbook=xw.books.add()
new_worksheet=new_workbook.sheets.add('保险箱')
new_worksheet['A1'].options(index=False).value=product
new_worksheet.autofit()
new_workbook.save(r'http://viplao.com/index.php/2023/05/03/%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%b7%a5%e4%bd%9c%e7%b0%bf%e4%b8%8e%e5%b7%a5%e4%bd%9c%e8%a1%a8/d:\22\保险箱.xlsx')
new_workbook.close()
workbook.close()
app.quit()
对多个工作簿中的工作表分别进行分类汇总
- 代码文件：对多个工作簿中的工作表分别进行分类汇总.py – 数据文件：销售表（文件夹）
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表'
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)
for j in workbook.sheets:
values=j.range('A1').expand().options(pd.DataFrame).value
values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
result=values.groupby('销售区域').sum()
j.range('J1').value=result['销售利润']
workbook.save()
workbook.close()
app.quit()
- 第13行代码中的astype()是pandas模块中DataFrame对象的函数，用于转换指定列的数据类型。该函数的语法格式和常用参数含义如下。
- 第14行代码中groupby()函数后接的sum()函数用于进行求和汇总，还可以使用其他函数完成其他类型的汇总运算。常用的有：用mean()函数求平均值，用count()函数统计个数，用max()函数求最大值，用min()函数求最小值。
举一反三 批量分类汇总多个工作簿中的指定工作表
- 代码文件：批量分类汇总多个工作簿中的指定工作表.py – 数据文件：销售表1（文件夹）
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表1'
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().options(pd.DataFrame).value
values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
result=values.groupby('销售区域').sum()
worksheet.range('J1').value=result['销售利润']
workbook.save()
workbook.close()
app.quit()
举一反三 将多个工作簿数据分类汇总到一个工作簿
- 代码文件：将多个工作簿数据分类汇总到一个工作簿.py – 数据文件：销售表（文件夹）
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\Administrator\Desktop\22\03\销售表'
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().options(pd.DataFrame).value
filtered=values[['销售区域','销售利润']]
collection.append(filtered)
workbook.close()
new_values=pd.concat(collection,ignore_index=False).set_index('销售区域')
values['销售利润']=values['销售利润'].astype('float') #转换‘销售利润’列的数据类型
result=new_values.groupby('销售区域').sum()
new_workbook=app.books.add()
new_worksheet=new_workbook.sheets.add('汇总表')
new_worksheet.range('A1').value=result
new_worksheet.autofit()
new_workbook.save(r'C:\Users\Administrator\Desktop\22\03\销售汇总表.xlsx')
new_workbook.close()
app.quit()
案例02 对一个工作簿中的所有工作表分别求和
- 代码文件：对一个工作簿中的所有工作表分别求和.py – 数据文件：采购表.xlsx
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
for i in workbook.sheets:
values=i.range('A1').expand()
data=values.options(pd.DataFrame).value
sums=data['采购金额'].sum()
column=values.value[0].index('采购金额')+1
row=values.shape[0]
i.range(row+1,column).value=sums
workbook.save()
workbook.close()
app.quit()
- 第10行代码中的index()是Python中列表对象的函数，常用于在列表中查找某个元素的索引位置。该函数的语法格式和常用参数含义如下。- 第11行代码中的shape是pandas模块中DataFrame对象的一个属性，它返回的是一个元组，其中有两个元素，分别代表DataFrame的行数和列数。
举一反三 对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格
- 代码文件：对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格.py – 数据文件：采购表.xlsx
2
3
4
5
6
7
8
9
10
11
12
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\Administrator\Desktop\22\采购表.xlsx')
for i in workbook.sheets:
values=i.range('A1').expand().options(pd.DataFrame).value
sums=values['采购金额'].sum()
i.range('F1').value=sums #将求和后的内容写到F1单元格中
workbook.save()
workbook.close()
app.quit()
