使用pandas处理excel，并使用Openpyxl修改单元格格式
使用pandas处理excel，并使用Openpyxl修改单元格格式
起因：在某团实习，每天要处理格式固定的excel数据，觉得有点烦，尝试下能不能写个自动的程序，所以花了将近两天时间，从0到1基本完成了对excel的处理（纯谷歌）。
下边简要介绍不同需求的处理，以及中间遇到的问题。
需求
数据大致如图，图片是模拟原数据生成的一个测试样例，只需要关注行索引即可，注意这里的’A’是第一行的名称，是行索引，不是最上方的ABCD行号。其中的数据也是随机生成的。需要筛选出部分数据，并根据某列计算出新的指标，写入新的sheet，最后对sheet添加标题栏，说明包含日期，sheet名称。
处理基本流程
- 利用pandas读取表格数据
- 根据某几行，生成新的指标，将新的指标添加到表格中
- 筛选出需要的列
- 添加标题栏，设置部分单元格格式
查询资料过程
声明：本文中涉及到读取，保存数据得操作均使用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)
样式调整，设置字体、颜色、标题栏
基本流程之后，实现的结果如下所示：
数据是按照某项指标从大到小排序。
和最终结果只有一步之遥了。
最终结果如上所示，可以看出，有以下几个区别
- 最上方有日期，说明 等标题栏，并且背景填充，加粗。
- 序号列（这个最初保存的时候就有，我给它取消了），但是原本保存的序号是从0开始，这里需要改为从1开始。
- 列索引一行背景填充
- 格式的设置：字体微软雅黑，左对齐。
下边开始最后的完善
该部分中间找了很久，就不放中间的改进过程了，直接一步到位：序号对应上边的区别序号
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()
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
