Pandas实现自定义Excel格式并导出多个sheet表

目录

• 前言
• 1.导入pandas及数据
• 2.修改pd.to_excle方法
• 3.构建保存方法save
• 4.创建pd.ExcelWriter实例
• 5.创建多个sheet表
• 6.设置自定义列宽
• 7.设置列不同颜色
• 8.设置首行格式
• 9.可以设置隐藏不显示列 
• 10.完整代码
• 总结

前言

pandas 默认整合XlsxWriter驱动，自动化处理excel操作，提供公式、设置单元格格式、可视化分析图片等操作

XlsxWriter官方使用说明：

XlsxWriter Documentation

1.导入pandas及数据

import pandas as pd
df =  pd.read_excel(r"C:\Users\xxx\xxx\XLSX 工作表.xlsx")

2.修改pd.to_excle方法

定义类writer：

class writer():
    def __init__(self,df,sheet_name,key,path):  #设置对象参数
        self.df = df                 #dataframe数据（列表）
        self.sheet_name = sheet_name #sheet名称（列表）
        self.key = key               #需要上色字段（列表）
        self.path = path             #excel保存地址

3.构建保存方法save

    def save(self):
        #设置保存地址，默认桌面
        path = "C:/Users/xxx/Desktop/{}{}（导出时间{}）.xlsx".format(pd.datetime.now().strftime("%y%m%d"),self.path,pd.datetime.now().strftime("%H时%M分%S秒"))

4.创建pd.ExcelWriter实例

writer = pd.ExcelWriter(path,engine='xlsxwriter') #创建pandas.ExcelWriter实例，赋值给writer

5.创建多个sheet表

        for j in range(len(self.sheet_name)):  #遍历sheet名称列表
            #创建sheet表并写入dataframe数据
            self.df[j].to_excel(writer, sheet_name=self.sheet_name[j], index=False #不写入索引index=False
                                ,freeze_panes=(1,2)) #设置固定1、2列（固定列无法左右移动）
            workbook = writer.book        #工作蒲方法
            worksheet = writer.sheets[self.sheet_name[j]] #读取sheet表

6.设置自定义列宽

            #  计算每列的合适字符宽度，放到元组中（可以设置最大值）
            widths = (self.df[j].astype(str).applymap(lambda x: 40 if len(x)>61 else len(x)+6 if len(x)<8 else len(x)).agg(max).values)
            #设置每列宽度大小
            [worksheet.set_column(i, i, width) for i, width in enumerate(widths)] #计算的宽度，设置列宽

7.设置列不同颜色

设置颜色列表，给不同列设计喜欢的颜色

          color = ['#03A89E','#00C78C','#FFFFCD','#FFC0CB','#808A87','#FFE384','#ED9121','#40E0D0','#FFFFCD']*5 #颜色列表

            [worksheet.set_column(i,i,widths[i],workbook.add_format({'fg_color': color[i-11],

                            &phpnbsp;                           'valign': 'vcenter',# 垂直对齐方式

                                                        'font_size': 10, #字体大小

           javascript;                                             'border': 4, #单元格边框宽度

                                                        'align': 'left' # 水平对齐方式

                                                                    })) for 

      &nbs开发者_Go教程p;     i,x in enumerate(self.df[j].columns) if self.df[j].columns[i] in self.key[0]]

8.设置首行格式

            #设置首行宽度 
            worksheet.set_row(0,20,workbook.add_format({'fg_color':'#40E0D0'#背景颜色
                                                        ,'bold': True,#字体加粗
                                                        'valign': 'vcenter',# 垂直对齐方式
                                                        'font_size': 16, #字体大小
                                                        'border': 10, #单元格边框宽度
                                                        'align': 'left' # 水平对齐方式
                                                       }))
        writer.save()
        writer.close()

9.可以设置隐藏不显示列 

worksheet.set_column("A:B",None,None,{'hidden':1})  #A至B列，隐藏

调用方法：

