Trying to sort excel range by two columns using c#

Question

I have a range in excel that I need to sort by two columns. The data will always range from Column A to Column AA and I need to first sort it by column A (which is a date column, so oldest to newest), then by column F (numeric column, smallest to highest). The number of rows will vary.

Here is what I've got so far, keep in mind I'm relatively new to c#.

         Excel.Worksheet JobDataSheet = new Excel.Worksheet();

         foreach (Excel.Worksheet tmpSheet in Globals.ThisAddIn.Application.ActiveWorkbook.Sheets)
         {
             if (tmpSheet.Name == "Job Labour" || tmpSheet.Name == "Job Materials" || tmpSheet.Name == "Job Cost Report")
             {
                 tmpSheet.Delete();
             }
             if (tmpSheet.Name == "Job Cost")
                 JobDataSheet = tmpSheet;
         }


         int MyCount = JobDataSheet.UsedRange.Rows.Count;

          //Sort Collection by Date & Ref Line
         Excel.Range tempRange = JobDataSheet.get_Range("A2:A" + MyCount);
         Excel.Range tempRange2 = JobDataSheet.get_Range("F2:F" + MyCount);

         JobDataSheet.Sort.SortFields.Clear();
         JobDataSheet.Sort.SortFields.Add(tempRange // First Key
                                           ,Excel.XlSortOn.xlSortOnValues
                                           ,Excel.XlSortOrder.xlAscending
                                           ,Type.Missing
                                           ,Excel.XlSortDataOption.xlSortNormal);
         JobDataSheet.Sort.SortFields.Add(tempRange2 // Second Key
                                           , Excel.XlSortOn.xlSortOnValues
                                           , Excel.XlSortOrder.xlAscending
                                           , Type.Missing
                                           , Excel.XlSortDataOption.xlSortNormal);

         JobDataSheet.Sort.SetRange(JobDataSheet.get_Range("A1:AA" + MyCount));
         JobDataSheet.Sort.Header = Excel.XlYesNoGuess.xlYes;
         JobDataSheet.Sort.MatchCase = false;
         JobDataSheet.Sort.Orientation = Excel.XlSortOrientation.xlSortRows;
         JobDataSheet.Sort.SortMethod = Excel.XlSortMethod.xlPinYin;
         JobDataSheet.Sort.Apply();

At the JobDataSheet.Sort.Apply(); line excel throws "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 in the same or blank."

Answer 1

This is what works for me:

private void SortExcel()
{
    //Set up
    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    Excel.Range oRng;
    Excel.Range oLastAACell;
    Excel.Range oFirstACell;

    //Start Excel and get Application object.
    oXL = new Excel.Application();
    oXL.Visible = true;

    //Get a new workbook.;
    oWB = (Excel._Workbook)(oXL.Workbooks.Open(@"C:\Book.Xlsx"));

    //Get Sheet Object
    oSheet = (Excel.Worksheet)oWB.Worksheets["Sheet1"];

    //Get complete last Row in Sheet (Not last used just last)     
    int intRows = oSheet.Rows.Count;

    //Get the last cell in Column AA
    oLastAACell = (Excel.Range)oSheet.Cells[intRows, 27];

    //Move courser up to the last cell in AA that is not blank
    oLastAACell = oLastAACell.End[Excel.XlDirection.xlUp];

    //Get First Cell of Data (A2)
    oFirstACell = (Excel.Range)oSheet.Cells[2, 1];

    //Get Entire Range of Data
    oRng = (Excel.Range)oSheet.Range[oFirstACell, oLastAACell];

    //Sort the range based on First Columns And 6th (in this case A and F)
    oRng.Sort(oRng.Columns[1, Type.Missing],Excel.XlSortOrder.xlAscending, // the first sort key Column 1 for Range
              oRng.Columns[6, Type.Missing],Type.Missing, Excel.XlSortOrder.xlAscending,// second sort key Column 6 of the range
              Type.Missing, Excel.XlSortOrder.xlAscending,  // third sort key nothing, but it wants one
              Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, 
              Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,   
              Excel.XlSortDataOption.xlSortNormal,
              Excel.XlSortDataOption.xlSortNormal, 
              Excel.XlSortDataOption.xlSortNormal);
    }