Python读取excel生成word
目的：根据学校上报疫的情信息生成word汇报材料。import datetime
import pandas
import docx
import re
start_time = "2021年12月21日"
date_now_str = datetime.datetime.now().strftime("%Y年%m月%d日") # 文档结尾日期
date_now_str = date_now_str[0:3] + date_now_str[3:].replace("0", "")
user_type = ("教职工", "教职工家属", "学生", "学生家属", "离退休教师", "离退休教师家属") # 人员类别
city_name = ("天津市", "郑州市", "禹州市", "安阳市", "北京市", "上海市", "广东省", "雄安新区", "杭州市", "牡丹江市", "新疆", "西双版纳")
myfilepath = "C:\\Users\\Administrator\\Desktop\\20220201\\下载\\（2月1日）三门峡市域外返灵来灵人员排查登记表.xlsx"
new_myfilepath = "C:\\Users\\Administrator\\Desktop\\20220201\\下载\\（2月1日）三门峡市域外返灵来灵人员排查登记表(已格式化）.xlsx"
# 数据处理
def data_format(path, new_path):
data = pandas.read_excel(path, sheet_name=0, header=2, names=["序号", "单位", "姓名", "手机号", "身份证号",
"人员类别", "户籍", "省", "市", "县", "街道", "集中", "居家", "已过",
"核酸", "报备", "备注"], usecols=None)
data.to_excel(new_path)
data_format(myfilepath, new_myfilepath)
# ---------------------------------进入写入word操作----------------------------------------
word_path = "C:\\Users\\Administrator\\Desktop\\20220201\\汇报\\{0}三门峡外返灵人员统计报告.docx".format(date_now_str) # word文件路径
# 处理根据疫区反馈人员分类信息
data = pandas.read_excel(new_myfilepath, sheet_name=0, header=2,
names=["姓名", "人员类别", "省", "市", "县", "集中", "居家", "已过", "核酸",
"报备"], usecols=[2, 5, 7, 8, 9, 12, 13, 14, 15, 16])
# 关注城市数据空字典
city_dict = {}
# 根据关注城市创建数据基本字典
for i_t in city_name:
city_dict[i_t] = {}
# city_dict[i_t]["总人数"] = 0
for j_t in user_type:
city_dict[i_t][j_t] = 0
# print(city_dict)
# city_dict = {"天津市": {"总人数": 0, "教职工": 0, "教职工家属": 0, "学生": 0, "学生家属": 0, "离退休教师": 0, "离退休教师家属": 0},
# "郑州市": {"总人数": 0, "教职工": 0, "教职工家属": 0, "学生": 0, "学生家属": 0, "离退休教师": 0, "离退休教师家属": 0},
# "禹州市": {"总人数": 0, "教职工": 0, "教职工家属": 0, "学生": 0, "学生家属": 0, "离退休教师": 0, "离退休教师家属": 0},
# "安阳市": {"总人数": 0, "教职工": 0, "教职工家属": 0, "学生": 0, "学生家属": 0, "离退休教师": 0, "离退休教师家属": 0}}
all_dict = {"总人数": 0, "集中": 0, "居家": 0, "已过": 0, "核酸": 0, "报备": 0}
# 添加人员类别到数据字典中方便统计
for i_t in user_type:
all_dict[i_t] = 0
# 将数据封装入字典
# data pandas读取的数据
# city_dict 基本字典
# all_dict 统计字典
def city_count(data, city_dict, all_dict):
for date_t, row_t in data.iterrows():
# 省市县字符串
ssx_str = "{0}省{1}市{2}县".format(str(row_t["省"]), str(row_t["市"]), str(row_t["县"]))
# print(ssx_str)
for i_t in city_dict.keys():
if ssx_str.find(i_t[0:2]) != -1:
city_dict[i_t][row_t["人员类别"]] = city_dict[i_t][row_t["人员类别"]] + 1
# 数据填入统计字典
if str(row_t["姓名"]) != "0" and row_t["姓名"] != "" and row_t["姓名"] != "姓名" and str(row_t["姓名"]) != "nan":
all_dict["总人数"] = all_dict["总人数"] + 1
if str(row_t["人员类别"]) != "nan" and row_t["人员类别"] != "":
all_dict[row_t["人员类别"]] = all_dict[row_t["人员类别"]] + 1
if str(row_t["集中"]) != "nan" and row_t["集中"] != "":
all_dict["集中"] = all_dict["集中"] + 1
if str(row_t["居家"]) != "nan" and row_t["居家"] != "":
all_dict["居家"] = all_dict["居家"] + 1
if str(row_t["已过"]) != "nan" and row_t["已过"] != "":
all_dict["已过"] = all_dict["已过"] + 1
if str(row_t["核酸"]) != "nan" and row_t["核酸"] != "":
all_dict["核酸"] = all_dict["核酸"] + 1
if str(row_t["报备"]) != "nan" and row_t["报备"] != "":
all_dict["报备"] = all_dict["报备"] + 1
city_count(data, city_dict, all_dict)
str_1 = "三门峡市域外返灵来灵人员统计报告"
str_2 = "\t今日排查出{0}以来三门峡域外返灵来灵人员共计{1}人，其中教职工{2}人、教职工家属{3}人、学生{4}人、学生家属{5}人、离退休教师{6}人、" \
"离退休教师家属{7}人。"
str_3 = "一、落实管控措施情况"
str_4 = "\t集中隔离{0}人、居家隔离{1}人、已过管控期{2}人。"
str_5 = "二、返灵后落实核酸检测情况"
str_6 = "\t返灵后做过核酸{0}人，未做核酸{1}人。"
str_7 = "三、报备情况"
str_8 = "\t落实报备{0}人，未报备{1}人。"
str_9 = "四、重点疫区情况"
str_2 = str_2.format(start_time, all_dict["总人数"], all_dict["教职工"], all_dict["教职工家属"], all_dict["学生"],
all_dict["学生家属"], all_dict["离退休教师"], all_dict["离退休教师家属"])
str_4 = str_4.format(all_dict["集中"], all_dict["居家"], all_dict["已过"])
str_6 = str_6.format(all_dict["核酸"], all_dict["总人数"] - all_dict["核酸"])
str_8 = str_8.format(all_dict["报备"], all_dict["总人数"] - all_dict["报备"])
# print(city_dict)
# print(all_dict)
# 字典转化为需要的字符串
# city_index 序号索引
# city_name_t 城市名称
# city_data 数据字典
def city_count_to_str(city_index, city_name_t, city_data):
str_temp = "{0}、{1}：{2}人返灵".format(city_index, city_name_t, sum(city_data[city_name_t].values()))
for j_t in city_data[city_name_t]:
if city_data[city_name_t][j_t] > 0:
str_temp = str_temp + "，" + j_t + str(city_data[city_name_t][j_t]) + "人"
str_temp = str_temp + "。"
return str_temp
str_10 = city_count_to_str("\t1", "天津市", city_dict)
str_11 = city_count_to_str("\t2", "郑州市", city_dict)
str_12 = city_count_to_str("\t3", "禹州市", city_dict)
str_13 = city_count_to_str("\t4", "安阳市", city_dict)
str_14 = city_count_to_str("\t5", "北京市", city_dict)
str_15 = city_count_to_str("\t6", "上海市", city_dict)
str_16 = city_count_to_str("\t7", "广东省", city_dict)
str_17 = city_count_to_str("\t8", "雄安新区", city_dict)
str_18 = city_count_to_str("\t9", "杭州市", city_dict)
str_19 = city_count_to_str("\t10", "牡丹江市", city_dict)
str_20 = city_count_to_str("\t11", "新疆", city_dict)
str_21 = city_count_to_str("\t12", "西双版纳", city_dict)
file = docx.Document() # 打开文件
file.styles["Normal"].font.name = "宋体" # 设置字体
file.add_paragraph(str_1) # 写入标题
file.add_paragraph(str_2) # 写入内容
file.add_paragraph(str_3) # 写入内容
file.add_paragraph(str_4) # 写入内容
file.add_paragraph(str_5) # 写入内容
file.add_paragraph(str_6) # 写入内容
file.add_paragraph(str_7) # 写入内容
file.add_paragraph(str_8) # 写入内容
file.add_paragraph(str_9) # 写入内容
file.add_paragraph(str_10) # 写入内容
file.add_paragraph(str_11) # 写入内容
file.add_paragraph(str_12) # 写入内容
file.add_paragraph(str_13) # 写入内容
file.add_paragraph(str_14) # 写入内容
file.add_paragraph(str_15) # 写入内容
file.add_paragraph(str_16) # 写入内容
file.add_paragraph(str_17) # 写入内容
file.add_paragraph(str_18) # 写入内容
file.add_paragraph(str_19) # 写入内容
file.add_paragraph(str_20) # 写入内容
file.add_paragraph(str_21) # 写入内容
file.add_paragraph(date_now_str) # 写入内容
# 设置文档格式
i = 1
file.styles["Normal"].font.size = docx.shared.Pt(16) # 设置字体大小
for par_t in file.paragraphs:
par_t.paragraph_format.space_before = docx.shared.Pt(0) # 上行间距
par_t.paragraph_format.space_after = docx.shared.Pt(0) # 下行间距
par_t.paragraph_format.line_spacing = docx.shared.Pt(27) # 行距
if i == 1:
par_t.paragraph_format.alignment = 1 # 段落对齐,0=左对齐,1=居中,2=右对齐
# file.styles["Normal"].font.size = docx.shared.Pt(30) # 设置字体大小
# par_t.paragraph_format.line_spacing = docx.shared.Pt(35) # 行距
elif i == len(file.paragraphs):
par_t.paragraph_format.alignment = 2
i = i + 1
file.save(word_path) # 保存文件
print("保存成功！")
