使用pandas处理excel,并使用Openpyxl修改单元格格式

使用pandas处理excel,并使用Openpyxl修改单元格格式

起因:在某团实习,每天要处理格式固定的excel数据,觉得有点烦,尝试下能不能写个自动的程序,所以花了将近两天时间,从0到1基本完成了对excel的处理(纯谷歌)。
下边简要介绍不同需求的处理,以及中间遇到的问题。

需求

在这里插入图片描述

数据大致如图,图片是模拟原数据生成的一个测试样例,只需要关注行索引即可,注意这里的’A’是第一行的名称,是行索引,不是最上方的ABCD行号。其中的数据也是随机生成的。需要筛选出部分数据,并根据某列计算出新的指标,写入新的sheet,最后对sheet添加标题栏,说明包含日期,sheet名称。

处理基本流程

  1. 利用pandas读取表格数据
  2. 根据某几行,生成新的指标,将新的指标添加到表格中
  3. 筛选出需要的列
  4. 添加标题栏,设置部分单元格格式

查询资料过程

声明:本文中涉及到读取,保存数据得操作均使用excel,不涉及csv,不过这两者应该是共通的,且处理csv的函数应该比excel的更加智能。所有的列索引均不涉及到实际数据,(ps:就是有的忘了改)
**工具:**最初使用jupyter,因为方便查看中间过程,方便调试,最后使用pycharm一键处理。

1.读取数据,

参数包括excel路径,sheet页面名称,其中df为DataFrame格式

`df = pd.read_excel(path_data,sheet_name=sheet_original)`

2.指标的计算:计算较为简单,新指标由某几列的四则运算生成,放代码:

df['A'] = (df['B'] / df['C'])
df['D'] =((df['A'] - df['C']) / df['A'])

其中引号中间的为列名,新生成的数据会自动插入到df中。
其中数据格式需要设置:保留小数点,设置为百分数,保留小数点较为简单,代码如下:

df['A'] = df['A'].round(decimals=2)
df['B'] = df['B'].round(decimals=2)

百分比的功能最开始有点问题,代码如下:

df['A'] = df['A'].map(lambda x : format(x,'.2%'))

但是这个代码在excel中生成的百分数是文本格式,左上角会有个绿色的三角,很难受,而且百分数不能直接从文本转成数值形式,本来一开始都打算在excel里边手调百分比,后来在openpyxl中找到了解决办法,这个之后再说。

3.筛选需要的列:新的df列索引的顺序会按照代码中顺序生成

df1 = df[['A','B','C']]

根据某项指标排序并选择最大的n行
ascending是决定升序(True)降序(False)。
还有可以将某些索引提取出来,放在最前方,然后观测部分列的功能,涉及到’index’,'values’等关键字,这些很容易搜到,就不做介绍了

#将原数据排序
df1.sort_values(ascending=False,by = 'A').head(n)
#拷贝一份新的数据
df2 = df1.sort_values(ascending=False,by = 'A').head(n)

4.如何保存dataframe到新的sheet

这里遇到了第一个难点:怎么在保留原数据的基础上,保存多个sheet。
搜索到的都会覆盖原数据,有的为了避免覆盖,会先将原数据保存,再保存新数据,但是这样在需要保存多个新数据时,仍然只能保存一个,代码如下,真保存操作需要设置engine = ‘openpyxl’,mode=‘a’,即添加模式。这里如果不设置engine的话,是无法使用mode=‘a’,但是这里后来又埋了坑,一些xlsxwrite的功能没法使用,但是xlsxwrite又没有添加模式,太难了。

#保存操作 会覆盖原数据,去除索引
df2.to_excel('测试.xlsx',sheet_name = 'topn',index = False)

#保存操作,仍然是覆盖,只不过是原页又保存了一遍
with pd.ExcelWriter('2.xlsx') as writer:
     df.to_excel(writer, sheet_name='data')
     df.to_excel(writer, sheet_name='data2')
     
