python - 使用 xlsxwriter 将 pandas dataframe 写入 Excel 并包含 `write_rich_string` 格式

Question

以下是可重现的并生成所需的输出。

import xlsxwriter, pandas as pd

workbook = xlsxwriter.Workbook('pandas_with_rich_strings.xlsx')
worksheet = workbook.add_worksheet()

# Set up some formats to use.
bold = workbook.add_format({'bold': True})
italic = workbook.add_format({'italic': True})
red = workbook.add_format({'color': 'red'})

df = pd.DataFrame({
    'numCol': [1, 50, 327],
    'plainText': ['plain', 'text', 'column'],
    'richText': [
        ['This is ', bold, 'bold'],
        ['This is ', italic, 'italic'],
        ['This is ', red, 'red']
    ]
}) 


headRows = 1


for colNum in range(len(df.columns)):
    xlColCont = df[df.columns[colNum]].tolist()
    worksheet.write_string(0, colNum , str(df.columns[colNum]), bold)
    for rowNum in range(len(xlColCont)):
        if df.columns[colNum] == 'numCol': 
            worksheet.write_number(rowNum+headRows, colNum , xlColCont[rowNum])        
        elif df.columns[colNum] == 'richText':
            worksheet.write_rich_string(rowNum+headRows, colNum , *xlColCont[rowNum])
        else:
            worksheet.write_string(rowNum+headRows, colNum , str(xlColCont[rowNum]))


workbook.close()

但是，如果不遍历每一列并将整个 pandas 数据框一次性写入 Excel 文件，我将如何做同样的事情和包括 write_rich_string 格式化？

以下不起作用。

writer = pd.ExcelWriter('pandas_with_rich_strings.xlsx', engine='xlsxwriter')
workbook = xlsxwriter.Workbook('pandas_with_rich_strings.xlsx')
worksheet = workbook.add_worksheet('pandas_df')
df.to_excel(writer,'pandas_df')
writer.save()

Answer 1

我不确定我的回答是否比您的回答好得多，但我已将其缩减为仅使用一个 for 循环并使用 pandas.DataFrame.to_excel() 最初将数据框放入 excel 中。请注意，我随后使用 worksheet.write_rich_string() 覆盖了最后一列。

import pandas as pd

writer = pd.ExcelWriter('pandas_with_rich_strings.xlsx', engine='xlsxwriter')
workbook  = writer.book
bold = workbook.add_format({'bold': True})
italic = workbook.add_format({'italic': True})
red = workbook.add_format({'color': 'red'})
df = pd.DataFrame({
    'numCol': [1, 50, 327],
    'plainText': ['plain', 'text', 'column'],
    'richText': [
        ['This is ', bold, 'bold'],
        ['This is ', italic, 'italic'],
        ['This is ', red, 'red']
    ]
}) 
df.to_excel(writer, sheet_name='Sheet1', index=False)
worksheet = writer.sheets['Sheet1']
# you then need to overwite the richtext column with
for idx, x in df['richText'].iteritems():
    worksheet.write_rich_string(idx + 1, 2, *x)
writer.save()

预期输出 .xlsx: