In this article I will explain with an example, how to read and import data from Excel using
ADO.Net in ASP.Net with C# and VB.Net.
Connection Strings
Excel 97-2003
Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'" />
Excel 2007 and higher
Excel 2007 format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'" />
HTML Markup
The following HTML Markup contains of:
FileUpload – For uploading Excel file.
Button – For reading and importing the Excel file data.
GridView – For displaying the imported data.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />
<hr />
<asp:GridView ID="gvCustomers" runat="server"></asp:GridView>
Namespaces
You need to import the following namespaces.
C#
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.IO;
VB.Net
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Imports System.IO
Reading the Excel Sheet
When the Upload button is clicked, file will be saved to a Folder (Directory) on Server's disk.
Once the file is saved in the Folder (Directory) on Server's disk.
Then, based on the selected Excel file extension the connection string is built by replacing the placeholder and the saved Excel file is read using the selected OLEDB driver.
Next, the Schema of the Excel file is read and the Name of the first Sheet is determined.
Finally, the data from the Excel sheet is read into a DataTable and the GridView is populated.
C#
protected void Upload(object sender, EventArgs e)
{
if (fuUpload.HasFile)
{
string fileName = Path.GetFileName(fuUpload.PostedFile.FileName);
string extension = Path.GetExtension(fuUpload.PostedFile.FileName);
string filePath = Server.MapPath("~/Files/" + fileName);
fuUpload.SaveAs(filePath);
string conString = "";
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
using (DataTable dt = new DataTable())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView.
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
}
}
}
}
}
}
VB.Net
Protected Sub Upload(sender As Object, ByVal e As EventArgs)
If fuUpload.HasFile Then
Dim fileName As String = Path.GetFileName(fuUpload.PostedFile.FileName)
Dim extension As String = Path.GetExtension(fuUpload.PostedFile.FileName)
Dim filePath As String = Server.MapPath("~/Files/" & fileName)
fuUpload.SaveAs(filePath)
Dim conString As String = ""
SelectCase extension
Case ".xls"'Excel 97-03
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Case ".xlsx"'Excel 07
conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
End Select
conString = String.Format(conString, filePath)
Using connExcel As OleDbConnection = New OleDbConnection(conString)
Using cmdExcel As OleDbCommand = New OleDbCommand()
Using oda As OleDbDataAdapter = New OleDbDataAdapter()
Using dt As DataTable = New DataTable()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As DataTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'Get the name of First Sheet.
Dim sheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
'Read Data from First Sheet.
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & sheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
connExcel.Close()
'Bind Data to GridView.
gvCustomers.DataSource = dt
gvCustomers.DataBind()
End Using
End Using
End Using
End Using
End If
End Sub
Errors
The following error (exception) will occur if you try to read the Excel file when it is open or used by some other application.
Thus, you need to make sure the Excel file is closed.
Server Error in 'ASP.Net' Application.
The process cannot access the file 'F:\Files\Excel07.xlsx' because it is being used by another process.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IO.IOException: The process cannot access the file 'F:\ReadExcelToGrid\Files\Excel07.xlsx' because it is being used by another process.
Screenshots
The Excel File
![Read and Import Excel Sheet using ADO.Net and C#]()
Imported data
![Read and Import Excel Sheet using ADO.Net and C#]()
Downloads
