Writing text wrapped Excel Files using Python

Question

I am new to Python and I was practicing by processing some CSV files and making an excel file from them. So far I can get the excel file however, I am unable to wrap the cells via python. I have tried multiple ways but none of it would work. Perhaps it is because of my poor understanding of Python. Can anyone suggest me how can I wrap text while writing the excel file? And please explain the code along the way? The error that i am getting for the following code is: 'str' object has no attribute 'alignment'

This is what I have done so far:

df=pd.DataFrame(list(zip(Dticketnumberlist,Dcategorylist)), 
             columns=['Ticket', 'Category'])

writer = pd.ExcelWriter('Trial Version.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet = writer.sheets['Sheet1']

wrap_alignment = Alignment(wrap_text=True)
cell.alignment = wrap_alignment

Pandas doc : http://nbviewer.jupyter.org/urls/bitbucket.org/hrojas/learn-pandas/raw/master/lessons/01%20-%20Lesson.ipynb#Get-Data — Rachit kapadia, Aug 01 '18 at 10:40

score 13 · Answer 1 · answered Aug 01 '18 at 14:22

You can use pandas with the xlsxwriter engine (the default).

You need to create a format object by calling the workbook.add_format() method as outlined in the xlsxwriter docs (link here).

Once you've used pandas.DataFrame.to_excel(), you can add the format using worksheet.set_column(). An example of this can be found in the xlsxwriter docs (link here).

I've provided a fully reproducible example below with the expected output.

import pandas as pd

df = pd.DataFrame({'Ticket': ['a','b','c','d'],
                  'Category': [2,1,4,3]})


writer = pd.ExcelWriter('Trial Version.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook=writer.book
worksheet = writer.sheets['Sheet1']

format = workbook.add_format({'text_wrap': True})

# Setting the format but not setting the column width.
worksheet.set_column('A:B', None, format)

writer.save()

Expected Output:

Thanks! finally something works! :D all stackoverflow Q/A give syntax errors!! — Abeer Sul, May 06 '21 at 11:48

score 0 · Answer 2 · answered Aug 01 '18 at 10:48

0

Use python xlsxwriter

The question answered previously can help you better.

answered Aug 01 '18 at 10:48

nirmit

55
1
1
5

1

I have went through this one actually. However I am trying to put the dataframe values in to the cell. In this example they have directly written the string. – T0167 Aug 01 '18 at 10:52

Writing text wrapped Excel Files using Python

2 Answers2

Linked