Python批量处理Excel中的数据(6)

676 阅读2分钟

「这是我参与11月更文挑战的第6天,活动详情查看:2021最后一次更文挑战

1 批量升序排序

只要用过Excel,对排序功能就一定不陌生。

如下图,在要排序的列中选中任意单元格,里面就有一个升序的按钮,就可以对物料编码这一列进行升序排序。

image.png 很显然,在Excel中使用这功能没什么难度,那么如何用Python来完成数据的排序呢?

Sort_values()

在pandas模块中sort_values()函数完成数据的排序。

代码如下:

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
result = values.sort_values(by='物料编码')
i.range('A1').value = result
workbook.save()
workbook.close()
app.quit()

其中第四行代码中设置的工作簿文件路径可根据实际需求更改。

第六至九行代码是核心功能,对工作簿中的所有工作表进行升序排序。

sort_values()是pandas模块中DataFrame对象函数,用于将数据区域按照某个字段的数据进行排序,这个字段可以是行字段,也可以是列字段。其语法如下:

sort_values(by='##',axis=0,ascending=True,inplace=False,na_position='last')

如果想要进行降序排序,则在sort_values()函数中用参数ascending来指定排序方式。

result = values.sort_values(by='物料编码',ascending = False)

2 筛选Excel中的数据

用Python对明细数据按物品名称进行归类整理,并对每种物品的销售金额进行求和:

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,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)
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'].wxpand('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)
    new_worksheet[sum_cell_name].formula = formula
    new_worksheet.autofit()
    new_workbook.save('业绩分类表.xlsx')
workbook.close()
app.quit()

第9行代码中的列标题必须和工作表中实际的列标题一致,顺序可以根据需求调整。

第11行代码中用于筛选的列为“产品”,可根据实际需求更改为其他列。

第22行代码中构造的Excel公式用于对采购金额进行求和,可根据实际需求更改公式,完成其他计算,例如,将“SUM”改为“AVERAGE”就是求平均值,改为“MAX”就是求最大值,等等。

reindex()是pandas模块中的函数,用于改变行、列的顺序。groupby()用于对数据进行分组。