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?

Answer 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.