#保存操作,不覆盖(真)
with pd.ExcelWriter(path_data, engine='openpyxl', mode='a') as writer:
    df2.to_excel(writer,sheet_name=sheet_name1,index=False)
    df3.to_excel(writer, sheet_name=sheet_name2, index=False)

样式调整,设置字体、颜色、标题栏

基本流程之后,实现的结果如下所示:
在这里插入图片描述

数据是按照某项指标从大到小排序。

和最终结果只有一步之遥了。
最终结果如上所示,可以看出,有以下几个区别
在这里插入图片描述

  1. 最上方有日期,说明 等标题栏,并且背景填充,加粗。
  2. 序号列(这个最初保存的时候就有,我给它取消了),但是原本保存的序号是从0开始,这里需要改为从1开始。
  3. 列索引一行背景填充
  4. 格式的设置:字体微软雅黑,左对齐。

下边开始最后的完善
该部分中间找了很久,就不放中间的改进过程了,直接一步到位:序号对应上边的区别序号

1.插入标题和日期

df2.to_excel(writer,sheet_name=sheet_1,index=True,header=True, startrow=2)
关键在于后边的几个关键词,大家可以从字面意思理解:index,保存索引,startrow是开始的行,header会决定列索引那行会不会变为序号的一部分,大家可以试着修改这些,查看不同之处。
最好去看一下函数中还有什么参数,比如这个startrow,我最开始在想如何在行索引上边插入东西的时候走了很多弯路,后来发现有这么个参数,豁然开朗。
下方为在最上方插入日期和标题,today是调用函数得到当前日期,3行一列插入‘序号’,

ws.cell(row=1, column=1, value='日期')
        ws.cell(row=1, column=2, value=today)
        ws.cell(row=1, column=3, value=sheet_1+'产品')
        ws.cell(row = 3,column=1,value='序号')

2.排序后索引会乱,所以这里需要索引重新开始,并从1开始

#索引重新排序,并从1开始
df2.reset_index(drop=True, inplace=True)
df2.index = np.arange(1,len(df2)+1)

3和4放在一起,都是使用openpyxl设置格式

最初找格式设置的时候走了不少弯路,styler等设置,但是最后发现openpyxl最方便
放上网址

https://zhuanlan.zhihu.com/p/261962080

上述网址包含了对字体,填空,对齐方式等设置,但是存在一个缺点,很多都是对单个单元格进行设置
体。无法对某片区域设置,
在某篇博客找到了部分区域设置的方法:https://blog.csdn.net/chaodaibing/article/details/108823878
这里是自定义了一个函数,对某片区域遍历操作。
因为我的格式设置比较单一,删除了一部分边框设置,另外设置了,填充颜色的设置。代码比较简单,大家可以根据自己的需要自行修改代码。其中百分数的设置就在这里!!,也不会在显示文本格式了!!拿捏!几位百分比都可以改写,函数中包含了设置黑体,单一对齐方式等功能,如果大家的需求比较复杂,比如要设置不同的对齐方式,除了黑体还需要其他的,都可以修改该函数,修改难度较小。
可以将参数传递到Font,Alignment等函数中。
博客中还有设置单元格宽度的函数,本文中没有用到(emmm其实是手动设置行宽)。

def set_cells(cells,type,color=000000):
  aligncenter=Alignment(horizontal='center',vertical='center')  #居中

  # border=Border(left=sidestyle,right=sidestyle,top=sidestyple,bottom=sidestyle)
  for i in cells:
    for j in i:
        if(type=='percent'):
            j.number_format='0.00%'
        elif(type=='bold'):
            j.style='Pandas'
        elif(type=='center'):
            j.alignment=aligncenter
        elif(type=='color'):
      	    j.font = Font(color=color)  #solid为样式
        elif(type=='fill'):
            j.fill = PatternFill(patternType="solid", start_color=color)  # 纯色填充

最后附上全文代码,如果有错误请大家指教,会及时改正的,代码写的比较烂,大家不要介意。由于实习原因,里边的列索引我都把改成了ABCD,数量可能和图文的图片对应不上,大家只要改成自己的数据就可以了,环境的安装也比较简单,就不赘述了。

