logo标签列表

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

c#.netexceloffice-interop
50

我使用Office Interop将DataTable导出到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难道不应该以与我输入相同的方式显示所有内容吗？

13个回答
48

你是否尝试将整个列格式化为日期列？像这样：

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

你可以尝试的另一件事是在将文本加载到Excel单元格之前，在字符串表达式之前放置一个单引号（不确定是否重要，但在直接输入文本到单元格时有效）。

单独格式化每个单元格不应该有什么区别。我该如何正确地将所有内容格式化为文本？ - codymanix
1
如果您希望Excel将该列视为日期列，则它应该具有日期格式，否则会发生奇怪的事情，这已经是您已经经历过的。但是，您可以参考我的最新编辑，了解另一个可能的解决方法。 - dcp
谢谢。不过，我可以问一下 NumberFormat 是否需要特定的格式，比如 MM/dd/yyyy 中的 MM 表示 月 而不是 分钟，即如果日期时间将被用作输入？ - Artfaith
另外，首先是否可以将默认/初始格式保存到变量中？其次，更改为上述格式。然后，导入日期数据。最后，从变量中恢复格式？ - Artfaith
@F8ER - 我建议你针对那些项目提出一个新问题，因为你所问的与原始问题非常不同。 - dcp
明白，亲爱的@dcp - Artfaith
15
尝试使用。
DateTime.ToOADate()

把它作为双精度浮点数放入单元格中。在Mac系统上，Excel可能存在问题（它使用不同的日期时间->双精度转换），但对于大多数情况应该能够正常工作。

希望这可以帮助您。

为什么我的@没有起作用？我希望所有的内容都能按照我放置的方式显示。 - codymanix
好的解决方案...其他一切都失败了！ - Sheridan
6
这对我有用：

这个方法适用于我：

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

请注意在日期前添加的勾号。

不是最美观的解决方案，但在搜索和尝试了许多无效答案后，我最终采用了这个解决方案。 - Mattkwish
我不喜欢我这样做，但它确实有效，其他方法都无法奏效。 - Daniel
也许只是我个人的看法，但是这种使用方式似乎会强制将值存储为文本而不是日期。这意味着我们无法进行日期特定的过滤。 - Martijn
4

在Excel单元格中通过代码格式化日期，请尝试以下方法：

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

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

在此之后，您可以将DateTime值设置为特定的单元格。
xlWorkSheet.Cells[numberRow, numberColumn] = myDate;

如果您想设置整个列，请尝试以下操作： Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];
rg.EntireColumn.NumberFormat = 
    CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
3
我知道这个问题很老，但通过VSTO填充Excel单元格的日期有一些陷阱。

我发现公式在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相同：
2
稍微扩展一下@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();
1

虽然这个问题已经老了，但仍然很有意义。我生成了一个字典，为每个地区获取适当的日期时间格式，这是我生成的帮助类：

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

FWIW，这是我处理的方式：

  1. 打开Excel，在工作簿的第一个单元格中手动输入日期时间
  2. 在控制面板中打开“区域”对话框
  3. 使用Spy查找区域组合框和应用按钮的HWND，以便使用SetForegroundWindow和SendKey更改区域（无法找到通过Windows API更改区域的方法）
  4. 遍历所有区域，并针对每个区域询问Excel包含日期的单元格的NumberFormat，将此数据保存到文件中
这可能不是问答者想要的答案，但我觉得它非常有帮助（但我希望有更直接的解决方案...）。感谢分享你的代码！ - habakuk
1
是的，我也在寻找一个更简单的解决方案，但花了几个小时后得出结论，没有这样的解决方案。 Excel 没有提供一种不受地区限制的日期格式化方式。我发布了这篇文章，因为被接受的答案没有考虑到这一点；它对英语和大多数其他文化来说都可以正常工作，但对于德语、法语和许多其他地区来说则会失败。是的，也许不是 OP 应得的答案，但却是他需要的答案 :) - anakic
0
在我的情况下，我得到的日期是以文本格式（dd/MM/yyyy）表示的。当我将其传递给Excel时，有时它是正确的，但有时它会变成MM/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
0

这是一个旧的帖子。到现在为止，人们要么使用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;
0
这对我有用：
hoja_trabajo.Cells[i + 2, j + 1] = fecha.ToString("dd-MMM-yyyy").Replace(".", "");
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接