To sort across multiple columns in Excel using C#

Question

I need to sort across multiple columns in Excel using C#. I am using Microsoft.Office.Interop.Excel for doing this. But the Range.Sort allows me to sort across only three columns. I want to sort across more than three columns? Is there a way that I can use the Excel.Range.Sort method to sort across more than three columns?

Answer 1

Before Excel 2007 you were limited to 3 sort keys - Range.Sort won't let you use more. If that's your target version then you'll need to get creative: a couple of possible ideas being to pull the data into your C# code and sort it there, or build an extra worksheet column containing the concatenated keys, then apply Sort as normal.

If you're using Excel 2007 or later exclusively, then there's a much more flexible sorting capability available: Worksheet.Sort. I built a 30x10 table of random numbers in A1:J30 and recorded a macro that sorted on the first five columns. This is what I got (after cleaning and de-duplicating the code somewhat):

With ActiveWorkbook.Worksheets("Sheet1").Sort
    With .SortFields
        .Clear
        .Add Key:=Range("A1:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("B1:B30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("C1:C30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("D1:D30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("E1:E30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    .SetRange Range("A1:J30")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

That should be fairly easy to apply to your C# code...