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()