Using C# to sort a column in Excel

Question

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."}

Answer 1

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);

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.

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]);

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.

Answer 2

Try this:

((Excel.Range)valueRange.Columns.get_Item(1, Type.Missing))
    .Sort(valueRange.Columns[1, Type.Missing],
    Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, 
    Excel.XlSortOrder.xlAscending, Type.Missing,
    Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlGuess, 
    Type.Missing, Type.Missing, 
    Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,   
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);

Basically, do your Sort from the Column, not the base range.

Also, I would strongly recommend using Visual Studio 2010 if you can. The above code gets simplified down to this in VS 2010:

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

EDIT: If you need to sort across multiple columns, Excel allows you to sort on up to three columns. Here's how you would do it:

valueRange.Sort(valueRange.Columns[1, Type.Missing], // the first sort key
    Excel.XlSortOrder.xlAscending, 
    valueRange.Columns[2, Type.Missing], // second sort key
    Type.Missing, Excel.XlSortOrder.xlAscending, 
    valueRange.Columns[3, Type.Missing], // third sort key
    Excel.XlSortOrder.xlAscending, 
    Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, 
    Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,   
    Excel.XlSortDataOption.xlSortNormal,
    Excel.XlSortDataOption.xlSortNormal, 
    Excel.XlSortDataOption.xlSortNormal);

EDIT2: Loading values into a 2D array:

var myArray = (object[,])valueRange.Value2;

Loading the array back into the range:

var arrayCount = myArray.GetLength(0);
var columnCount = GetTheColumnCountHere();
valueRange = valueRange.get_Resize(arrayCount, columnCount);
valueRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, myArray);