writer1 = writer(df=[数据列表],path='文件名称（保存到桌面）',key=[上色字段列表],sheet_name=['sheet表名列表'])
writer1.save()

10.完整代码

class writer():
    def __init__(self,df,sheet_name,key,path):
        self.df = df                 #dataframe数据（列表）
        self.sheet_name = sheet_name #sheet名称（列表）
        self.key = key               #需要上色字段（列表）
        self.path = path             #excel保存地址
    def save(self):
        #设置保存地址，默认桌面
        path = "C:/Users/xxx/Desktop/{}{}（导出时间{}）.xlsx".format(pd.datetime.now().strftime("%y%m%d"),self.path,pd.datetime.now().strftime("%H时%M分%S秒"))
       php #创建pandas.ExcelWriter实例
        writer = pd.ExcelWriter(path,engine='xlsxwriter') #创建pandas.ExcelWriter实例，赋值给writer
        for j in range(len(self.sheet_name)):  #遍历sheet名称列表
            #创建sheet表并写入dataframe数据
            self.df[j].to_excel(writer, sheet_name=self.sheet_name[j], index=False #不写入索引index=False
                                ,freeze_panes=(1,2)) #设置固定1、2列（固定列无法左右移动）
            workbook = writer.book        #工作蒲格式方法
            worksheet = writer.sheets[self.sheet_name[j]] #读取sheet表
            #  计算每列的合适字符宽度，放到元组中
            widths = (self.df[j].astype(str).applymap(lambda x: 40 if len(x)>61 else len(x)+6 if len(x)<8 else len(x)).agg(max).values)
            #设置每列宽度大小
            [worksheet.set_column(i, i, width) for i, width in enumerate(widths)] #计算的宽度，设置列宽
            color = ['#03A89E','#00C78C','#FFFFCD','#FFC0CB','#808A87','#FFE384','#ED9121','#40E0D0','#FFFFCD']*5 #颜色列表
            [worksheet.set_column(i,i,widths[i],workbook.add_format({'fg_color': color[i-11],
                                                        'valign': 'vcenter',# 垂直对齐方式
                                                        'font_size': 10, #字体大小
                                                        'border': 4, #单元格边框宽度
                                                        'align': 'left' # 水平对齐方式
                                                                    })) for 
            i,x in enumerate(self.df[j].columns) if self.df[j].columns[i] in self.key[0]]
            #设置首行宽度 
            worksheet.set_row(0,20,workbook.add_format({'fg_color':'#40E0D0'#背景颜色
                                                        ,'bold': True,#字体编程客栈加粗
                                                        'valign': 'vcenter',# 垂直对齐方式
                                                    编程客栈    'font_size': 16, #字体大小
                                                        'border': 10, #单元格边框宽度
                                                        'align': 'left' # 水平对齐方式
                                                       }))
#         worksheet.set_column("A:B",None,None,{'hidden':1})  #A至B列，隐藏
        writer.save()
        writer.close()
writer1 = writer(df=[df],path='フィロソフィー',key=[df],sheet_name=['sheet表名'])
writer1.save()

 输出格式（如图）：

文件名：230121フィロソフィー（导出时间21时53分49秒）.xlsx

输出多个sheet表时，改为：

writer1 = writer(df=[df1,df2],path='文件名称（路径修改path）',
key=[df.columns,df2.columns],
sheet_name=['sheet表名1','sheet表名2'])
writer1.save()

总结

坑：pandas.ExcelWriter默认首行颜色格式无法修改，需要删除首行后重新写入首行

补充：除了xlsxwriter驱动外，还可以设置pandas.ExcelWriter的其他驱动，如：openxlsx驱动，区别：openxlsx支持对已有文件进行读写，Xlsxwriter只支持重新写入，Xlsxwriter速度更快和excel操作更丰富

到此这篇关于Pandas实现自定义Excel格式并导出多个sheet表的文章就介绍到这了,更多相关Pandas自定义Excel格式内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！