gpt4 book ai didi

c# - 尝试使用 c# 按两列对 excel 范围进行排序

转载 作者:行者123 更新时间:2023-11-30 13:46:49 24 4
gpt4 key购买 nike

我在 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 抛出 “排序引用无效。确保它在您要排序的数据内,并且第一个排序By box 不相同或为空。”

最佳答案

这对我有用:

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# - 尝试使用 c# 按两列对 excel 范围进行排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19529436/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com