This example explains How To Upload And Read Excel File Data Into DataTable DataSet Using FileUpload and display in gridview using C# and VB.NET in Asp.Net.
First of all put a fileUpload control, and a GridView in design view of aspx page to upload and display excel file data.
Now place a button on the page, in click even of this button we will be uploading the excel file in a folder on server and read it's content.
Add these namespaces in code behind of page
Write below mentioned code in Click Event of Upload Button
Build and run the application.
In this code if the excel sheet contains text characters or special characters in numeric field like EmpID, then it's not read by C# or VB.NET and display blank in gridview as shown in Image.
The reason for this is excel doesn't handle mixed data format very well, entry like 1A or 1-A etc doesn't get read by this code.
To fix this error we need to make some changes in connection string of excel, and need to add some extended properties, change the connection string as shown below.
Now it will read the excel sheet without any errors.
Now place a button on the page, in click even of this button we will be uploading the excel file in a folder on server and read it's content.
HTML SOURCE OF PAGE
<form id="form1" runat="server"> <div> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="btnUpload" runat="server" Height="21px" Text="Upload" Width="92px" onclick="btnUpload_Click"/> </div> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </form>
Add these namespaces in code behind of page
using System.IO; using System.Data.OleDb; using System.Data;
Write below mentioned code in Click Event of Upload Button
C# CODE
protected void btnUpload_Click(object sender, EventArgs e)
{
string connectionString ="";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUpload1.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();
}
}
VB.NET CODE
Protected Sub btnUpload_Click(sender As Object, e As EventArgs)
Dim connectionString As String = ""
If FileUpload1.HasFile Then
Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim fileLocation As String = Server.MapPath("~/App_Data/" & fileName)
FileUpload1.SaveAs(fileLocation)
'Check whether file extension is xls or xslx
If fileExtension = ".xls" Then
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
ElseIf fileExtension = ".xlsx" Then
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
End If
'Create OleDB Connection and OleDb Command
Dim con As New OleDbConnection(connectionString)
Dim cmd As New OleDbCommand()
cmd.CommandType = System.Data.CommandType.Text
cmd.Connection = con
Dim dAdapter As New OleDbDataAdapter(cmd)
Dim dtExcelRecords As New DataTable()
con.Open()
Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()
cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
dAdapter.SelectCommand = cmd
dAdapter.Fill(dtExcelRecords)
con.Close()
GridView1.DataSource = dtExcelRecords
GridView1.DataBind()
End If
End Sub
Build and run the application.
To fix this error we need to make some changes in connection string of excel, and need to add some extended properties, change the connection string as shown below.
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
Now it will read the excel sheet without any errors.
