util_excel.py 2022-09-13 笔记,技巧 暂无评论 976 次阅读 ```python ''' @File :util_excel.py @Description :Excel表格工具类 @Date :2022/09/12 14:36:10 @Author :QianCheng @Version :1.0 ''' from openpyxl import Workbook,load_workbook _path_xlsx = __file__[:__file__.rfind('.')]+'.xlsx' #=============================================================# # 1. 保存二维数组 mat_save(name,mat) # 读取二维数组 mat_load(name) # 2. 保存对象数组 objs_save(name,objs) # 读取对象数组 objs_load(name) #-------------------------------------------------------------# def mat_save(name='default',mat=[['11','12'],['21','22']],path_xlsx=_path_xlsx,retry=3): try: wb = load_workbook(path_xlsx) if name in wb: del wb[name] ws = wb.create_sheet(title=name) except Exception as e: wb = Workbook() ws = wb.active ws.title = name #-----------------------------------------------# if not type(mat)== list: ws.append([mat]) elif type(mat)==list and not type(mat[0])==list: ws.append(mat) else: for arr in mat: ws.append(arr) #-----------------------------------------------# for i in range(retry): try: wb.save(path_xlsx) return True except Exception as e: input("Please close Excel and press Enter.") return False def mat_iload(name='default',path_xlsx=_path_xlsx,n_col=None): """读取表格为 list<list> Args: name (str, optional): 工作簿名. Defaults to 'default'. path_xlsx (str, optional): Excel文件路径. Defaults to _path_xlsx. n_col (int, optional): 规范化列数. Defaults to None. Yields: list<list>: 表格内容 mat[行号][列号] """ wb=load_workbook(path_xlsx) if name not in wb: return ws = wb[name] for row in ws.rows: arr=[cell.value for cell in row[:n_col]] if n_col: arr+=[None for _ in range(n_col-len(arr))] yield arr def mat_load(name='default',path_xlsx=_path_xlsx,n_col=None): return list(mat_iload(name,path_xlsx,n_col)) def objs_save(name='default',objs=[{'id':1,'s':'F'},{'id':2,'s':'M'}],path_xlsx=_path_xlsx,retry=3): if objs==[]: return if type(objs)==list and type(objs[0]==dict): mat = [[]] for obj in objs: arr = [] for key in mat[0]: if key in obj.keys(): arr.append(obj[key]) else: arr.append('') for key in obj.keys(): if key not in mat[0]: mat[0].append(key) arr.append(obj[key]) mat.append(arr) mat_save(name,mat,path_xlsx,retry) def objs_iload(name='default',path_xlsx=_path_xlsx,headers=None,skip_none=True): """读取表格为 list<dict> Args: name (str, optional): 工作簿名. Defaults to 'default'. path_xlsx (str, optional): Excel文件路径. Defaults to _path_xlsx. headers (list[str], optional): 自定义表头，代替原表头作为键名. Defaults to None. skip_none (bool,optional): 是否跳过空行. Defaults to True Yields: list<dict>: 表格内容 objs[行号][表头] """ wb=load_workbook(path_xlsx) if name not in wb: return rows = wb[name].rows row0 = next(rows) if not headers: headers = [cell.value for cell in row0] n_col = len(headers) for row in rows: arr=[cell.value for cell in row[:n_col]] if all([not x for x in arr]):continue if n_col: arr+=[None for _ in range(n_col-len(arr))] yield {k:v for k,v in zip(headers,arr) if k!=None} def objs_load(name='default',path_xlsx=_path_xlsx,headers=None): return list(objs_iload(name,path_xlsx,headers)) def tab_load(name='default',path_xlsx=_path_xlsx,headers=None,i_key=0,s_key=None,skip_none=True): """读取表格为 dict<dict> Args: name (str, optional): 工作簿名. Defaults to 'default'. path_xlsx (str, optional): Excel文件路径. Defaults to _path_xlsx. headers (list[str], optional): 自定义表头，代替原表头作为键名. Defaults to None. i_key (int, optional): 主键列号. Defaults to 0. s_key (str, optional): 主键名（有的话以此为准）. Defaults to None. skip_none (bool,optional): 是否跳过空行. Defaults to True Returns: dict<dict>: 表格内容 tab[主键][表头] """ items = objs_iload(name,path_xlsx,headers,skip_none) item0 = next(items) if not headers: headers = list(item0.keys()) if not s_key : s_key = headers[i_key] tab={item0[s_key]:item0} for item in items: tab[item[s_key]]=item return tab if __name__=='__main__': table = [['','属性1','属性2','属性3'],['物品1'],['物品2'],['物品3']] mat_save('测试表1',table) for i,d in enumerate(mat_iload('测试表1',n_col=6)): print(i,d) print(mat_load('测试表1',n_col=6)) peoples = [ { "name":"张三", "age" :11, "role":"学生" }, { "name":"李四", "age" :22, "role":"教师" }, { "name":"王五", "age" :33, "role":"家长", }, ] objs_save("测试表2",peoples) for i,d in enumerate(objs_iload('测试表1',headers=[1,2,3,4,5,6])): print(i,d) ``` 标签: none 本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
评论已关闭