3

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.

Improve this question
4
  • What you do mean by "Is there a way to name my row?" The index is included in the written data by default and the index effectively names all of the rows. Commented Dec 31, 2015 at 18:51
  • I meant the row header. Sorry! Commented Dec 31, 2015 at 18:52
  • You should include at least a sample of your dataframe to reproduce your problem. Commented Dec 31, 2015 at 21:00
  • @FabioLamanna Please see the update one Commented Dec 31, 2015 at 21:20

1 Answer 1

Reset to default
2

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.

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

1 Comment

In relation to the last paragraph, that isn't a bug. XlsxWriter hides the gridlines, which can be verified by going into print preview. The lines that are visible around the indexes are borders (not gridlines) which are added by default by Pandas.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.