利用Python解決Excel問題的最佳方案總結(jié)
「問題說明」
這次要處理的excel有兩個(gè)sheet,要根據(jù)其中一個(gè)sheet的數(shù)據(jù)來計(jì)算另外一個(gè)sheet的值。造成問題的點(diǎn)在于,要計(jì)算值的sheet里不僅僅有數(shù)值,還有公式。我們來看一下:
如上圖所示,這個(gè)excel一共有兩個(gè)sheet:CP和DS,我們要按照一定的業(yè)務(wù)規(guī)則,根據(jù)CP中的數(shù)據(jù)計(jì)算DS對(duì)應(yīng)單元格的數(shù)據(jù)。圖中藍(lán)色方框框出來的是帶公式的,而其他區(qū)域是數(shù)值。
我們來看看,如果我們按照之前說的處理邏輯,把excel一次性批量讀取到dataframe處理,然后再一次性批量寫回去有啥問題。這部分代碼如下:
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ì)算過程省略...... #保存結(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()
如上代碼存在的問題在于,pd.read_excel()方法從excel里讀取數(shù)據(jù)到dataframe的時(shí)候,對(duì)于有公式的單元格,會(huì)直接讀取公式計(jì)算的結(jié)果(如果沒有結(jié)果則返回Nan),而我們寫入excel的時(shí)候是直接把dataframe一次性批量寫回的,這樣之前帶公式的單元格,被寫回的就是計(jì)算出來的值或Nan,而丟掉了公式。
好了,問題出現(xiàn)了,我們?cè)撊绾谓鉀Q呢?這里會(huì)想到兩個(gè)思路:
- dataframe寫回excel的時(shí)候,不要一次性批量寫回,而是通過行和列的迭代,只寫回計(jì)算的數(shù)據(jù),有公式的單元格不動(dòng);
- 讀取excel的時(shí)候,有沒有辦法做到對(duì)于有公式的單元格,讀取公式,而不是讀取公式計(jì)算的結(jié)果;
我確實(shí)按照上面兩個(gè)思路分別嘗試了一下,我們一起來看一下。
「方案1」
如下代碼嘗試遍歷dataframe然后按單元格寫入對(duì)應(yīng)的值,有公式的單元格不動(dòng)
#根據(jù)ds_df來寫excel,只寫該寫的單元格 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.所在的行不寫 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í)解決了問題,也即有公式的單元格的公式被保留了。但是,根據(jù)我們文章開頭提到的Python處理excel的忠告,這個(gè)代碼是有嚴(yán)重性能問題的,因?yàn)樗ㄟ^api頻繁操作excel的單元格,導(dǎo)致寫入非常慢,在我的老邁Mac本上一共跑了40分鐘,簡(jiǎn)直不可接受,故該方案只能放棄。
「方案2」
這個(gè)方案是希望做到讀取excel有公式值的單元格的時(shí)候,能保留公式值。這只能從各個(gè)Python的excel庫(kù)的API來尋找有無對(duì)應(yīng)的方法了。Pandas的read_excel()方法我仔細(xì)看了一下沒有對(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ù),為True則返回?cái)?shù)據(jù),為False的情況下可以保留公式值
本以為找到了對(duì)應(yīng)解決方案正一頓竊喜,但當(dāng)我看到通過openpyxl讀取到dataframe中的數(shù)據(jù)結(jié)構(gòu)的時(shí)候,才被破了一盆冷水。因?yàn)槲业膃xcel表的表頭是比較復(fù)雜的兩級(jí)的表頭,表頭中還存在合并和拆分單元格的情況,這樣的表頭被openpyxl讀取到dataframe后,沒有按照pandas的多級(jí)索引進(jìn)行處理,而是簡(jiǎn)單的被處理成數(shù)字索引0123...
但我對(duì)dataframe的計(jì)算會(huì)依賴多級(jí)索引,因此openpyxl的這種處理方式導(dǎo)致我后面的計(jì)算無法處理。
openpyxl不行,再看看xlwings呢?通過對(duì)xlwings API文檔的一通尋找,還真給我找到了,如下所示:
Range類提供了一個(gè)Property叫formula,可以獲取和設(shè)置formula。
看到這個(gè)我簡(jiǎn)直如獲至寶,趕緊代碼操練起來。也許出于慣性,又或許是被之前按行列單元格操作excel的效率搞怕了,我直接先想到的方案還是一次性批量搞定,也即一次性讀取excel所有的公式,然后再一次性寫回去,所以我一開始的代碼是這樣的:
#使用xlwings來讀取formula app = xw.App(visible=False,add_book=False) ds_format_workbook = app.books.open(fpath) ds_worksheet = ds_format_workbook.sheets["DS"] #先把所有公式一次性讀取并保存下來 formulas = ds_worksheet.used_range.formula #中間計(jì)算過程省略... #一次性把所有公式寫回去 ds_worksheet.used_range.formula = formulas
可是我想錯(cuò)了,ds_worksheet.used_range.formula讓我誤解只會(huì)返回excel中的有公式的單元格的公式,但其實(shí)它返回的是所有的單元格,只是對(duì)有公式的單元格保留了公式。所以,當(dāng)我重新寫回公式的時(shí)候,會(huì)覆蓋掉我通過dataframe計(jì)算完并寫入excel的其他的值。
既然這樣的話,那我只能對(duì)有公式的單元格分別處理而不是一次性處理了,所以代碼得這樣寫:
#使用xlwings來讀取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ù)行開始計(jì)數(shù),而且從0開始計(jì)數(shù)。excel從表頭就開始計(jì)數(shù),而且從1開始計(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行開始一直到末尾所有行的formula total_to_last_formula = ds_worksheet.range(f'A{excel_total_row_idx+1}:AL{excel_last_row_idx}').formula #中間計(jì)算過程省略... #保存結(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è)試,如上代碼完美地解決我的需求,而且性能上也完全沒問題。
「寫在最后」
通過這幾次用Python對(duì)Excel進(jìn)行處理的實(shí)踐,讓我深刻感覺到,Pandas用于對(duì)Excel數(shù)據(jù)的高效內(nèi)存計(jì)算是很不錯(cuò)的,但涉及到對(duì)Excel的讀寫以及一些跟樣式、格式相關(guān)的操作,還是得依賴xlwings或openpyxl等其他庫(kù)來完成,因此,在用Python處理Excel的場(chǎng)景,最佳方案是將Pandas和xlwings或openpyxl等庫(kù)結(jié)合起來一起使用是最佳組合。
到此這篇關(guān)于利用Python解決Excel問題的文章就介紹到這了,更多相關(guān)Python解決Excel問題內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Python根據(jù)站點(diǎn)列表繪制站坐標(biāo)全球分布圖的示例
這篇文章主要介紹了Python根據(jù)站點(diǎn)列表繪制站坐標(biāo)全球分布圖,輸入站點(diǎn)列表文件、snx全球站點(diǎn)坐標(biāo)文件,本文通過示例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下2021-12-12python 實(shí)現(xiàn)將list轉(zhuǎn)成字符串,中間用空格隔開
今天小編就為大家分享一篇python 實(shí)現(xiàn)將list轉(zhuǎn)成字符串,中間用空格隔開,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2019-12-12Python 實(shí)現(xiàn) 貪吃蛇大作戰(zhàn) 代碼分享
本文給大家分享的是一個(gè)使用cocos2d-python游戲引擎庫(kù)制作出來的貪吃蛇大作戰(zhàn)的游戲代碼,基于Python 2.7 和 cocos2d 庫(kù),有需要的小伙伴可以參考下2016-09-09git查看、創(chuàng)建、刪除、本地、遠(yuǎn)程分支方法詳解
這篇文章主要介紹了git查看、創(chuàng)建、刪除、本地、遠(yuǎn)程分支方法詳解,需要的朋友可以參考下2020-02-02詳解python中靜態(tài)方法staticmethod用法
本文主要介紹了python中靜態(tài)方法staticmethod用法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07詳解python 利用echarts畫地圖(熱力圖)(世界地圖,省市地圖,區(qū)縣地圖)
這篇文章主要介紹了詳解python 利用echarts畫地圖(熱力圖)(世界地圖,省市地圖,區(qū)縣地圖),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08