如何检查Excel工作表中的列是否为日期格式？ [英] How to check the column in excel sheet is date format or not?

问题描述

我有数据表导出它到Excel。在某些版本中格式化不正确所以我的日期格式为一些数字。我需要在编码时直接将某些列格式化为datetime。有没有可能。



我的尝试：

Hi,
Im having datatable to export it to Excel. In some versions the formatting is not correct so im getting my date format as some numbers. I need to format the some columns to datetime directly in coding. Is there any possibility.

What I have tried:

worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(worksheet.Cells[rowsCount + 1, columnsCount])).Value = cells; // dataTable.AsEnumerable().Select(row => row.ItemArray).ToArray();
              worksheet.Columns.AutoFit();
              foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.Columns)
              {
                  worksheet.Columns[3].NumberFormat = "dd/MM/yyyy";
              }

推荐答案

您已经确定存在NumberFormat属性并且您知道如何设置它到了约会，所以不要只是设置它与它的格式进行比较。



这里有一个有用的例子 C＃：如何识别单元格格式是使用Interop在Excel中使用会计或百分比的数字或货币[ ^ ]

不幸的是，解决方案中的链接已被破坏，但我认为这篇文章将提供您的任何额外信息ed Excel自定义数字格式| Exceljet [ ^ ]

或者，您可以使用您可能遇到的不同格式来格式化各种列，并使用以下内容来确定格式字符串的外观（未经测试）：

You have already established that there is a NumberFormat property and you know how to set it to a date, so instead of just setting it compare it against that format.

There is a worked example here C# : How to identify the cell format is Number or currency ot accounting or percentage in excel using Interop[^]
Unfortunately the link in the solution is broken, but I think this article will provide any extra information you need Excel custom number formats | Exceljet[^]
Alternatively you could format a variety of columns with the different formats you might come across and use the following to work out what the format strings look like (untested):

foreach (Microsoft.Office.Interop.Excel.Range col in worksheet.Columns)
{
    Debug.Print(col.NumberFormat);
}

顺便说一句，使用 row 的变量名称是不好的做法按照

As an aside, it is not good practice to use a variable name of row to hold a column as in

foreach (Microsoft.Office.Interop.Excel.Range row in worksheet.Columns)

如果您要在

worksheet.Columns[3].NumberFormat = "dd/MM/yyyy";

请参阅上面的代码，了解更好的方法

See my code above for a better way of doing this

这篇关于如何检查Excel工作表中的列是否为日期格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！