如何使用Python xlwings写入pandas df到Excel时启用skip_blanks跳过空值

问题描述 #

我的Python脚本生成了一个包含若干NaN值的pandas DataFrame，样例数据如下：

A         B         C         D
0.351741  NaN       0.238705  NaN     
0.950817  0.665594  0.671151  NaN     
NaN       0.442725  0.658816  NaN     
0.155604  0.567044  NaN       0.666576
NaN       0.751562  NaN       0.597252
0.577770  NaN       NaN       0.123392

核心需求：使用xlwings将该DataFrame写入Excel工作表时，跳过值为NaN的单元格，保留Excel中对应位置的原有内容——待保留的内容可能是实时Excel公式、已存储数值，甚至是空单元格。

目标Excel区域原有内容如下：

A         B         C         D
Excel A1  Excel B1  Excel C1  Excel D1 
Excel A2  Excel B2  Excel C2  Excel D2 
Excel A3  Excel B3  Excel C3  Excel D3 
Excel A4  Excel B4  Excel C4  Excel D4
Excel A5  Excel B5  Excel C5  Excel D5
Excel A6  Excel B6  Excel C6  Excel D6

写入后期望得到的输出结果为：

A         B         C         D
0.351741  Excel B1  0.238705  Excel D1
0.950817  0.665594  0.671151  Excel D2
Excel A3  0.442725  0.658816  Excel D3
0.155604  0.567044  Excel C4  0.666576
Excel A5  0.751562  Excel C5  0.597252
0.577770  Excel B6  Excel C6  0.123392

常规写入代码如下：

r = 'A1:D6'
sht.range(r).options(index=False, header=False).value = df

该写法的问题是NaN值会将对应Excel单元格覆盖为空值，原有内容丢失，错误写入结果如下：

A         B         C         D
0.351741  BLANK     0.238705  BLANK   
0.950817  0.665594  0.671151  BLANK   
BLANK     0.442725  0.658816  BLANK   
0.155604  0.567044  BLANK     0.666576
BLANK     0.751562  BLANK     0.597252
0.577770  BLANK     BLANK     0.123392

查阅xlwings官方文档与源码后发现，skip_blanks参数目前仅在paste()函数中实现，默认值为False。当前找到的临时方案需要借助临时工作表，代码如下：

tmp_sht.range(r).options(index=False, header=False).value = df 
tmp_sht.range(r).copy(destination=None)
sht.range(r).paste(paste='values', skip_blanks=True) 

该方案需要额外创建临时工作表，期望能实现直接写入时跳过空值，类似如下写法：

sht.range(r).options(index=False, header=False, skip_blanks=True).value = df

实现方案 #

不需要等待官方为options()新增skip_blanks参数，也不需要使用临时工作表，以下两种方案可直接实现需求：

• 方案1：逐非空单元格写入（适合小数据量场景）
  遍历DataFrame所有值，仅对非NaN的对应单元格赋值，完全不触碰NaN位置的原有内容：

  import pandas as pd
  
  r = 'A1:D6'
  # 获取写入区域的起始坐标
  start_row = sht.range(r).row
  start_col = sht.range(r).column
  
  # 遍历写入非空值
  for row_idx in range(df.shape[0]):
      for col_idx in range(df.shape[1]):
          cell_val = df.iat[row_idx, col_idx]
          if not pd.isna(cell_val):
              sht.cells(start_row + row_idx, start_col + col_idx).value = cell_val
  

  • 优势：逻辑简单，完整保留原有单元格的公式、格式，不操作剪贴板
  • 劣势：逐单元格调用COM接口，数据量较大时写入速度慢

• 方案2：掩码合并后批量写入（适合大数据量场景，性能最优）
  先读取目标区域原有内容，用原有内容填充DataFrame中的NaN位置，再一次性批量写入，写入性能和原生直接写入几乎一致：

  import pandas as pd
  import numpy as np
  
  r = 'A1:D6'
  target_range = sht.range(r)
  # 读取目标区域原有内容：如果要保留公式，加value=False参数读取公式本身
  original_data = np.array(
      target_range.options(ndim=2, value=False).value,
      dtype=object
  )
  # 合并数据：用原有内容填充df中的NaN
  df_data = df.to_numpy(dtype=object)
  nan_mask = pd.isna(df_data)
  df_data[nan_mask] = original_data[nan_mask]
  # 一次性批量写入
  target_range.options(index=False, header=False).value = df_data
  

  • 优势：批量写入性能极高，不需要临时表、不占用剪贴板，可选择保留原有公式或计算结果
  • 注意：需保证DataFrame的形状和目标写入区域的形状完全一致

截止xlwings 0.31.x稳定版本，Range.options()暂未内置写入时的skip_blanks参数，上述两种方案可完全替代临时工作表方案。

内容的提问来源于stack exchange，提问作者Romain Capron