如上图所示，这个excel一共有两个sheet:cp和ds，我们要按照一定的业务规则，根据cp中的数据计算ds对应单元格的数据。图中蓝色方框框出来的是带公式的，而其他区域是数值。
我们来看看，如果我们按照之前说的处理逻辑，把excel一次性批量读取到dataframe处理，然后再一次性批量写回去有啥问题。这部分代码如下：
import pandas as pdimport xlwings as xw #要处理的文件路径fpath = "data/ds_format.xlsm" #把cp和ds两个sheet的数据分别读入pandas的dataframecp_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的时候，有没有办法做到对于有公式的单元格，读取公式，而不是读取公式计算的结果；
我确实按照上面两个思路分别尝试了一下，我们一起来看一下。
「方案1」如下代码尝试遍历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分钟，简直不可接受，故该方案只能放弃。
「方案2」这个方案是希望做到读取excel有公式值的单元格的时候，能保留公式值。这只能从各个python的excel库的api来寻找有无对应的方法了。pandas的read_excel()方法我仔细看了一下没有对应的参数可以支持。openpyxl我倒是找到了一个api可以支持，如下：
import openpyxlds_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来读取formulaapp = 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来读取formulaapp = 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之后的行都是formularow = 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#保留按日期计算的各列的formulai_col_formula = ds_worksheet.range(f'i3:i{excel_total_row_idx}').formulan_col_formula = ds_worksheet.range(f'n3:n{excel_total_row_idx}').formulat_col_formula = ds_worksheet.range(f't3:t{excel_total_row_idx}').formulau_col_formula = ds_worksheet.range(f'u3:u{excel_total_row_idx}').formulaz_col_formula = ds_worksheet.range(f'z3:z{excel_total_row_idx}').formulaae_col_formula = ds_worksheet.range(f'ae3:ae{excel_total_row_idx}').formulaak_col_formula = ds_worksheet.range(f'ak3:ak{excel_total_row_idx}').formulaal_col_formula = ds_worksheet.range(f'al3:al{excel_total_row_idx}').formula#保留total行开始一直到末尾所有行的formulatotal_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_formulads_worksheet.range(f'n3:n{excel_total_row_idx}').formula = n_col_formulads_worksheet.range(f't3:t{excel_total_row_idx}').formula = t_col_formulads_worksheet.range(f'u3:u{excel_total_row_idx}').formula = u_col_formulads_worksheet.range(f'z3:z{excel_total_row_idx}').formula = z_col_formulads_worksheet.range(f'ae3:ae{excel_total_row_idx}').formula = ae_col_formulads_worksheet.range(f'ak3:ak{excel_total_row_idx}').formula = ak_col_formulads_worksheet.range(f'al3:al{excel_total_row_idx}').formula = al_col_formulads_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文件？的详细内容。