Python之Openpyxl

设置Excel为自适应列宽

    def style_excel(self, sheet_name):
        '''
        :param sheet_name:  excel中的sheet名
        :return:
        '''
        # 打开excel
        # wb = load_workbook(self.excel_name)
        # 选择对应的sheet
        sheet = wb[sheet_name]
        # 获取最大行数与最大列数
        max_column = sheet.max_column
        max_row = sheet.max_row
        # 将每一列，单元格列宽最大的列宽值存到字典里，key:列的序号从1开始(与字典num_str_dic中的key对应)；value:列宽的值
        max_column_dict = {}
        # 生成列名字典，只是为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
        num_str_dict = DoSmoke.get_num_colnum_dict(self)
        # 遍历全部列
        for i in range(1, max_column + 1):
            # 遍历每一列的全部行
            for j in range(1, max_row + 1):
                column = 0
                # 获取j行i列的值
                sheet_value = sheet.cell(row=j, column=i).value
                # 通过列表生成式生成字符列表，将当前获取到的单元格的str值的每一个字符放在一个列表中（列表中一个元素是一个字符）
                sheet_value_list = [k for k in str(sheet_value)]
                # 遍历当前单元格的字符列表
                for v in sheet_value_list:
                    # 判定长度，一个数字或一个字母，单元格列宽+=1.1，其它+=2.2（长度可根据需要自行修改，经测试一个字母的列宽长度大概为1）
                    if v.isdigit() == True or v.isalpha() == True:
                        column += 1.1
                    else:
                        column += 2.2
                # 当前单元格列宽与字典中的对比，大于字典中的列宽值则将字典更新。如果字典没有这个key，抛出异常并将值添加到字典中
                try:
                    if column > max_column_dict[i]:
                        max_column_dict[i] = column
                except Exception as e:
                    max_column_dict[i] = column
        # 此时max_column_dict字典中已存有当前sheet的所有列的最大列宽值，直接遍历字典修改列宽
        for key, value in max_column_dict.items():
            sheet.column_dimensions[num_str_dict[key]].width = value

写入内容

 def write_column_excel(self,column):
        """
        某列写入内容
        """
        for i in range(1, ws.max_row + 1):
            ws_value = ws.cell(row=i, column=column).value
            ws.cell(row=i, column=column).value = ws_value

def write_row_excel(self,row):
        """
        某行写入内容
        """
        for i in range(1, ws.max_column + 1):
            ws_value = ws.cell(row=row, column=i).value
            ws.cell(row=row, column=i).value = ws_value

设置字体大小

def setting_front(self,front_size,row,column):
        """
        设置字体大小
        """
        fontStyle = Font(size=front_size)
        ws.cell(row=row, column=column).font = fontStyle

创建excel文件

    def creat_excel(self,path):
        wb = Workbook()
        sheet = wb.get_sheet_names()
        wb.save(path)

插入、合并、居中

 # 插入第一行
        ws.insert_rows(1)
        # 合并单元格
        ws.merge_cells("A1:E1")
        ws.cell(1, 1).value = "问题版本汇总"
        ws.cell(row=1, column=1).alignment = Alignment(horizontal='center', vertical='center')

赋值

	wb = openpyxl.load_workbook(path)
    ws = wb.active
    ws.cell(row=2, column=2).value = "111"

执行SQL语句并将结果写入Excel

    def save_as_excel(self):
        # 打开数据库连接
        db = pymysql.connect("10.150.154.190", "reporter", "Reporter01", "reports", charset='utf8' )
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        date = input("请输入开始时间：格式（年-月-日 00：00：00）")
        # date = '2020-12-20 10:00:00'
        # 筛选
        sql = f'''SELECT versionPath,pass_rate,detail,end_time,analysis 
                            FROM tb_report_smoke_brief as a 
                            INNER JOIN tb_test_event as b 
                            WHERE  a.test_event_id = b.id 
                            AND end_time > '{date}' 
                            AND (pass_rate NOT LIKE '100%' OR detail not LIKE 'ok')'''
        try:
            print("执行查询语句")
            cursor.execute(sql)
            #执行命令
            db.commit()
            # 关闭数据库
            cursor.close()
            db.close()
            myresult = cursor.fetchall()
            # 打开excel
            print("查询结果写入excel")
            wb = openpyxl.load_workbook(path)
            ws = wb.active
            i = 1
            for x in myresult:
                print(x)
                for j in range(0, len(x)):
                    ws.cell(row=i, column=j+1).value = x[j]
                i += 1
                wb.save(path)
        except:
            print("error")