You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
107 lines
3.9 KiB
Python
107 lines
3.9 KiB
Python
# from datetime import date
|
|
# from sqlalchemy.orm import Session
|
|
# from openpyxl import load_workbook
|
|
# from pathlib import Path
|
|
#
|
|
# import pandas as pd
|
|
# import os
|
|
# import logging
|
|
#
|
|
# from app.entity.database.session import get_db
|
|
# from app.entity.models.PowerOutageStats import PowerOutageStats
|
|
#
|
|
# # 获取日志记录器
|
|
# logger = logging.getLogger(__name__)
|
|
#
|
|
# # 三份累计表
|
|
# def accumulated_statistics(start_time, end_time, save_path=None):
|
|
#
|
|
# try:
|
|
# logger.info('对数据库的查询结果进行处理，完成三个累计表数据的组装')
|
|
#
|
|
# db: Session = next(get_db())
|
|
# # 查询某个时间段的数据
|
|
# results = db.query(PowerOutageStats.province_company,PowerOutageStats.outage_users,PowerOutageStats.short_outage_users,
|
|
# PowerOutageStats.repair_count,PowerOutageStats.complaint_count,PowerOutageStats.public_opinion_count,
|
|
# PowerOutageStats.major_event_count, PowerOutageStats.statistics_time)
|
|
#
|
|
# # Excel 模板路径
|
|
# # 获取当前文件夹路径
|
|
# current_path = Path(__file__).parent
|
|
# templates_path = str(os.path.join(current_path.parent, 'templates')).replace('\\', '/')
|
|
#
|
|
# # 加载 Excel 模板
|
|
# book = load_workbook(f'{templates_path}/累计数据模板.xlsx')
|
|
#
|
|
# # 选择要写入的 Sheet 页
|
|
# sheet_name = 'Sheet1' # 替换为你的 Sheet 页名称
|
|
# sheet = book[sheet_name]
|
|
#
|
|
# # 查询结果用pandas进行处理
|
|
# if results:
|
|
#
|
|
# # 将数据转成pandas数据结构
|
|
# df = pd.read_sql(results.statement, results.session.bind)
|
|
# # 插入序号列作为第一列
|
|
# df.insert(0, 'num', df.index + 1)
|
|
#
|
|
# # 组成表1数据
|
|
# df_temp = df[(df['statistics_time'] >= start_time) & (df['statistics_time'] <= end_time)]
|
|
# df_table1 = df_temp[['statistics_time', 'outage_users', 'complaint_count','public_opinion_count']]
|
|
# df_table1 = df_table1.groupby('statistics_time').sum()
|
|
# df_table1 = df_table1.reset_index()
|
|
#
|
|
# # 表1写入excel的位置
|
|
# start_row1 = 3
|
|
# start_col1 = 1
|
|
# print(df_table1)
|
|
# write_to_excel(df_table1,sheet,start_row1,start_col1)
|
|
#
|
|
#
|
|
# # 组成表2数据
|
|
# df_table2 = df_temp[['statistics_time', 'outage_users', 'short_outage_users', 'repair_count','complaint_count','public_opinion_count']]
|
|
# df_table2 = df_table2.groupby('statistics_time').sum()
|
|
# df_table2 = df_table2.reset_index()
|
|
#
|
|
# # 表2写入excel的位置
|
|
# start_row2 = 3
|
|
# start_col2 = 6
|
|
# print(df_table2)
|
|
# write_to_excel(df_table2,sheet,start_row2,start_col2)
|
|
#
|
|
# # 表3写入excel的位置
|
|
# start_row3 = 3
|
|
# start_col3 = 13
|
|
# df_table3 = df.drop('statistics_time', axis=1)
|
|
# write_to_excel(df_table3,sheet,start_row3,start_col3)
|
|
#
|
|
# # 最终结果生成
|
|
# book.save(f'{save_path}/累积统计表.xlsx')
|
|
# except Exception as e:
|
|
# logger.error(f'写入excel失败: {e}')
|
|
# raise e
|
|
#
|
|
# #对三张表进行组装
|
|
# def write_to_excel(df, sheet, start_row, start_col):
|
|
#
|
|
# try:
|
|
# logger.info('开始写入excel')
|
|
# # 将 DataFrame 写入指定位置
|
|
# for i, row in enumerate(df.itertuples(index=False), start=start_row):
|
|
# for j, value in enumerate(row, start=start_col):
|
|
# sheet.cell(row=i, column=j, value=value)
|
|
# except Exception as e:
|
|
# logger.error(f'写入excel失败: {e}')
|
|
# raise e
|
|
#
|
|
#
|
|
# if __name__ == '__main__':
|
|
#
|
|
#
|
|
# start_time = date(2025,3,9)
|
|
# end_time = date(2025,3,10)
|
|
# print(end_time)
|
|
# accumulated_statistics(start_time, end_time)
|
|
#
|
|
#
|