CODEWITHSUNDEEP
pythonexcelxlsxwriter
mikelowry
mikelowry

Reputation: 1727

Remove borders on indexes with Pandas + xlsxwriter

It seems that xlsxwriter automatically adds borders around pandas df indexes. How can I remove the borders, after the data has been written? It seems when I try to use something like 

worksheet.set_column('A:A', None, test_format) #test_format just makes borders = 0

It either removes the values, or does nothing at all...

Here is an example setup:

import xlsxwriter
import pandas as pd

# Create a test df
df = pd.DataFrame({'Name': ['Tesla','Tesla','Toyota','Ford','Ford','Ford'],
                   'Type': ['Model X','Model Y','Corolla','Bronco','Fiesta','Mustang']})

df = df.set_index('Name').groupby('Name').apply(lambda x:x[:]) 


# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='test', startrow=1, merge_cells=True)

## Get the xlsxwriter objects from the dataframe writer object. Setting up xlsxwriter for formatting
workbook = writer.book
worksheet = writer.sheets['test']


writer.save()

Any way to apply formatting after the df has been written to excel? Or altering index formatting?

Upvotes: 4

Views: 7824

Answers (4)

Franky1
Franky1

Reputation: 484

Pandas 2.0.1

import pandas as pd
pd.io.formats.excel.ExcelFormatter.header_style = None

does not work:

AttributeError: module 'pandas.io.formats' has no attribute 'excel'

Upvotes: 1

Wouter
Wouter

Reputation: 3261

From: https://stackoverflow.com/a/72419827/11770286

import pandas as pd

pd.io.formats.excel.ExcelFormatter.header_style = None

df.to_excel('')

Upvotes: 3

mikelowry
mikelowry

Reputation: 1727

For those looking to solve this issue with multi-indexes, you can use something like the below: 

# changes row index format, 4 denotes the start row, 0 denotes the column
for row_num, value in enumerate(df.index.get_level_values(level=1)):
    worksheet.write(4+row_num , 0, value, FORMAT_OF_CHOICE)

Upvotes: 1

jmcnamara
jmcnamara

Reputation: 41584

It seems that xlsxwriter automatically adds borders around pandas df indexes.

Strictly speaking, Pandas adds the borders, using xlsxwriter (or openpyxl or xlwt).

It seems when I try to use something like ... It either removes the values, or does nothing at all.

That is because the cell format, that is already there, overrides the column format.

The usual workaround for this is to turn off the header or index and specify your own like this example in the XlsxWriter docs.

However, in your case you have a multi-index with merged cells so that is a bit trickier to replicate.

Upvotes: 3

Related Questions