這次要處理的excel有兩個sheet，要根據其中一個sheet的資料來計算另一個sheet的值。造成問題的點在於，要計算值的sheet裡面不只有數值，還有公式。讓我們來看看：
如上圖所示，這個excel一共有兩個sheet:CP和DS，我們要按照一定的業務規則，根據CP中的數據計算DS對應單元格的資料。圖中藍色方框框出來的是帶有公式的，而其他區域是數值。
我們來看看，如果我們照著之前說的處理邏輯，把excel一次批次讀取到dataframe處理，然後再一次批次寫回去有啥問題。這部分程式碼如下：
import pandas as pd
import xlwings as xw
#要处理的文件路径
fpath = "data/DS_format.xlsm"
#把CP和DS两个sheet的数据分别读入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])
#计算过程省略......
#保存结果到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讀取資料到dataframe的時候，對於有公式的單元格，會直接讀取公式計算的結果（如果沒有結果則回傳Nan），而我們寫入excel的時候是直接把dataframe一次性批次寫回的，這樣之前帶公式的單元格，被寫回的就是計算出來的值或Nan，而丟掉了公式。
好了，問題出現了，我們該如何解決呢？這裡會想到兩個想法：
dataframe寫回excel的時候，不要一次批量寫回，而是透過行和列的迭代，只寫回計算的數據，有公式的單元格不動；
讀取excel的時候，有沒有辦法做到對於有公式的單元格，讀取公式，而不是讀取公式計算的結果；
我確實按照上面兩個想法分別嘗試了一下，我們一起來看一下。
如下程式碼嘗試遍歷dataframe然後按單元格寫入對應的值，有公式的單元格不動
#根据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列被设置为{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}列被设置为{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}列被设置为{row[(f'{ds_month}',ds_datetime)]}")如上的程式碼確實解決了問題，也即有公式的單元格的公式被保留了。但是，根據我們文章開頭提到的Python處理excel的忠告，這個程式碼是有嚴重性能問題的，因為它通過api頻繁操作excel的單元格，導致寫入非常慢，在我的老邁Mac本上一共跑了40分鐘，簡直不可接受，故方案只能放棄。
這個方案是希望做到讀取excel有公式值的儲存格的時候，能保留公式值。這只能從各個Python的excel庫的API來尋找有無對應的方法了。 Pandas的read_excel()方法我仔細看了一下沒有對應的參數可以支援。 Openpyxl我倒是找到了一個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)
關鍵是這裡的data_only參數，為True則返回數據，為False的情況下可以保留公式值
#本以為找到了對應解決方案正一頓竊喜，但當我看到透過openpyxl讀取到dataframe中的資料結構的時候，才被破了一盆冷水。因為我的excel表的表頭是比較複雜的兩級的表頭，表頭中還存在合併和拆分單元格的情況，這樣的表頭被openpyxl讀取到dataframe後，沒有按照pandas的多級索引進行處理，而是簡單的被處理成數字索引0123...
但我對dataframe的計算會依賴多層索引，因此openpyxl的這種處理方式導致我後面的計算無法處理。
openpyxl不行，再看看xlwings呢？透過對xlwings API文件的一通尋找，還真給我找到了，如下所示：
#Range類別提供了一個Property叫formula，可以取得和設定formula。
看到這個我簡直如獲至寶，趕緊程式碼操練起來。也許出於慣性，又或許是被之前按行列單元格操作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 #中间计算过程省略... #一次性把所有公式写回去 ds_worksheet.used_range.formula = formulas
可是我想錯了，ds_worksheet.used_range.formula讓我誤解只會返回excel中的有公式的單元格的公式，但其實它傳回的是所有的儲存格，只是對有公式的儲存格保留了公式。所以，當我重新寫回公式的時候，會覆寫我透過dataframe計算完並寫入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]
#获取对应excel的行号(dataframe把两层表头当做索引，从数据行开始计数，而且从0开始计数。excel从表头就开始计数，而且从1开始计数)
excel_total_row_idx = int(total_row_index+2)
#获取excel最后一行的索引
excel_last_row_idx = ds_worksheet.used_range.rows.count
#保留按日期计算的各列的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
#中间计算过程省略...
#保存结果到excel
#直接把ds_df完整赋值给excel，会导致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()經測試，如上代碼完美地解決我的需求，而且效能上也完全沒問題。
以上是Python如何處理Excel檔案？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
