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

2
Our Reader Score
[Total: 0 Average: 0]

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.


# 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 withGetOleDbSchemaTable 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.


# Code – Pagination to Gridview control.

 

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!


Get your Printable Copy

2 COMMENTS

  1. 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

    • 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}'.

LEAVE A REPLY