CODEWITHSUNDEEP
pythonpandasxlsxwriter
Serdia
Serdia

Reputation: 4418

How to get rid of index columns and start writing defined header in first column

The output in excel looks like this:

enter image description here

So now I simply want to get rid of index column and modify header format. For that I'll use parameters:

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

and Now: 

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

But the output looks incorrect:

So what would be the way to fix it?

enter image description here

The whole code is below:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np

df = pd.DataFrame({'Column1':['Roman','Nick','Jarrod','Spencer','Sasha'],
                   'Column2':['Red','Blue','Green','Yellow','Orange']})


writer = ExcelWriter('TestFile.xlsx')

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

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': False,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})
# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)
# Close the Pandas Excel writer and output the Excel file.
writer.save()

Upvotes: 0

Views: 261

Answers (2)

jmcnamara
jmcnamara

Reputation: 41524

Your column index is off by 1. Use:

worksheet.write(0, col_num, value, header_format)

Instead of:

worksheet.write(0, col_num + 1, value, header_format)

Upvotes: 1

Abby
Abby

Reputation: 26

The following code should work:

import csv
df.to_excel(filename,index=False)

Upvotes: 0

Related Questions