用Python 进行Excel 文件的整理大致分为这几个流程: 读取-整理-写入
有时写入的时候需要按照规定的格式填入指定的位置,这时可以选择整理的时候就按照指定的位置进行整理,方便写入。
首先是模块选择,这里我选择了openpyxl , Pandas 模块。Openpyxl用于操作Excel写入可以说是相当方便,而Pandas用于整理数据
也是不错的选择。
对于数据源并不是很规范的时候,整理的过程耗时比较长。
下面开始我们的示例,这是我们单位工作时用到的表,表数据其实是非常不规范的。
以下两张表可以视为两个报告表,并不是很好的数据源。所以整理数据源就是第一大块工作了。
这是src表,包括了pn列和日期列
这是tg表,有pn列 model列,部分日期列
========================这是分割线================================
1. 文件读取 :
Openpyxl : wb = load_workbook(‘url’) 除了url以外还有几个参数可供选择 : read_only[大文件时启用] ; data_only[公式or 值]
Wb.sheetnames – 获取sheet名
Wb[‘sheetname’] – 获取 sheet
sheet[‘A1’].value || sheet.cell(row=1,column=1).value – 获取指定单元格值
sheet.max_row , sheet.max_column - 获取行列数
sheet[‘A1’: ‘C3’] – 获取指定区域
Pandas : src = pd.read_excel(‘url’,sheet_name=’’,header=0) 以外还有几个参数: sheet_name[可以写序号,可以选多张] ; index_col[设置为索引列] (此方法读取出的数据为DataFrame格式)
Src[‘column’] – 读取指定列数据
import pandas as pd
from openpyxl import load_workbook
# 读取文件
src = pd.DataFrame(pd.read_excel('/Users/Documents/Code/Python/excel/dataSource.xlsx',sheet_name=0,header=2))
tg = pd.DataFrame(pd.read_excel('/Users/Documents/Code/Python/excel/report.xlsx',sheet_name=0,header=3))
tg_wb = load_workbook('/Users/Documents/Code/Python/excel/report.xlsx',data_only=True)
src_wb = load_workbook('/Users/Documents/Code/Python/excel/dataSource.xlsx',data_only=True)
tg_sheet = tg_wb['Sheet1']
src_sheet = src_wb['Sheet1']
=========================这是分割线==========================
2. 数据整理
定义索引:使用Pandas读取的数据可以定义序列 src.set_index(‘index’)
# 设置索引
df_src = src.set_index('pn')
对于序列不规范情况,找出其规律比如分组
# src_pn集合
src_pnList = []
for pn in src['pn']:
src_pnList.append(pn)
筛选: 确定一个基准列后,就可以进行筛选。筛选一般用loc , iloc 比较多 loc[1,’column’] , 对于序列化的DataFrame 可直接用loc[‘row’,’column’] ; iloc一般为iloc[1,1]
遍历:整理时一般使用 tg = update(src) 比较方便,但前提是序列一致。不一致时我们需要自己重写。
特殊情况处理:待写入数据为nan时,无法写入新数据,需要先判断用pd.isna(),然后可以赋值为0
for i in range(0,tg.shape[0]):
# 如果model 是 1
if tg.loc[i,'model'] == 1:
# 如果有一个元素
if len(tg.loc[i,'pn']) == 1:
for j in range(3,tg.shape[1]+1):
# 写入
tg.loc[i,tg.columns[j-1]] = df_src.loc[tg.loc[i,'pn'],tg.columns[j-1]]
# 如果有多个元素
else:
# 拆分成数组
pn_list = (tg.loc[i,'pn']).split('/')
# 遍历元素组
for n in pn_list:
# 如果在数据源内存在
if n in src_pnList:
# 遍历目标
for k in range(3,tg.shape[1]+1):
# 如果目标单元格是空
if pd.isna(tg.loc[i,tg.columns[k-1]]):
tg.loc[i,tg.columns[k-1]] = 0
# 累积数字
tg.loc[i,tg.columns[k-1]] += df_src.loc[n,tg.columns[k-1]]
至此 数据的整理就差不多了
别忘记给整理的数据添加一个索引,并且确定唯一值
# 添加整理好的索引 - 确认待写入的唯一值
df_tg = tg[tg['model'] == 1].set_index('pn')
=====================我是分割线==========================
直接写入:由于数据整理时依照待写入数据的格式整理,故写入过程比较简单。可直接使用cell(row=1,column=1).value进行
最后别忘记wb.save(‘url’)
# df_tg pn 集合
df_tg_pnList = []
for df_tg_pn in df_tg.index:
df_tg_pnList.append(df_tg_pn)
# 遍历循环写入
for i in range(5, tg_sheet.max_row + 1):
# 获取目标源的pn
tg_pn = tg_sheet.cell(row=i,column=1).value
# 如果目标源pn在DataFrame内,并且model==1
if (tg_pn in df_tg_pnList) & (tg_sheet.cell(row=i,column=2).value == 1):
# 遍历日期相关所有列
for j in range(3, tg_sheet.max_column + 1):
# 赋值
tg_sheet.cell(row=i,column=j).value = df_tg.loc[tg_pn,df_tg.columns[j-2]]
# 写入excel
tg_wb.save('/Users/zean/Documents/Code/Python/excel/reportNew.xlsx')
=========================大功告成==========================