1. 完整代码
import glob
import os
import openpyxl
import csv
TARGET_FILE = glob.glob("日报表/*.xlsx")[-1]
def read_xls_file(file_path=TARGET_FILE):
"""读取xlsx文件并打印
:param file_path: Excel文件路径
"""
wb = openpyxl.load_workbook(file_path, data_only=True)
for sheet_name in wb.sheetnames:
print(sheet_name)
sheet = wb[sheet_name]
for row in sheet.rows:
for cell in row:
print(cell.value, end="\t")
print()
def get_target_data_list(file_path=TARGET_FILE):
"""
读取xlsx文件,获得日期、底薪人数、每日总业绩、每日总成本。\n
:param file_path: Excel文件路径
:return: date_list: 日期列表; work_people_num_list: 底薪(出勤)人数列表; daily_performance_list: 每日业绩列表; daily_cost_list: 每日成本列表; daily_profit_list: 每日利润列表。
"""
wb = openpyxl.load_workbook(file_path, data_only=True)
date_list = []
work_people_num_list = []
daily_performance_list = []
daily_cost_list = []
daily_profit_list = []
for sheet_name in wb.sheetnames:
date_ = sheet_name
date_list.append(sheet_name)
print(f"日期:{sheet_name}")
sheet = wb[sheet_name]
one_sheet_work_people_num = sheet['R5'].value
print(f"底薪人数:{one_sheet_work_people_num}人")
work_people_num_list.append(one_sheet_work_people_num)
one_sheet_daily_performance = sheet['R6'].value
daily_performance_list.append(one_sheet_daily_performance)
print(f"每日总业绩:{one_sheet_daily_performance}")
one_sheet_daily_cost = sheet['R7'].value
daily_cost_list.append(one_sheet_daily_cost)
print(f"每日总成本:{one_sheet_daily_cost}")
one_sheet_daily_profit = sheet['R8'].value
daily_profit_list.append(one_sheet_daily_profit)
print(f"每日利润:{one_sheet_daily_profit}")
print()
return date_list, work_people_num_list, daily_performance_list, daily_cost_list, daily_profit_list
def csv_to_excel(csv_filename, excel_filename):
"""
将csv文件转换为excel文件。\n
:param csv_filename: 被转换的csv文件的名称。
:param excel_filename: 目标生成的Excel文件的名称。
:return:
"""
csv_data = []
with open(csv_filename) as f:
csv_data = [row for row in csv.reader(f)]
workbook = openpyxl.workbook.Workbook()
worksheet = workbook.active
for row in csv_data:
worksheet.append(row)
workbook.save(excel_filename)
def main():
"""过渡函数,作为主函数和其他函数的中介。主要管理和疏通整个代码运行逻辑。
"""
date_list, work_people_num_list, daily_performance_list, daily_cost_list, daily_profit_list = get_target_data_list()
date_list_ = [""] + date_list
work_people_num_list_ = ["底薪人数"] + work_people_num_list
daily_performance_list_ = ["总业绩"] + daily_performance_list
daily_cost_list_ = ["总成本"] + daily_cost_list
daily_profit_list_ = ["利润"] + daily_profit_list
with open("统计汇总.csv", "w", newline="", encoding="gbk") as f:
writer = csv.writer(f)
writer.writerow(date_list_)
writer.writerow(work_people_num_list_)
writer.writerow(daily_performance_list_)
writer.writerow(daily_cost_list_)
writer.writerow(daily_profit_list_)
csv_filename = "统计汇总.csv"
excel_filename = f"{date_list[0]}到{date_list[-1]}数据统计汇总.xlsx"
csv_to_excel(csv_filename, excel_filename)
os.remove("统计汇总.csv")
if __name__ == "__main__":
main()
2. 结果数据格式
- 需要处理的数据格式:
- 最终生成的数据结果格式: