Writing excel files, the .xlsx format in Python

Question

I'm starting with a big data frame which after some data mining work I need to reduce it to a file with 2 sheets and writing the file after... This question refers to the writing part and more exactly if I use:

#write the files:

# first file:
# Specify a writer
writer = pd.ExcelWriter('example1.xlsx', engine='xlsxwriter')

# Write your DataFrame to a file     
df11.to_excel(writer, 'Sheet1')
df12.to_excel(writer, 'Sheet2')
# Save the result 
writer.save()

I get the same dimension allocated for all the columns, even if they are formed from numbers or text. And for some reason I get an another column with the row nr from the previous file, despite the fact that if I visualize the files in Rodeo looks just fine...

The file before writing:

The file after writing:

Any ideas are appreciated.

Stephen Rauch · Accepted Answer · 2017-05-29 18:59:43Z

6

pandas does not do the column widths (or styling) for you. It does however provide the means to apply these yourself by accessing the xlsxwriter. To set the width of a column you can do:

Code:

# make wide column wide
workbook = writer.book
worksheet = workbook.worksheets()[1]
worksheet.set_column('B:B', 24)

Test Code:

# Specify a writer
writer = pd.ExcelWriter('example1.xlsx', engine='xlsxwriter')

df1 = pd.DataFrame([1], columns=['A'])
df2 = pd.DataFrame(['My data'], columns=['This is a very wide column'])

# Write your DataFrame to a file
df1.to_excel(writer, 'Sheet1')
df2.to_excel(writer, 'Sheet2')

# make wide column wide
workbook = writer.book
worksheet = workbook.worksheets()[1]
worksheet.set_column('B:B', 24)

# Save the result
writer.save()

Results:

edited May 29, 2017 at 18:59

answered May 29, 2017 at 18:52

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stephen Rauch Over a year ago

Had no way to duplicate the row nr. Can you post an MCVE?

s7c Over a year ago

I understand why in your example the column dimension it applies just to the sheet 2, first it doesn't have the B column. Right? But then why is the same in my case, I have exactly the same columns in both sheets but the changes are applying just to the second sheet. Why? What am I missing? And by the row nr thing I was was referring at the discrepancy of the same data frame before and after the writing, which can be seen in the first post. Why that first column appears after writing and how can I change that? Peace!

Stephen Rauch Over a year ago

Suggest you follow the link in the example and look at the xlsxwriter docs. But you will notice that the set column method is applied to the sheet, not the workbook. The worksheet selected in the example is [1], which is the second sheet (zero based). Also the columns are xlswriter (excel) based, and the column is B (the second column).

s7c Over a year ago

Yes, probably I should have done that from the beginning...appreciating the support

s7c Over a year ago

I dropped the first column (column A which I get after the writing and the afferent image can be seen in the first post) by setting the width to 0. I still don't understand why I get that column and I don't have time for a more elegant solution... Peace!

Collectives™ on Stack Overflow

Writing excel files, the .xlsx format in Python

The file before writing:

The file after writing:

1 Answer 1

Code:

Test Code:

Results:

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The file before writing:

The file after writing:

1 Answer 1

Code:

Test Code:

Results:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related