Friday, September 25, 2015

Asp.net: Import MS Excel sheet records into Sql server database

In this tutorial I am going to explain how to import the MS Excel sheet records into Sql server database in asp.net

Description:

I have a MS excel file which have records/details of employees. I want to import all records from excel file into database table.


Implementation:
I have a table in Tb_Employee


HTML Markup:
<table>
        <tr><td>Upload Excel:</td><td><asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
        <tr><td></td><td><asp:Button ID="btnupload" runat="server" Text="Upload"/></td></tr>
    </table>ssss

Add namespace
C#:
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;

VB:
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Data

C#:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

VB:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

Validate the extension

C#:
protected bool IsValidExtension()
    {
        if (FileUpload1.HasFile)
        {
            string FileExt = Path.GetExtension(FileUpload1.FileName);

            FileExt = FileExt.ToLower();
            if (FileExt != ".xls" && FileExt != ".xlsx")
            {
                Response.Write("<script>alert('Upload Excel file only');</script>");
                return false;
            }
        }
        return true;
    }

VB:
Protected Function IsValidExtension() As Boolean
        If FileUpload1.HasFile Then
            Dim FileExt As String = Path.GetExtension(FileUpload1.FileName)
            FileExt = FileExt.ToLower()
            If FileExt <> ".xls" AndAlso FileExt <> ".xlsx" Then
                Response.Write("<script>alert('Upload Excel file only');</script>")
                Return False
            End If
        End If
        Return True
    End Function

Upload the Excel file
On upload button click write the below given code:
C#:
protected void btnupload_Click(object sender, EventArgs e)
    {
        try
        {
            if (IsValidExtension())
            {
                string filepath = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
                FileUpload1.SaveAs(filepath);
                OleDbConnection excelcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + filepath + ";Extended Properties=Excel 12.0;");
                excelcon.Open();
                DataTable ds = new DataTable();
                OleDbDataAdapter oda = new OleDbDataAdapter("SELECT *FROM [sheet1$]", excelcon);
                oda.Fill(ds);
                excelcon.Close();
                con.Open();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(con);
                bulkcopy.DestinationTableName = "Tb_Employee";
                bulkcopy.ColumnMappings.Add("Name", "Name");
                bulkcopy.ColumnMappings.Add("Phone", "Phone");
                bulkcopy.ColumnMappings.Add("Salary", "Salary");
                bulkcopy.WriteToServer(ds);
                con.Close();
            }
        }
            catch(Exception ex)
        { }
    }

VB:
Protected Sub btnupload_Click(sender As Object, e As EventArgs) Handles btnupload.Click
        Try
            If IsValidExtension() Then
                Dim filepath As String = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
                FileUpload1.SaveAs(filepath)
                Dim excelcon As New OleDbConnection((Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=") & filepath) + ";Extended Properties=Excel 12.0;")
                excelcon.Open()
                Dim ds As New DataTable()
                Dim oda As New OleDbDataAdapter("SELECT *FROM [sheet1$]", excelcon)
                oda.Fill(ds)
                excelcon.Close()
                con.Open()
                Dim bulkcopy As New SqlBulkCopy(con)
                bulkcopy.DestinationTableName = "Tb_Employee"
                bulkcopy.ColumnMappings.Add("Name", "Name")
                bulkcopy.ColumnMappings.Add("Phone", "Phone")
                bulkcopy.ColumnMappings.Add("Salary", "Salary")
                bulkcopy.WriteToServer(ds)
                con.Close()
            End If
        Catch ex As Exception
        End Try
    End Sub
  

Build and run the application. Test it by uploading the excel file.
Result:

    In this article we have learn to how to import the MS Excel sheet records into Sql server database in asp.net using C# and VB.netI hope you enjoyed this article.

No comments:

Post a Comment