使用C#对Excel中的多个列进行排序 [英] To sort across multiple columns in Excel using C#

问题描述

我需要使用C#对Excel中的多个列进行排序.我正在使用Microsoft.Office.Interop.Excel来执行此操作.但是Range.Sort允许我仅对三列进行排序.我想对超过三列进行排序吗?有没有一种方法可以使用Excel.Range.Sort方法对超过三列进行排序?

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?

推荐答案

在Excel 2007之前，您只能使用3个排序键-Range.Sort不允许您使用更多键.如果这是您的目标版本，那么您将需要发挥创意:一些可能的想法是将数据提取到C#代码中并在那里进行排序，或者构建一个包含连接键的额外工作表列，然后将Sort应用为正常.

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.

如果仅使用Excel 2007或更高版本，则可以使用更加灵活的排序功能:Worksheet.Sort.我在A1:J30中建立了一个30x10的随机数表，并记录了在前五列中排序的宏.这就是我得到的(在对代码进行了一些清除和重复复制之后):

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

这应该很容易应用于您的C#代码...

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

这篇关于使用C#对Excel中的多个列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！