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

发表于： 2023年8月22日 2023年8月22日
标签： python数据分析
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()
viplao

265