利用pandas 操作EXCEL

最近写软件要操作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