使用C#对Excel中的列进行排序 [英] Using C# to sort a column in Excel

问题描述

我正在尝试使用INTEROP按excel中的第一列对工作表进行排序.

I am trying to sort a worksheet by the first column in excel using INTEROP.

我只想要第一列的整个范围的简单排序.我正在执行以下操作:

I just want a simple sort of the entire range by the first column. I am doing the following:

valueRange.Sort(valueRange.Columns[7, Type.Missing], Excel.XlSortOrder.xlAscending, valueRange.Columns[7, Type.Missing],
                Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, 
                Excel.XlYesNoGuess.xlNo, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, 
                Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, 
                Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);

但是出现错误.我找不到有关如何进行此排序的适当文档.

But getting errors. I am unable to find proper documentation on how to do this sorting.

有人可以给我一个通过特定列指定范围的简单示例的工作示例吗?

Could someone please give me a working example of a simple sort of a specified range by a specific column?

根据文档，我试图这样做:

as per documentation I tried to do this:

valueRange.Sort(valueRange.Columns[7, Type.Missing],
                        Excel.XlSortOrder.xlAscending,
                        Type.Missing,
                        Type.Missing,
                        Excel.XlSortOrder.xlAscending,
                        Type.Missing,
                        Excel.XlSortOrder.xlAscending,
                        Excel.XlYesNoGuess.xlNo,
                        Type.Missing,
                        Type.Missing,
                        Excel.XlSortOrientation.xlSortColumns,
                        Excel.XlSortMethod.xlStroke,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal);

但是现在我遇到了错误:

However right now I am getting the errors:

{排序参考无效.请确保它在您要排序的数据中，并且第一个排序依据"框不相同或为空白."}

{"The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."}

推荐答案

为了按该范围内的单个列对范围进行排序，您可以执行以下操作(如果使用的是VS 2010及更高版本，请使用动态"关键字):

In order to sort a range by a single column in that range, you can do something like the following (if you are using VS 2010 and above with the "dynamic" keyword):

dynamic allDataRange = worksheet.UsedRange;
allDataRange.Sort(allDataRange.Columns[7], Excel.XlSortOrder.xlDescending);

在我的示例中，我有一个包含约10列的电子表格，我想按第7列的降序对整个电子表格进行排序.

In my example, I had a spreadsheet with 10 or so columns, and I wanted to sort the entire spreadsheet by the 7th column, in descending order.

上面的答案为我提供了帮助，但是当我尝试Code4Life的代码段时:

I was helped by the above answer, but when I tried Code4Life's snippet:

dynamic valueRange = GetTheRange();
valueRange.Columns.get_Item(1)).Sort(valueRange.Columns[1]);

它仅对范围的第一列进行了排序. OP要求按一列对整个范围进行排序，而不是对范围内的一列进行排序.因此，经过一番尝试和错误后，我得到了上面的简化代码.

it only sorted the first column of the range. The OP asked for sorting an entire range by one column, not sorting one column in a range. So after a little trial and error I got my above simplified code.

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