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