CODEWITHSUNDEEP
c#excelsortingexcel-interop
user1546315
user1546315

Reputation: 703

Sorting Excel Rows using Excel Interop C#

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,

Upvotes: 2

Views: 8832

Answers (4)

Xavave
Xavave

Reputation: 685

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

Upvotes: 2

Michael
Michael

Reputation: 659

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

Upvotes: 0

Quannt
Quannt

Reputation: 2075

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

Upvotes: 2

Samir Banjanovic
Samir Banjanovic

Reputation: 400

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

Upvotes: 0

Related Questions