0

Hi I am trying to figure out how to filter in an Excel table.

import xlsxwriter
import pandas as pd

df = pd.DataFrame({'Region' : ['East', 'West', 'South', 'North'],
               'Item' : ['Pear', 'Grape', 'Peach', 'March'],
               'Volume' : [9000, 8000, 7000, 6000],
               'Month' : ['July', 'March', 'April', 'June']})

writer = pd.ExcelWriter('filter_Test.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
(max_row, max_col) = df.shape
worksheet.set_column(0,  max_col - 1, 12)

worksheet.autofilter(0, 0, max_row, max_col - 1)
worksheet.filter_column(3, 'Month == March')

for row_num in (df.index[(df['Month'] != 'March')].tolist()):
    worksheet.set_row(row_num + 1, options={'hidden': True})

This is the result but I want to see in a table:

filter

if I add these lines.

  column_settings = [{'header': column} for column in df.columns]
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

I will get this error:

error

Any help will be greatly appreciated.

Quinten
  • 35,235
  • 5
  • 20
  • 53
Dokyung Yun
  • 1
  • 1
  • 1
    Unfortunately, if you check under "autofilter" at https://xlsxwriter.readthedocs.io/working_with_tables.html, you'll find that it says: "Filter conditions within the table are not supported". – ouroboros1 Oct 08 '22 at 18:33
  • @ouroboros1 Thanks. Is there a way to add a filter in the table? – Dokyung Yun Oct 08 '22 at 18:48

0 Answers0