devbox
很楠不爱3
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

【Python实现调查问卷数据统计】_python程序员问卷调查

作者：很楠不爱3 | 2024-05-22 02:53:43

赞

踩

python程序员问卷调查

一、背景

系统发出调查问卷，问卷的填写记录入了数据库，现在需要对用户的调查问卷填写情况进行统计。

这里使用Python对数据进行简单的处理，并将统计结果保存为excel文件。

二、连接MySQL查询数据

因为数据存储在MySQL，我使用pymysql库连接数据库。

这里编写了三条查询SQL语句，将数据分为三部分：调查问卷基本信息、调查问卷填写详情、调查问卷问题和选项。

将这三部分数据保存在一个列表中，方便后续处理。

  1. import traceback
  2. from pprint import pprint
  3. import pymysql
  4. import yaml
  5.  
  6.  
  7. def data_detail(dbinfo, survey_name):
  8.     """
  9.     连接数据库查询详细数据，保存为excel
  10.     :param dbinfo: 数据库连接信息
  11.     :param survey_name: 调查名称
  12.     :return: 数据库查询结果
  13.     """
  14.     # SQL列表示例
  15.     sql_list = [f'调查问卷基本信息查询SQL:select * from table1 where name={survey_name}', '调查问卷填写详情查询SQL', '调查问卷问题和选项查询SQL']
  16.     data = [execute_sql(dbinfo, sql) for sql in sql_list]
  17.     return data
  18.  
  19.  
  20. def execute_sql(dbinfo, sql):
  21.     """
  22.     连接数据库，执行sql查询语句，返回查询结果
  23.     :param dbinfo: 数据库配置信息
  24.     :param sql: 数据库查询语句
  25.     :return:
  26.     """
  27.     try:
  28.         conn = pymysql.connect(**dbinfo)  # 连接数据库
  29.         cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 数据库游标
  30.         cursor.execute(sql)  # 执行sql语句
  31.         results = cursor.fetchall()  # 查询结果
  32.         conn.close()  # 关闭数据库连接
  33.         return results
  34.     except Exception as err:
  35.         info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  36.         print(info)
  37.  
  38.  
  39. def yaml_read(filename):
  40.     """
  41.     读取yaml文件
  42.     """
  43.     with open(file=filename, mode="r", encoding="utf-8") as f:
  44.         data = yaml.safe_load(f.read())
  45.     return data
  46.  
  47.  
  48. if __name__ == "__main__":
  49.     db_info = yaml_read('cfg_mysql_test.yaml')
  50.     datas = data_detail(db_info, '调查问卷数据统计测试')
  51.     pprint(datas)

运行结果： 

三、查询结果保存为excel

将查询的数据结果保存到同一个excel表格中，用于后续分析统计。

先看一下查询的数据结果示例：[[{k:v,k:v},{k:v,k:v}],[{k:v,k:v},{k:v,k:v}],[{k:v,k:v},{k:v,k:v}]]

从内到外：字典-列表-字典

