Python使用反射来实现Excel与对象之间的转换。代码在最下方，手拉手直接滚动到最后一个场景。它需要从Excel加载到内存中并转换为类对象。执行运行环境Python3.8openpyxl==3.0.5前置知识Reflection（仅此helper类中用到的几个反射方法）setattr,getattrclassPerson():name=Nonedef__init__(self,name):self.name=namep=Person("laowang")n=getattr(p,"name")print(n)#打印结果：laowangsetattr(p,"name","laoxu")n2=getattr(p,"name")print(n2)#打印结果:laoxu反射实例化对象classPerson():name=Nonedefprint_sth(self):print("测试实例化方法",self.name)deftest(clazz):"""实例化对象:paramclazz:需要实例化的类型"""x=clazz()setattr(x,"name","老王")x.print_sth()#打印：测试实例化方法老王test(Person)Excel操作类库-openpyxl创建ExcelfromopenpyxlimportWorkbookwb=Workbook()ws1=wb.activews1.append(['name','age','isManager','remark'])ws1.append(['','','',''])ws1.append(['老王','18','false',''])ws1.append(['老徐','28.4','TRUE','zaoricaifuziyou'])ws1.append(['','','',''])ws2=wb.create_sheet("ws2")ws2.append(['name','age','isManager','remark'])ws2.append(['小李','50','TRuE','飞刀'])ws2.append(['','','',''])ws2.append(['aaa','11','false','hehehe'])wb.save("test_convert_2_class.xlsx")ReadExcelfromopenpyxlimportWorkbookdefprint_row(arr):"""For显示方便，打印"""行foriteminarr:print(item,end="\t\t|")print()#读取前面代码块创建的Excel代码work_book=load_workbook("test_convert_2_class.xlsx")result=[]forwork_book.sheetnames中的sheet_name:print("----------------",sheet_name,"-----------------")ws=work_book[sheet_name]#获取表头table_heads=[]fortitle_rowinws.iter_rows(max_row=1):forcellintitle_row:table_heads.append(cell.value)print_row(table_heads)#获取表格数据table=[]forrowinws.iter_rows(min_row=2):row_data=[]forcolumn_indexinrange(len(row)):row_data.append(row[column_index].value)print_row(row_data)#打印结果如下：#--------------Sheet----------------#姓名|年龄|isManager|备注|#无|无|无||#老王|18|false|--------------ws2--------------#name|age|isManager|remark|#小李|50|TRuE|飞刀|#None|None|None|#aaa|11|false|hehehe|动手代码excel_helper.pyimportosimportrefromos.pathimportisfilefromopenpyxlimportload_workbook,Workbookdef_convert_value(value):"""转换单位网格中的数据，区分基本类型像“true”/“false”（不区分大小写）转换为bool值，看起来像数字转换为float类型其他（空格，空行）转换为None：param值：单元格值：返回：转换类型"""value_str=str(value).lower()ifvalue_str=='true':returnTrueelifvalue_str=='false':returnFalseelifre.match(r"^[+|-]?\d+.?\d*$",value_str):returnfloat(value_str)elifre.match(r"^\s*$",value_str):返回Noneelse:returnvalueclassExcelHelper:"""Excelhelperclass"""@classmethoddefconvert_2_class(cls,file_path,clazz):"""转换为class，转换多个sheet，转换为统一的clazz对象，过滤掉为Emptyline:paramfile_path:Excel文件路径:paramclazz:结果转换为clazz对象:return:对象列表列表，结构为[[clazz(),clazz()],[clazz()]]"""ifnotfile_path.endswith(".xlsx"):raiseValueError("ThefilemustbeanExcelfileendingin.xlsx")ifnotisfile(file_path):raiseFileNotFoundError("Thefilepath{0}doesnotexist".format(file_path))work_book=load_workbook(file_path)result=[]forsheet_nameinwork_book.sheetnames:ws=work_book[sheet_name]#gettableheadertable_heads=[]fortitle_rowinws.iter_rows(max_row=1)：对于title_row中的单元格：table_heads.append(cell.value)#获取表格数据table=[]forrowinws.iter_rows(min_row=2):#实例化对象instance=clazz()forcolumn_indexinrange(len(row)):setattr(instance,table_heads[column_index],_convert_value(row[column_index].value))#过滤空行（属性为None的对象）is_valid=Falseforattrininstance.__dict__:ifnotattr.startswith("_")andinstance.__dict__[attr]isnotNone:is_valid=Truebreakifis_valid:table.append(instance)result.append(table)返回结果@classmethoddefsave(cls,file_path,tables):ifnotfile_path.endswith(".xlsx"):raiseValueError("ThefilemustbeanExcelfileendingin.xlsx")work_book=Workbook()is_first=Truefortableintables:ifis_first:ws=work_book.activeis_first=Falseelse:ws=work_book.create_sheet()#添加表头table_heads=[]forattrintable[0].__dict__:#过滤"_"打开头的属性ifnotattr.startswith("_"):table_heads.append(attr)ws.append(table_heads)#添加数据forrowintable:data=[]forheadintable_heads:data.append(getattr(row,head))ws.append(data)try:#生成保存文件路径folder_index=max(file_path.rfind("\\"),file_path.rfind("/"))iffolder_index!=-1:folder_path=file_path[0:folder_index]如果不是os.path.exists(folder_path):os.mkdir(folder_path)work_book.save(file_path)除了异常：raiseOSError("failedtocreateExcel")Usingmethod#Importclassfromexcel_helperimportExcelHelper#ExampleobjectclassA:name=Noneage=NoneisManager=None#读取Excel文件并将其转换为指定类型对象列表tables=Excel助手。convert_2_class("123.xlsx",A)#SaveasExcela1=A()table=[a1]ExcelHelper.save("456.xls",[table])注意helper类是@classmethod，它是用反射实现的，所以表头的名称需要对应对象的字段名（如代码中的类A和下表“表1-1”）。Excel中可以有多个sheet（sheettabs），所以参数为对象列表List，请注意对应关系目前看Excel只能转成类类型，所以多表结构请使用多表
