Sorting Excel Rows using Excel Interop C#

Question

I have a C# program that is out putting results to an excel spread sheet. Each row contains the information for a competitor including several fields such as name, id number, address, score, etc with each being in a different column. I want to sort all of these competitors (so I want to sort these rows) based upon the score with the records being sorted descendingly from highest to lowest. What is the best way to go about this? Here is the code I am trying which is not working.

 Excel.Range sortRange;
                sortRange = worksheet.get_Range("A14", "K32");
                Excel.Range scoreColumn;
                scoreColumn = worksheet.get_Range("C14", "C32");
                sortRange.Sort(scoreColumn, Excel.XlSortOrder.xlDescending,

Does `scoreColumn = worksheet.get_Range("C14", "C14")` make it work? — lc., Oct 17 '12 at 14:23
Well I need to sort based on all of the scores. Which will be from C14 to C32 — user1546315, Oct 17 '12 at 14:26
Yes I know, it's just the documentation http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.sort.aspx leads me to believe if you specify one cell it automatically expands downward for you. Just figured since you have the code right there you could test it faster than I. — lc., Oct 17 '12 at 14:32
For your code to be in C#, please refer to the following link: http://stackoverflow.com/questions/19529436/trying-to-sort-excel-range-by-two-columns-using-c-sharp — Humbert, Mar 23 '15 at 15:26
For your code to be in C#, please refer to the following link: http://stackoverflow.com/questions/19529436/trying-to-sort-excel-range-by-two-columns-using-c-sharp — Humbert, Mar 23 '15 at 15:27
For your code to be in C#, please refer to the following link: [trying to sort excel range by two columns using c sharp][1] [1]: http://stackoverflow.com/questions/19529436/trying-to-sort-excel-range-by-two-columns-using-c-sharp — Humbert, Mar 23 '15 at 15:31

score 2 · Answer 1 · answered Nov 05 '12 at 10:12

Ah Hah, I have just done this, sadly the code is in VB.NET, let me know if you have any difficulty translating into C#

        Dim lastrow As Integer = 0
        lastrow = xlsSheet.UsedRange.Row + xlsSheet.UsedRange.Rows.Count - 1


        Dim myRange = xlsSheet.range("A2", "Q" & lastrow)
        myRange.Select()
        xlsApp.ActiveWorkbook.Worksheets("your_work_sheet_name").Sort.SortFields.Add(Key:= _
    xlsSheet.Range("A2:A" & lastrow), Order:=Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending)
        xlsApp.ActiveWorkbook.Worksheets("your_work_sheet_name").Sort.SortFields.Add(Key:= _
    xlsSheet.Range("B2:B" & lastrow), Order:=Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending)

         With xlsApp.ActiveWorkbook.Worksheets("your_work_sheet_name").Sort
            .SetRange(myRange)
            .Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes
            .MatchCase = False
            .Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns
            .SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin
            .Apply()
        End With

When I go to save it and then try opening the file, it says the file is corrupted and have to be recovered. However, the sort is reflected. I have to open it and save it as a different filename before it doesn't give the message of it being corrupted. Any idea? — Si8, Dec 16 '16 at 11:13

Xavave · Answer 2 · 2017-01-06T11:32:28.143

~~this worked for me , but I had to add a table with EPPLUS for this line to work:~~

sheet.AutoFilter.Sort.SortFields.Add(oRange, XlSortOrder.xlAscending);</s>

   <s> // add the excel table entity with EPPLUS (available on Nuget)
var table = ws.Tables.Add(range, "table1");
table.TableStyle = OfficeOpenXml.Table.TableStyles.Light2;

I don't add a table anymore, just setting autofilter with epplus for sheet.AutoFilter not to be NULL on larger Excel files

      var colindexM = ws.Cells["M5"].Start.Column;
                        var endcell = ws.Cells[ws.Dimension.End.Row, ws.Dimension.End.Column];
                        var range = ws.Cells[4, 2, endcell.Start.Row, endcell.Start.Column];
                        range.AutoFilter = true;

then:

     //open workbook
    workBook = oXL.Workbooks.Open(outputfilepath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    //oXL.Visible = false;
    Worksheet sheet = workBook.Sheets["Rapport1"];
    // set Date Format to the sort column 
    Range rg = sheet.Cells[5, colindexM];
    rg.EntireColumn.NumberFormat = "DD/MM/YYYY";

    sheet.EnableAutoFilter = true;
  sheet.AutoFilter.Sort.SortFields.Add(rg, XlSortOn.xlSortOnValues, XlSortOrder.xlAscending,"", XlSortDataOption.xlSortTextAsNumbers );        
    sheet.AutoFilter.ApplyFilter();    

    workBook.Save();

    oXL.Quit();

updated after Jan 'splite' K answer

Welcome to Stack Overflow! :) I formatted your answer (4x space = code) Also, you dont have to call `GC.Collect()` and OP is asking for Excel Interop, not about EPPlus (but fine answer anyway) — Jan 'splite' K., Jan 06 '17 at 08:52
thank you! on a bigger excel data file, my solution was not working because sheet.AutoFilter was null and `sheet.EnableAutoFilter = true;` was not setting autofilter so I did that trick (setting autofilter with epplus before calling interop ) and now it works fine , with EPPLUS mixed with Interop: `var endcell = ws.Cells[ws.Dimension.End.Row, ws.Dimension.End.Column]; var colindexM = ws.Cells["M5"].Start.Column; var range = ws.Cells[4, 2, endcell.Start.Row, endcell.Start.Column]; range.AutoFilter = true;` — Xavave, Jan 06 '17 at 09:53

score 0 · Accepted Answer · edited May 23 '17 at 11:47

0

I would move away from using the Excel Interop class.
There are multiple APIs already out that do a great job.

Create Excel without Interop

Another great SO post is reading files. Which could point you in the right direction of writing files using non interop C# classes.

.net - Reading Excel files

edited May 23 '17 at 11:47

Community

1
1

answered Oct 17 '12 at 14:27

Samir Banjanovic

400
1
4
16

I appreciate the suggestion but I have put hours and thousands of lines of code into this program using the Excel Interop so at this point it is too late to change that. – user1546315 Oct 17 '12 at 14:29
Ah yes...I see. I wasn't aware the code was that mature. Is there no call to use the Excel filter function? – Samir Banjanovic Oct 17 '12 at 14:42
1

You should probably mark @Quannt answer as the right answer as mine doesn't help. – Samir Banjanovic Nov 05 '12 at 20:16

score 0 · Answer 4 · edited May 23 '17 at 10:30

using System.Runtime.InteropServices;
...    

    /// <summary>
    /// <para>Customized function for Range property of Microsoft.Office.Interop.Excel.Worksheet</para>
    /// </summary>
    /// <param name="WS"></param>
    /// <param name="Cell1"></param>
    /// <param name="Cell2"></param>
    /// <returns>null if Range property of <paramref name="WS"/> throws exception, otherwise corresponding range</returns>
    public static Range GetRange(this Worksheet WS, object Cell1, [Optional] object Cell2)
    {
        try
        {
            return WS.Range[Cell1: Cell1, Cell2: Cell2];
        }
        catch (Exception ex)
        {
            return null;
        }
    }

    ...

    Excel.Range sortRange = worksheet.GetRange("A14", "K32");
    Excel.Range scoreColumn = worksheet.GetRange("C14", "C32");

    // for this particular case, this check is unuseful
    // but if you set you scoreColumn as:
    // Excel.Range scoreColumn = worksheet.GetRange("COMPETITORS[[#Data], [SCORE]]");
    // you will have scoreColumn as null if COMPETITORS table has no column named "SCORE"
    if (sortRange != null && scoreColumn != null)
    {
        sortRange.Sort.SortFields.Clear();
        sortRange.Sort.SortFields.Add(scoreColumn,
                                      Excel.XlSortOn.xlSortOnValues, 
                                      Excel.XlSortOrder.xlDescending, 
                                      Type.Missing, 
                                      Excel.XlSortDataOption.xlSortNormal);
        sortRange.Sort.Header = XlYesNoGuess.xlYes;
        sortRange.Sort.MatchCase = false;

        // avoid setting this when you're dealing with a table
        // the only available option is xlSortColumns
        // if you apply a sort to a table and you set XlSortOrientation.xlRows, 
        // you will get an exception
        // see https://stackoverflow.com/questions/13057631/f-excel-range-sort-fails-or-rearranges-columns
        //sortRange.Sort.Orientation = XlSortOrientation.xlSortColumns;

        sortRange.Sort.SortMethod = XlSortMethod.xlPinYin;
        sortRange.Sort.Apply();
    }

Further reading:
1) F# Excel Range.Sort Fails or Rearranges Columns

Sorting Excel Rows using Excel Interop C#

4 Answers4