[Python] 用Python处理Excel(一)

用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')

=========================大功告成==========================