从excel中获取最小和最大日期。c#

I am trying to return the minimum and maximum dates in a column in an excel sheet. i have a program that i created in VBA, that i am rewriting in C# and am not quite sure on this part. The original code i used was;

我正在尝试返回excel表中列中的最小和最大日期。我在VBA中创建了一个程序，我在c#中重写，在这部分我不太确定。我使用的原始代码是;

WB.Activate
    DataInputRows = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count
    ActiveSheet.Range("S2:T" & Cells(Rows.Count, "A").End(xlUp).Row).NumberFormat = "dd/mm/yyyy"
    ActiveSheet.Range("V2:V" & Cells(Rows.Count, "A").End(xlUp).Row).NumberFormat = "dd/mm/yyyy"
    ActiveSheet.Range("A1:" & LastColumn & Cells(Rows.Count, "A").End(xlUp).Row).Sort _
        Key1:=Range("T1"), Header:=xlYes

SYear = Format(WorksheetFunction.Min(Range("T1:T" & DataInputRows)), "dd/MM/yyyy")
EYear = Format(WorksheetFunction.Max(Range("T1:T" & DataInputRows)), "dd/MM/yyyy")

I have the sort and format area covered, but i cant quite get the "SYear =" or "EYear = " bit nailed down.

我已经介绍了排序和格式区域，但是我不能很好地确定“SYear =”或“EYear =”。

Does anyone know a "Simple" way of finding the minimum and maximum value from a column?

有人知道从一列中找到最小值和最大值的“简单”方法吗?

The code i have in c# so far is;

到目前为止，c#中的代码是;

                            DataInputRows = InputSheet.UsedRange.Rows.Count;
                        Excel.Range rng = (Excel.Range)OutputSheet.Cells[2, 19];
                        rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                        rng = (Excel.Range)OutputSheet.Cells[2, 20];
                        rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                        rng = (Excel.Range)OutputSheet.Cells[2, 22];
                        rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                        SourceRange.Sort(SourceRange.Columns[20, Type.Missing], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);

                        var SYear = (This is where i need the minimum value of column T (20))

GOT IT!!

明白了! !

                    DataInputRows = InputSheet.UsedRange.Rows.Count;
                    Excel.Range rng = (Excel.Range)InputSheet.Cells[2, 19];
                    rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                    rng = (Excel.Range)InputSheet.Cells[2, 20];
                    rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                    rng = (Excel.Range)InputSheet.Cells[2, 22];
                    rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                    SourceRange.Sort(SourceRange.Columns[20, Type.Missing], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);


                    Excel.WorksheetFunction wsf = ObjApp.WorksheetFunction;

                    rng = (Excel.Range)InputSheet.Cells[2, 20];
                    var SYear = wsf.Min(rng);
                    DateTime dt = DateTime.FromOADate(SYear);
                    MessageBox.Show(dt.ToString());

Thank you to Ralph for the jumpstart.

谢谢拉尔夫的帮助。

1 个解决方案

#1

So @Ralph got me thinking. I was pretty sure it shouldnt be as difficult as it seems, and the commands should be similar enough. so i did a little digging and found where i was going wrong (or more specifically where i wasnt going). edited my code to show the working variant in case anyone needs it in future and +1'd Ralph for the kick in the brain. :)

所以@Ralph让我思考。我很确定它不应该像看起来那样困难，而且命令应该足够相似。所以我做了一些挖掘，发现我哪里做错了(或者更具体地说，我没有去哪里)。编辑了我的代码，以显示工作变异体，以防将来有人需要它，并+ 1d Ralph来刺激大脑。:)

DataInputRows = InputSheet.UsedRange.Rows.Count;
                Excel.Range rng = (Excel.Range)InputSheet.Cells[2, 19];
                rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                rng = (Excel.Range)InputSheet.Cells[2, 20];
                rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                rng = (Excel.Range)InputSheet.Cells[2, 22];
                rng.EntireColumn.NumberFormat = "dd/MM/yyyy";
                SourceRange.Sort(SourceRange.Columns[20, Type.Missing], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);


                Excel.WorksheetFunction wsf = ObjApp.WorksheetFunction;

                rng = (Excel.Range)InputSheet.Cells[2, 20];
                var SYear = wsf.Min(rng);
                DateTime dt = DateTime.FromOADate(SYear);
                MessageBox.Show(dt.ToString());