python实践：excel公式填充+筛选排序

工作中用到python处理excel。目前的水平还是个裁缝，记录一下。

一 需求

有一份excel统计数据A会定期生成，A中包含多个sheet，每个sheet的表头跟数据均不一致。需要对每个sheet进行以下加工处理：

1. 在A中新增一列【所属】
2. 根据已有匹配表，通过excel公式自动匹配填充【所属】列。
3. 根据特殊值进行数据清洗
4. 对【所属】列进行筛选并排序，生成多个excel文件。

二 问题

先说说遇到的问题。

1. excel处理常用的库就是pandas跟openpyxl。这次处理的过程基本上都是用库名+功能点查找相应资料。总的来看，openpyxl更偏向于对excel文件本身的操作指令，能够很大程度保留源文件的格式等内容；pandas实际上是把数据读取到framedata中再对其进行各种灵活操作。我原本设想尽可能用一个库解决所有问题，最后发现搞不定，还是得综合使用。
2. 除了上面两个常用的库，在研究公式填充的时候也试了一下其他库，比如xlwings也是可以实现的。

3. 填充公式那一步遇到一个坎，由于我的公式涉及到与其他本地文件的引用匹配，excel默认不会自动带出公式结果，尝试各种设置以及用代码打开再保存也没生效，所以最后决定拆成两步执行，中间手动打开一次excel把公式结果带出来，再执行第二步。
   最后整体思路是：

   
   
   image.png

三 代码实现

step1

# step1 

from openpyxl import load_workbook
from openpyxl.styles import PatternFill  # 导入填充模块
from openpyxl.styles import Alignment


oriexcelname = r'C:\Users\admin\Desktop\A.xlsx'
newexcelname = r'C:\Users\admin\Desktop\B.xlsx'


wb = load_workbook(oriexcelname)
print (wb.sheetnames)

## SHEET1
st1 = wb['SHEET1']
st1.insert_cols(1,1)#给原第1列前面插入1列
st1["A1"] = "所属" #表头赋值
excel_formula = r"=INDEX('C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$A:$A,MATCH(C2,'C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$B:$B,0))" #给第一个单元格赋值公式

#根据行数，填充剩余公式
max_row1=st1.max_row
print(max_row1)

for row in range(2,max_row1+1):
    cell=st1.cell(row=row,column=1)
    cell.value=excel_formula
    excel_formula=excel_formula.replace(f'C{row}',f'C{row+1}')

## SHEET2
st2 = wb['SHEET2']
st2.insert_cols(1,1)#给原第1列前面插入1列
st2["A1"] = "所属"
excel_formula = r"=INDEX('C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$A:$A,MATCH(B2,'C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$B:$B,0))"

max_row2=st2.max_row
print(max_row2)

for row in range(2,max_row2+1):
    cell=st2.cell(row=row,column=1)
    cell.value=excel_formula
    excel_formula=excel_formula.replace(f'B{row}',f'B{row+1}')


## SHEET3
st3 = wb['SHEET3']
st3.insert_cols(1,1)#给原第1列前面插入1列
st3["A1"] = "所属"
excel_formula = r"=INDEX('C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$A:$A,MATCH(C2,'C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$B:$B,0))"

max_row3=st3.max_row
print(max_row3)

for row in range(2,max_row3+1):
    cell=st3.cell(row=row,column=1)
    cell.value=excel_formula
    excel_formula=excel_formula.replace(f'C{row}',f'C{row+1}')

## SHEET4
st4 = wb['SHEET4']
st4.insert_cols(1,1)#给原第1列前面插入1列
st4["A1"] = "所属"
excel_formula = r"=INDEX('C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$A:$A,MATCH(B2,'C:\Users\admin\Desktop\[匹配表.xlsx]匹配表'!$B:$B,0))"

max_row4=st4.max_row
print(max_row4)

for row in range(2,max_row4+1):
    cell=st4.cell(row=row,column=1)
    cell.value=excel_formula
    excel_formula=excel_formula.replace(f'B{row}',f'B{row+1}')


wb.save(newexcelname)#保存修改到新excel
wb.close()  # 记住读取excell表格时一定要关闭，不然容易出现报错

step2

# step2

import numpy as np
import pandas as pd
import openpyxl

oriexcelname = r'C:\Users\admin\Desktop\B.xlsx'
newexcelname1 = r'C:\Users\admin\Desktop\C.xlsx'
newexcelname2 = r'C:\Users\admin\Desktop\D.xlsx'


df=pd.DataFrame() #构造原始数据文件
df.to_excel(newexcelname1)
df.to_excel(newexcelname2)



def exfilter(sheetname):
    # 读文件
    df=pd.read_excel(oriexcelname,sheet_name=sheetname)
    # 数据清洗
    # 1.有3个非空值的数据才可以保留下来的行数据。
    df=df.dropna(axis=0, thresh=3, subset=None, inplace=False)
    print(df)
    # 2.特殊值清洗
    if '联系人' in df.columns:
        df.所属[(df.联系人 =='XX')]= 'YY'    
        
    # 筛选    
    df1 = df[(df.所属 != '其他')]
    # 排序
    df1 = df1.sort_values(['所属','名称'],ascending=[True,True])
    # 数据写入到excel
    writer = pd.ExcelWriter(newexcelname1, mode='a',engine='openpyxl')
    df1.to_excel(writer,sheet_name=sheetname,index=False)
    writer.close()

    # 筛选
    df2 = df[(df.所属 == '其他')]
    # 排序
    df2 = df1.sort_values(['所属','名称'],ascending=[True,True])  
    # 数据写入到excel
    writer = pd.ExcelWriter(newexcelname2, mode='a',engine='openpyxl')
    df2.to_excel(writer,sheet_name=sheetname,index=False)
    writer.close()


#用openpyxl删除多余sheet
def exdrop(newexcelname,sheetname):
    # 载入工作簿
    workbook = openpyxl.load_workbook(newexcelname)
    # 删除目标Sheet
    worksheet = workbook[sheetname]
    workbook.remove(worksheet)
    # 保存已做删除处理的工作簿
    workbook.save(newexcelname)


exfilter('SHEET1')
exfilter('SHEET2')
exfilter('SHEET3')
exfilter('SHEET4')


exdrop(newexcelname1,'Sheet1')
exdrop(newexcelname2,'Sheet1')