尝试使用C#按两列对Excel范围进行排序

Question

尝试使用C#按两列对Excel范围进行排序

4

我有一个需要按两列排序的Excel范围。数据始终从A列到AA列，我需要首先按A列排序（这是一个日期列，从旧到新），然后按F列排序（数字列，从小到大）。行数会有所变化。

以下是我目前的代码，需要注意我相对于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();

JobDataSheet.Sort.Apply();这一行代码出现错误，Excel提示："排序引用无效。请确保它在您要排序的数据范围内，并且第一个排序依据框不与相同或空白。"

- Cornelius

1

这不是你的实际代码吗？根据此看来，你是在对一张空白表格进行排序？ - user2140261

我忘记复制声明表格的部分，我编辑了我的帖子以包括这个部分。我声明了一个tmpSheet变量，并在循环中使用它来检查三个特定的表格，如果存在则删除。接下来，我检查tmpSheet =“Job Cost”，这是代码需要执行的表格，然后将我的JobDataSheet变量=我的tmpSheet。 - Cornelius

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user2140261 · Accepted Answer

这是对我有效的方法：

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