def weeknum_data(workbook: xlsxwriter.Workbook, company: str,
conn: object) -> None:
"""
写入周数据的主函数
参数：
workbook: xlsxwriter.Workbook，用于确认数据写入的工作簿
company: str，机构名称
返回值：
无
"""
logging.info("开始写入周数据统计表")
# 设置全局变量
global name
global wb
global ws
global nrow
global ncol
global sy
# 对部分全局变量进行初始化
wb = workbook
name = company
sy = Style(wb)
idate: IDate = IDate(2020)
table_name = f"{name}周数据统计表"
ws = wb.add_worksheet(table_name)
# 需要统计的险种信息
risks = ["整体", "车险", "非车险"]
istong = ["同期", "整周"]
# 记录快捷菜单栏的项目信息
menu = []
# 写入数据统计表并记录快捷菜单栏不同快捷菜单的锚信息和现实文本信息
for risk in risks:
for tong in istong:
menu.append((nrow, f"{risk}{tong}"))
# 写入表头
header_write(xian_zhong=risk, tong=tong, idate=idate)
# 写入表数据
data_write(xian_zhong=risk, tong=tong, idate=idate, conn=conn)
# 写入快捷菜单栏
menu_write(table_name=table_name, menu=menu)
nrow = 1
ncol = 0
# 冻结快捷工具条所在行（第一行）
ws.freeze_panes(row=nrow, col=ncol + 1, top_row=1, left_col=1)
# 设置列宽
ws.set_column(first_col=ncol, last_col=ncol, width=10)
ws.set_column(first_col=ncol + 1, last_col=ncol + 25, width=12)
import logging
import xlsxwriter
from code.tong_ji import Tong_Ji
from code.style import Style
logging.disable(logging.DEBUG)
logging.basicConfig(level=logging.info,
format=' %(asctime)s | %(levelname)s | %(message)s')
logging.basicConfig(level=logging.debug,
format=' %(asctime)s | %(levelname)s | %(message)s')
wb = xlsxwriter.Workbook(r"Report\昆明机构周报.xlsx")
ws = wb.add_worksheet('昆明机构周报')
sy = Style(wb=wb)
ji_gou_list = [
'百大国际', '春怡雅苑', '香榭丽园', '宜良', '东川', '安宁', '春之城', '分公司本部', '航旅项目', '昆明'
]
xian_zhong_list = ['整体', '车险', '财产险', '人身险']
nrow = 0
for xian_zhong in xian_zhong_list:
ncol = 0
ws.merge_range(nrow, 0, nrow, 8, f'昆明地区机构{xian_zhong}保费汇总表',
sy.string_bold)
nrow += 1
ws.write(nrow, ncol, '机构', sy.string_bold)
ws.write(nrow, ncol + 1, '周保费', sy.string_bold)
def year_data(workbook: xlsxwriter.Workbook, company: str) -> None:
"""
写入年度数据的主函数
参数：
workbook: xlsxwriter.Workbook，用于确认数据写入的工作簿
company: str，机构名称
返回值：
无
"""
logging.info("开始写入年度数据统计表")
# 设置全局变量
global name
global wb
global ws
global nrow
global ncol
global sy
# 对部分全局变量进行初始化
wb = workbook
name = company
sy = Style(wb)
table_name = f"{name}年度数据统计表"
ws = wb.add_worksheet(table_name)
# 需要统计的险种信息
risks = ["整体", "车险", "财产险", "人身险", "非车险"]
# 记录快捷菜单栏的项目信息
menu = []
# 写入数据统计表并记录快捷菜单栏信息
for risk in risks:
menu.append((nrow, risk))
data_write(xian_zhong=risk)
# 创建表头的快捷工具条
nrow = 0
ncol = 0
ws.write_url(
row=nrow,
col=ncol,
url="internal:目录!A1",
string="返回目录",
cell_format=sy.menu,
)
ncol += 1
for col, data in menu:
ws.write_url(
row=nrow,
col=ncol,
url=f"internal:{table_name}!A{col+1}",
string=data,
cell_format=sy.menu,
)
ncol += 1
nrow += 1
ncol = 0
# 冻结快捷工具条所在行（第一行）
ws.freeze_panes(row=nrow, col=ncol, top_row=1)
# 设置列宽
ws.set_column(first_col=ncol, last_col=ncol, width=8)
ws.set_column(first_col=ncol + 1, last_col=ncol + 6, width=12)
ws.set_column(ncol + 3, ncol + 3, width=12)
if __name__ == "__main__":
conn = sqlite3.connect(r"Data\data.db")
cur = conn.cursor()
str_sql = f"SELECT MAX([投保确认日期]) \
FROM [2020年]"
cur.execute(str_sql)
ri_qi = cur.fetchone()[0]
wb = xlsxwriter.Workbook(r"Report\2020年开门红竞赛统计表.xlsx")
sy = Style(wb)
ws = wb.add_worksheet("昆明机构车险统计表")
kun_ming_che(sy, ws, ri_qi)
ws = wb.add_worksheet("规模达成奖统计表")
gui_mo_da_cheng(sy, ws, ri_qi)
ws = wb.add_worksheet("责任险成长奖统计表")
ze_ren_xian(sy, ws, ri_qi)
ws = wb.add_worksheet("诉讼保全激励奖统计表")
su_ze_xian(sy, ws, ri_qi)
ws = wb.add_worksheet("机构驾意险保费达成奖统计表")
ji_gou_jia_yi_xian(sy, ws, ri_qi)