工作中用到python處理excel凿掂。目前的水平還是個裁縫是牢，記錄一下灯荧。

一 需求

有一份excel統(tǒng)計數(shù)據(jù)A會定期生成礁击，A中包含多個sheet，每個sheet的表頭跟數(shù)據(jù)均不一致逗载。需要對每個sheet進行以下加工處理：

1. 在A中新增一列【所屬】
2. 根據(jù)已有匹配表哆窿，通過excel公式自動匹配填充【所屬】列。
3. 根據(jù)特殊值進行數(shù)據(jù)清洗
4. 對【所屬】列進行篩選并排序厉斟，生成多個excel文件挚躯。

二 問題

先說說遇到的問題。

1. excel處理常用的庫就是pandas跟openpyxl擦秽。這次處理的過程基本上都是用庫名+功能點查找相應(yīng)資料码荔。總的來看感挥，openpyxl更偏向于對excel文件本身的操作指令缩搅，能夠很大程度保留源文件的格式等內(nèi)容；pandas實際上是把數(shù)據(jù)讀取到framedata中再對其進行各種靈活操作触幼。我原本設(shè)想盡可能用一個庫解決所有問題硼瓣，最后發(fā)現(xiàn)搞不定，還是得綜合使用置谦。
2. 除了上面兩個常用的庫巨双，在研究公式填充的時候也試了一下其他庫噪猾，比如xlwings也是可以實現(xiàn)的。

3. 填充公式那一步遇到一個坎筑累，由于我的公式涉及到與其他本地文件的引用匹配，excel默認不會自動帶出公式結(jié)果丝蹭，嘗試各種設(shè)置以及用代碼打開再保存也沒生效慢宗，所以最后決定拆成兩步執(zhí)行，中間手動打開一次excel把公式結(jié)果帶出來奔穿，再執(zhí)行第二步镜沽。
   最后整體思路是：

   
   
   image.png

三 代碼實現(xiàn)

step1

# step1 

from openpyxl import load_workbook
from openpyxl.styles import PatternFill  # 導(dǎo)入填充模塊
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))" #給第一個單元格賦值公式

#根據(jù)行數(shù)，填充剩余公式
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表格時一定要關(guān)閉贱田，不然容易出現(xiàn)報錯

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() #構(gòu)造原始數(shù)據(jù)文件
df.to_excel(newexcelname1)
df.to_excel(newexcelname2)



def exfilter(sheetname):
    # 讀文件
    df=pd.read_excel(oriexcelname,sheet_name=sheetname)
    # 數(shù)據(jù)清洗
    # 1.有3個非空值的數(shù)據(jù)才可以保留下來的行數(shù)據(jù)缅茉。
    df=df.dropna(axis=0, thresh=3, subset=None, inplace=False)
    print(df)
    # 2.特殊值清洗
    if '聯(lián)系人' in df.columns:
        df.所屬[(df.聯(lián)系人 =='XX')]= 'YY'    
        
    # 篩選    
    df1 = df[(df.所屬 != '其他')]
    # 排序
    df1 = df1.sort_values(['所屬','名稱'],ascending=[True,True])
    # 數(shù)據(jù)寫入到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])  
    # 數(shù)據(jù)寫入到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')