CODEWITHSUNDEEP
pythonwin32comautofilter
Alexander Witte
Alexander Witte

Reputation: 215

Python- How to show the selections in an Excel AutoFilter

This may be a simple task but for the life of me I cannot find a solution. I have an excel document that has a table. The Columns in this table all have autofilters applied. All I want to do is to be able to select all the entries in that autofilter (for the 9th column) and store it in an array. I'm using Win32Com.

import win32com.client as win32

working_dir = 'C:\\invoice\\'
save_dir = 'C:\\test\\'

xl = win32.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True

template_wb = xl.Workbooks.Open(working_dir + 'Settlement report V6-EMPLATE.xlsm')

#Worksheets
orgdata_ws = template_wb.Sheets('Organization Data')
masterdata_ws = template_wb.Sheets('Master Data')

#I want to access the autofilter in column 9 and simply get the contents in the autofilter list and put them in the array
filtercontents = []
thefilter = orgdata_ws.Columns(9).Autofilter
for i in thefilter:
    filtercontents.append(i)     ?????????

Upvotes: 2

Views: 3667

Answers (3)

Alexander Witte
Alexander Witte

Reputation: 215

I figured it out for anyone interested. Turns out the column I wanted to access was referenced as well as a Pivot Field in a Pivot Table. So once I was able to read the contents of that pivotfield I could then funnel it into an array (and then use that array to print out pdf invoices). Had some encoding weirdness but solved that with the setdefaultcoding function. Here is the code:

import win32com.client as win32
import sys

reload(sys)
sys.setdefaultencoding("UTF-8")

working_dir = 'C:\\invoice\\'
save_dir = 'C:\\test\\'

xl = win32.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True

template_wb = xl.Workbooks.Open(working_dir + 'Settlement report V6- TEMPLATE.xlsm')

#Worksheets
settlements_ws = template_wb.Sheets('Settlement')
orgdata_ws = template_wb.Sheets('Organization Data')
masterdata_ws = template_wb.Sheets('Master Data')

settlements_ws.Activate()

agencies = []

def maxrow(sheet):
    used = sheet.UsedRange
    nrows = used.Row + used.Rows.Count - 1
    return nrows

mypivot = settlements_ws.PivotTables("PivotTable2").PivotFields("AgencyName")

for j in mypivot.PivotItems():
    j = str(j)
    if j == "#N/A":
        continue
    else:
        j = j.replace("\xc2\xa0","")
        agencies.append(j)
print agencies

#Looping through agencies and saving PDFs
for i in agencies:
    settlements_ws.Cells(8,3).Value = i
    print settlements_ws.Cells(8,3).Value
    settlements_ws.ExportAsFixedFormat(0, save_dir + i + '.pdf')

print "Finished!"

Upvotes: 4

Raphaël Gomès
Raphaël Gomès

Reputation: 1028

You're trying to iterate over the method reference Autofilter, and not its return value(s) Autofilter(). By adding the brackets, you call the method. Without the brackets, you just have a reference to that method.

Upvotes: 0

jeremyforan
jeremyforan

Reputation: 1437

I remember my first script....

are you married to win32?

https://mail.python.org/pipermail/python-list/2011-October/613213.html

Python - How to turn-on Excel Auto Filter on cells in non-first row?

Upvotes: 0

Related Questions