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.
– lc., Commented Oct 17, 2012 at 14:23
Well I need to sort based on all of the scores. Which will be from C14 to C32 — user1546315
– user1546315, Commented Oct 17, 2012 at 14:26
Yes I know, it's just the documentation msdn.microsoft.com/en-us/library/… 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.
– lc., Commented Oct 17, 2012 at 14:32
For your code to be in C#, please refer to the following link: stackoverflow.com/questions/19529436/… — Humbert
– Humbert, Commented Mar 23, 2015 at 15:26
For your code to be in C#, please refer to the following link: stackoverflow.com/questions/19529436/… — Humbert
– Humbert, Commented Mar 23, 2015 at 15:27

Quannt · Accepted Answer · 2012-11-05 10:12:06Z

2

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

answered Nov 5, 2012 at 10:12

Quannt

2,0752 gold badges22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Si8 Over a year ago

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?

Xavave · Accepted Answer · 2017-01-06 11:32:28Z

2

~~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

edited Jan 6, 2017 at 11:32

answered Jan 6, 2017 at 8:24

Xavave

69511 silver badges16 bronze badges

2 Comments

Jan 'splite' K. Over a year ago

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)

Xavave Over a year ago

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;

Community · Accepted Answer · 2017-05-23 11:47:35Z

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, 2017 at 11:47

CommunityBot

11 silver badge

answered Oct 17, 2012 at 14:27

Samir Banjanovic

4101 gold badge4 silver badges16 bronze badges

3 Comments

user1546315 Over a year ago

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.

Samir Banjanovic Over a year ago

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 Over a year ago

You should probably mark @Quannt answer as the right answer as mine doesn't help.

Community · Accepted Answer · 2017-05-23 10:30:37Z

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

Collectives™ on Stack Overflow

Sorting Excel Rows using Excel Interop C#

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related