目录
举一反三 对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格
案例01 批量升序排序一个工作簿中的所有工作表
- 代码文件：批量升序排序一个工作簿中的所有工作表.py
- 数据文件：产品销售统计表.xlsx
每个表批量对销售利润进行升序排列：
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\MLoong\Desktop\22\产品销售统计表.xlsx')
for i in workbook.sheets:
values=i.range('A1').expand().options(pd.DataFrame).value #读取当前工作表的数据并装换成DataFrame类型
result=values.sort_values(by='销售利润') #对销售利润进行排序
i.range('A1').value=result
workbook.save()
workbook.close()
app.quit()知识延伸
第
8
行代码中的
sort_values()
是
pandas
模块中DataFrame对象的函数,用于将数据区域按照某个字段的数据进行排序，这个字段可以是行字段，也可以是列字段。在
3.5.3
节曾简单介绍过s
ort_values()函数的用法，这里再详细介绍一下该函数的语法格式和常用参数含义。
sort_values(by='##',axis=0,ascending=True,inplace=False,na_position='last')
举一反三 批量排序多个工作簿中的数据
- 代码文件：批量排序多个工作簿中的数据.py
- 数据文件：产品销售统计表（文件夹）
除了对一个工作簿的数据进行批量排序，还可以对多个工作簿的数据进行批量排序，具体代码如下
import os
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
file_path=r'C:\Users\MLoong\Desktop\22\产品销售统计表'
file_list=os.listdir(file_path)
for i in file_list:
if os.path.splitext(i)[1]=='.xlsx':
file_paths=os.path.join(file_path,i)
workbook=app.books.open(file_paths)
for j in workbook.sheets:
values= j.range('A1').expand().options(pd.DataFrame).value
result=values.sort_values(by='销售利润')
j.range('A1').value=result
workbook.save()
workbook.close()
app.quit()案例02 筛选一个工作簿中的所有工作表数据
- 代码文件：筛选一个工作簿中的所有工作表数据.py
- 数据文件：采购表.xlsx
下图所示是按月份存放在不同工作表中的物品采购明细数据，如果要更改为按物品名称存放在不同工作表中，你会怎么做呢？
思路：先合并所有表，再按采购物品名称进行分表：
import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
workbook=app.books.open(r'C:\Users\MLoong\Desktop\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
如果要筛选的只是某一种物品的明细数据，如
“
保险箱
”，那么代码会更简单，具体如下。
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')
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'C:\Users\Administrator\Desktop\22\保险箱.xlsx')
new_workbook.close()
workbook.close()
app.quit()案例03 对多个工作簿中的工作表分别进行分类汇总
- 代码文件：对多个工作簿中的工作表分别进行分类汇总.py
- 数据文件：销售表（文件夹）
import os
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（文件夹）
如果只想分类汇总多个工作簿中的指定工作表，可以对案例
03的代码进行修改，修改后的代码如下。
import os
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
- 数据文件：销售表（文件夹）
如果想要将多个工作簿中的数据分类汇总到一个工作簿中，可以使用以下代码。
import os
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.
最低0.47元/天 解锁文章
6013
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
