将数据写入Excel时如何设置正确的日期格式

Question

我使用 Office 互操作将数据表导出到 Excel 文件。问题是，Excel 本身不识别日期，而是显示数字。在另一种情况下，我传递一个字符串，然后它将其识别为日期。在这两种情况下，数据都是混乱的。

我尝试了 NumberFormat @ ，它应该以文本格式存储单元格，但它也不起作用。

Application app = new Application();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
app.EnableAnimations = false;
app.EnableAutoComplete = false;
app.EnableSound = false;
app.EnableTipWizard = false;
app.ErrorCheckingOptions.BackgroundChecking = false; 

Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];

for (int j = 0; j < dt.Rows.Count; j++)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        Range rng = ws.Cells[j+2, i+1]as Range;
        rng.Value2 = dt.Rows[j][i].ToString();
        rng.NumberFormat = "@";
    }   
}           

wb.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
       Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

wb.Close(false, Missing.Value, Missing.Value);            

app.Workbooks.Close();
app.Application.Quit();
app.Quit();    
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
ws = null;
wb = null;
app = null;
GC.Collect();

为什么我的 NumberFormat @ 不起作用？ Textformat 显示的所有内容不应该与我输入的内容相同吗？

Answer 1

您是否尝试将整个列格式化为日期列？像这样的事情：

Range rg = (Excel.Range)worksheetobject.Cells[1,1];
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";

您可以尝试的另一件事是在将文本加载到 Excel 单元格之前在字符串表达式之前打一个勾（不确定这是否重要，但在直接将文本键入单元格时它可以工作）。

Answer 2

尝试使用

DateTime.ToOADate()

并将其作为双精度值放入单元格中。 Mac 系统上的 Excel 可能会出现问题（它使用不同的日期时间 --> 双重转换），但它在大多数情况下应该可以正常工作。

希望这有帮助。

Answer 3

这对我有用：

sheet.Cells[currentRow, ++currentColumn] = "'" + theDate.ToString("MM/dd/yy");

注意日期之前添加的刻度线。

Answer 4

要按 Excel 单元格中的代码日期格式化，请尝试以下操作：

Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];

rg.NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

此后，您可以将日期时间值设置为特定单元格

xlWorkSheet.Cells[numberRow, numberColumn] = myDate;

如果您想设置整个列，请尝试以下操作：
Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];

rg.EntireColumn.NumberFormat = 
    CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

Answer 5

我知道这个问题很老了，但是通过 VSTO 用日期填充 Excell 单元格有一些问题。

• 格式化整个列不对我有用。

• 即使是微软的这种方法也不起作用：http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.numberformat.aspx

我发现公式不适用于 yyyy-mmm-dd 格式的日期 - 即使单元格是日期格式！您必须将日期转换为 dd/mm/yyyy 格式才能在公式中使用。

例如，我从 SQL Analysis Server 返回的日期，我必须翻转它们，然后格式化它们：

using (var dateRn = xlApp.Range["A1"].WithComCleanup())
{
    dateRn.Resource.Value2 = Convert.ToDateTime(dateRn.Resource.Value2).ToString("dd-MMM-yyyy");
}


using (var rn = xlApp.Range["A1:A10"].WithComCleanup())
{
    rn.Resource.Select();
    rn.Resource.NumberFormat =  "d-mmm-yyyy;@";
}

否则使用日期的公式将不起作用 - 单元格 C4 中的公式与 C3 相同：

Answer 6

稍微扩展一下@Assaf的答案，为了正确应用格式，我还必须在格式生效之前通过.ToOADate()函数转换DateTime。您可以逐个单元格地执行此操作：

xlWorkSheet.Cells[Row, Col].NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();

或者您可以将格式应用于整个列：

xlWorkSheet.Cells[Row, Col].EntireColumn.NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();

Answer 7

老问题但仍然相关。我生成了一个字典，它为每个区域获取适当的日期时间格式，这是我生成的帮助器类：

