| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1304 人关注过本帖
标题:Python读取excel生成word
只看楼主 加入收藏
shmily_cbb
Rank: 2
等 级:论坛游民
帖 子:10
专家分:15
注 册:2021-12-10
收藏
 问题点数:0 回复次数:2 
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("保存成功!")
搜索更多相关主题的帖子: 学生 file 写入 教师 内容 
2022-02-07 09:11
shmily_cbb
Rank: 2
等 级:论坛游民
帖 子:10
专家分:15
注 册:2021-12-10
收藏
得分:0 
有需要数据文件格式的可以私信我。
2022-02-07 09:34
sssooosss
Rank: 9Rank: 9Rank: 9
等 级:禁止访问
威 望:3
帖 子:664
专家分:1115
注 册:2019-8-27
收藏
得分:0 
共同学习
2022-02-09 07:55
快速回复:Python读取excel生成word
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.018799 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved