如何使用Pandas在Excel每行后插入空白行？

需求说明 #

希望实现表头行之后、每一行数据之后都插入空白行的效果，示例格式如下：

Col1 Col2 Col3 Col4   (This is the Header)
--------------Blank row
C1    C2   C3   C4
--------------Blank row
C1    C2   C3   C4
--------------Blank row
C1    C2   C3   C4

用户原代码 #

def create_excel_with_format(headers,values,full_file_name_with_path):
   
   df = pd.DataFrame(data=values,columns=headers)
   with pd.ExcelWriter(full_file_name_with_path) as writer:
       df.to_excel(writer, index=False)

   workbook  = writer.book
   worksheet = writer.sheets['Sheet1']
   #font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
   header_format = workbook.add_format({
        'bold': False,
        'border': False,
        'text_wrap': True})
   
   for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)

   font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 13})
   worksheet.set_row(0, None, font_fmt)

两种可行修改方案 #

方案一：修改DataFrame后写入（推荐，逻辑清晰）

先在DataFrame的每行数据后插入空行，再写入Excel，避免操作Excel行列时的索引混乱：

import pandas as pd

def create_excel_with_format(headers, values, full_file_name_with_path):
    # 1. 构造原始数据的DataFrame
    df = pd.DataFrame(data=values, columns=headers)
    
    # 2. 构造包含空行的组合DataFrame：每行数据后加一行空行
    rows_list = []
    for _, row in df.iterrows():
        rows_list.append(row)
        # 生成和表头列数一致的空行
        rows_list.append(pd.Series([None]*len(headers), index=headers))
    combined_df = pd.DataFrame(rows_list, columns=headers)
    
    # 3. 写入Excel并设置格式
    with pd.ExcelWriter(full_file_name_with_path, engine='xlsxwriter') as writer:
        combined_df.to_excel(writer, index=False, header=True)
        
        workbook = writer.book
        worksheet = writer.sheets['Sheet1']
        
        # 设置表头格式
        header_format = workbook.add_format({
            'bold': False,
            'border': False,
            'text_wrap': True,
            'font_name': 'Arial',
            'font_size': 13
        })
        # 给表头列应用格式
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)
        
        # 设置数据行和空行的字体格式
        font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
        for row_num in range(1, len(combined_df)+1):
            worksheet.set_row(row_num, None, font_fmt)

方案二：直接操作Excel工作表插入行

如果不想修改DataFrame，可以直接通过xlsxwriter的API插入行，注意要从下往上插，避免索引混乱：

import pandas as pd

def create_excel_with_format(headers, values, full_file_name_with_path):
    df = pd.DataFrame(data=values, columns=headers)
    
    with pd.ExcelWriter(full_file_name_with_path, engine='xlsxwriter') as writer:
        df.to_excel(writer, index=False)
        
        workbook = writer.book
        worksheet = writer.sheets['Sheet1']
        
        # 设置表头格式
        header_format = workbook.add_format({
            'bold': False,
            'border': False,
            'text_wrap': True,
            'font_name': 'Arial',
            'font_size': 13
        })
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)
        
        # 从下往上插入空行：避免插入后索引错乱
        insert_pos = len(df) + 1
        for _ in range(len(df)):
            worksheet.insert_row(insert_pos, 1)
            insert_pos -= 2
        # 最后在表头后插入一行空行
        worksheet.insert_row(1, 1)
        
        # 设置所有行的字体格式
        font_fmt = workbook.add_format({'font_name': 'Arial', 'font_size': 10})
        worksheet.set_row(0, None, header_format)
        for row_num in range(1, len(df)*2 + 2):
            worksheet.set_row(row_num, None, font_fmt)

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