Excel file upload in asp.net 2.0 display in grid view
I am Creating one ASP.NET web application.
This article gives you a short idea about a How to Upload Excel file in ASP.Net Applications.
In this application, I am uploading Excel file and Display in Gridview.
Description
I am Creating one ASP.NET web application.In this Application I want to Upload excel file into server using asp .net With c#.
A web Page have a file upload button and Gridview.File upload is used upload file in server.Gridview is used to dispaly record from excel file.
A Page browse button and it can select the excel file from any location of the local computer.and when select the excel data file then upload button upload that Server and Display the Records in Gridview.
System.Data.OleDb Includes OLE DB .NET Data Provider types.it is used the Excel File connect to .Net Framework.it support Ms Access and Ms Excel files to connect Visual Studio.Net.if want to read information from MS Excel file,We need to use oledb Namespace and its provider.
Suppose We have more than 1000 records in Excel. we need to upload the Excel file in server and display in GridView.
Connection String is :
For .xls
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
For .xlsx
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
In this application, I am uploading Excel file and Display in Gridview.
You Should use using System.Data.OleDb; The types (classes, structs, enums, and so on) associated with each .NET data provider are located in their own namespaces. System.Data.OleDb support following class
Oledbconnection :it is used connect asp.net with Excel file
OledbCommand : It is used to excute the command
OledbDataAdapter :it is acting as bridge between Excel and .Net.
This application is used to read content from Excel Sheet.
FileUploadXls.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class FileUploadXls : System.Web.UI.Page
{
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string query = null;
string connString = "";
string strFileType = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (FileUpload1.HasFile)
{
try
{
if (strFileType == ".xls" || strFileType == ".xlsx")
{
FileUpload1.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
}
else
{
lblMessage.Text = "Only excel files allowed";
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Visible = true;
return;
}
string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
query = "SELECT * FROM [Sheet1$]";
//Create the connection object
conn = new OleDbConnection(connString);
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
//Create the command object
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
grvExcelData.DataSource = ds.Tables[0];
grvExcelData.DataBind();
lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count;
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Visible = true;
da.Dispose();
conn.Close();
conn.Dispose();
}
catch(Exception ex)
{
Response.Write("Error: " + ex.Message.ToString());
}
}
else
{
Response.Write("Please select a file to upload.");
}
}
}
Finally We Create Application Excel file upload in asp.net 2.0 display in grid view. I have left one job to readers, In this application if you want to store Excel data in Sql Server, in future i will add that functionality in this application.
Thanks for spending your valuable time.
Hope you will enjoy this article and provide your valuable suggestion and feedback.
Thanks
Happy Coding.....