Import excel sheet in asp.net c#
In this article, we learn how to insert the data in the table using excel sheet in asp.net c#, as previous, we have seen how to How to import excel sheet in MVC and Import Excel Sheet Data in Database in MVC.
Database:-
Script:-
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblreg](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](max) NULL,
[contactno] [nvarchar](max) NULL,
[city] [nvarchar](50) NULL,
CONSTRAINT [PK_tblreg] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Table:-
Source code:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="import-excelsheet.aspx.cs" Inherits="import_excelsheet" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset style="width: 350px">
<legend>Insert data using excel sheet in asp.net c#</legend>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Export" OnClick="btnUpload_Click" />
</fieldset>
</div>
</form>
</body>
</html>
Code Behind (C#)
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class import_excelsheet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
try
{
string path = string.Concat(Server.MapPath(FileUpload1.FileName));
// if you want to save your file in solution , please uncomment below line.
//FileUpload1.SaveAs(path);
// Connection String to Excel Workbook
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr = command.ExecuteReader();
// SQL Server Connection String
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// Bulk Copy (Insert values)to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(constr);
bulkInsert.DestinationTableName = "tblreg";
//tblreg database table name
bulkInsert.WriteToServer(dr);
string message = "value successfully inserted";
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + message + "');", true);
}
catch (Exception ex)
{
string message = ex.ToString() ;
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + message + "');", true);
}
}
}
}
Out-Put:-
Import excel sheet in asp.net c#
Reviewed by NEERAJ SRIVASTAVA
on
12:17:00 PM
Rating:
Reviewed by NEERAJ SRIVASTAVA
on
12:17:00 PM
Rating:
No comments: