python自动合计各部周销

下载依赖

pip install openpyxl -i https://pypi.doubanio.com/simple

pip install pandas -i https://pypi.doubanio.com/simple

引入依赖

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string

数据读入

	filePath1 = './src/超级原始数据精修.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"#销售明细df0 = pd.read_excel(filePath1, sheet_name='销售明细')

计算每周数据，并添加新列

获取表头字符串

# 获取列名
#column_names = df.columns
column_names_list = df0.columns.values
#获取列长度
col_num = len(column_names_list)

指定列求和

使用iloc选择要求和的列：

df['sum'] = code_table_data.iloc[:,3:6].sum(axis=1)

完整代码

from openpyxl import load_workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
# Press the green button in the gutter to run the script.
if __name__ == '__main__':filePath1 = './src/超级原始数据.xlsx'filePath2 = './src/数据精修.xlsx'# 加载工作簿wb = load_workbook(filePath1)# 获取sheet页,修改第一个sheet页面为name1 = wb.sheetnames[0]ws1 = wb[name1]ws1.title = "销售明细"wb.save(filePath1);# 销售明细df0 = pd.read_excel(filePath1, sheet_name='销售明细')column_names_list = df0.columns.valuescol_num = len(column_names_list)for k in range(0, int(col_num/7)):start_pos = k*7+1end_pos = k*7+7col_name = column_names_list[start_pos] +"到"+ column_names_list[end_pos]df0[col_name] = df0.iloc[:, start_pos:end_pos+1].sum(axis=1)# 将生成的工作表导入到程序中for k in range(1,len(column_names_list)):name=column_names_list[k]df0.pop(name)result_sheet = pd.ExcelWriter(filePath2, engine='openpyxl')  # 先定义要存入的文件名xxx，然后分别存入xxx下不同的sheet# df1将0转变为空df0.to_excel(result_sheet, "销售明细", index=False, na_rep=0, inf_rep=0)# 这步不能省，否则不生成文件result_sheet.save()print(column_names_list)
# See PyCharm help at https://www.jetbrains.com/help/pycharm/