CODEWITHSUNDEEP
excelpandasdataframexlwings
alant
alant

Reputation: 183

Formatting xlwings view output

I am using xlwings to write a dataframe to an excel sheet. Nothing special, and all works perfectly.

   xw.view(
       dataframe,
       abook.sheets.add(after = abook.sheets[-1]),
       table=True
   )

My issue is that the output excel sheet has filters in the top two rows, which I have to manually disable (by selecting the rows and clearning contents).

Thanks to https://github.com/xlwings/xlwings/issues/679#issuecomment-369138719

I changed my code to the following:

abook = xw.books.active
xw.view(
   dataframe,
   abook.sheets.add(after = abook.sheets[-1]),
   table=True
)
sheetname = abook.active.name
if wb.sheets[sheetname].api.AutoFilterMode == True:
      wb.sheets[sheetname].api.AutoFilterMode = False

which looked promising, but it didn't resolve my issue.

sample sheet header

I would appreciate any pointers, how I can have the filters turned off by default. I am using the latest xlwings on win 10, 11.

Thanks

Upvotes: 1

Views: 590

Answers (2)

mouwsy
mouwsy

Reputation: 1933

Updated on 14 January 2023:
Just for completeness, using the argument table=True in view adds a table with a filter. If you would like to keep the table, but remove the filter, you can remove the filter with ws.tables[0].show_autofilter = False:

import xlwings as xw
import pandas as pd

df = pd._testing.makeDataFrame()

xw.view(df, table=True)
ws = xw.sheets.active

ws.tables[0].show_autofilter = False

Or with api.AutoFilter(Field=[...], VisibleDropDown=False), whereby Field is a list of integers describing the concerning column numbers:

import xlwings as xw
import pandas as pd

df = pd._testing.makeDataFrame()

xw.view(df, table=True)
ws = xw.sheets.active

ws.used_range.api.AutoFilter(Field=list(range(1, ws.used_range[-1].column + 1)), VisibleDropDown=False)

Upvotes: 0

alant
alant

Reputation: 183

The solution was to add the

table=False

parameter to the xw.view(df) method. According to the docs:

table (bool, default True) – If your object is a pandas DataFrame, by default it is formatted as an Excel Table

Now to write a dataframe df, I call:

import xlwings as xw
import pandas as pd
df = pd.DataFrame(...)
xw.view(df, table=False)

Upvotes: 1

Related Questions