自学教程：Python 实现自动化Excel报表的步骤

import pandas as pd import xlwings as xwimport pymssql# 各品类月同期 def get_last_year_sale(start_date, end_date):  """各品类同期销量, 对比19年"""  sql_01 = f"""  SELECT    品类   , SUM(数量) AS QTY  FROM XXX  WHERE 是否电商 = 1    AND 销售时间 BETWEEN DATEADD(YEAR, -2, '{start_date}') AND DATEADD(YEAR, -2, '{end_date}')     GROUP BY 品类  """  df = pd.read_sql(sql_01, con=con)  df_xtc = df[df['品类'] == 'A品类'][['品类', 'QTY']]  df_bbk = df[df['品类'] == 'B品类'][['品类', 'QTY']]  return df_xtc, df_bbk   def get_anget_sale(start_date, end_date):    """返回各品类, 各区域的时间段销量"""    sql = f"""    SELECT      品类     , AGENT     , SUM(数量) AS QTY     , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK    FROM XXX    WHERE 是否电商 = 1      AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'    GROUP BY AGENT, 品类    """    df = pd.read_sql(sql, con=con)    df_xtc = df[df['品类'] == 'A品类'][['AGENT', 'QTY']]    df_bbk = df[df['品类'] == 'B品类'][['AGENT', 'QTY']]    df_pad = df[df['品类'] == 'C品类'][['AGENT', 'QTY']]    return df_xtc, df_bbk, df_pad   def get_machine_sale(start_date, end_date):  """返回各品类, 各区域的时间段销量"""  sql = f"""  SELECT    品类   , 机型   , SUM(数量) AS QTY   , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK  FROM V_REALSALE  WHERE 是否电商 = 1    AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'  GROUP BY 机型, 品类  """  df = pd.read_sql(sql, con=con)  df_xtc = df[df['品类'] == 'A品类'][['机型', 'QTY']]  df_bbk = df[df['品类'] == 'B品类'][['机型', 'QTY']]  return df_xtc, df_bbk # main con = pymssql.connect('xxxxx', 'sxxx', 'xxxxxx', 'xxxxx')# 基础配置: 根据用户输入当前日期, 输出当月, 当季度第一天 print("欢迎哦, 此小程序专门为XX看板做数据自动更新呢~")print()today = input("请输入截止日期(昨天), 形如: 2021/5/20 按回车结束:  ")if len(today.split('/')) != 3:  raise "日期格式输入错误!!, 请按照形如 '2021/5/20'的格式重新输入"else:  m_cur = today.split('/')[1]  m_first_day = '2021/' + m_cur + '/1'# 季度第一天 if m_cur in ('1', '01', '2', '02', '3', '03'):  q_time_start = '2021/1/1'  elif m_cur in ('4', '04', '5', '05', '6', '06'):  q_time_start = '2021/4/1'  elif m_cur in ('7', '07', '8', '08', '9', '09'):  q_time_start = '2021/7/1'else:  q_time_start = '2021/10/1'print()print("正在开始更新....")print("提示, 接下看到闪退, 是正常现象, 就程序模拟人去打开文件, 填充数据, 不要紧张哦~~~")# 去年月, 季度同期 df_mm_xtc, df_mm_bbk = get_last_year_sale(m_first_day, today)df_qq_xtc, df_qq_bbk = get_last_year_sale(q_time_start, today)# 当月各地区累积销量df_m_xtc, df_m_bbk, df_m_pad = get_anget_sale(m_first_day, today)# 各地区当季度销量 df_q_xtc, df_q_bbk, df_q_pad = get_anget_sale(q_time_start, today)# 各机型当季度销量 df_q_type_xtc, df_q_type_bbk = get_machine_sale(q_time_start, today) # 过滤掉 销量为0的型号 df_q_type_xtc = df_q_type_xtc[df_q_type_xtc.QTY > 0]df_q_type_xtc.replace('Z6áÛ·å°æ', 'Z6巅峰版', inplace=True)df_q_type_bbk = df_q_type_bbk[df_q_type_bbk.QTY > 0]# 打开excel 模板 等待数据填充 app = xw.App(visible=True, add_book=False)app.display_alerts = False  # 关闭一些提示信息，可以加快运行速度。 默认为 True。app.screen_updating = Truewb = app.books.open("XXX_全品类_看板.xlsx")data_sht = wb.sheets['数据']# 19年当月同期销量data_sht.range('B9').value = df_mm_xtc.valuesdata_sht.range('G9').value = df_mm_bbk.values# 当季度同比data_sht.range('B10').value = df_qq_xtc.valuesdata_sht.range('G10').value = df_qq_bbk.values# 填充各品类当月销量, 注意单元格是写死的哦data_sht.range('I72').value = df_m_xtc.valuesdata_sht.range('T72').value = df_m_bbk.valuesdata_sht.range('AE72').value = df_m_pad.values# 填充当季度销量, 同理是写死的data_sht.range('A54').value = df_q_xtc.valuesdata_sht.range('F54').value = df_q_bbk.valuesdata_sht.range('K54').value = df_q_pad.values# 填充当季度各型号, 同理是写死的data_sht.range('A21').value = df_q_type_xtc.valuesdata_sht.range('F21').value = df_q_type_bbk.valueswb.save()app.quit()print()print("~~更新结束了哦~~")print()input("请按任意键退出~~")print()print('BYE~~ 人生若只如初见呢~~')