Get Data from MS Excel in asp.net

Question

I can retrieve the data from excel to GridView.

Below is the 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()

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

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.

Does anyone know the solution?

no code for the gridview. In the above code gvReadFiles is a GridView. — Vishal
– Vishal, Commented May 3, 2013 at 19:38
oh I am sorry. Here it is <asp:GridView ID="gvReadFiles" runat="server" Width = "84%" Height = "360px" Visible="False"> </asp:GridView> — Vishal
– Vishal, Commented May 3, 2013 at 19:44

Steve · Accepted Answer · 2013-05-03 19:50:26Z

1

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"""

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

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

answered May 3, 2013 at 19:50

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get Data from MS Excel in asp.net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related