python自动化web测试-openpyxl居中对齐和设置列宽

接口实现

import time
import openpyxl
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter

stime = time.strftime("%Y-%m-%d-%H_%M_%S",time.localtime(time.time()))

#创建新excel文件
def openpyxl_create_report(fileName):
    path = '../report/' + fileName + ".xlsx"
    data = openpyxl.Workbook() # 新建工作簿
    data.create_sheet('Report',0)  # 添加页,设置第一个工作页
    table = data.active # 获得当前活跃的工作页，默认为第一个工作页
    ncols = 1
    values = ["设备IP",
              "B40功放开启时间", "结果",
              "B40功放关闭时间", "结果",
              "B1功放开启时间", "结果",
              "B1功放关闭时间", "结果",
              "GSM功放开启时间", "结果",
              "GSM功放关闭时间", "结果",  # ok
              "B1核心板软重启时间", "结果",
              "B40核心板断电重启时间", "结果",
              "GSM核心板软重启时间", "结果",  #
              "B1核心板断电重启时间", "结果",
              "B40核心板软重启时间", "结果",
              "GSM核心板断电重启时间", "结果",  #
              "主控重启时间", "结果",
              "测试结果"
              ]  # 需要写入的值

    for value in values:
        # table.cell(1,1,'Test') # 行，列，值 这里是从1开始计数的
        table.cell(1, ncols, value)  # 因为单元格从0开始算，所以row不需要加一
        col_letter = get_column_letter(ncols)
        if ncols ==1 or (ncols%2) == 0: #从1开始 2 4 6 8
            table.column_dimensions[col_letter].width = 20
        else:
            table.column_dimensions[col_letter].width = 10
        ncols = ncols + 1
    data.save(path) # 一定要保存

def openpyxl_addin(fileName,values):
    fileName = '../report/' + fileName + ".xlsx"
    data = openpyxl.load_workbook(fileName)
    table = data.get_sheet_by_name('Report')
    nrows = table.max_row  # 获得行数
    col = 1
    for value in values:
        print("value:%s, col:%d"%(value,col))
        table.cell(nrows + 1, col).value = value
        col += 1
    data.save(fileName)

#居中对齐，通过遍历方式实现
def set_from_center(fileName,tableName):
    fileName = '../report/' + fileName + ".xlsx"
    data = openpyxl.load_workbook(fileName)
    table = data.get_sheet_by_name(tableName)
    nrows = table.max_row  # 获得行数
    ncols = table.max_column
    for i in range(nrows):
        for j in range(ncols):
            table.cell(row=i+1, column=j+1).alignment = Alignment(horizontal='center', vertical='center')
    data.save(fileName)

API说明

#设置列宽
#视乎没有对整行列进行操作的方法，最后只能通过遍历方式实现
#col_letter不能是索引数字，只能是字符串
table.column_dimensions[col_letter].width = 20
#居中对齐，这是我找到最简单且有效的方式
table.cell(row=i+1, column=j+1).alignment = Alignment(horizontal='center', vertical='center')