快捷導(dǎo)航

利用Python解決Excel問題的最佳方案總結(jié)

更新時(shí)間：2023年04月10日 16:24:30 作者：畢加鎖

python處理excel文件有很多方法,最開始接觸的是xlrd、xlsxwriter模塊,分別用于excel文件的讀、寫,后來又學(xué)習(xí)了openpyxl模塊,可以同時(shí)完成excel文件的讀、寫,下面這篇文章主要給大家介紹了關(guān)于利用Python解決Excel問題的最佳方案,需要的朋友可以參考下

「問題說明」

這次要處理的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對應(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í)候，對于有公式的單元格，會直接讀取公式計(jì)算的結(jié)果（如果沒有結(jié)果則返回Nan），而我們寫入excel的時(shí)候是直接把dataframe一次性批量寫回的，這樣之前帶公式的單元格，被寫回的就是計(jì)算出來的值或Nan，而丟掉了公式。

好了，問題出現(xiàn)了，我們該如何解決呢？這里會想到兩個(gè)思路：

dataframe寫回excel的時(shí)候，不要一次性批量寫回，而是通過行和列的迭代，只寫回計(jì)算的數(shù)據(jù)，有公式的單元格不動；
讀取excel的時(shí)候，有沒有辦法做到對于有公式的單元格，讀取公式，而不是讀取公式計(jì)算的結(jié)果；

我確實(shí)按照上面兩個(gè)思路分別嘗試了一下，我們一起來看一下。

「方案1」

如下代碼嘗試遍歷dataframe然后按單元格寫入對應(yī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分鐘，簡直不可接受，故該方案只能放棄。

「方案2」

這個(gè)方案是希望做到讀取excel有公式值的單元格的時(shí)候，能保留公式值。這只能從各個(gè)Python的excel庫的API來尋找有無對應(yīng)的方法了。Pandas的read_excel()方法我仔細(xì)看了一下沒有對應(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的情況下可以保留公式值

本以為找到了對應(yīng)解決方案正一頓竊喜，但當(dāng)我看到通過openpyxl讀取到dataframe中的數(shù)據(jù)結(jié)構(gòu)的時(shí)候，才被破了一盆冷水。因?yàn)槲业膃xcel表的表頭是比較復(fù)雜的兩級的表頭，表頭中還存在合并和拆分單元格的情況，這樣的表頭被openpyxl讀取到dataframe后，沒有按照pandas的多級索引進(jìn)行處理，而是簡單的被處理成數(shù)字索引0123...

但我對dataframe的計(jì)算會依賴多級索引，因此openpyxl的這種處理方式導(dǎo)致我后面的計(jì)算無法處理。

openpyxl不行，再看看xlwings呢？通過對xlwings API文檔的一通尋找，還真給我找到了，如下所示：

Range類提供了一個(gè)Property叫formula，可以獲取和設(shè)置formula。

看到這個(gè)我簡直如獲至寶，趕緊代碼操練起來。也許出于慣性，又或許是被之前按行列單元格操作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

可是我想錯了，ds_worksheet.used_range.formula讓我誤解只會返回excel中的有公式的單元格的公式，但其實(shí)它返回的是所有的單元格，只是對有公式的單元格保留了公式。所以，當(dāng)我重新寫回公式的時(shí)候，會覆蓋掉我通過dataframe計(jì)算完并寫入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"]
 
#保留excel中的formula
#找到DS中Total所在的行，Total之后的行都是formula
row = ds_df.loc[ds_df[('Total','Capabity')]=='Total ']
total_row_index = row.index.values[0]
#獲取對應(yīng)excel的行號(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，會導(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)測試，如上代碼完美地解決我的需求，而且性能上也完全沒問題。

「寫在最后」

通過這幾次用Python對Excel進(jìn)行處理的實(shí)踐，讓我深刻感覺到，Pandas用于對Excel數(shù)據(jù)的高效內(nèi)存計(jì)算是很不錯的，但涉及到對Excel的讀寫以及一些跟樣式、格式相關(guān)的操作，還是得依賴xlwings或openpyxl等其他庫來完成，因此，在用Python處理Excel的場景，最佳方案是將Pandas和xlwings或openpyxl等庫結(jié)合起來一起使用是最佳組合。

到此這篇關(guān)于利用Python解決Excel問題的文章就介紹到這了,更多相關(guān)Python解決Excel問題內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: