python处理excel数据
这里用python的openpyxl库来处理excel下面是代码，写的很烂，但是up会保持学习，一起和小伙伴们进步。
import openpyxl
#import jieba
#import jieba.analyse
class Case: # 这个类用来存储用例的
def __init__(self, attrs):
"""
初始化用例
:param attrs:zip类型——>[{key,value},(key1,value1)......]
"""
for i in attrs:
setattr(self, i[0], i[1])
class ReadExcel(object):
def __init__(self, filename, sheetname):
"""
定义需要打开的文件及表名
:param filename: 文件名
:param sheetname: 表名
"""
self.wb = openpyxl.load_workbook(filename)
self.sheet = self.wb[sheetname]
def read_data_obj_new(self, list2):
# 获取最大行数
max_r1 = self.sheet.max_row
cases = []
# 用来存放表头数据
titles = []
for row in range(1, max_r1 + 1):
if row != 1:
case_data = []
for column in list2:
info = self.sheet.cell(row, column).value#这里cell读取第一行、第一列的数据：ce = sh.cell(row = 1,column = 1)
# print(info)
case_data.append(info)
case = list(zip(titles, case_data))
# 新建对象时，将对象传给Case类
case_obj = Case(case)
# print(case_obj.caseid,case_obj.excepted,case_obj.data)
cases.append(case_obj)
else:
# 获取表头
for column in list2:
title = self.sheet.cell(row, column).value
titles.append(title)
if None in titles:
raise ValueError("传入的表头的数据有显示为空")
return cases
def printans(filename, sheetname):
r = ReadExcel(filename,'shell')
if r==None:
return 0
res1 = r.read_data_obj_new([1, 2, 3,4,5,6,7,8,9,10])
#l=0
word_list=[]
itmes=[]
#for i in res1:
#word_list += jieba.cut(i.题干,cut_all=True)
#stopwords = {}.fromkeys(['的', '附近','。', '：', '，',' ','《','》','、',' ','（','）','“','”','；','\n','了',''])
#segs = jieba.cut(word_list, cut_all=False)
#final = ''
#print(word_list)
#seg=[]
#for seg in word_list:
#seg = seg.encode('gbk')
#if seg not in stopwords:
#itmes += seg
count_dict = {}
#print(itmes)
#print(itmes)
#for item in word_list:
#if item not in stopwords:
#count_dict[item] = count_dict[item] + 1 if item in count_dict else 1
#print(sorted(count_dict.items(), key=lambda x: x[1], reverse=True))
#print(" ".join(seg_list))
#tags = jieba.analyse.extract_tags(seg_list)
#print(",".join(tags))
#if i.答案==chara:
#l+=1
#print(i.题干+'A:'+i.A+'B:'+i.B+ 'C:'+i.C+'D:'+i.D+'答案：'+i.答案)
#seg_list = jieba.cut(i.题干,cut_all=True)
#print("Full Mode:", "/ ".join(seg_list))
#return l
#print(l)
j=0
for i in res1:
#if i.答案=='B':
#print(i.题干+'A:'+i.A+'B:'+i.B+ 'C:'+i.C+'D:'+i.D)
j+=1
#if i.答案2!=None:
#print(str(j)+':'+i.答案2,end=' ')
if i.题干==None:
return 0
if i.A==i.B:
print('判断:',end='')
print(str(j)+':'+i.题干)
elif len(str_Nempty(i.答案2)) ==1:
print('单选:',end='')
print(str(j)+':'+i.题干+'A:'+i.A+'B:'+i.B+ 'C:'+i.C+'D:'+i.D)
elif len(str_Nempty(i.答案2)) >1 and len(str_Nempty(i.答案2)) <=4:
print('多选:',end='')
print(str(j)+':'+i.题干+'A:'+i.A+'B:'+i.B+ 'C:'+i.C+'D:'+i.D)
elif len(str_Nempty(i.答案2)) >4 and i.答案1!=None:
print('多选*:',end='')
print(str(j)+':'+i.题干+'A:'+i.A+'B:'+i.B+ 'C:'+i.C+'D:'+i.D+'E:'+i.答案1)
def str_Nempty(s):
if s==None:
pass
else:
s=s
return s
def printchara():
num=0
for j in range(1,10):
print('第'+str(j)+'讲')
filename=str(j)+'.xlsx'
printans(filename,'shell')
#print(num)
if __name__ == '__main__':
printchara()
