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."

Is this not your actual code? Based on this it looks like you are sorting a blank Sheet? — user2140261
– user2140261, Commented Oct 22, 2013 at 22:45
I forgot to copy the section where I declare my sheet, I edited my post to include this section.I declare a tmpSheet variable and I use that in a loop to check for three specific sheets which I then delete if present. Next I check if the tmpSheet = "Job Cost" which is the sheet were the code needs to execute and I then make my JobDataSheet variable = my tmpSheet. — Cornelius
– Cornelius, Commented Oct 23, 2013 at 14:30

user2140261 · Accepted Answer · 2013-10-23 16:25:42Z

10

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);
    }

edited Oct 23, 2013 at 16:25

answered Oct 23, 2013 at 16:19

user2140261

8,0037 gold badges35 silver badges45 bronze badges

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

3 Comments

user2140261 Over a year ago

@Cornelius Glad to here! Feel free to Accept this answer as the answer to you question by clicking the Checkmark next to the answer!

Si8 Over a year ago

I added the following at the end oWB.Save();oWB.Close(); to save the updated sheet with sort and close the Excel sheet but when I go back to open it, I get the following error:

Excel found unreadable content in 'file.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

If I click yes, it does open and displays the Excel file with the sort. Any idea why and how to prevent it? Thanks. +1 btw for the code to work.

Si8 Over a year ago

If I press Yes and open and save it as something else, the new file opens just fine. I wonder why the issue with the first file. Any help would be appreciated. Thank you.

Collectives™ on Stack Overflow

Trying to sort excel range by two columns using c#

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related