表格数据读取为字典/写入指定位置数据

##测试数据 表格数据转化为字典提供测试脚本使用,

import sys,os
from openpyxl import load_workbook
add_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(add_path)


path = os.path.dirname(__file__)
print(path)

data_path = path + '/baidu_data.xlsx'
sheet_name = 'login'

#打开表格
wb = load_workbook(data_path)
#读取表格的sheet_name
sheet_obj = wb[sheet_name]

#获取表格每行的数据
list_h = list(sheet_obj.rows)
#获取表格第一行的数据,也就是表头的数据
list_h1 = list_h[0]
#因为表格内字典已caseName再次封装区分行数据,先获取caseName所在列
w = 0
for i in range(len(list_h1)):
    if list_h1[i].value == 'caseName':
        w = i
        print(w)
# 封装表格字典
xlsl_dict = {}
for j in list(sheet_obj.rows)[1:]:  # 遍历表格每行数据,但是不遍历第一行表头数据
    case_name = j[w].value  # 现获取第j行表格数据 的casename值,再次封装字典使用
    case_dict = {}  # 以casename值 为区分再次封装字典塞入整个表格字典
    for k in range(len(list_h1)):  # 遍历每列字段
        data_value = j[k].value
        if type(data_value) == str:#表格内数据可为字典,读取为字典,自点击换行处理
            if '{' in j[k].value or '}' in j[k].value:
                data_value = j[k].value.replace('\n','')
                data_value = json.loads(data_value)

        case_dict[list_h1[k].value] = data_value  # case_dict内 key取值表头的列的值,value取值j行k列的值
    xlsl_dict[case_name] = case_dict  # 将遍历好的case_dict字典塞入整体xlsl_dict
print('当前表格字典为',xlsl_dict)
wb.close()#关闭表格

　　

根据需求写入指定位置数据,单个数据写入

import sys,os
from openpyxl import load_workbook
add_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(add_path)


path = os.path.dirname(__file__)
print(path)

data_path = path + '/baidu_data.xlsx'
sheet_name = 'login'

#打开表格
wb = load_workbook(data_path)
#读取表格的sheet_name
sheet_obj = wb[sheet_name]

#获取表格每行的数据
list_h = list(sheet_obj.rows)
#获取表格第一行的数据,也就是表头的数据
list_h1 = list_h[0]
w_text = 'stute'#测试结果状态表头
h_text = 'test_signin_password_error'#测试标题字段

#获取caseame列宽
case_name_w = 0
for i in range(len(list_h1)):
    if list_h1[i].value == 'caseName':
        case_name_w = i
        print(case_name_w)

#根据列宽,已知测试标题,求出stute状态写入宽度
w = 0#列宽
for i in range(len(list_h1)):
    if list_h1[i].value == w_text:
        w = i+1#i为列表下标,实际需要+1
        break

#根据case_name求出行高
xxx = list(sheet_obj.rows)
h = 0#行高
for j in range(len(xxx)):
    print(xxx[j][case_name_w].value)
    if xxx[j][case_name_w].value==h_text:
        h = j+1#j为列表下标,实际需要+1

print('行高为%d' %h)
print('宽为%d' %w)
sheet_obj.cell(row = h,column = w,value = 'TEST')#写入数据
wb.save('baidu_data.xlsx')#保存,写入不保存则不生效
ce = sheet_obj.cell(row = h,column = w)#读取写入表格位置数据
print(ce.value)

　　

字典写入表格

def input_xlsx_dict(data_path,sheet_name,input_data):
    '''

    :param data_path: 表格路径
    :param sheet_name: sheet页名称
    :param input_data: 写入数据,这里的写入数据是字典
    :return:
    '''
    # 打开表格
    wb = load_workbook(data_path)
    # 读取表格的sheet_name
    sheet_obj = wb[sheet_name]
    for key in input_data.keys():
        # 获取表格每行的数据
        stute_text = 'testResult'
        w,h = return_table_coordinates(sheet_obj,stute_text,key)
        print(w, h)
        stute = input_data[key]['testResult']
        sheet_obj.cell(row=h, column=w, value=stute)  # 写入数据

        data_test = 'testTime'
        w2, h2 = return_table_coordinates(sheet_obj, data_test,key)
        test_data = input_data[key]['testTime']
        print(w2,h2)
        sheet_obj.cell(row=h2, column=w2, value=test_data)  # 写入数据
        wb.save(data_path)  # 保存,写入不保存则不生效
        # ce = sheet_obj.cell(row=h, column=w)  # 读取写入表格位置数据
        # print(ce.value)



def return_table_coordinates(sheet_obj,x_coordinate,y_coordinate):
    # 根据列宽,已知测试标题,求出stute状态写入宽度
    '''
    :param sheet_obj: xlsx sheet页读取数据
    :param x_coordinate: x轴名称
    :param y_coordinate: y轴名称
    :return:
    '''
    number_lines = list(sheet_obj.rows)
    # 获取表格第一行的数据,也就是表头的数据
    number_lines_1 = number_lines[0]
    # 获取caseame列宽
    case_name_w = 0
    for i in range(len(number_lines_1)):
        if number_lines_1[i].value == 'caseName':
            case_name_w = i
            print(case_name_w)
            break
    x = 0  # 列宽
    for i in range(len(number_lines_1)):
        if number_lines_1[i].value == x_coordinate:
            x = i + 1  # i为列表下标,实际需要+1
            break

    # 根据case_name求出行高
    y = 0  # 行高
    for j in range(len(number_lines)):
        if number_lines[j][case_name_w].value == y_coordinate:
            y = j + 1  # j为列表下标,实际需要+1
    print('当前坐标%s%s' %(str(x),str(y)))
    return x,y

　　

表格数据修改及删除操作

import openpyxl

old_data = {
    1:{'id':'RT891ES-E21530-3',
        'price': '995.84'
        },
    2:{'id':'RT891ES-E13869',
       'price':'51111.31'
},
3:{'id':'9',
       'price':'542.31'
}
}


xlsx_path = r'E:\ROBOT\pending.xlsx'
wp = load_workbook(xlsx_path)#读取表格
ws = wp.get_sheet_names()[0]#获取表格所有sheet
wb = wp.get_sheet_by_name('0')#获取表格sheet名称为0的页


list_w = list(wb.rows)#获取行数
#获取表格第一行的数据,也就是表头的数据
list_h1 = list_w[0]#获取第一行的数据
for i in range(len(list_h1)):#遍历第一行的数据,取出指定字段坐标
    print(list_h1[i].value)
    if 'Seller SKU' == list_h1[i].value:
        hhh = i
    elif 'Campaign Price' == list_h1[i].value:
        price_h = i
#
#遍历列数据,指定列数据校验
list_sh = list(wb.columns)
#获取表格第一行的数据,也就是表头的数据
list_h2 = list_sh[hhh]
dedal = 0#处理数据列坐标,删除操作会让后续数据前移
for i in range(2,len(list_h2)):
    h_value = list_h2[i].value
    print('当前单单元格值为%s' %h_value)
    d_h = i - dedal + 1
    for key in old_data.values():
        if key['id'] == str(h_value):
            print('改价格')
            g_h = d_h
            g_w = price_h + 1
            wb.cell(row=g_h, column=g_w,value=key['price'])
            break

    else:
        wb.delete_rows(d_h)
        dedal += 1
        print('删除')
wp.save(xlsx_path)