How to import excel data into GridView
I have MS excel with more than 100 records in the same time I need to import that data into SQL server. The Excel file path is choose from the Client machine using file upload control by user. After user select that file and click the submit button. Then all import data from Excel in to DataTable and then bind it in the Grid View control.
In this below code I have mention two connection strings. If you have 2003 .xls format then use below code or if you have 2007.xlsx file then change connection string (I put it in comment line check in the source code)
For example I have excel file with data look like this.
I need to import that data into dataset. I use the below code for getting records.
I placed one Grid view, File upload and Button Controls.
public partial class _Default : System.Web.UI.Page
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
protected void Button1_Click(object sender, EventArgs e)
//Check file is available in File upload Control
//Store file name in the string variable
string filename = FileUpload1.FileName;
//Save file upload file in to server path for temporary
//Export excel data into Gridview using below method
void ExportToGrid(String path)
OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null;
//Connection for MS Excel 2003 .xls format
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
//Connection for .xslx 2007 format
// MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;");
//Select your Excel file
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DtSet = new System.Data.DataSet();
//Bind all excel data in to data set
dt = DtSet.Tables;
//Check datatable have records
if (dt.Rows.Count > 0)
GridView1.DataSource = dt;
//Delete temporary Excel file from the Server path
Source Code Detail:
Here with I have attached source code download it and try to learn about import data from Excel To Dataset using C# and file upload control.
Front End : ASP.NET
Code Behind : C#
I hope my article help to beginner for know about Import data from excel.
My gridview is not fill with that table records what I have to do now can You please tell me?
In this gridview is not bind with that dt records could you pls help me?
Shwetha can you explain clearly you not get data in datatable?
This seems like it will work great, I am getting an issue of "Could not find installable ISAM " im trying to run it in a closed network, does this have to connect to the internet to work?
how to retain the filename in the fileupload control after hitting submit and viewing the grid. As of now it is going blank on button click.
yes this seems good. but then what about retaining the file name in the file upload control after hitting the submit button. as of now its erasing. can someone help on this?
Smita security reason not allowed to keep file in the file upload control during post back occur like password textbox.
Great article, works like a charm after downloading the accessdbconnectivity file