Python运用反射机制实现Excel数据与对象属性的高效映射转换

Python使用反射实现Excel与对象之间的转换

代码在最下方&＃xff0c;伸手党直接滚动到最后

场景

需要从Excel中加载到内存中&＃xff0c;转换为class对象执行操作

环境

Python3.8

openpyxl&＃61;&＃61;3.0.5

前置知识

反射(仅介绍这个帮助类用到的几个反射方法)

setattr、getattr

class Person():

name &＃61; None

def __init__(self, name):

self.name &＃61; name

p &＃61; Person("laowang")

n &＃61; getattr(p, "name")

print(n)

# 打印结果&＃xff1a; laowang

setattr(p, "name", "laoxu")

n2 &＃61; getattr(p, "name")

print(n2)

# 打印结果&＃xff1a; laoxu

反射实例化对象

class Person():

name &＃61; None

def print_sth(self):

print("测试实例化方法", self.name)

def test(clazz):

"""

实例化对象

:param clazz: 要实例化的类型

"""

x &＃61; clazz()

setattr(x, "name", "老王")

x.print_sth()

# 打印&＃xff1a; 测试实例化方法 老王

test(Person)

Excel操作类库 - openpyxl

创建Excel

from openpyxl import Workbook

wb &＃61; Workbook()

ws1 &＃61; wb.active

ws1.append([&＃39;name&＃39;, &＃39;age&＃39;, &＃39;isManager&＃39;, &＃39;remark&＃39;])

ws1.append([&＃39;&＃39;, &＃39;&＃39;, &＃39;&＃39;, &＃39; &＃39;])

ws1.append([&＃39;老王&＃39;, &＃39;18&＃39;, &＃39;false&＃39;, &＃39; &＃39;])

ws1.append([&＃39;laoxu&＃39;, &＃39;28.4&＃39;, &＃39;TRUE&＃39;, &＃39;zaoricaifuziyou&＃39;])

ws1.append([&＃39;&＃39;, &＃39;&＃39;, &＃39;&＃39;, &＃39; &＃39;])

ws2 &＃61; wb.create_sheet("ws2")

ws2.append([&＃39;name&＃39;, &＃39;age&＃39;, &＃39;isManager&＃39;, &＃39;remark&＃39;])

ws2.append([&＃39;小李&＃39;, &＃39;50&＃39;, &＃39;TRuE&＃39;, &＃39;fly knife&＃39;])

ws2.append([&＃39;&＃39;, &＃39;&＃39;, &＃39;&＃39;, &＃39; &＃39;])

ws2.append([&＃39;aaa&＃39;, &＃39;11&＃39;, &＃39;false&＃39;, &＃39;hehehe&＃39;])

wb.save("test_convert_2_class.xlsx")

读取Excel

from openpyxl import Workbook

def print_row(arr):

"""为了显示方便&＃xff0c;打印行"""

for item in arr:

print(item,end&＃61;"\t\t|")

print()

# 读取上一个代码块创建的Excel代码

work_book &＃61; load_workbook("test_convert_2_class.xlsx")

result &＃61; []

for sheet_name in work_book.sheetnames:

print("-----------------",sheet_name,"-----------------")

ws &＃61; work_book[sheet_name]

# 获取表头

table_heads &＃61; []

for title_row in ws.iter_rows(max_row&＃61;1):

for cell in title_row:

table_heads.append(cell.value)

print_row(table_heads)

# 获取表数据

table &＃61; []

for row in ws.iter_rows(min_row&＃61;2):

row_data&＃61;[]

for column_index in range(len(row)):

row_data.append(row[column_index].value)

print_row(row_data)

# 打印结果如下&＃xff1a;

# ----------------- Sheet -----------------

# name |age |isManager |remark |

# None |None |None | |

# 老王 |18 |false | |

# laoxu |28.4 |TRUE |zaoricaifuziyou |

# None |None |None | |

# ----------------- ws2 -----------------

# name |age |isManager |remark |

# 小李 |50 |TRuE |fly knife |

# None |None |None | |

# aaa |11 |false |hehehe |

伸手党代码

excel_helper.py

import os

import re

from os.path import isfile

from openpyxl import load_workbook, Workbook

def _convert_value(value):

