使用Python的XlsxWriter与Pandas时，行格式被列格式覆盖的解决问询

问题根源：XlsxWriter中单元格的格式由最后一次写入操作决定。你先给第4行设置了global_format，后续调用write_column时会覆盖该行对应单元格的格式，导致行样式丢失。

以下是两种可行解决方案：

方案1：调整写入顺序（最简单） #

先写入所有列的数据和格式，再单独写入第4行的总计内容与global_format。后写入的行格式会覆盖列格式在第4行的部分，其他行则保留列格式。

修改后的代码示例：

import numpy as np
import xlsxwriter

workbook = xlsxwriter.Workbook('abc.xlsx', {'nan_inf_to_errors': True})

# 定义各类格式
cell_format = workbook.add_format({'align': 'left','border': 1, "border_color": "black"})
ROI_format = workbook.add_format({'num_format': '$#,##0.00','align':'center','border': 1, "border_color": "black"})
spend_format = workbook.add_format({'num_format': '$#,##0','align':'right','border': 1, "border_color": "black"})
Header_format = workbook.add_format({'bold':True,'align':'center','bg_color':'#FDE9D9','border': 1, "border_color": "black"})
global_format = workbook.add_format({'bold':True,'align':'center','bg_color':'#DCE6F1','border': 1, "border_color": "black"})

WS1 = workbook.add_worksheet()
colsToWrite = COUNTRY_DATA.columns.tolist()  # 假设表头为列名
first_col = 1  # B列对应的索引
first_row = 5  # 数据从第5行开始，第4行是总计行

# 第一步：写入列数据及对应格式
for col_idx, col in enumerate(COUNTRY_DATA.columns):
    start_col = first_col + col_idx
    if 'ROI' in col:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], ROI_format)
    elif 'SPEND' in col:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], spend_format)
    else:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], cell_format)

# 第二步：写入总计行（第4行）的内容与格式
# 替换为你的总计计算逻辑，这里示例用列求和
total_row_data = [np.sum(COUNTRY_DATA[col]) for col in COUNTRY_DATA.columns]
WS1.write_row('B4', total_row_data, global_format)

# 第三步：写入表头
WS1.write_row('B3', colsToWrite, Header_format)

workbook.close()

方案2：创建复合格式（保留列格式特性） #

如果需要总计行同时保留对应列的数字格式（如ROI的货币小数、SPEND的整数格式）和总计行的样式（背景色、加粗），可以为每个列类型创建复合格式，单独写入总计行的每个单元格。

代码示例：

import numpy as np
import xlsxwriter

workbook = xlsxwriter.Workbook('abc.xlsx', {'nan_inf_to_errors': True})

# 基础格式
cell_format = workbook.add_format({'align': 'left','border': 1, "border_color": "black"})
ROI_format = workbook.add_format({'num_format': '$#,##0.00','align':'center','border': 1, "border_color": "black"})
spend_format = workbook.add_format({'num_format': '$#,##0','align':'right','border': 1, "border_color": "black"})
Header_format = workbook.add_format({'bold':True,'align':'center','bg_color':'#FDE9D9','border': 1, "border_color": "black"})

# 创建总计行专属的复合格式（融合列格式特性+总计样式）
ROI_global_format = workbook.add_format({
    'num_format': '$#,##0.00',
    'align':'center',
    'border': 1,
    "border_color": "black",
    'bold':True,
    'bg_color':'#DCE6F1'
})

spend_global_format = workbook.add_format({
    'num_format': '$#,##0',
    'align':'right',
    'border': 1,
    "border_color": "black",
    'bold':True,
    'bg_color':'#DCE6F1'
})

cell_global_format = workbook.add_format({
    'align': 'left',
    'border': 1,
    "border_color": "black",
    'bold':True,
    'bg_color':'#DCE6F1'
})

WS1 = workbook.add_worksheet()
colsToWrite = COUNTRY_DATA.columns.tolist()
first_col = 1
first_row = 5

# 写入列数据
for col_idx, col in enumerate(COUNTRY_DATA.columns):
    start_col = first_col + col_idx
    if 'ROI' in col:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], ROI_format)
    elif 'SPEND' in col:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], spend_format)
    else:
        WS1.write_column(first_row, start_col, COUNTRY_DATA[col], cell_format)

# 写入总计行，按列类型应用对应复合格式
for col_idx, col in enumerate(COUNTRY_DATA.columns):
    col_letter = chr(ord('B') + col_idx)
    total_cell = f'{col_letter}4'
    total_value = np.sum(COUNTRY_DATA[col])
    
    if 'ROI' in col:
        WS1.write(total_cell, total_value, ROI_global_format)
    elif 'SPEND' in col:
        WS1.write(total_cell, total_value, spend_global_format)
    else:
        WS1.write(total_cell, total_value, cell_global_format)

# 写入表头
WS1.write_row('B3', colsToWrite, Header_format)

workbook.close()

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