df.to_excel将Decimal列转为文本，如何导出正确数值格式？

我有一个部分列采用Decimal数据类型的DataFrame，想要导出到Excel并保留小数/数值格式，但当前代码会把这些列转成文本格式。代码示例如下：

from decimal import Decimal
import pandas as pd
import io

df['some_col1'] = df['some_col1'].apply(lambda x: Decimal(x) if pd.notnull(x) else None)
df['some_col2'] = df['some_col2'].apply(lambda x: Decimal(x) if pd.notnull(x) else None)
output = io.BytesIO()
with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # Define number format
    number_format = workbook.add_format({'num_format': '0.0000'})
    
    # Apply formatting based on column name
    for col_num, col_name in enumerate(df.columns):
        if col_name == 'some_col1' or col_name == 'some_col2':  # Specify by column name
              worksheet.set_column(col_num, col_num, None, number_format)

即使直接在set_column中指定列（如"A:A"）也无法解决问题，导出的Excel列显示为文本而非数值格式，请问该如何实现正确格式的导出？

问题出在Pandas处理Decimal类型的逻辑上：导出时会默认把Decimal转为字符串存储到Excel，后续仅设置单元格格式无法改变底层的数据类型，所以Excel识别为文本。

以下两种方案可以解决：

方案一：转换Decimal列为Pandas数值类型 #

在导出前将Decimal列转为float64类型（如果是整数Decimal可以用Int64），让Pandas以数值类型写入Excel，再配合格式设置即可生效：

from decimal import Decimal
import pandas as pd
import io

# 示例数据
df = pd.DataFrame({
    'some_col1': ['1.2345', '6.7890', None],
    'some_col2': ['3.1415', '2.7182', '9.9999'],
    'other_col': ['text1', 'text2', 'text3']
})

# 先转为Decimal处理，再转成float64类型
df['some_col1'] = df['some_col1'].apply(lambda x: Decimal(x) if pd.notnull(x) else None).astype('float64')
df['some_col2'] = df['some_col2'].apply(lambda x: Decimal(x) if pd.notnull(x) else None).astype('float64')

output = io.BytesIO()
with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # 定义小数格式
    number_format = workbook.add_format({'num_format': '0.0000'})
    
    # 为目标列应用格式
    for col_num, col_name in enumerate(df.columns):
        if col_name in ['some_col1', 'some_col2']:
              worksheet.set_column(col_num, col_num, None, number_format)

方案二：手动用write_number写入Decimal值 #

如果需要避免float类型的精度损失，可以遍历数据行，用xlsxwriter的write_number方法将Decimal转为数值写入Excel，同时应用格式：

from decimal import Decimal
import pandas as pd
import io

df = pd.DataFrame({
    'some_col1': ['1.2345', '6.7890', None],
    'some_col2': ['3.1415', '2.7182', '9.9999'],
    'other_col': ['text1', 'text2', 'text3']
})

# 保留Decimal类型
df['some_col1'] = df['some_col1'].apply(lambda x: Decimal(x) if pd.notnull(x) else None)
df['some_col2'] = df['some_col2'].apply(lambda x: Decimal(x) if pd.notnull(x) else None)

output = io.BytesIO()
with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    workbook = writer.book
    worksheet = workbook.add_worksheet('Sheet1')
    
    # 写入表头
    worksheet.write_row(0, 0, df.columns)
    
    # 定义小数格式
    number_format = workbook.add_format({'num_format': '0.0000'})
    
    # 遍历数据行写入
    for row_idx, row in enumerate(df.itertuples(index=False), start=1):
        for col_idx, value in enumerate(row):
            col_name = df.columns[col_idx]
            if col_name in ['some_col1', 'some_col2']:
                if pd.notnull(value):
                    # 将Decimal转为float后以数值类型写入
                    worksheet.write_number(row_idx, col_idx, float(value), number_format)
                else:
                    worksheet.write_blank(row_idx, col_idx, None)
            else:
                # 其他列正常写入
                worksheet.write(row_idx, col_idx, value)

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