[email protected]      beta

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 RowsSorting 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: 

Import Excel sheet data to gridview asp.net c-sharp

Import Excel sheet data to Gridview Asp.net C#

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.

how to import Excel Sheet Data into asp.net Gridview Control in c#.

Our Excel sheet (.xls) file used to read data and import in Gridview control

# 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;

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

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

	//Read Data from First Sheet
	myExcelCmd.CommandText = "SELECT * From [" + SheetName + "]";
	myDataAdapter.SelectCommand = myExcelCmd;

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

# 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"];


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

Import Excel sheet data to gridview asp.net c-sharp


Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }

3 comments on “Import Excel Sheet Data to Gridview Asp.net C# with pagination”

  1. Yohan

    i can’t do that like yours, my error like this : External table is not in the expected format.
    would you like help me please? thanks for any advice

    • Satinder singh

      This error occurs when you trying to access different verison of excel file.
      Note: if your file extension is .xlsx then use Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'
      And if it is .xls then you connection string would be Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'.

Post Comment

Your email address will not be published. Required fields are marked *