Excel Sheet Import and Read Data using ADO.Net in Asp.net Using C#

This article will show you how you can import the excel sheet and read it’s data using ADO.Net in asp.net using c#.net.

Some of my previous articles are as follows: Search and Display Data In MVC WebGrid in Asp.Net MVC Using C#.Net, Excel File Upload Or Import and Display In GridView Using C# In Asp.Net, Export GridView Data To Excel Sheet Using C#.Net In Windows Application, Asp.Net MVC Export Data to Excel File Of WebGrid Using C#.Net.

So for this article first we will create a new excel sheet.

Now first we will create the connection string to read the excel sheet.

string excelFilePath = @"ExcelFile\StudentData.xls";             String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(excelFilePath) + ";Extended Properties='Excel 8.0;HDR=Yes'";             OleDbConnection con = new OleDbConnection(strExcelConn);             con.Open();

In above code I have defined the path of the excel sheet and prepared the connectionstring. I have passed the connection string to OleDbConnection provider to establish the connection. In above code one of the most important thing is that you must use Server.MapPath to read the file.

After making the connection I have read the excel sheets. So that we can prepare the query to read the data of specific sheet.

DataTable dtExcelSheet = new DataTable();             dtExcelSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);             string sheetname = dtExcelSheet.Rows[0]["TABLE_NAME"].ToString();

Above code will return the sheet names present in the excel file. Now we will read the data of excel sheet.

//Read data of the selected excel sheet             DataSet ds = new DataSet();             OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [" + sheetname + "]", con);             da.Fill(ds);             con.Close();

In above code I have used the OleDbDataAdapter to read the data in the dataset.

Here is the complete code.

string excelFilePath = @"ExcelFile\StudentData.xls";             String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(excelFilePath) + ";Extended Properties='Excel 8.0;HDR=Yes'";             OleDbConnection con = new OleDbConnection(strExcelConn);             con.Open();             //Read data sheet name             DataTable dtExcelSheet = new DataTable();             dtExcelSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);             string sheetname = dtExcelSheet.Rows[0]["TABLE_NAME"].ToString();             //Read data of the selected excel sheet             DataSet ds = new DataSet();             OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [" + sheetname + "]", con);             da.Fill(ds);             con.Close();

Now we Have done run the page to check the output.