利用Python解決Excel問(wèn)題的最佳方案總結(jié)
「問(wèn)題說(shuō)明」
這次要處理的excel有兩個(gè)sheet，要根據(jù)其中一個(gè)sheet的數(shù)據(jù)來(lái)計(jì)算另外一個(gè)sheet的值。造成問(wèn)題的點(diǎn)在于，要計(jì)算值的sheet里不僅僅有數(shù)值，還有公式。我們來(lái)看一下：
如上圖所示，這個(gè)excel一共有兩個(gè)sheet:CP和DS，我們要按照一定的業(yè)務(wù)規(guī)則，根據(jù)CP中的數(shù)據(jù)計(jì)算DS對(duì)應(yīng)單元格的數(shù)據(jù)。圖中藍(lán)色方框框出來(lái)的是帶公式的，而其他區(qū)域是數(shù)值。
我們來(lái)看看，如果我們按照之前說(shuō)的處理邏輯，把excel一次性批量讀取到dataframe處理，然后再一次性批量寫(xiě)回去有啥問(wèn)題。這部分代碼如下：
import pandas as pd
import xlwings as xw
#要處理的文件路徑
fpath = "data/DS_format.xlsm"
#把CP和DS兩個(gè)sheet的數(shù)據(jù)分別讀入pandas的dataframe
cp_df = pd.read_excel(fpath,sheet_name="CP",header=[0])
ds_df = pd.read_excel(fpath,sheet_name="DS",header=[0,1])
#計(jì)算過(guò)程省略......
#保存結(jié)果到excel
app = xw.App(visible=False,add_book=False)
ds_format_workbook = app.books.open(fpath)
ds_worksheet = ds_format_workbook.sheets["DS"]
ds_worksheet.range("A1").expand().options(index=False).value = ds_df
ds_format_workbook.save()
ds_format_workbook.close()
app.quit()如上代碼存在的問(wèn)題在于，pd.read_excel()方法從excel里讀取數(shù)據(jù)到dataframe的時(shí)候，對(duì)于有公式的單元格，會(huì)直接讀取公式計(jì)算的結(jié)果（如果沒(méi)有結(jié)果則返回Nan），而我們寫(xiě)入excel的時(shí)候是直接把dataframe一次性批量寫(xiě)回的，這樣之前帶公式的單元格，被寫(xiě)回的就是計(jì)算出來(lái)的值或Nan，而丟掉了公式。
好了，問(wèn)題出現(xiàn)了，我們?cè)撊绾谓鉀Q呢？這里會(huì)想到兩個(gè)思路：
- dataframe寫(xiě)回excel的時(shí)候，不要一次性批量寫(xiě)回，而是通過(guò)行和列的迭代，只寫(xiě)回計(jì)算的數(shù)據(jù)，有公式的單元格不動(dòng)；
- 讀取excel的時(shí)候，有沒(méi)有辦法做到對(duì)于有公式的單元格，讀取公式，而不是讀取公式計(jì)算的結(jié)果；
我確實(shí)按照上面兩個(gè)思路分別嘗試了一下，我們一起來(lái)看一下。
「方案1」
如下代碼嘗試遍歷dataframe然后按單元格寫(xiě)入對(duì)應(yīng)的值，有公式的單元格不動(dòng)
#根據(jù)ds_df來(lái)寫(xiě)excel，只寫(xiě)該寫(xiě)的單元格
for row_idx,row in ds_df.iterrows():
total_capabity_val = row[('Total','Capabity')].strip()
total_capabity1_val = row[('Total','Capabity.1')].strip()
#Total和1Gb Eqv.所在的行不寫(xiě)
if total_capabity_val!= 'Total' and total_capabity_val != '1Gb Eqv.':
#給Delta和LOI賦值
if total_capabity1_val == 'LOI' or total_capabity1_val == 'Delta':
ds_worksheet.range((row_idx + 3 ,3)).value = row[('Current week','BOH')]
print(f"ds_sheet的第{row_idx + 3}行第3列被設(shè)置為{row[('Current week','BOH')]}")
#給Demand和Supply賦值
if total_capabity1_val == 'Demand' or total_capabity1_val == 'Supply':
cp_datetime_columns = cp_df.columns[53:]
for col_idx in range(4,len(ds_df.columns)):
ds_datetime = ds_df.columns.get_level_values(1)[col_idx]
ds_month = ds_df.columns.get_level_values(0)[col_idx]
if type(ds_datetime) == str and ds_datetime != 'TTL' and ds_datetime != 'Total' and (ds_datetime in cp_datetime_columns):
ds_worksheet.range((row_idx + 3,col_idx + 1)).value = row[(f'{ds_month}',f'{ds_datetime}')]
print(f"ds_sheet的第{row_idx + 3}行第{col_idx + 1}列被設(shè)置為{row[(f'{ds_month}',f'{ds_datetime}')]}")
elif type(ds_datetime) == datetime.datetime and (ds_datetime in cp_datetime_columns):
ds_worksheet.range((row_idx + 3,col_idx + 1)).value = row[(f'{ds_month}',ds_datetime)]
print(f"ds_sheet的第{row_idx + 3}行第{col_idx + 1}列被設(shè)置為{row[(f'{ds_month}',ds_datetime)]}") 如上的代碼確實(shí)解決了問(wèn)題，也即有公式的單元格的公式被保留了。但是，根據(jù)我們文章開(kāi)頭提到的Python處理excel的忠告，這個(gè)代碼是有嚴(yán)重性能問(wèn)題的，因?yàn)樗ㄟ^(guò)api頻繁操作excel的單元格，導(dǎo)致寫(xiě)入非常慢，在我的老邁Mac本上一共跑了40分鐘，簡(jiǎn)直不可接受，故該方案只能放棄。
「方案2」
這個(gè)方案是希望做到讀取excel有公式值的單元格的時(shí)候，能保留公式值。這只能從各個(gè)Python的excel庫(kù)的API來(lái)尋找有無(wú)對(duì)應(yīng)的方法了。Pandas的read_excel()方法我仔細(xì)看了一下沒(méi)有對(duì)應(yīng)的參數(shù)可以支持。Openpyxl我倒是找到了一個(gè)API可以支持，如下：
import openpyxl ds_format_workbook = openpyxl.load_workbook(fpath,data_only=False) ds_wooksheet = ds_format_workbook['DS'] ds_df = pd.DataFrame(ds_wooksheet.values)
關(guān)鍵是這里的data_only參數(shù)，為T(mén)rue則返回?cái)?shù)據(jù)，為False的情況下可以保留公式值
本以為找到了對(duì)應(yīng)解決方案正一頓竊喜，但當(dāng)我看到通過(guò)openpyxl讀取到dataframe中的數(shù)據(jù)結(jié)構(gòu)的時(shí)候，才被破了一盆冷水。因?yàn)槲业膃xcel表的表頭是比較復(fù)雜的兩級(jí)的表頭，表頭中還存在合并和拆分單元格的情況，這樣的表頭被openpyxl讀取到dataframe后，沒(méi)有按照pandas的多級(jí)索引進(jìn)行處理，而是簡(jiǎn)單的被處理成數(shù)字索引0123...
但我對(duì)dataframe的計(jì)算會(huì)依賴(lài)多級(jí)索引，因此openpyxl的這種處理方式導(dǎo)致我后面的計(jì)算無(wú)法處理。
openpyxl不行，再看看xlwings呢？通過(guò)對(duì)xlwings API文檔的一通尋找，還真給我找到了，如下所示：
Range類(lèi)提供了一個(gè)Property叫formula，可以獲取和設(shè)置formula。
看到這個(gè)我簡(jiǎn)直如獲至寶，趕緊代碼操練起來(lái)。也許出于慣性，又或許是被之前按行列單元格操作excel的效率搞怕了，我直接先想到的方案還是一次性批量搞定，也即一次性讀取excel所有的公式，然后再一次性寫(xiě)回去，所以我一開(kāi)始的代碼是這樣的：
#使用xlwings來(lái)讀取formula app = xw.App(visible=False,add_book=False) ds_format_workbook = app.books.open(fpath) ds_worksheet = ds_format_workbook.sheets["DS"] #先把所有公式一次性讀取并保存下來(lái) formulas = ds_worksheet.used_range.formula #中間計(jì)算過(guò)程省略... #一次性把所有公式寫(xiě)回去 ds_worksheet.used_range.formula = formulas
可是我想錯(cuò)了，ds_worksheet.used_range.formula讓我誤解只會(huì)返回excel中的有公式的單元格的公式，但其實(shí)它返回的是所有的單元格，只是對(duì)有公式的單元格保留了公式。所以，當(dāng)我重新寫(xiě)回公式的時(shí)候，會(huì)覆蓋掉我通過(guò)dataframe計(jì)算完并寫(xiě)入excel的其他的值。
既然這樣的話，那我只能對(duì)有公式的單元格分別處理而不是一次性處理了，所以代碼得這樣寫(xiě)：
#使用xlwings來(lái)讀取formula
app = xw.App(visible=False,add_book=False)
ds_format_workbook = app.books.open(fpath)
ds_worksheet = ds_format_workbook.sheets["DS"]
#保留excel中的formula
#找到DS中Total所在的行，Total之后的行都是formula
row = ds_df.loc[ds_df[('Total','Capabity')]=='Total ']
total_row_index = row.index.values[0]
#獲取對(duì)應(yīng)excel的行號(hào)(dataframe把兩層表頭當(dāng)做索引，從數(shù)據(jù)行開(kāi)始計(jì)數(shù)，而且從0開(kāi)始計(jì)數(shù)。excel從表頭就開(kāi)始計(jì)數(shù)，而且從1開(kāi)始計(jì)數(shù))
excel_total_row_idx = int(total_row_index+2)
#獲取excel最后一行的索引
excel_last_row_idx = ds_worksheet.used_range.rows.count
#保留按日期計(jì)算的各列的formula
I_col_formula = ds_worksheet.range(f'I3:I{excel_total_row_idx}').formula
N_col_formula = ds_worksheet.range(f'N3:N{excel_total_row_idx}').formula
T_col_formula = ds_worksheet.range(f'T3:T{excel_total_row_idx}').formula
U_col_formula = ds_worksheet.range(f'U3:U{excel_total_row_idx}').formula
Z_col_formula = ds_worksheet.range(f'Z3:Z{excel_total_row_idx}').formula
AE_col_formula = ds_worksheet.range(f'AE3:AE{excel_total_row_idx}').formula
AK_col_formula = ds_worksheet.range(f'AK3:AK{excel_total_row_idx}').formula
AL_col_formula = ds_worksheet.range(f'AL3:AL{excel_total_row_idx}').formula
#保留Total行開(kāi)始一直到末尾所有行的formula
total_to_last_formula = ds_worksheet.range(f'A{excel_total_row_idx+1}:AL{excel_last_row_idx}').formula
#中間計(jì)算過(guò)程省略...
#保存結(jié)果到excel
#直接把ds_df完整賦值給excel，會(huì)導(dǎo)致excel原有的公式被值覆蓋
ds_worksheet.range("A1").expand().options(index=False).value = ds_df
#用之前保留的formulas，重置公式
ds_worksheet.range(f'I3:I{excel_total_row_idx}').formula = I_col_formula
ds_worksheet.range(f'N3:N{excel_total_row_idx}').formula = N_col_formula
ds_worksheet.range(f'T3:T{excel_total_row_idx}').formula = T_col_formula
ds_worksheet.range(f'U3:U{excel_total_row_idx}').formula = U_col_formula
ds_worksheet.range(f'Z3:Z{excel_total_row_idx}').formula = Z_col_formula
ds_worksheet.range(f'AE3:AE{excel_total_row_idx}').formula = AE_col_formula
ds_worksheet.range(f'AK3:AK{excel_total_row_idx}').formula = AK_col_formula
ds_worksheet.range(f'AL3:AL{excel_total_row_idx}').formula = AL_col_formula
ds_worksheet.range(f'A{excel_total_row_idx+1}:AL{excel_last_row_idx}').formula = total_to_last_formula
ds_format_workbook.save()
ds_format_workbook.close()
app.quit()經(jīng)測(cè)試，如上代碼完美地解決我的需求，而且性能上也完全沒(méi)問(wèn)題。
「寫(xiě)在最后」
通過(guò)這幾次用Python對(duì)Excel進(jìn)行處理的實(shí)踐，讓我深刻感覺(jué)到，Pandas用于對(duì)Excel數(shù)據(jù)的高效內(nèi)存計(jì)算是很不錯(cuò)的，但涉及到對(duì)Excel的讀寫(xiě)以及一些跟樣式、格式相關(guān)的操作，還是得依賴(lài)xlwings或openpyxl等其他庫(kù)來(lái)完成，因此，在用Python處理Excel的場(chǎng)景，最佳方案是將Pandas和xlwings或openpyxl等庫(kù)結(jié)合起來(lái)一起使用是最佳組合。
到此這篇關(guān)于利用Python解決Excel問(wèn)題的文章就介紹到這了,更多相關(guān)Python解決Excel問(wèn)題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
4種方法教你利用Python發(fā)現(xiàn)數(shù)據(jù)的規(guī)律
發(fā)現(xiàn)數(shù)據(jù)的規(guī)律是數(shù)據(jù)分析和數(shù)據(jù)科學(xué)中非常重要的一個(gè)步驟。這篇文章主要給大家整理了4個(gè)可以發(fā)現(xiàn)數(shù)據(jù)規(guī)律的方法，希望對(duì)大家有所幫助2023-03-03
scikit-learn線性回歸，多元回歸，多項(xiàng)式回歸的實(shí)現(xiàn)
這篇文章主要介紹了scikit-learn線性回歸，多元回歸，多項(xiàng)式回歸的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
這篇文章主要介紹了Python 常用模塊 re 使用方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-06-06
python一行代碼就能實(shí)現(xiàn)數(shù)據(jù)分析的pandas-profiling庫(kù)
這篇文章主要為大家介紹了python一行代碼就能實(shí)現(xiàn)數(shù)據(jù)分析的pandas-profiling庫(kù),有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2024-01-01
用python 批量更改圖像尺寸到統(tǒng)一大小的方法
下面小編就為大家分享一篇用python 批量更改圖像尺寸到統(tǒng)一大小的方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-03-03
Python實(shí)現(xiàn)PS圖像調(diào)整黑白效果示例
這篇文章主要介紹了Python實(shí)現(xiàn)PS圖像調(diào)整黑白效果,結(jié)合實(shí)例形式分析了Python實(shí)現(xiàn)PS圖像的黑白效果原理與相關(guān)操作技巧,需要的朋友可以參考下2018-01-01
這篇文章主要介紹了PyCharm下載和安裝詳細(xì)步驟，本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-12-12
python實(shí)現(xiàn)對(duì)圖片進(jìn)行旋轉(zhuǎn),放縮,裁剪的功能
今天小編就為大家分享一篇python實(shí)現(xiàn)對(duì)圖片進(jìn)行旋轉(zhuǎn),放縮,裁剪的功能，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2019-08-08
Python實(shí)現(xiàn)將橫表和縱表任意轉(zhuǎn)換的兩種方法
在日常做數(shù)據(jù)分析,接收到最多的表格是縱表,每個(gè)字段變量都有很長(zhǎng)數(shù)據(jù)的長(zhǎng)表,我們稱(chēng)之為縱向數(shù)據(jù),但是,有時(shí)候,我們也會(huì)遇到橫表,對(duì)于橫向數(shù)據(jù),我們會(huì)數(shù)據(jù)轉(zhuǎn)化,將其轉(zhuǎn)化為縱向數(shù)據(jù),感興趣的同學(xué)跟著小編一起來(lái)學(xué)習(xí)吧2023-12-12
python實(shí)現(xiàn)大戰(zhàn)外星人小游戲?qū)嵗a
這篇文章主要介紹了python實(shí)現(xiàn)大戰(zhàn)外星人小游戲,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-12-12