Excel拉错了数据

I want to get the data displayed in the cell ( for example AA2 00:43.6), not the value that's written above 12:00:44 AM. How can I manage to do so without any API etc. Check this image from my excel 2010 example for more info:

我想获取单元格中显示的数据(例如AA2 00:43.6),而不是上午12:00:44写的值。如何在没有任何API等的情况下设置这样做。有关详细信息,请参阅我的excel 2010示例中的此图像:

Here is my code for importing the excel file

这是我导入excel文件的代码

MyConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & _
                          path & "';Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";") 
'i also tried IMEX=0

dtTimes = New DataTable
Dim sqlstrTimes As String = "SELECT [RACETIME] " & "FROM [SheetH$];"
'SheetF$
Try
    Dim MyCommandHistory As New OleDb.OleDbDataAdapter(sqlstrTimes , MyConnection)
    MyCommandHistory.Fill(dtTimes )
Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK
            , MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
    MyConnection.Close()
End Try

I checked the website of connectionstrings.com for excel-2010, but no luck there.

我查看了connectionstrings.com的网站excel-2010,但没有运气。

1 个解决方案

#1

You can try it this way :

你可以这样试试:

1. Set connection string to read excel file without header (HDR=NO). Column name will be autogenerated for each column starting from F1 to Fn.

设置连接字符串以读取没有标题的excel文件(HDR = NO)。将从F1到Fn的每列自动生成列名称。

2. Fill DataTable the way you currently do

按照您目前的方式填写DataTable

3. Remove first row from DataTable since it actually column header name in excel

从DataTable中删除第一行,因为它实际上是excel中的列标题名称

With that you'll get the text instead of DateTime value from excel. In my understanding, that because excel determines data type from the first row. So with this hack the first row is a text (the header) hence decided that the column is of type text/string.

有了这个,您将从excel获取文本而不是DateTime值。据我所知,因为excel决定了第一行的数据类型。因此,对于这个hack,第一行是文本(标题),因此决定该列是text / string类型。

MyConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & _
                          path & "';Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";") 

dtTimes = New DataTable
Dim sqlstrTimes As String = "SELECT [F1] " & "FROM [SheetH$];"

Try
    Dim MyCommandHistory As New OleDb.OleDbDataAdapter(sqlstrTimes , MyConnection)
    MyCommandHistory.Fill(dtTimes)
Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK
            , MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
    MyConnection.Close()
End Try

dtTimes.Rows[0].Delete()