"""

将单元格中数据&＃xff0c;区分基本类型

类似"true"/"false"(不区分大小写)转换为bool值

长得像数字的转换为float类型

其他(空格、空行)转换为None

:param value: 单元格的值

:return: 转换后的类型

"""

value_str &＃61; str(value).lower()

if value_str &＃61;&＃61; &＃39;true&＃39;:

return True

elif value_str &＃61;&＃61; &＃39;false&＃39;:

return False

elif re.match(r"^[&＃43;|-]?\d&＃43;.?\d*$", value_str):

return float(value_str)

elif re.match(r"^\s*$", value_str):

return None

else:

return value

class ExcelHelper:

"""

Excel帮助类

"""

&＃64;classmethod

def convert_2_class(cls, file_path, clazz):

"""

转换为class&＃xff0c;可转换多张sheet表&＃xff0c;转换为统一clazz对象

过滤掉为空行

:param file_path:Excel文件路径

:param clazz:结果转换为clazz对象

:return: 对象列表的列表&＃xff0c;结构为[[clazz(),clazz()],[clazz()]]

"""

if not file_path.endswith(".xlsx"):

raise ValueError("文件必须为.xlsx结尾的Excel文件")

if not isfile(file_path):

raise FileNotFoundError("文件路径 {0} 不存在".format(file_path))

work_book &＃61; load_workbook(file_path)

result &＃61; []

for sheet_name in work_book.sheetnames:

ws &＃61; work_book[sheet_name]

# 获取表头

table_heads &＃61; []

for title_row in ws.iter_rows(max_row&＃61;1):

for cell in title_row:

table_heads.append(cell.value)

# 获取表数据

table &＃61; []

for row in ws.iter_rows(min_row&＃61;2):

# 实例化对象

instance &＃61; clazz()

for column_index in range(len(row)):

setattr(instance, table_heads[column_index], _convert_value(row[column_index].value))

# 过滤空行(所有属性均为None的对象)

is_valid &＃61; False

for attr in instance.__dict__:

if not attr.startswith("_") and instance.__dict__[attr] is not None:

is_valid &＃61; True

break

if is_valid:

table.append(instance)

result.append(table)

return result

&＃64;classmethod

def save(cls, file_path, tables):

if not file_path.endswith(".xlsx"):

raise ValueError("文件必须为.xlsx结尾的Excel文件")

work_book &＃61; Workbook()

is_first &＃61; True

for table in tables:

if is_first:

ws &＃61; work_book.active

is_first &＃61; False

else:

ws &＃61; work_book.create_sheet()

# 添加表头

table_heads &＃61; []

for attr in table[0].__dict__:

# 过滤"_"开头的属性

if not attr.startswith("_"):

table_heads.append(attr)

ws.append(table_heads)

# 添加数据

for row in table:

data &＃61; []

for head in table_heads:

data.append(getattr(row, head))

ws.append(data)

try:

# 生成保存文件夹路径

folder_index &＃61; max(file_path.rfind("\\"), file_path.rfind("/"))

if folder_index !&＃61; -1:

folder_path &＃61; file_path[0:folder_index]

if not os.path.exists(folder_path):

os.mkdir(folder_path)

work_book.save(file_path)

except Exception:

raise OSError("创建Excel失败")

使用方法

# 导入类

from excel_helper import ExcelHelper

# 示例对象

class A:

name&＃61;None

age&＃61;None

isManager&＃61;None

# 读取Excel文件&＃xff0c;并转换为指定类型对象列表

tables &＃61; ExcelHelper.convert_2_class("123.xlsx", A)

# 保存为Excel

a1&＃61;A()

table&＃61;[a1]

ExcelHelper.save("456.xls", [table])

注意

该帮助类均为&＃64;classmethod

该帮助类使用反射实现&＃xff0c;所以表头名称需要与对象的字段名一一对应(如代码中的class A 与 下表"表1-1")

Excel中可以有多张表(sheet tab)&＃xff0c;所以参数为对象列表的列表&＃xff0c;请注意对应关系

当前读取Excel仅能转换为一个class类型&＃xff0c;所以多种表结构请使用多张表

表1-1

name

age

isManager

老王

18

True

Tom

28

FALSE

参考资料

以上