Adding sorting for multiple columns

[adam....@gmail.com]

I'm trying to figure out how to apply sorting rules across multiple columns. I can almost get it to work, but Excel complains about the output. First, here is a modified example from the documentation example for adding filters and sorts:

----

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

data = [

    ["Fruit", "Quantity", "Color"],

    ["Kiwi", 3, "Green"],

    ["Grape", 15, "Purple"],

    ["Apple", 3, "Red"],

    ["Mango", 3, "Yellow"],

]

for r in data:

    ws.append(r)

ws.auto_filter.ref = "A1:C5"

# Individual sort conditions don't work:

#ws.auto_filter.add_sort_condition("B2:B5")

#ws.auto_filter.add_sort_condition("C2:C5")

# Neither does one big one

ws.auto_filter.add_sort_condition("B2:C5")

wb.save(r"C:\temp\filtered.xlsx")

----

I added a color column to the fruit table so that I'd have more columns to sort. I'd like to be able to sort by the quantity, and the color. I've tried adding more than one sort condition, as well as giving a sort condition spanning both columns. In Excel, I seem to actually get this, but it opens with an popup error: "We found a problem with some content in filters.xlsx. Do you want to try to recover as much as we can? If you trust the source of the workbook, click Yes."

Naturally I clicked Yes and then was able to get a recovery log that is IMO useless:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error118800_01.xml</logFileName><summary>Errors were detected in file 'C:\temp\filtered.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Sorting from /xl/worksheets/sheet1.xml part</removedRecord></removedRecords></recoveryLog>

Any ideas what I am doing wrong?

[Charlie Clark]

Am .01.2017, 18:33 Uhr, schrieb <adam....@gmail.com>:

> Any ideas what I am doing wrong?

I can't remember all the details but you might want to look at the
discussion I had with Beth about this last year. Otherwise, you should
look at reverse engineering the conditions based on what you can see in a
file created by Excel.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[John McNamara]

On Thursday, 12 January 2017 17:33:57 UTC, adam....@gmail.com wrote:

I'm trying to figure out how to apply sorting rules across multiple columns.

Hi,

Are you sure this will work? even if you overcome your issues with <sortState> and <sortCondition>.

I tried this in the past and although the filter shows up with the sorted icon overlaid on the dropdown, the data itself wasn't actually sorted. My understanding of this is that the data must be sorted in the file. Which is what happens dynamically in Excel.

So the user would still have to sort the data before writing it to the file, which sort of defeats the purpose for most people. That is why I never implemented this feature in XlsxWriter and generally tell people that it can't be done automatically.

I'd be happy to be proven wrong, though. :-)

John

 

 

[Charlie Clark]

Am .01.2017, 16:37 Uhr, schrieb John McNamara <jmcna...@gmail.com>:

> Hi,
>
> Are you sure this will work? even if you overcome your issues with
> <sortState> and <sortCondition>.
>
> I tried this in the past and although the filter shows up with the sorted
> icon overlaid on the dropdown, the data itself wasn't actually sorted. My
> understanding of this is that the data must be sorted in the file. Which
> is what happens dynamically in Excel.
>
> So the user would still have to sort the data before writing it to the
> file, which sort of defeats the purpose for most people. That is why I
> never implemented this feature in XlsxWriter and generally tell people
> that it can't be done automatically.
>
> I'd be happy to be proven wrong, though. :-)

Hiya John,

you're not wrong: the files must be sorted by an application. However,
there is still value in providing and, in our case, persisting the
settings because it means the controls are in place and can be used if
required.

[Adam Preble]

Yeap--I just wanted the controls as a nice-to-have. I'm not pressing the issue until an end user complains that they want it too. It was one of the reasons I was exporting an xlsx rather than just using the CSV format.

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/9gx8jiMG4bU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.