所有的列索引名称都是用ABCD代替

# 导入必要的包
import pandas as pd
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment
import datetime
import numpy as np


def set_cells(cells, type, color=000000):
    aligncenter = Alignment(horizontal='left', vertical='center')  # 居中

    # border=Border(left=sidestyle,right=sidestyle,top=sidestyple,bottom=sidestyle)
    for i in cells:
        for j in i:
            j.font=Font(name = u'微软雅黑')
            if (type == 'percent'):
                j.number_format = '0%'
            elif (type == 'bold'):
                j.style = 'Pandas'
            elif (type == 'left'):
                j.alignment = aligncenter
            elif (type == 'color'):
                j.font = Font(color=color)  # solid为样式
            elif (type == 'fill'):
                j.fill = PatternFill(patternType="solid", start_color=color)  # 纯色填充


def main():
    df = pd.read_excel(path_data, sheet_name=sheet_original)

    # 数据处理生成
    df['A'] = df['B'] / df['C']
    df['D'] = df['E'] / df['F)']
    df['X'] = df['A'] / df['B']
    df['Y'] = (df['A'] - df['B']) / df['A']

    # 保留两位小数
    df['A'] = df['A'].round(decimals=2)
    df['A'] = df['B'].round(decimals=2)

    # 筛选需要的表格,excel的列会按照这个顺序生成

    df1 = df[['A', 'B', 'C', ...]]
    # 重新对序号排序,索引从1开始
    df2 = df1.sort_values(ascending=False, by='A').head(50)
    df2.reset_index(drop=True, inplace=True)
    df2.index = np.arange(1,len(df2)+1)
    df3 = df1.sort_values(ascending=False, by='B').head(50)
    df3.reset_index(drop=True, inplace=True)

    # 保存操作,不覆盖
    with pd.ExcelWriter(path_data, engine='openpyxl', mode='a') as writer:
        df2.to_excel(writer, sheet_name=sheet_1, index=True, header=True, startrow=2)
        ws = writer.sheets[sheet_1]

        # 插入标题:日期、说明
        ws.cell(row=1, column=1, value='日期')
        ws.cell(row=1, column=2, value=today)
        ws.cell(row=1, column=3, value=sheet_2 + '产品')
        ws.cell(row=3, column=1, value='序号')

        # 设置字体与单元格格式
        set_cells(ws['A1:C1'], 'bold')
        set_cells(ws['A1:M53'], 'left')
        set_cells(ws['A3:O3'], 'fill', 'FF8C00')
        set_cells(ws['C1:C1'], 'fill', 'FFFF00')

        # 设置为百分数
        set_cells(ws['L4:L53'], 'percent')
        set_cells(ws['O4:O53'], 'percent')

        
        df3.to_excel(writer, sheet_name=sheet_2, index=True,header=True,startrow=2)
        ws = writer.sheets[sheet_2]
        ws.cell(row=1, column=1, value='日期')
        ws.cell(row=1, column=2, value=today)
        ws.cell(row=1, column=3, value=sheet_2 + '产品')
        ws.cell(row=3, column=1, value='序号')

        # 设置字体与单元格格式
        set_cells(ws['A1:C1'], 'bold')
        set_cells(ws['A1:M53'], 'left')
        set_cells(ws['A3:O3'], 'fill', 'FF8C00')
        set_cells(ws['C1:C1'], 'fill', 'FFFF00')

        # 设置为百分数
        set_cells(ws['L4:L53'], 'percent')
        set_cells(ws['O4:O53'], 'percent')


if __name__ == "__main__":
        # 数据源路径  页面名称
        path_data = r'xxx.xlsx'
        sheet_original = r'sheet_name'
        today = str(datetime.date.today())
        # 保存页面名称
        sheet_1 = '1TOPn'
        sheet_2 = '2TOPn'

        main()
参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

weixin_42523992

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值