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# Import excel sheet in asp.net c# Reviewed by NEERAJ SRIVASTAVA on 12:17:00 PM Rating: 5

No comments:

Powered by Blogger.