最近写软件要操作pandas 的dataframe数据,需要对list以及dataframe数据进行操控加入到EXCEL表中,以下代码可直接对list和dataframe数据操作对EXCEL进行写入,读写,追加
class MamangeFire:
def read_yaml(self,firepath):
'''
:return:yaml格式数据
'''
with open(firepath, mode="r", encoding="utf8") as f:
return yaml.safe_load(f)
def read_json(self,firepath):
'''
:return:json格式数据
'''
with open(firepath, mode="r", encoding="utf8") as f:
return json.load(f)
def write_json(self,firepath):
'''
:return:json格式数据
'''
JudgeFile = self.JudgeFile(firepath)
if JudgeFile and JudgeFile.setdefault('file')==1:
old_datas = self.read_json(firepath)
if isinstance(old_datas,dict):
datas = [old_datas,self.datas]
with open(firepath,mode='w+') as a:
a.writelines(json.dumps(datas,ensure_ascii=False,indent=4))
elif isinstance(old_datas,list):
old_datas.append(self.datas)
with open(firepath,mode='w+') as a:
a.writelines(json.dumps(old_datas,ensure_ascii=False,indent=4))
else:
with open(firepath, mode="w+") as a:
return a.write(json.dumps(self.datas,ensure_ascii=False,indent=4))
# 读取excel
def ReadExcel(self, firepath, sheet='Sheet1', ReadType='df'):
'''
df:Dataframe
list:列表
'''
wb = openpyxl.load_workbook(str(firepath))
sheet = wb[str(sheet)]
res1 = []
for value in sheet.values:
res = []
for i in value:
if i == None:
res.append(None)
else:
res.append(i)
res1.append(res)
if ReadType == 'df':
return pd.DataFrame(res1[1:], columns=res1[0])
elif ReadType == 'list':
return res1
def WriteExcel(self, datas, firepath, sheet='Sheet1',columes=None):
'''
datas:[[],[],[]] or [(),(),()]
'''
wb = xw.Workbook(filename=str(firepath))
sheet = wb.add_worksheet(name=str(sheet))
if isinstance(datas, list):
if columes and isinstance(columes,list):
sheet.write_row(0,columes)
for row, arrys in enumerate(datas):
row = row+1
for column, data in enumerate(arrys):
if isinstance(data,datetime):
sheet.write_datetime(row,column, data)
else:
sheet.write(row,column,data)
wb.close()
else:
for row, arrys in enumerate(datas):
for column, data in enumerate(arrys):
if isinstance(data,datetime):
sheet.write_datetime(row,column, data)
else:
sheet.write(row,column,data)
wb.close()
elif isinstance(datas, pd.DataFrame):
datas.to_excel(str(firepath))
def AddExcel(self, datas, firepath, sheet='Sheet1',columes=None):
JudgeFile = MamangeFire().JudgeFile(firepath)
if JudgeFile and JudgeFile.setdefault('file') == 1:
if isinstance(datas, pd.DataFrame):
old_df = pd.read_excel(firepath,sheet_name=sheet)
new_df = pd.concat([old_df,datas],axis=0,join='inner',ignore_index=True)
new_df.drop_duplicates(keep='last',inplace=True)
pd.DataFrame(new_df).to_excel(firepath)
elif isinstance(datas, list):
old_datas = self.ReadExcel(firepath=firepath, ReadType='list', sheet=sheet)
if columes:
old_datas.append(datas)
pd.DataFrame(old_datas,columns=columes).to_excel(firepath,sheet_name=sheet)
else:
old_datas.append(datas)
pd.DataFrame(old_datas).to_excel(firepath, sheet_name=sheet)
if JudgeFile==None:
self.WriteExcel(datas, firepath,sheet=sheet,columes=columes)
def JudgeFile(self,firepath):
"""
:param : fire 文件 dir 文件夹
"""
try:
if os.path.isdir(str(firepath)):
return {'dir':1}
elif os.path.isfile(str(firepath)):
return {'file':1}
except Exception as e:
print(e)
return None