尝试使用c＃将excel范围分为两列

尝试使用c＃将excel范围分为两列

关注
发信
关注(28)粉丝(399)

尝试使用c＃将excel范围分为两列

本文介绍了尝试使用c＃将excel范围分为两列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个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 Labor|| tmpSheet.Name ==Job Materials|| tmpSheet.Name ==作业成本报告）
 {
 tmpSheet.Delete（）; 
} 
 if（tmpSheet.Name ==Job Cost）
 JobDataSheet = tmpSheet; 
} 
 
 
 int MyCount = JobDataSheet.UsedRange.Rows.Count; 
 
 //按日期& 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（）; line excel throws 排序引用无效，请确保它在您要排序的数据内，而第一个排序方式框不是相同或空白。

解决方案

这对我有用：

  private void SortExcel（）
 {
 //设置
 Excel.Application oXL; 
 Excel._Workbook oWB; 
 Excel._Worksheet oSheet; 
 Excel.Range oRng; 
 Excel.Range oLastAACell; 
 Excel.Range oFirstACell; 
 
 //启动Excel并获取Application对象。 
 oXL = new Excel.Application（）; 
 oXL.Visible = true; 
 
 //获取新的工作簿
 oWB =（Excel._Workbook）（oXL.Workbooks.Open（@C：\Book.Xlsx））; 
 
 //获取工作表对象
 oSheet =（Excel.Worksheet）oWB.Worksheets [Sheet1]; 
 
 //获取完整的最后一行（不是最后一次使用）
 int intRows = oSheet.Rows.Count; 
 
 //获取列AA中的最后一个单元格
 oLastAACell =（Excel.Range）oSheet.Cells [intRows，27]; 
 
 //将路由器移动到AA中不为空的最后一个单元格
 oLastAACell = oLastAACell.End [Excel.XlDirection.xlUp]; 
 
 //获取数据的第一个单元格（A2）
 oFirstACell =（Excel.Range）oSheet.Cells [2，1]; 
 
 //获取整个数据范围
 oRng =（Excel.Range）oSheet.Range [oFirstACell，oLastAACell]; 
 
 //根据第一列和第六列（本例中为A和F）对范围进行排序
 oRng.Sort（oRng.Columns [1，Type.Missing]，Excel.XlSortOrder。 xlAscending，//第一个排序键范围
 oRng.Columns [6，Type.Missing]，Type.Missing，Excel.XlSortOrder.xlAscending，//第二个排序键的列1范围
 Type.Missing，Excel.XlSortOrder.xlAscending，//第三个排序键没有，但它需要一个
 Excel.XlYesNoGuess.xlGuess，Type.Missing，Type.Missing，
 Excel.XlSortOrientation.xlSortColumns， Excel.XlSortMethod.xlPinYin，
 Excel.XlSortDataOption.xlSortNormal，
 Excel.XlSortDataOption.xlSortNormal，
 Excel.XlSortDataOption.xlSortNormal）; 
}


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

解决方案

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

这篇关于尝试使用c＃将excel范围分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-23 21:57
Powered by LMLPHP ©2024 尝试使用c＃将excel范围分为两列 0.032443