python - 使用 Pandas + xlsxwriter 删除索引的边框

Question

似乎 xlsxwriter 会自动在 pandas df 索引周围添加边框。数据写入后如何删除边框？似乎当我尝试使用类似的东西时

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

它要么删除值，要么什么都不做......

这是一个示例设置:

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

将df写入excel后有什么方法可以应用格式？或者改变索引格式？

Answer 1

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

严格来说，Pandas 使用 xlsxwriter(或 openpyxl 或 xlwt)添加边框。

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

这是因为已经存在的单元格格式会覆盖列格式。

通常的解决方法是关闭标题或索引并像这样指定您自己的 example in the XlsxWriter docs .

但是，在您的情况下，您有一个包含合并单元格的多索引，因此复制起来有点棘手。