Python 日常数据分析 – EXCEL实战- 分类汇总

1
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=False, add_book=False)

workbook = app.books.open('文件名.xlsx')

worksheets = workbook.sheets

table = pd.DataFrame()

for i, j in enumerate(worksheets):

    data = j.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value

    data = data.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'].expand('table').last_cell

    last_row = last_cell.row

    last_column = last_cell.column

    last_column_letter = chr(64 + last_column)

    sum_cell_name = f'{last_column_letter}{last_row + 1}'

    sum_last_row_name = f'{last_column_letter}{last_row}'

    formula = f'=SUM({last_column_letter}2:{sum_last_row_name})'

    new_worksheet[sum_cell_name].formula = formula

    new_worksheet.autofit()

new_workbook.save('文件名.xlsx')

app.quit()