c# - 如何在 C# 中将 Excel 值转换为日期时间格式

Question

我正在尝试使用 Excel Interop 从 Excel 工作表中提取数据。此处使用范围内的单元格包含日期/时间值。我已设法将值作为文本获取，但如何将其转换为可用于比较操作的数字或日期/时间格式？

using System;
using Excel = Microsoft.Office.Interop.Excel;
class Program
{
    static void Main(string[] args)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        string str;
        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open("Wind_Speed.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;

        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                // here I can get a string value... how to make it date/time
                str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Text                                
                Console.WriteLine(str);
                Console.ReadLine();                       
            }
        }

        xlWorkBook.Close(true, null, null);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        Console.ReadLine();
    }

Answer 1

如果原始数据是 Excel 中的“日期”值(而非文本)，您应该能够提取值 并使用 DateTime.FromOADate 将其转换为日期时间方法:

double dateValue = range.Cells[rCnt, cCnt].Value2;  // not "Text"
DateTime dateTime = DateTime.FromOADate(dateValue);

另请注意，使用 Office 互操作逐个单元格地提取数据通常比将所有数据提取到一个大数组中然后在 C# 中处理该数组要慢得多:

    range = xlWorkSheet.UsedRange;
    object[,] rangeDate = (object[,])range.Value;

    for (rCnt = 0; rCnt <= Array.GetLength(rangeDate, 0) ; rCnt++)
    {
        for (cCnt = 0; cCnt <= Array.GetLength(rangeDate, 1); cCnt++)
        {
           object value = rangeDate[rCnt, cCnt] ;

           ...
        }
    }