python xlsxwriter不覆盖写入_python xlsxwriter：添加表时将标题保留在excel中

我有一个panda数据帧,我写入xslx文件,并希望在该数据上添加一个表.我还想保留我已经编写的标题,而不是再次添加它们.那可能吗？

例：

import pandas as pd

import xlsxwriter as xw

# random dataframe

d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}

df = pd.DataFrame(d)

# write data to file

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer,"sheet without table")

df.to_excel(writer,"sheet with table")

df.to_excel(writer,"sheet with table and header")

# get sheets to add the tables

workbook = writer.book

worksheet_table = writer.sheets['sheet with table']

worksheet_table_header = writer.sheets['sheet with table and header']

# the range in which the table is

end_row = len(df.index)

end_column = len(df.columns)

cell_range = xw.utility.xl_range(0, 0, end_row, end_column)

# add the table that will delete the headers

worksheet_table.add_table(cell_range,{'header_row': True,'first_column': True})

######################################

# The hack

# Using the index in the Table

df.reset_index(inplace=True)

header = [{'header': di} for di in df.columns.tolist()]

worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})

writer.save()

解决方法:

黑客/解决方案是唯一的选择(从@jmcnamara可以看出).简而言之就是：

import pandas as pd

import xlsxwriter as xw

# random dataframe

d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}

df = pd.DataFrame(d)

# write data to file

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer,"sheet with table and header")

# get sheets to add the tables

workbook = writer.book

worksheet_table_header = writer.sheets['sheet with table and header']

# the range in which the table is

end_row = len(df.index)

end_column = len(df.columns)

cell_range = xw.utility.xl_range(0, 0, end_row, end_column)

######################################

# The hack

# Using the index in the Table

df.reset_index(inplace=True)

header = [{'header': di} for di in df.columns.tolist()]

worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})

writer.save()

标签：python,excel,pandas,xlsxwriter,tableheader