Wednesday, March 14, 2012

Excel Data into grid view in asp.net

Title:
How to import Excel data to grid view in asp.net using c#

Description:
Reporting is the key features of any application.Because the end user wants the see the data what they want.
Some time we might need to import the data from files(Excel) ,then insert into data base.So i would like to explain one example for importing

Example:
The recent recent post have given the process of exporting.how to export grid view data to excel,Export Grid view Data to Word Document in asp.net,Export Grid view data to PDF document in asp.net,Import XML data to Grid View.Here i will show how to Import the Excel data to Grid view.For this i have used OLEDB connection to connect the excel database.Then get the data of sheet1 in to Data Adapter and fill the data set.Finally this data is bind to Grid view Excel to grid view in asp.net process:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GvImpOrder"  AllowPaging="True" Runat="server" AutoGenerateColumns="False">
<asp:Button ID="btnImportOrders" runat="server" Text="Import excel Data" 
onclick="btnImportOrders_Click" /></div>
</form>
</body>
</html>

Code behind:
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class ImportExcelToGidview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnImportOrders_Click(object sender, EventArgs e)
{
String Con = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\ImportGidview\OrderReport.xls;" +
"Extended Properties=Excel 8.0;";
OleDbConnection objCn = new OleDbConnection(con);
DataSet ds = new DataSet();
OleDbDataAdapter Oda = new OleDbDataAdapter("select * from[Sheet1$]", Con); 
Oda.Fill(ds);
GVImpOrder.DataSource = ds.Tables[0].DefaultView;
GVImpOrder.DataBind();
}
}

No comments:

Bel