CODEWITHSUNDEEP
pythonpandasdataframexls
Sarah
Sarah

Reputation: 413

Pandas: Writing data frame to Excel (.xls) file issue

I am trying to write the data frame to excel and also making the cell width (20) and trying to hide grid lines. So far I did like :

writer = ExcelWriter('output.xlsx')

df.to_excel(writer,'Sheet1')
writer.save()


worksheet = writer.sheets['Sheet1']

# Trying to set column width as 18
worksheet.set_column(0,3,18)   
#the data frame has 2 columns and a row  - in xls it converts them to 3 columns

worksheet.hide_gridlines()
# I tried with option 2 - but this hides only the column grids, I mean the row(in data fram now became column A - it still has grid lines) 

writer.save()

My Data frame looks like:

                              Col1      Col2
  Time 
   2011-01-01 01:00:00        4345        0.444

   2011-01-01 11:00:00        443         7.4

Is this wrong way? I don't see the changes in the output file. What I am doing wrong here? Is there a way to name my row header?

Here. When try to hide the grid line. The row(in data frame['Time]) now column A in excel still has the grid.

Upvotes: 3

Views: 3488

Answers (1)

Fabio Lamanna
Fabio Lamanna

Reputation: 21584

IIUC, for the set_column width you're actually write your df twice; the correct workflow should be the following (EDIT: add engine keyword):

import pandas as pd

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

df.to_excel(writer,'Sheet1')

worksheet = writer.sheets['Sheet1']

# Trying to set column width as 18
worksheet.set_column(0,3,18)   
#the data frame has 2 columns and a row  - in xls it converts them to 3 columns

worksheet.hide_gridlines()
# I tried with option 2 - but this hides only the column grids, I mean the row(in data fram now became column A - it still has grid lines) 

writer.save()

This should correctly set the columns width. If you don't want to have the index Time column in your output, you should set:

df.to_excel(writer,'Sheet1',index=False)

if you have previously set:

df.set_index = 'Time'

The grid issue actually it's still there when you plot the full dataframe. I think the the current ExcelWriter object doesn't support the index for the hide_gridlines() option, but I don't know if it is a bug or not.

EDIT: thanks to the comments, this isn't a bug.

Upvotes: 2

Related Questions