从MS Excel中获取数据在asp.net [英] Get Data from MS Excel in asp.net

问题描述

我可以检索从Excel的数据GridView控件。

I can retrieve the data from excel to GridView.

下面是code：

If Extension = "xls" Then

    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileUploadPath & sender.text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

ElseIf Extension = "xlsx" Then

    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileUploadPath & sender.text & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""

End If

Dim query As String = "SELECT * FROM [Sheet1$]"

Dim conn As New OleDbConnection(connString)

If conn.State = ConnectionState.Closed Then

    conn.Open()

End If

Dim cmd As New OleDbCommand(query, conn)

Dim da As New OleDbDataAdapter(cmd)

Dim ds As New DataSet

da.Fill(ds)

gvReadFiles.DataSource = ds.Tables(0)

gvReadFiles.DataBind()

da.Dispose()

conn.Close()

conn.Dispose()

但在Excel中的第一行文字成为GridView的标题的文本。

But the first row text in excel becomes the header's text in GridView.

这也不是一个大问题，但主要的问题是，当任一单元格中排在第一位的Excel我没有得到同样的标题单元格GridView的虚空。相反的，我得到像F2一些文本。

That is also not a big problem but the main problem is when any cell is empty in first row in excel I don't get the same header cell empty in GridView. Instead of that I get some text like F2.

有谁知道解决办法？

推荐答案

如果您的Excel文件的第一行包含数据和列，然后在连接字符串应改为不头

If the first row of your Excel file contains data and not the header of your columns then your connection string should be changed to

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileUploadPath & _ 
             sender.text & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=2"""

在 http://www.connectionstrings.com/这里Excel的＃微软喷气OLE-DB-4-0 在与Excel 2003中的段落，你可以阅读

Here at http://www.connectionstrings.com/excel#microsoft-jet-ole-db-4-0 in the paragraph related to Excel 2003 you can read

HDR =是;指示第一行包含COLUMNNAMES，不
  数据。 HDR =无;表示相反。

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

这篇关于从MS Excel中获取数据在asp.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！