以一般格式阅读excel提取日期 [英] reading excel fetching date in general format

问题描述

在应用程序中，我们有一个带有多个选项卡的向导。在第一个选项卡上，我们可以使用浏览按钮选择Excel表单路径。当您选择excel，excel文件内容加载到数据集（一切正确的格式，包括DATE字段）。然后单击下一步进入下一个向导选项卡。

In the application we have a wizard with multiple tabs. On the first tab we can select a excel sheet path using a browse button. The moment you select the excel, excel file contents are loaded in a dataset (everything in correct format including DATE fields). Then click Next to go to the next wizard tab.

但是，如果文件是打开的程序加载excel，并且这次与通用格式的所有日期字段。
例如：6/22/2006 as 38890

But if the file is open program loads the excel and this time with all the date fields in General format. eg: 6/22/2006 as 38890

我们正在尝试查找excel是否打开或被另一种格式使用：

We are trying to find if the excel is open or being used by another format as:

    bool IsFilebeingUsed(FileInfo file)
    {
        FileStream stream = null;

        try
        {
            stream = file.Open(FileMode.Open, FileAccess.Read, FileShare.None);
        }
        catch (IOException)
        {
            return true;
        }
        finally
        {
            if (stream != null)
                stream.Close();
        }
        return false;
    }

我们试图阅读excel的方式如下：

The way we are trying to read the excel is as follows:

string sql = string.Format("SELECT * FROM [{0}]", excelSheetName);
internal OleDbCommand command = new OleDbCommand();
command.CommandText = sql;
OleDbDataReader oleRdr = command.ExecuteReader();

DataTable dataTable = new DataTable();
datatable.TableName = excelSheetName;
dataTable.Load(oleRdr);

任何人都可以告诉应用程序或代码有什么问题？

Can anyone tell whats wrong with the application or code?

推荐答案

可以在此处找到更多更新的问题。

More updated question can be found here.

所以我以不满意的方式解决了这个问题。我注意到我的分析和实验，最终得出结论，当excel在MS-Excel中打开，我们尝试使用OleDbDataReader读取C＃中的excel，一些DataTypes是不正确的，不幸的是，DateTime是我的情况之一。

So I solved the issue in a unsatisfactory way. I noticed with my analysis and experiments and ultimately concluded that when the excel is open in MS-Excel and we try reading the excel in C# using OleDbDataReader, some of the DataTypes are not read properly and unfortunately DateTime being one of them in my case.

作为解决方案，目前我们强制用户在阅读excel文件时关闭excel。我甚至注意到，如果excel在MS-Excel中以只读模式打开，那么对所有DataTypes（包括DateTime）读取的数据是正确的。但是当以写入模式打开时，数据读取不正确。

As a solution, at present we are forcing the user to close the excel at the time we are reading the excel file. I even noticed that if the excel is open in Read-only mode in MS-Excel, then the data read is correct for all DataTypes including DateTime. But when opened in Write-Mode, then data read is incorrect.

这些是我的分析结果，我知道/同意结果有点奇怪。如果有任何人不同意或有其他想法，请让我知道，请更正我。

These are my analysis results and I know/agree that the results are a bit weird. If any one disagree or have something else in their mind, then let me know and please correct me.

这篇关于以一般格式阅读excel提取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！