https://github.com/anakic/ExcelDateTimeFormatHelper/blob/master/FormatHelper.cs

FWIW这就是我的做法：

1. 打开Excel，手动在第一个单元格中输入日期时间工作簿
2. 在控制面板中打开区域对话框，
3. 使用 Spy 找出区域组合框和应用按钮的 HWND，以便我可以使用 SetForegroundWindow 和 SendKey 来更改区域（无法找到如何通过 Windows API 更改区域
4. ）所有区域以及每个区域向 Excel 询问包含日期的单元格的 NumberFormat，并将该数据保存到文件中

Answer 8

这对我有用：

hoja_trabajo.Cells[i + 2, j + 1] = fecha.ToString("dd-MMM-yyyy").Replace(".", "");

Answer 9

希望这有帮助

private bool isDate(Range cell)
    {
        if (cell.NumberFormat.ToString().Contains("/yy"))
        {
            return true;
        }
        return false;
    }

isDate(worksheet.Cells[irow, icol])

Answer 10

尝试这个解决方案，在我的软件中工作得很好：

if (obj != null)
{
    if (obj is DateTime)
    {
        if (DateTime.MinValue == ((DateTime)obj))
        {

            xlWorkSheet.Cells[x,y] = String.Empty;

        }
        else
        {

            dynamic opp = ((DateTime)obj);
            xlWorkSheet.Cells[x,y] = (DateTime)opp;

        }
    }
}

Answer 11

这是一个旧线程。到了这个时候，人们要么使用 OpenXML。 OpenXML 好多了。好吧，许多像我这样的人都陷入困境，因为最初的开发人员使用互操作性。

我也经历了同样的挣扎几个小时。我已经尝试过这里的所有内容和其他用法。它仍然给了我数字表示。

然后我发现风格是我定的。风格属性毁了一切。我刚刚添加了 NumberFormat 属性，

这就是我所做的。有用

                //set the font style and size
                Excel.Style styleDate = MyBook.Styles.Add("StyleDate");
                styleDate.NumberFormat = "mm/dd/yyyy";//remember to include this when setting style property
                styleDate.Font.Size = 10;
                styleDate.Font.Name = "Arial"

                //the function will return datetime value from database or whatever
                DateTime DtVal= GetdatetimeVal(); 

                xlWorkSheet.Cells[Row, Col].Style = styleDate
                xlWorkSheet.Cells[Row, Col] = DtVal;

Answer 12

这取决于最后的问题。就我而言，我两者都有，这需要两种解决方案。
看一下下图，在元素 [4, 3] 中我有一个通用的 Date，所以通用的 C# 方法 DateTime.TryParse(formatted_target, out temptime);工作正常。

元素 [4, 5] 和[4, 6]，显示在 Excel 端，仅包含时间和自定义格式（hh:mm，因此我们只能在 Excel 端的单元格中看到时间，但单元格本身包含完整的日期和时间对象） ，所以我们必须考虑到它们是用OLE自动化格式表达的，所以我们必须解析它们，所以之前将字符串解析为double； DateTime.FromOADate(Double.TryParse(item[4, 5]) 工作正常。

Answer 13

就我而言，我得到了文本格式的日期（dd/MM/yyyy）。
当我将它传递给 Excel 时，有时它没问题，但有时它会变成 M​​M/dd/yyyy（当日期小于 12 时）。经过几次尝试和失败后，我找到了一种可行的方法。

我将日期作为字符串传递给 Excel 的格式是 yyyy/MM/dd。

这里有一些有用的代码示例。如何检查字符串是否为日期以及如何重新格式化它。

    DateTime parsed;
    String dtAsStr = "03/01/2022";
    if (string.IsNullOrWhiteSpace(dtAsStr) == false
        && dtAsStr.Split('/').GetUpperBound(0) == 2
        && DateTime.TryParse(dtAsStr, out parsed))  // looks like a date
...
    parsed.ToString("yyyy/MM/dd"); // this is the way i found for excel to correctly understand a date