Resources » .NET programming » ASP.NET/Web Applications

Excel file upload in asp.net 2.0 display in grid view


Posted Date: 08-Jul-2011  Last Updated:   Category: ASP.NET/Web Applications    
Author: Member Level: Gold    Points: 8


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


Did you like this resource? Share it with your friends and show your love!

Responses to "Excel file upload in asp.net 2.0 display in grid view"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    chirag
    chiru
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India