Python：导出 DataFrame 时在 Excel 单元格中添加换行符

Question

我有以下 df ，其中有新的line字符 \ n data ，如下所示。

import pandas as pd
import xlsxwriter
df = pd.DataFrame({'ID': ['111', '222', '222'],
                   'Data': ['Population\nDensity','Population\nDensity','Population\nDensity\nArea']})
print(df)

    ID  Data
0   111 Population\nDensity
1   222 Population\nDensity
2   222 Population\nDensity\nArea

在将此 df 导出到Excel时，我希望在 \ n 上断路。它应该看起来像：

我从 > ，使用 xlsxwriter ，但没有奏效。

Answer 1

如果您使用 xlsxwriter 作为 Excel 写入引擎，您可以向列添加文本换行格式，如下所示：

import pandas as pd

df = pd.DataFrame({'ID': ['111', '222', '222'],
                   'Data': ['Population\nDensity',
                            'Population\nDensity',
                            'Population\nDensity\nArea']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a text wrap format.
text_wrap_format = workbook.add_format({'text_wrap': True})

# Add the format to column 2 (zero-indexed) and adjust the width.
worksheet.set_column(1, 1, 15, text_wrap_format)

# Close the Pandas Excel writer and output the Excel file.
writer.close()

输出：

Answer 2

将 \n 替换为 CHAR(10)，如下所示：https://exceljet.net/formula/add-a-line-break-with-a-formula