1、openpyxl使用
涉及到单元格合并、赋值
''' 1.格式转换 raw_data create_data boolean Bool uint8 UInt8 int8 Int8 uint16 UInt16 int16 Int16 uint32 UInt32 int32 Int32 2.定点数数据类型格式转换 ''' from openpyxl import load_workbook from openpyxl.styles import Color, Font, Alignment # wb = load_workbook("EPA2_data.xlsx") wb = load_workbook("EPA2_data.xlsx") sheet = wb.get_sheet_by_name('Output') #格式居中显示 align = Alignment(horizontal='center', vertical='center') TCrows=[] Cases=[] #<Worksheet "Output"> 601 11 # print(sheet) # print(sheet.max_row) # print(sheet.max_column) raw_type_dic = { 'boolean': 'Bool', 'uint8': 'UInt8', 'int8': 'Int8', 'uint16': 'UInt16', 'int16': 'Int16', 'uint32': 'UInt32', 'int32': 'Int32', } for row in sheet.iter_rows(): TCrows.append(row) for i in range(1, 1448): Cases.append(TCrows[i]) totalNum = 1447 runnable_data_list = [] start = 2 count = 0 test_merge_list = [] for item in range(0,totalNum): #Runnable名称 runnable_data = Cases[item][1].value # fixdt(0,16,0.01,0) print('第%s次'%count+':'+str(runnable_data)) merge_list = [] if item != 0: if runnable_data != runnable_data_list[item-1]: print('meege====='+ str(item)) # sheet.merge_cells("B%s:B%s" % (temp, item+1)) merge_list.append(start) stop = item+1 merge_list.append(stop) start = stop+1 test_merge_list.append(merge_list) if item == totalNum-1: print('最后一次%s'%count) stop = item merge_list.append(start) merge_list.append(stop) test_merge_list.append(merge_list) # if item != 0: # if runnable_data != runnable_data_list[item-1]: # print('meege====='+ str(item)) # sheet.merge_cells("B%s:B%s" % (temp, item+1)) # temp= item+2 # print(temp) # print(runnable_data_list) # # print(runnable_data) type_data = Cases[item][7].value # fixdt(0,16,0.01,0) # 1. 判断是否字典类型type if type_data in raw_type_dic: #APP Data Type Cases[item][5].value = raw_type_dic[type_data] #Base Type Cases[item][6].value = type_data #Compu Method flag Cases[item][8].value = int('0') #精度 Cases[item][9].value = int('1') #offset # Cases[item][10].value = '0' elif "fixdt" in type_data: format_list = [] format_str = '' a = '' b = '' c = '' d = '' e = '' f = '' dstr = type_data.replace('fixdt(', '') dstr = dstr.replace(')', '') dlist = dstr.split(',') for d in dlist: format_list.append(d) # 1.规则1，判断符号位 if format_list[0] == '0': a = 'U' baseType = 'unit' else: a = 'I' baseType = 'int' # 规则2，判断数据长度位 b = format_list[1] if '.' in format_list[2]: c = len(format_list[2].split('.')[1]) temp = 0 d_count = 0 for i in format_list[2].split('.')[1]: if i != '0': temp = d_count d_count += 1 d = format_list[2].split('.')[1][temp:] else: c = '0' d = format_list[2] # 规则3，判断小数点长度 if '-' in format_list[3]: e = 'N' f = format_list[3][1:] else: e = 'P' f = format_list[3] format_str = '%s%sS%s%s%s%s' % (a, b, c, d, e, f) # APP Data Type Cases[item][5].value = format_str # Base Type Cases[item][6].value = baseType + b # Compu Method flag Cases[item][8].value = int('1') # 精度 Cases[item][9].value =format_list[2] # offset Cases[item][10].value = format_list[3] else: print("请确认第%s个是否输入格式正确" % count) count += 1 runnable_data_list.append(runnable_data) print(test_merge_list) for key in test_merge_list: sheet.merge_cells("B%s:B%s" % (key[0], key[1]),align) wb.save('EPA2_data.xlsx') def read_excel_type(): #读取excel的type列数据 raw_list = [] #获取数据类型这一列的数据 for cell in list(sheet.columns)[7]: raw_list.append(cell.value) return raw_list def format_data(data_list): #1. 判断是否字典类型type count = 0 for data in data_list: if data in raw_type_dic: data_list[count] = raw_type_dic[data] # print("第%s个判断在字典数据类型里"%count) # 2.判断是否是定点数数据类型 elif "fixdt" in data: format_list = [] format_str = '' a = '' b = '' c = '' d = '' e = '' f = '' dstr = data.replace('fixdt(', '') dstr = dstr.replace(')', '') dlist = dstr.split(',') for d in dlist: format_list.append(d) #1.规则1，判断符号位 if format_list[0] == '0': a = 'U' else: a = 'I' #规则2，判断数据长度位 b = format_list[1] if '.' in format_list[2]: c = len(format_list[2].split('.')[1]) temp = 0 d_count = 0 for i in format_list[2].split('.')[1]: if i != '0': temp = d_count d_count += 1 d = format_list[2].split('.')[1][temp:] else: c = '0' d = format_list[2] #规则3，判断小数点长度 if '-' in format_list[3]: e = 'N' f = format_list[3][1:] else: e = 'P' f = format_list[3] format_str = '%s%sS%s%s%s%s'%(a,b,c,d,e,f) data_list[count] = format_str else: print("请确认第%s个是否输入格式正确" % count) count +=1 print(data_list) #3.其他数据类型，显示提示判断输入数据是否正确 # raw_data = read_excel_type() # raw_data = raw_data[1:] # print(raw_data) # format_data(raw_data) #合并单元格RUNNANBLE 第1列 # def read_excel_type(): # #读取excel的type列数据 # raw_list = [] # #获取数据类型这一列的数据 # print() # for cell in list(sheet.columns)[1]: # print(cell.value) #ws.merge_cells ("A1:C3") #合并单元格 # read_excel_type() max_row = sheet.max_row # print(max_row) # for i in range(2,10): # for j in range(2,3): # a = sheet.cell(row = i, column = j) # print(a.value) # # # sheet.merge_cells ("B2:B10") # sheet.unmerge_cells ("B2:B10") # wb.save('EPA2_data.xlsx')
