Import Excel Sheet Data to Gridview Asp.net C# with pagination

Import excel sheet data to gridview:  Here in this article we are going to learn how to import Excel to Gridview Control in Asp.net C# with pagination. This is a common task for every ASP.NET developers  where we  have to populate Gridview control from external data files ( MS Excel File or CSV file etc). So in this post will explain, how to import xls data on gridview Control .i.e display excel sheet data in gridview C# or import excel file to gridview in Asp.net using C# with pagination by using session.

You can also have a look at my previous article related Gridview .i.e Export Gridview to Excel in Asp.net C# , Reorder Gridview Asp.net JQuery : Drag Drop reorder of GridView Rows,  Sorting Gridview control on header click with pagination Asp.net,  Gridview row color change based on data asp.net c#,  How to bind drop-down list inside Gridview edit template .

Example Demo: 

[caption id="attachment_2711" align="aligncenter" width="580"] Import Excel sheet data to Gridview Asp.net C#[/caption]

Steps To Import Excel file to Gridview control C#.

• Use OLEDB connection , add Connection String (web.config).
• Download and install Access Database Engine 2010.
• Read Excel sheet File (.xlsx file data ).
• Bind Gridview with pagination.

Screenshot of Our Excel Sheet file. [caption id="attachment_2713" align="aligncenter" width="527"] Our Excel sheet (.xls) file used to read data and import in Gridview control[/caption]

---

# Adding Connection string to our web.config file.

In below code have added connection string, which we used to read data from our Excel Sheet ( .xlsx ). Here ConStr is the name of my connection string.

//*
<add name ="Excel07_ConStr" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
//*

---

# Download and install Access Data Engine.

If you getting this error "The 'microsoft.ace.oledb.12.0' provider is not registered on the local machine" then download  Microsoft Access Database Engine 2010 Redistributable .

---

# Code to Read Excel Sheet file Data and bind to Gridview control.

Here first we get our excel file path, whose data to has been importing in Asp.net Gridview control. Using `OLEDB` we read the Excel Sheet ( .xlsx ) file data,  and then with`GetOleDbSchemaTable` method will get schema information from a data source as indicated by a GUID, and after it applies the specified restrictions. Once we get dtExcelSchema now we fetch the first sheet name, so we fire select query over it and fill `DataAdapter`. Later we add a session variable as `mySessionTable`  and store the datatable, this will be using for Gridview pagination. So this is how by setting Gridview Datasource with Excel Datasource will display excel sheet data in gridview. Our Full code to import data from Excel Sheet to  Gridview Control will look like as given below.

//*
protected void btn_import_excel_to_grid_Click(object sender, EventArgs e)
{
	// Get the excel file path
	string csv_Excel_Path = Server.MapPath("~/csv_files/MobileInfo.xlsx");
	Import_ExcelData_To_Grid(csv_Excel_Path, ".xlsx", "Yes");
}

private void  Import_ExcelData_To_Grid(string FilePath, string Extension, string isHDR)
{
	
	string conStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; 

	conStr = String.Format(conStr, FilePath, isHDR);
	OleDbConnection myExcelConn = new OleDbConnection(conStr);
	OleDbCommand myExcelCmd = new OleDbCommand();
	OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
	DataTable mydt = new DataTable();
	myExcelCmd.Connection = myExcelConn;
	myExcelConn.Open();

	DataTable dtExcelSchema;
	dtExcelSchema = myExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

	//Fetch the name of First Sheet
	string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
	myExcelConn.Close();

	//Read Data from First Sheet
	myExcelConn.Open();
	myExcelCmd.CommandText = "SELECT * From [" + SheetName + "]";
	myDataAdapter.SelectCommand = myExcelCmd;
	myDataAdapter.Fill(mydt);
	myExcelConn.Close();

	//  save datatable in a session which we used for pagination
	Session.Add("mySessionTable", mydt);
	
	//Bind mySessionTable to gridview control
	GridView1.DataSource = (DataTable) Session["mySessionTable"];
	GridView1.DataBind();
}
//*

---

# Code - Pagination to Gridview control.

//*
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
	GridView1.PageIndex = e.NewPageIndex;
	if (Session["mySessionTable"] != null) 
	{
		GridView1.DataSource = (DataTable)Session["mySessionTable"];
		GridView1.DataBind();
	}
}
//*

 

Output: Here's we Import Excel Sheet Data into Gridview Control as shown in below Image