In this article I have explained how to import excel data in GridView using c#
in asp.net.
First create an excel data base file. Give it a name like “registration.xlsx”.
Make table structure
like this in excel file.
|
ID
|
Name
|
Designation
|
|
|
1
|
sushil
|
Soft
|
|
|
2
|
pradeep
|
Engg
|
|
|
3
|
Rahul
|
HOD
|
|
After that Open
Visual Studio -> open new website ->
Click on Empty Web Application -> Right click on Project file ->
Add New Item -> click on web form -> click ok.
Default.aspx:-
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Import Excel file in GridView</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<table>
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td>
<asp:FileUpload ID="fileuploadExcel"
runat="server"
/>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSend"
runat="server"
Text="Export"
onclick="btnSend_Click" />
</td>
</tr>
</table>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Code Behind Page:-
using System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data.OleDb;
using
System.Data.SqlClient;
public partial class Default : System.Web.UI.Page
{
private String
strConnection = "Data Source=SUSHIL-PC;Initial
Catalog=modi;Integrated Security=True";
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnSend_Click(object sender, EventArgs e)
{
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + path + ";Extended
Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from
[Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "reg";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
}
Debuge the code and run .
No comments:
Post a Comment
Note: only a member of this blog may post a comment.