Using Microsoft.Office.Interop.Excel, How to sort rows in a worksheet?

It is very easy to sort rows using Microft.Office.Interop.Excel in a worksheet. PART I - SORTING BASED ON 1 OR 2 COLUMNS We can use the Sort method of the NamedRange object to sort the data in a worksheet. Just create a Range object i.e. Excel.Range sRng; Also, you already have a worksheet object, Worksheet SummSheet; Now assign Range to sRng , sRng = SummSheet.get_Range("A4","N" + rowNum);// here A4 is the starting cell and N"rowNum" is the ending cell Apply this sort method

sRng.Sort(sRng.Columns[1,System.Type.Missing],XlSortOrder.xlAscending, sRng.Columns[10,System.Type.Missing], System.Type.Missing,XlSortOrder.xlAscending,  System.Type.Missing,XlSortOrder.xlAscending,XlYesNoGuess.xlNo,System.Type.Missing,System.Type.Missing,XlSortOrientation.xlSortColumns,XlSortMethod.xlPinYin,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal);

In the above method we are sorting the rows as per 1st column .In case 1st column has same values the row will be sorted according to the 10th column. The Sorting Order is Ascending order. The same can be applied for Descending Order also. Only the SortOrder needs to be changed. PART II - SORTING BASED ON MORE THAN 2 COLUMNS In case you need to sort the rows based on more than 2 columns, we can implement ICOMPARABLE Interface's CompareTo method. We can have a class implement IComparable Interface and then re-define the CompareTo method. For e.g.

public class TryCompare:IComparable
{
 public string ID
 {
  get
  {
   return Did;
  }
  set
  {
   Did = value;
  }
 }
 public string QuanDiff
 {
  get
  {
   return DQuanDiff;
  }
  set
  {
   DQuanDiff = value;
  }
 }
 public string PriceDiff
 {
  get
  {
   return DPriceDiff;
  }
  set
  {
   DPriceDiff = value;
  }
 }


 public int CompareTo(object obj)
 {
  TryCompare CompareObj =(TryCompare)obj;
  int compResults = this.ID.CompareTo(CompareObj.ID);
  if(compResults==0)
  {
   if(this.QuanDiff==CompareObj.QuanDiff)
   {
    if(this.PriceDiff==CompareObj.PriceDiff)
     return 0;
    else if(this.PriceDiff>CompareObj.PriceDiff)
     return 1;
    else if(this.PriceDiff<CompareObj.PriceDiff)
                  return -1;
   }
   else if(this.QuanDiff>CompareObj.QuanDiff)
   {
    return 1;
   }
   else if(this.QuanDiff<CompareObj.QuanDiff)
              {
               return -1;
              }
  }
  return compResults;
 }

}

So in the example above , we are sorting based on 3 values. Now we just need to call , TryCompareObj.Sort(); This will solve all our sorting requirements.