Reputation: 81
I am trying to sort a spreadsheet using openpyxl and Python. I have read the documents and I don't quite understand this page. I am expecting it to either add the auto filter dropdown arrows or sort my spreadsheet and it is returning errors. Here's my code
wb = openpyxl.load_workbook('report.xlsx')
ws = wb.active
ws['A2'] = "Store"
ws['B2'] = "Manager"
ws['C2'] = "Zone"
ws.column_dimensions.group('F','DU',hidden=True)
#ws.AutoFilter.add_sort_condition('C:C')
wb.save("report.xlsx")
According to the documents it looks like the line "ws.AutoFilter.add_sort_condition('C:C')" should give me the result I want. (Yes I understand it is currently a comment line. The rest of my code runs fine without that line so I commented it.)
When I have that line in the code I get the error - 'Worksheet' object has no attribute 'AutoFilter' but according to the documents it looks like it does. http://openpyxl.readthedocs.org/en/latest/_modules/openpyxl/worksheet/filters.html#AutoFilter.
If anyone can help explain to me why it is failing or what the documents mean that would be great.
This statement in the documents is particularly confusing to me:
"Don't create auto filters by yourself. It is created by :class:
~openpyxl.worksheet.Worksheet
. You can use via :attr:~~openpyxl.worksheet.Worksheet.auto_filter
attribute."
because I tried that too and it also failed.
Update: @crussell's reply worked in that it added the auto filter to my spreadsheet. However, it is still not adding the sort condition to the appropriate column.
Upvotes: 5
Views: 22134
Reputation: 61
According to the documentation openpyxl can define the filter and/or sort but does not apply them!
They can only be applied from within Excel
Upvotes: 6
Reputation: 51
I don't have a full answer for this, but I did find something interesting when using filters.
I added a filter column,
eg:
ws.auto_filter.add_filter_column(1,{},True)
then I opened the resulting spreadsheet. The column showed the filter! But the data was not actually filtered in the spreadsheet. I had to click on the "Data" tab and click "Reapply" the filter.
So it looks like the adding of a sort or filter column works, except it never actually applies the sort or filter.
I have been hunting down a function that will apply the filter, but have not yet had any luck. If anyone has thoughts, I'd love to hear them!
Thanks!
Upvotes: 5
Reputation: 179
The auto_filter command returns the AutoFilter object, so in a sense they are the same thing.
What you need is ws.auto_filter.ref = 'C1:C20'
with the range of cells those of which you want to filter.
Upvotes: 5
Reputation: 51
After looking at the documentation and the source code for the AutoFilter.add_sort_condition() function it looks like the ref you're providing may need to be changed to include row indices, like "C1:C120", for example. Have you tried it with specific row numbers? Also, be sure to take a look at the comment regarding the ref variable right below the function declaration in:
http://openpyxl.readthedocs.org/en/latest/_modules/openpyxl/worksheet/filters.html#AutoFilter
if you're not following where I'm coming from. Cheers and good luck!
Upvotes: 1