Python: Add Line breaks into Excel cells while exporting the DataFrame

Question

I have the following df, where there are new line characters \n in column Data, as shown below.

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

While exporting this df to Excel, I want line breaks at \n. It should look like:

I sought some help from here, using xlsxwriter but didn't work out.

jmcnamara · Accepted Answer · 2023-05-08T22:47:44.353

2

If you use xlsxwriter as the Excel writing engine you can add a text wrap format to the column like this:

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()

Output:

edited May 08 '23 at 22:47

answered Apr 11 '22 at 10:56

jmcnamara

38,196
6
90
108

Thanks for the answer. Just one question - In `to_excel()` function, if `index=False` is removed, then we don't get line breaks. Why? – cph_sto Apr 11 '22 at 12:04
Probably you need to adjust the column number index in `set_column()` to 2 to account for the extra column. – jmcnamara Apr 11 '22 at 12:33
1

Unfortunately, this doesn't always work. :( – infiniteloop Jul 20 '22 at 22:45
doesn't work for me either. – 3r1c Dec 19 '22 at 10:52
To the folks saying this doesn't work I would suggest suggest opening a new question with sample code. – jmcnamara Dec 19 '22 at 11:38
I'm getting AttributeError: `'XlsxWriter' object has no attribute 'save'` – emilaz May 08 '23 at 12:43
@emilaz See https://stackoverflow.com/a/76119258/10238 – jmcnamara May 08 '23 at 22:48

score 0 · Answer 2 · answered Apr 11 '22 at 10:38

0

Replace \n with CHAR(10) as per here: https://exceljet.net/formula/add-a-line-break-with-a-formula

answered Apr 11 '22 at 10:38

pseudostack

21
5

1

I want a Python solution. – cph_sto Apr 11 '22 at 10:39
1

@cph_sto you may have better luck with this solution then: https://stackoverflow.com/a/17279740/14852056 – pseudostack Apr 11 '22 at 10:41

Python: Add Line breaks into Excel cells while exporting the DataFrame

2 Answers2