Remove borders on indexes with Pandas + xlsxwriter

Question

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?

Answer 1

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)