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?
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)