How to import excel data into GridView using File Upload Control?
In this article I have explained about how to read excel data and convert into dataset and bind in the Grid View. In this example I have get Excel file using file upload control. For the security reasons we are not keep the file upload client path in the server, so we need to save the excel file in to the server path then import data to dataset.
How to import excel data into GridView
Description:
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.
Connection strings:
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.
Client side:
I placed one Grid view, File upload and Button Controls.
Server side:
using System.Data;
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
if (FileUpload1.HasFile)
{
//Store file name in the string variable
string filename = FileUpload1.FileName;
//Save file upload file in to server path for temporary
FileUpload1.SaveAs(Server.MapPath(filename));
//Export excel data into Gridview using below method
ExportToGrid(Server.MapPath(filename));
}
}
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
MyCommand.Fill(DtSet, "[Sheet1$]");
dt = DtSet.Tables[0];
MyConnection.Close();
//Check datatable have records
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
//Delete temporary Excel file from the Server path
if(System.IO.File.Exists(path))
{
System.IO.File.Delete(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#
Output:
Conclusion:
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?