这里我做了如下处理：

        1.使用xlwt模块写入数据

        2.查询结果写入到同一个表格的多个工作表

        3.xlwt.xfstyle()设置表格的样式：表头、表格、时间数据

  1. import datetime
  2. import os
  3. import time
  4. import traceback
  5. import xlwt
  6.  
  7.  
  8. def to_excel(data, sheet_name=None, file_name=None):
  9.     """
  10.     数据写入excel
  11.     :param data: 数据查询结果
  12.     :param sheet_name: 工作表名称
  13.     :param file_name: excel文件名称
  14.     :return:
  15.     """
  16.     try:
  17.         book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
  18.         new_datas = data_pre_handle(data)  # 预处理数据
  19.         if sheet_name is None:
  20.             sheet_name = ["new_sheet" + str(i + 1) for i in range(len(new_datas))]
  21.         write_data(book, new_datas, sheet_name)  # 数据写入工作表
  22.         save_excel(book, file_name)  # 保存工作簿
  23.     except Exception as err:
  24.         info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  25.         print(info)
  26.  
  27.  
  28. def save_excel(workbook, file_name=None):
  29.     """
  30.     保存工作簿
  31.     :param workbook: 工作簿
  32.     :param file_name: excel文件名称
  33.     :return:
  34.     """
  35.     if file_name is None:
  36.         sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
  37.         name = sj + ".xlsx"
  38.     else:
  39.         name = file_name + ".xlsx"
  40.     workbook.save(name)
  41.     cur_file = os.path.dirname(os.path.abspath(__file__)) + os.sep + name  # 当前excel的保存路径
  42.     print(f"excel保存成功！【{cur_file}】")
  43.  
  44.  
  45. def data_pre_handle(data):
  46.     """
  47.     预处理数据库查询到的数据
  48.     :param data: 数据查询结果
  49.     :return:
  50.     """
  51.     return [[list(v[0].keys())] + [list(val.values()) for index, val in enumerate(v)] for i, v in enumerate(data)]
  52.  
  53.  
  54. def write_data(workbook, data, sheet_name):
  55.     """
  56.     数据写入sheet
  57.     :param workbook: 工作簿
  58.     :param data: 要写入的数据
  59.     :param sheet_name: 工作表名称
  60.     :return:
  61.     """
  62.     try:
  63.         for index, value in enumerate(data):
  64.             sheet = workbook.add_sheet(sheet_name[index], cell_overwrite_ok=True)  # 新建sheet
  65.             size = [12 for i in value[0]]
  66.             head_style = style_head(sheet, size, wrap=1, is_bg=1)  # 定义表头样式
  67.             time_style = style_table_time()  # 时间内容样式
  68.             normal_style = style_table_normal({"horz": "CENTER", "vert": "CENTER"})  # 普通内容样式
  69.             for col, val in enumerate(value[0]):
  70.                 sheet.write(0, col, val, head_style)
  71.             print(f"写入表头成功！{value[0]}")
  72.             for i, v in enumerate(value[1:]):
  73.                 for co, va in enumerate(v):
  74.                     val = str(va)
  75.                     if is_date(val):
  76.                         if len(val) > 19:
  77.                             new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
  78.                         elif 10 < len(val) <= 19:
  79.                             new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S")
  80.                         else:
  81.                             new_val = datetime.datetime.strptime(val, '%Y-%m-%d')
  82.                         sheet.write(i + 1, co, new_val, time_style)
  83.                     elif is_number(val) == 1 and len(val) < 11:
  84.                         sheet.write(i + 1, co, int(val), normal_style)
  85.                     else:
  86.                         sheet.write(i + 1, co, val, normal_style)
  87.             print(f"写入表格内容成功！共{len(value) - 1}行")
  88.     except Exception as err:
  89.         info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  90.         print(info)
  91.  
  92.  
  93. def is_date(date_str):
  94.     """
  95.     判断date_str是否为日期格式
  96.     :param date_str:
  97.     :return:
  98.     """
  99.     date_format = "%Y-%m-%d"
  100.     try:
  101.         if isinstance(date_str, str):
  102.             new_str = date_str[:10]
  103.             valid_date = time.strptime(new_str, date_format)
  104.             return True
  105.         else:
  106.             return False
  107.     except ValueError or TypeError as e:
  108.         return False
  109.  
  110.  
  111. def is_number(val: str):
  112.     """
  113.     判断字符串的数字类型
  114.     :param val: 字符串
  115.     :return: 1--整数，2--小数，0--非数字
  116.     """
  117.     try:
  118.         float(val)
  119.         if val.isdigit() or val.split('-')[-1].isdigit():
  120.             return 1
  121.         elif val.split('.')[0].isdigit() or val.split('-')[-1].split('.')[-1].isdigit():
  122.             return 2
  123.         else:
  124.             return 0
  125.     except ValueError:
  126.         pass
  127.     return 0
  128.  
  129.  
  130. def style_head(worksheet, size, wrap=0, is_bg=0, color=22):
  131.     """
  132.     表头的样式
  133.     :param worksheet: 表格
  134.     :param size: 表格列宽
  135.     :param wrap: 1--自动换行，默认不换行
  136.     :param is_bg: 1--设置背景色，默认不设置
  137.     :param color: 默认浅灰色背景
  138.     :return:
  139.     """
  140.     style = xlwt.XFStyle()
  141.     set_font(style, height=14)
  142.     set_border(style)
  143.     set_widths(worksheet, size)
  144.     dicts = {"horz": "CENTER", "vert": "CENTER"}
  145.     set_alignments(style, wrap=wrap, **dicts)
  146.     if is_bg == 1:
  147.         set_pattern(style, color=color)
  148.     return style
  149.  
  150.  
  151. def style_table_normal(dicts: dict):
  152.     """
  153.     普通表格内容样式
  154.     :param dicts: 对齐方式
  155.     :return:
  156.     """
  157.     style = xlwt.XFStyle()
  158.     set_font(style, bold=False)
  159.     set_border(style)
  160.     set_alignments(style, wrap=1, **dicts)
  161.     return style
  162.  
  163.  
  164. def style_table_time():
  165.     """
  166.     时间格式表格内容样式
  167.     :return:
  168.     """
  169.     style = xlwt.XFStyle()
  170.     set_border(style)
  171.     style.num_format_str = "yyyy/MM/dd HH:MM:SS"
  172.     dicts = {"horz": "LEFT", "vert": "CENTER"}
  173.     set_alignments(style, wrap=1, **dicts)
  174.     return style
  175.  
  176.  
  177. def set_font(style, bold=True, name='宋体', height=11):
  178.     """
  179.     设置字体，默认宋体加粗，高度11
  180.     :param style:
  181.     :param bold:
  182.     :param name:
  183.     :param height:
  184.     :return:
  185.     """
  186.     style.font.bold = bold
  187.     style.font.name = name
  188.     style.font.height = 20 * height
  189.  
  190.  
  191. def set_border(style, status=1):
  192.     """
  193.     设置边框
  194.     :param style:
  195.     :param status:
  196.     :return:
  197.     """
  198.     style.borders.left = status
  199.     style.borders.right = status
  200.     style.borders.top = status
  201.     style.borders.bottom = status
  202.  
  203.  
  204. def set_pattern(style, color=23):
  205.     """
  206.     设置表格背景颜色，默认深灰
  207.     0 = 黑, 1 = 白, 2 = 红, 3 = 绿, 4 = 蓝, 5 = 黄, 6 = 品红, 7 = 蓝绿,
  208.     16 = 褐红, 17 = 深绿, 18 = 深蓝, 19 = 棕色, 20 = 暗洋红, 21 = 蓝绿色, 22 = 浅灰, 23 = 深灰......
  209.     :param style:
  210.     :param color:
  211.     :return:
  212.     """
  213.     style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
  214.     style.pattern.pattern_fore_colour = color
  215.  
  216.  
  217. def set_widths(worksheet, size):
  218.     """
  219.     设置宽度
  220.     :param worksheet:
  221.     :param size:
  222.     :return:
  223.     """
  224.     for i, v in enumerate(size):
  225.         worksheet.col(i).width = v * 256
  226.  
  227.  
  228. def set_alignments(style, wrap=1, **kwargs):
  229.     """
  230.     设置对齐方式，默认自动换行
  231.     中心对齐参数：{"horz": "CENTER", "vert": "CENTER"}
  232.     horz（水平）：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右）,LEFT（左）
  233.     vert（垂直）：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下),TOP（上）
  234.     """
  235.  
  236.     if "horz" in kwargs.keys():
  237.         style.alignment.horz = eval(f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
  238.     if "vert" in kwargs.keys():
  239.         style.alignment.vert = eval(f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
  240.     style.alignment.wrap = wrap  # 设置自动换行
  241.  
  242.  
  243. if __name__ == "__main__":
  244.     db_info = yaml_read('cfg_mysql_test.yaml')
  245.     datas = data_detail(db_info, '调查问卷数据统计测试')
  246.     sheets_name = ['调查问卷基本信息', '调查问卷填写详情', '调查问卷问题和选项']
  247.     to_excel(datas, sheets_name)

 运行结果：

四、统计查询结果到excel

同样使用xlwt模块，统计我们想要的数据，写入excel，并设置单元格的样式

  1. import datetime
  2. import os
  3. import time
  4. import traceback
  5. import xlwt
  6.  
  7.  
  8. def to_excel(data, sheet_name=None, file_name=None):
  9.     """
  10.     数据写入excel
  11.     :param data: 数据查询结果
  12.     :param sheet_name: 工作表名称
  13.     :param file_name: excel文件名称
  14.     :return:
  15.     """
  16.     try:
  17.         book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
  18.         new_datas = data_pre_handle(data)  # 预处理数据
  19.         if sheet_name is None:
  20.             sheet_name = ["new_sheet" + str(i + 1) for i in range(len(new_datas))]
  21.         write_data(book, new_datas, sheet_name)  # 数据写入工作表
  22.         save_excel(book, file_name)  # 保存工作簿
  23.     except Exception as err:
  24.         info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  25.         print(info)
  26.  
  27.  
  28. def save_excel(workbook, file_name=None):
  29.     """
  30.     保存工作簿
  31.     :param workbook: 工作簿
  32.     :param file_name: excel文件名称
  33.     :return:
  34.     """
  35.     if file_name is None:
  36.         sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
  37.         name = sj + ".xlsx"
  38.     else:
  39.         name = file_name + ".xlsx"
  40.     workbook.save(name)
  41.     cur_file = os.path.dirname(os.path.abspath(__file__)) + os.sep + name  # 当前excel的保存路径
  42.     print(f"excel保存成功！【{cur_file}】")
  43.  
  44.  
  45. def data_pre_handle(data):
  46.     """
  47.     预处理数据库查询到的数据
  48.     :param data: 数据查询结果
  49.     :return:
  50.     """
  51.     return [[list(v[0].keys())] + [list(val.values()) for index, val in enumerate(v)] for i, v in enumerate(data)]
  52.  
  53.  
  54. def write_data(workbook, data, sheet_name):
  55.     """
  56.     数据写入sheet
  57.     :param workbook: 工作簿
  58.     :param data: 要写入的数据
  59.     :param sheet_name: 工作表名称
  60.     :return:
  61.     """
  62.     try:
  63.         for index, value in enumerate(data):
  64.             sheet = workbook.add_sheet(sheet_name[index], cell_overwrite_ok=True)  # 新建sheet
  65.             size = [12 for i in value[0]]
  66.             head_style = style_head(sheet, size, wrap=1, is_bg=1)  # 定义表头样式
  67.             time_style = style_table_time()  # 时间内容样式
  68.             normal_style = style_table_normal({"horz": "CENTER", "vert": "CENTER"})  # 普通内容样式
  69.             for col, val in enumerate(value[0]):
  70.                 sheet.write(0, col, val, head_style)
  71.             print(f"写入表头成功！{value[0]}")
  72.             for i, v in enumerate(value[1:]):
  73.                 for co, va in enumerate(v):
  74.                     val = str(va)
  75.                     if is_date(val):
  76.                         if len(val) > 19:
  77.                             new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
  78.                         elif 10 < len(val) <= 19:
  79.                             new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S")
  80.                         else:
  81.                             new_val = datetime.datetime.strptime(val, '%Y-%m-%d')
  82.                         sheet.write(i + 1, co, new_val, time_style)
  83.                     elif is_number(val) == 1 and len(val) < 11:
  84.                         sheet.write(i + 1, co, int(val), normal_style)
  85.                     else:
  86.                         sheet.write(i + 1, co, val, normal_style)
  87.             print(f"写入表格内容成功！共{len(value) - 1}行")
  88.     except Exception as err:
  89.         info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  90.         print(info)
  91.  
  92.  
  93. def is_date(date_str):
  94.     """
  95.     判断date_str是否为日期格式
  96.     :param date_str:
  97.     :return:
  98.     """
  99.     date_format = "%Y-%m-%d"
  100.     try:
  101.         if isinstance(date_str, str):
  102.             new_str = date_str[:10]
  103.             valid_date = time.strptime(new_str, date_format)
  104.             return True
  105.         else:
  106.             return False
  107.     except ValueError or TypeError as e:
  108.         return False
  109.  
  110.  
  111. def is_number(val: str):
  112.     """
  113.     判断字符串的数字类型
  114.     :param val: 字符串
  115.     :return: 1--整数，2--小数，0--非数字
  116.     """
  117.     try:
  118.         float(val)
  119.         if val.isdigit() or val.split('-')[-1].isdigit():
  120.             return 1
  121.         elif val.split('.')[0].isdigit() or val.split('-')[-1].split('.')[-1].isdigit():
  122.             return 2
  123.         else:
  124.             return 0
  125.     except ValueError:
  126.         pass
  127.     return 0
  128.  
  129.  
  130. def style_label():
  131.     """
  132.     主表头的样式
  133.     :return:
  134.     """
  135.     style = xlwt.XFStyle()
  136.     set_font(style, height=16)
  137.     set_border(style)
  138.     set_pattern(style)
  139.     dicts = {"horz": "CENTER", "vert": "CENTER"}
  140.     set_alignments(style, wrap=0, **dicts)
  141.     return style
  142.  
  143.  
  144. def style_head(worksheet, size, wrap=0, is_bg=0, color=22):
  145.     """
  146.     表头的样式
  147.     :param worksheet: 表格
  148.     :param size: 表格列宽
  149.     :param wrap: 1--自动换行，默认不换行
  150.     :param is_bg: 1--设置背景色，默认不设置
  151.     :param color: 默认浅灰色背景
  152.     :return:
  153.     """
  154.     style = xlwt.XFStyle()
  155.     set_font(style, height=14)
  156.     set_border(style)
  157.     set_widths(worksheet, size)
  158.     dicts = {"horz": "CENTER", "vert": "CENTER"}
  159.     set_alignments(style, wrap=wrap, **dicts)
  160.     if is_bg == 1:
  161.         set_pattern(style, color=color)
  162.     return style
  163.  
  164.  
  165. def style_table_normal(dicts: dict):
  166.     """
  167.     普通表格内容样式
  168.     :param dicts: 对齐方式
  169.     :return:
  170.     """
  171.     style = xlwt.XFStyle()
  172.     set_font(style, bold=False)
  173.     set_border(style)
  174.     set_alignments(style, wrap=1, **dicts)
  175.     return style
  176.  
  177.  
  178. def style_table_time():
  179.     """
  180.     时间格式表格内容样式
  181.     :return:
  182.     """
  183.     style = xlwt.XFStyle()
  184.     set_border(style)
  185.     style.num_format_str = "yyyy/MM/dd HH:MM:SS"
  186.     dicts = {"horz": "LEFT", "vert": "CENTER"}
  187.     set_alignments(style, wrap=1, **dicts)
  188.     return style
  189.  
  190.  
  191. def style_table_percent():
  192.     """
  193.     百分比格式表格内容样式
  194.     :return:
  195.     """
  196.     style = xlwt.XFStyle()
  197.     set_border(style)
  198.     style.num_format_str = '0%'
  199.     dicts = {"horz": "CENTER", "vert": "CENTER"}
  200.     set_alignments(style, **dicts)
  201.     return style
  202.  
  203.  
  204. def style_table_separator():
  205.     """
  206.     分隔列表格内容样式
  207.     :return:
  208.     """
  209.     style = xlwt.XFStyle()
  210.     set_border(style)
  211.     set_pattern(style, color=22)
  212.     return style
  213.  
  214.  
  215. def set_font(style, bold=True, name='宋体', height=11):
  216.     """
  217.     设置字体，默认宋体加粗，高度11
  218.     :param style:
  219.     :param bold:
  220.     :param name:
  221.     :param height:
  222.     :return:
  223.     """
  224.     style.font.bold = bold
  225.     style.font.name = name
  226.     style.font.height = 20 * height
  227.  
  228.  
  229. def set_border(style, status=1):
  230.     """
  231.     设置边框
  232.     :param style:
  233.     :param status:
  234.     :return:
  235.     """
  236.     style.borders.left = status
  237.     style.borders.right = status
  238.     style.borders.top = status
  239.     style.borders.bottom = status
  240.  
  241.  
  242. def set_pattern(style, color=23):
  243.     """
  244.     设置表格背景颜色，默认深灰
  245.     0 = 黑, 1 = 白, 2 = 红, 3 = 绿, 4 = 蓝, 5 = 黄, 6 = 品红, 7 = 蓝绿,
  246.     16 = 褐红, 17 = 深绿, 18 = 深蓝, 19 = 棕色, 20 = 暗洋红, 21 = 蓝绿色, 22 = 浅灰, 23 = 深灰......
  247.     :param style:
  248.     :param color:
  249.     :return:
  250.     """
  251.     style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
  252.     style.pattern.pattern_fore_colour = color
  253.  
  254.  
  255. def set_widths(worksheet, size):
  256.     """
  257.     设置宽度
  258.     :param worksheet:
  259.     :param size:
  260.     :return:
  261.     """
  262.     for i, v in enumerate(size):
  263.         worksheet.col(i).width = v * 256
  264.  
  265.  
  266. def set_alignments(style, wrap=1, **kwargs):
  267.     """
  268.     设置对齐方式，默认自动换行
  269.     中心对齐参数：{"horz": "CENTER", "vert": "CENTER"}
  270.     horz（水平）：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右）,LEFT（左）
  271.     vert（垂直）：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下),TOP（上）
  272.     """
  273.  
  274.     if "horz" in kwargs.keys():
  275.         style.alignment.horz = eval(f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
  276.     if "vert" in kwargs.keys():
  277.         style.alignment.vert = eval(f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
  278.     style.alignment.wrap = wrap  # 设置自动换行
  279.  
  280.  
  281. def data_statistics(data):
  282.     """
  283.     统计详细数据，保存为excel
  284.     :param data: 数据库查询结果
  285.     :return:
  286.     """
  287.     wb = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
  288.     ws = wb.add_sheet('活动问卷数据汇总', cell_overwrite_ok=True)  # 新建sheet
  289.  
  290.     label_style = style_label()
  291.     head_style = style_head(ws, size=[20, 20, 2, 10, 20, 20, 25, 2, 10, 25, 20, 2, 25, 20, 8, 25, 25])
  292.     dicts1 = {"horz": "CENTER", "vert": "CENTER"}
  293.     dicts2 = {"horz": "LEFT", "vert": "CENTER"}
  294.     normal_table_style_center = style_table_normal(dicts1)
  295.     normal_table_style_left = style_table_normal(dicts2)
  296.     separator_table_style = style_table_separator()
  297.     time_table_style = style_table_time()
  298.     percent_table_style = style_table_percent()
  299.  
  300.     # 基本信息
  301.     ws.write_merge(0, 0, 0, 1, '基本信息', label_style)
  302.     ws.write(1, 0, '调查名称', head_style)
  303.     ws.write(1, 1, data[0][0]['调查名称'], normal_table_style_center)
  304.     ws.write(2, 0, '调查问卷名称', head_style)
  305.     ws.write(2, 1, data[0][0]['调查问卷名称'], normal_table_style_center)
  306.     ws.write(3, 0, '问卷发送人数', head_style)
  307.     result1 = [v['姓名'] for v in data[0] if v['目前卡片数'] == 18]
  308.     ws.write(3, 1, len(result1), normal_table_style_center)
  309.     ws.write(4, 0, '问卷提交人数', head_style)
  310.     result2 = [v['姓名'] for v in data[0] if v['填写时长'] is not None]
  311.     ws.write(4, 1, len(result2), normal_table_style_center)
  312.  
  313.     # 分隔
  314.     ws.write_merge(0, 142, 2, 2, '', separator_table_style)
  315.  
  316.     # 用户问卷发送及提交信息
  317.     ws.write_merge(0, 0, 3, 6, '用户问卷发送及提交信息', label_style)
  318.     head1 = ['姓名', '问卷发送时间', '问卷提交时间', '填写时长（s）']
  319.     for i, v in enumerate(head1):
  320.         ws.write(1, i + 3, v, head_style)
  321.     for row, v in enumerate(data[0]):
  322.         ws.write(row + 2, 3, v['姓名'], normal_table_style_left)
  323.         if v['目前卡片数'] != 18:
  324.             ws.write(row + 2, 4, '', normal_table_style_left)
  325.         else:
  326.             ws.write(row + 2, 4, v['最后卡片时间'], time_table_style)
  327.         for j in data[1]:
  328.             if v['会员id'] == j['会员id']:
  329.                 ws.write(row + 2, 5, j['问卷提交时间'], time_table_style)
  330.                 break
  331.         if v['会员id'] not in [i['会员id'] for i in data[1]]:
  332.             ws.write(row + 2, 5, '', normal_table_style_left)
  333.         if v['填写时长'] is None:
  334.             ws.write(row + 2, 6, '', normal_table_style_left)
  335.         else:
  336.             ws.write(row + 2, 6, v['填写时长'], normal_table_style_center)
  337.  
  338.     # 分隔
  339.     ws.write_merge(0, 142, 7, 7, '', separator_table_style)
  340.  
  341.     # 用户问卷填写内容详情
  342.     ws.write_merge(0, 0, 8, 10, '用户问卷填写内容详情', label_style)
  343.     head2 = ['姓名', '问题', '答案']
  344.     for i, v in enumerate(head2):
  345.         ws.write(1, i + 8, v, head_style)
  346.     for row, val in enumerate(data[1]):
  347.         ws.write(row + 2, 8, val['姓名'], normal_table_style_left)
  348.         ws.write(row + 2, 9, val['问题'], normal_table_style_left)
  349.         ws.write(row + 2, 10, val['答案'], normal_table_style_left)
  350.  
  351.     # 分隔
  352.     ws.write_merge(0, 142, 11, 11, '', separator_table_style)
  353.  
  354.     # 问卷问题选项信息
  355.     ws.write_merge(0, 0, 12, 16, '问卷问题选项信息', label_style)
  356.     head3 = ['问题', '问题选项', '题型', '选项被选择的人数', '选项被选择的占比']
  357.     for i, v in enumerate(head3):
  358.         ws.write(1, i + 12, v, head_style)
  359.     for row, val in enumerate(data[2]):
  360.         ws.write(row + 2, 12, val['题目'], normal_table_style_left)
  361.         ws.write(row + 2, 13, val['选项'], normal_table_style_left)
  362.         ws.write(row + 2, 14, val['题型'], normal_table_style_center)
  363.         result3 = [v['答案'] for v in data[1] if val['题目'] in v['问题'] if val['选项'] in v['答案']]
  364.         ws.write(row + 2, 15, len(result3), normal_table_style_center)
  365.         ws.write(row + 2, 16, len(result3) / len(result2), percent_table_style)
  366.  
  367.     sj = datetime.datetime.now().strftime('%Y-%m-%d %H%M%S')
  368.     wb.save('调查问卷数据统计' + sj + '.xlsx')  # 保存excel
  369.  
  370.  
  371. if __name__ == "__main__":
  372.     db_info = yaml_read('cfg_mysql_test.yaml')
  373.     datas = data_detail(db_info, '调查问卷数据统计测试')
  374.     sheets_name = ['调查问卷基本信息', '调查问卷填写详情', '调查问卷问题和选项']
  375.     # to_excel(datas, sheets_name)
  376.     data_statistics(datas)

 运行结果：

五、封装形成完整代码

把上面的方法封装成类，这里使用静态方法装饰器@staticmethod：不需self参数，通过类或者类实例来调用

  1. import datetime
  2. import os
  3. import time
  4. import traceback
  5. import pymysql
  6. import xlwt
  7. import yaml
  8.  
  9. class ExcelStyle:
  10.  
  11.     @staticmethod
  12.     def style_label():
  13.         """
  14.         主表头的样式
  15.         :return:
  16.         """
  17.         se = ExcelStyle()
  18.         style = se.styles()
  19.         se.set_font(style, height=16)
  20.         se.set_border(style)
  21.         se.set_pattern(style)
  22.         dicts = {"horz": "CENTER", "vert": "CENTER"}
  23.         se.set_alignments(style, wrap=0, **dicts)
  24.         return style
  25.  
  26.     @staticmethod
  27.     def style_head(worksheet, size, wrap=0, is_bg=0, color=22):
  28.         """
  29.         表头的样式
  30.         :param worksheet: 表格
  31.         :param size: 表格列宽
  32.         :param wrap: 1--自动换行，默认不换行
  33.         :param is_bg: 1--设置背景色，默认不设置
  34.         :param color: 默认浅灰色背景
  35.         :return:
  36.         """
  37.         se = ExcelStyle()
  38.         style = se.styles()
  39.         se.set_font(style, height=14)
  40.         se.set_border(style)
  41.         se.set_widths(worksheet, size)
  42.         dicts = {"horz": "CENTER", "vert": "CENTER"}
  43.         se.set_alignments(style, wrap=wrap, **dicts)
  44.         if is_bg == 1:
  45.             se.set_pattern(style, color=color)
  46.         return style
  47.  
  48.     @staticmethod
  49.     def style_table_normal(dicts: dict):
  50.         """
  51.         普通表格内容样式
  52.         :param dicts: 对齐方式
  53.         :return:
  54.         """
  55.         se = ExcelStyle()
  56.         style = se.styles()
  57.         se.set_font(style, bold=False)
  58.         se.set_border(style)
  59.         se.set_alignments(style, wrap=1, **dicts)
  60.         return style
  61.  
  62.     @staticmethod
  63.     def style_table_time():
  64.         """
  65.         时间格式表格内容样式
  66.         :return:
  67.         """
  68.         se = ExcelStyle()
  69.         style = se.styles()
  70.         se.set_border(style)
  71.         style.num_format_str = "yyyy/MM/dd HH:MM:SS"
  72.         dicts = {"horz": "LEFT", "vert": "CENTER"}
  73.         se.set_alignments(style, wrap=1, **dicts)
  74.         return style
  75.  
  76.     @staticmethod
  77.     def style_table_percent():
  78.         """
  79.         百分比格式表格内容样式
  80.         :return:
  81.         """
  82.         se = ExcelStyle()
  83.         style = se.styles()
  84.         se.set_border(style)
  85.         style.num_format_str = '0%'
  86.         dicts = {"horz": "CENTER", "vert": "CENTER"}
  87.         se.set_alignments(style, **dicts)
  88.         return style
  89.  
  90.     @staticmethod
  91.     def style_table_separator():
  92.         """
  93.         分隔列表格内容样式
  94.         :return:
  95.         """
  96.         se = ExcelStyle()
  97.         style = se.styles()
  98.         se.set_border(style)
  99.         se.set_pattern(style, color=22)
  100.         return style
  101.  
  102.     @staticmethod
  103.     def styles():
  104.         """设置单元格的样式的基础方法"""
  105.         style = xlwt.XFStyle()
  106.         return style
  107.  
  108.     @staticmethod
  109.     def set_font(style, bold=True, name='宋体', height=11):
  110.         """
  111.         设置字体，默认宋体加粗，高度11
  112.         :param style:
  113.         :param bold:
  114.         :param name:
  115.         :param height:
  116.         :return:
  117.         """
  118.         style.font.bold = bold
  119.         style.font.name = name
  120.         style.font.height = 20 * height
  121.  
  122.     @staticmethod
  123.     def set_border(style, status=1):
  124.         """
  125.         设置边框
  126.         :param style:
  127.         :param status:
  128.         :return:
  129.         """
  130.         style.borders.left = status
  131.         style.borders.right = status
  132.         style.borders.top = status
  133.         style.borders.bottom = status
  134.  
  135.     @staticmethod
  136.     def set_pattern(style, color=23):
  137.         """
  138.         设置表格背景颜色，默认深灰
  139.         0 = 黑, 1 = 白, 2 = 红, 3 = 绿, 4 = 蓝, 5 = 黄, 6 = 品红, 7 = 蓝绿,
  140.         16 = 褐红, 17 = 深绿, 18 = 深蓝, 19 = 棕色, 20 = 暗洋红, 21 = 蓝绿色, 22 = 浅灰, 23 = 深灰......
  141.         :param style:
  142.         :param color:
  143.         :return:
  144.         """
  145.         style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
  146.         style.pattern.pattern_fore_colour = color
  147.  
  148.     @staticmethod
  149.     def set_widths(worksheet, size):
  150.         """
  151.         设置宽度
  152.         :param worksheet:
  153.         :param size:
  154.         :return:
  155.         """
  156.         for i, v in enumerate(size):
  157.             worksheet.col(i).width = v * 256
  158.  
  159.     @staticmethod
  160.     def set_alignments(style, wrap=1, **kwargs):
  161.         """
  162.         设置对齐方式，默认自动换行
  163.         中心对齐参数：{"horz": "CENTER", "vert": "CENTER"}
  164.         horz（水平）：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右）,LEFT（左）
  165.         vert（垂直）：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下),TOP（上）
  166.         """
  167.  
  168.         if "horz" in kwargs.keys():
  169.             style.alignment.horz = eval(f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
  170.         if "vert" in kwargs.keys():
  171.             style.alignment.vert = eval(f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
  172.         style.alignment.wrap = wrap  # 设置自动换行
  173.  
  174.  
  175. class DataStatistics(ExcelStyle):
  176.  
  177.     @staticmethod
  178.     def data_detail(dbinfo, survey_name):
  179.         """
  180.         连接数据库查询详细数据，保存为excel
  181.         :param dbinfo: 数据库连接信息
  182.         :param survey_name: 调查名称
  183.         :return: 数据库查询结果
  184.         """
  185.         se = DataStatistics()
  186.         sql_name = ['调查问卷基本信息', '调查问卷填写详情', '调查问卷问题和选项']
  187.         sql_list = [f'调查问卷基本信息查询SQL:select * from table1 where name={survey_name}', '调查问卷填写详情查询SQL', '调查问卷问题和选项查询SQL']
  188.         data = [se.execute_sql(dbinfo, sql) for sql in sql_list]
  189.         se.to_excel(data, sql_name)
  190.         return data
  191.  
  192.     @staticmethod
  193.     def execute_sql(dbinfo, sql):
  194.         """
  195.         连接数据库，执行sql查询语句，返回查询结果
  196.         :param dbinfo: 数据库配置信息
  197.         :param sql: 数据库查询语句
  198.         :return:
  199.         """
  200.         try:
  201.             conn = pymysql.connect(**dbinfo)  # 连接数据库
  202.             cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 数据库游标
  203.             cursor.execute(sql)  # 执行sql语句
  204.             results = cursor.fetchall()  # 查询结果
  205.             conn.close()  # 关闭数据库连接
  206.             return results
  207.         except Exception as err:
  208.             info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  209.             print(info)
  210.  
  211.     @staticmethod
  212.     def to_excel(data, sheet_name=None, file_name=None):
  213.         """
  214.         数据写入excel
  215.         :param data: 数据查询结果
  216.         :param sheet_name: 工作表名称
  217.         :param file_name: excel文件名称
  218.         :return:
  219.         """
  220.         se = DataStatistics()
  221.         try:
  222.             book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
  223.             new_datas = se.data_pre_handle(data)  # 预处理数据
  224.             if sheet_name is None:
  225.                 sheet_name = ["new_sheet" + str(i + 1) for i in range(len(new_datas))]
  226.             se.write_data(book, new_datas, sheet_name)  # 数据写入工作表
  227.             se.save_excel(book, file_name)  # 保存工作簿
  228.         except Exception as err:
  229.             info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  230.             print(info)
  231.  
  232.     @staticmethod
  233.     def save_excel(workbook, file_name=None):
  234.         """
  235.         保存工作簿
  236.         :param workbook: 工作簿
  237.         :param file_name: excel文件名称
  238.         :return:
  239.         """
  240.         if file_name is None:
  241.             sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
  242.             name = sj + ".xlsx"
  243.         else:
  244.             name = file_name + ".xlsx"
  245.         workbook.save(name)
  246.         cur_file = os.path.dirname(os.path.abspath(__file__)) + os.sep + name  # 当前excel的保存路径
  247.         print(f"excel保存成功！【{cur_file}】")
  248.  
  249.     @staticmethod
  250.     def data_pre_handle(data):
  251.         """
  252.         预处理数据库查询到的数据
  253.         :param data: 数据查询结果
  254.         :return:
  255.         """
  256.         return [[list(v[0].keys())] + [list(val.values()) for index, val in enumerate(v)] for i, v in enumerate(data)]
  257.  
  258.     @staticmethod
  259.     def write_data(workbook, data, sheet_name):
  260.         """
  261.         数据写入sheet
  262.         :param workbook: 工作簿
  263.         :param data: 要写入的数据
  264.         :param sheet_name: 工作表名称
  265.         :return:
  266.         """
  267.         se = DataStatistics()
  268.         try:
  269.             for index, value in enumerate(data):
  270.                 sheet = workbook.add_sheet(sheet_name[index], cell_overwrite_ok=True)  # 新建sheet
  271.                 size = [12 for i in value[0]]
  272.                 head_style = se.style_head(sheet, size, wrap=1, is_bg=1)  # 定义表头样式
  273.                 time_style = se.style_table_time()  # 时间内容样式
  274.                 normal_style = se.style_table_normal({"horz": "CENTER", "vert": "CENTER"})  # 普通内容样式
  275.                 for col, val in enumerate(value[0]):
  276.                     sheet.write(0, col, val, head_style)
  277.                 print(f"写入表头成功！{value[0]}")
  278.                 for i, v in enumerate(value[1:]):
  279.                     for co, va in enumerate(v):
  280.                         val = str(va)
  281.                         if se.is_date(val):
  282.                             if len(val) > 19:
  283.                                 new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
  284.                             elif 10 < len(val) <= 19:
  285.                                 new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S")
  286.                             else:
  287.                                 new_val = datetime.datetime.strptime(val, '%Y-%m-%d')
  288.                             sheet.write(i + 1, co, new_val, time_style)
  289.                         elif se.is_number(val) == 1 and len(val) < 11:
  290.                             sheet.write(i + 1, co, int(val), normal_style)
  291.                         else:
  292.                             sheet.write(i + 1, co, val, normal_style)
  293.                 print(f"写入表格内容成功！共{len(value) - 1}行")
  294.         except Exception as err:
  295.             info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
  296.             print(info)
  297.  
  298.     @staticmethod
  299.     def is_date(date_str):
  300.         """
  301.         判断date_str是否为日期格式
  302.         :param date_str:
  303.         :return:
  304.         """
  305.         date_format = "%Y-%m-%d"
  306.         try:
  307.             if isinstance(date_str, str):
  308.                 new_str = date_str[:10]
  309.                 valid_date = time.strptime(new_str, date_format)
  310.                 return True
  311.             else:
  312.                 return False
  313.         except ValueError or TypeError as e:
  314.             return False
  315.  
  316.     @staticmethod
  317.     def is_number(val: str):
  318.         """
  319.         判断字符串的数字类型
  320.         :param val: 字符串
  321.         :return: 1--整数，2--小数，0--非数字
  322.         """
  323.         try:
  324.             float(val)
  325.             if val.isdigit() or val.split('-')[-1].isdigit():
  326.                 return 1
  327.             elif val.split('.')[0].isdigit() or val.split('-')[-1].split('.')[-1].isdigit():
  328.                 return 2
  329.             else:
  330.                 return 0
  331.         except ValueError:
  332.             pass
  333.         return 0
  334.  
  335.     @staticmethod
  336.     def data_statistics(data):
  337.         """
  338.         统计详细数据，保存为excel
  339.         :param data: 数据库查询结果
  340.         :return:
  341.         """
  342.         se = DataStatistics()
  343.         wb = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
  344.         ws = wb.add_sheet('活动问卷数据汇总', cell_overwrite_ok=True)  # 新建sheet
  345.  
  346.         label_style = se.style_label()
  347.         head_style = se.style_head(ws, size=[20, 20, 2, 10, 20, 20, 25, 2, 10, 25, 20, 2, 25, 20, 8, 25, 25])
  348.         dicts1 = {"horz": "CENTER", "vert": "CENTER"}
  349.         dicts2 = {"horz": "LEFT", "vert": "CENTER"}
  350.         normal_table_style_center = se.style_table_normal(dicts1)
  351.         normal_table_style_left = se.style_table_normal(dicts2)
  352.         separator_table_style = se.style_table_separator()
  353.         time_table_style = se.style_table_time()
  354.         percent_table_style = se.style_table_percent()
  355.  
  356.         # 基本信息
  357.         ws.write_merge(0, 0, 0, 1, '基本信息', label_style)
  358.         ws.write(1, 0, '调查名称', head_style)
  359.         ws.write(1, 1, data[0][0]['调查名称'], normal_table_style_center)
  360.         ws.write(2, 0, '调查问卷名称', head_style)
  361.         ws.write(2, 1, data[0][0]['调查问卷名称'], normal_table_style_center)
  362.         ws.write(3, 0, '问卷发送人数', head_style)
  363.         result1 = [v['姓名'] for v in data[0] if v['目前卡片数'] == 18]
  364.         ws.write(3, 1, len(result1), normal_table_style_center)
  365.         ws.write(4, 0, '问卷提交人数', head_style)
  366.         result2 = [v['姓名'] for v in data[0] if v['填写时长'] is not None]
  367.         ws.write(4, 1, len(result2), normal_table_style_center)
  368.  
  369.         # 分隔
  370.         ws.write_merge(0, 142, 2, 2, '', separator_table_style)
  371.  
  372.         # 用户问卷发送及提交信息
  373.         ws.write_merge(0, 0, 3, 6, '用户问卷发送及提交信息', label_style)
  374.         head1 = ['姓名', '问卷发送时间', '问卷提交时间', '填写时长（s）']
  375.         for i, v in enumerate(head1):
  376.             ws.write(1, i + 3, v, head_style)
  377.         for row, v in enumerate(data[0]):
  378.             ws.write(row + 2, 3, v['姓名'], normal_table_style_left)
  379.             if v['目前卡片数'] != 18:
  380.                 ws.write(row + 2, 4, '', normal_table_style_left)
  381.             else:
  382.                 ws.write(row + 2, 4, v['最后卡片时间'], time_table_style)
  383.             for j in data[1]:
  384.                 if v['会员id'] == j['会员id']:
  385.                     ws.write(row + 2, 5, j['问卷提交时间'], time_table_style)
  386.                     break
  387.             if v['会员id'] not in [i['会员id'] for i in data[1]]:
  388.                 ws.write(row + 2, 5, '', normal_table_style_left)
  389.             if v['填写时长'] is None:
  390.                 ws.write(row + 2, 6, '', normal_table_style_left)
  391.             else:
  392.                 ws.write(row + 2, 6, v['填写时长'], normal_table_style_center)
  393.  
  394.         # 分隔
  395.         ws.write_merge(0, 142, 7, 7, '', separator_table_style)
  396.  
  397.         # 用户问卷填写内容详情
  398.         ws.write_merge(0, 0, 8, 10, '用户问卷填写内容详情', label_style)
  399.         head2 = ['姓名', '问题', '答案']
  400.         for i, v in enumerate(head2):
  401.             ws.write(1, i + 8, v, head_style)
  402.         for row, val in enumerate(data[1]):
  403.             ws.write(row + 2, 8, val['姓名'], normal_table_style_left)
  404.             ws.write(row + 2, 9, val['问题'], normal_table_style_left)
  405.             ws.write(row + 2, 10, val['答案'], normal_table_style_left)
  406.  
  407.         # 分隔
  408.         ws.write_merge(0, 142, 11, 11, '', separator_table_style)
  409.  
  410.         # 问卷问题选项信息
  411.         ws.write_merge(0, 0, 12, 16, '问卷问题选项信息', label_style)
  412.         head3 = ['问题', '问题选项', '题型', '选项被选择的人数', '选项被选择的占比']
  413.         for i, v in enumerate(head3):
  414.             ws.write(1, i + 12, v, head_style)
  415.         for row, val in enumerate(data[2]):
  416.             ws.write(row + 2, 12, val['题目'], normal_table_style_left)
  417.             ws.write(row + 2, 13, val['选项'], normal_table_style_left)
  418.             ws.write(row + 2, 14, val['题型'], normal_table_style_center)
  419.             result3 = [v['答案'] for v in data[1] if val['题目'] in v['问题'] if val['选项'] in v['答案']]
  420.             ws.write(row + 2, 15, len(result3), normal_table_style_center)
  421.             ws.write(row + 2, 16, len(result3) / len(result2), percent_table_style)
  422.  
  423.         sj = datetime.datetime.now().strftime('%Y-%m-%d %H%M%S')
  424.         wb.save('调查问卷数据统计' + sj + '.xlsx')  # 保存excel
  425.  
  426.  
  427. def yaml_read(filename):
  428.     """
  429.     读取yaml文件
  430.     """
  431.     with open(file=filename, mode="r", encoding="utf-8") as f:
  432.         data = yaml.safe_load(f.read())
  433.     return data
  434.  
  435.  
  436. if __name__ == "__main__":
  437.     db_info = yaml_read('cfg_mysql_test.yaml')
  438.     ds = DataStatistics()
  439.     datas = ds.data_detail(db_info, '调查问卷数据统计测试')
  440.     ds.data_statistics(datas)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/605940
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号