Import from excel to SQL using asp.net

on
Hello everybody!

Here one of my friend asked me about how to import excel to SQL using asp.net, here I am going to explain you.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImportExcel.aspx.cs" Inherits="Import_ImportExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <fieldset  style="width:600px;">
    <legend>Import data</legend>
 
    <div id="tdMsg" runat="server"></div>

    <table class="classTable">
    <tr>
    <td>
    <asp:RadioButtonList ID="chblExportOption" runat="server" CellPadding="6" CellSpacing="2" RepeatColumns="3" RepeatDirection="Horizontal">
    <asp:ListItem Value="xlsx" Selected="True">Xlsx</asp:ListItem>
    </asp:RadioButtonList>
    </td>
    <td style="width:95px;"><asp:FileUpload runat="server" ID="ffUpload" CssClass="upload" /></td>
    <td>
      <asp:Button ID="btnImport" runat="server"  Text="Import" OnClick="ImportData"/>
    </td>
    <td>
     <asp:Button ID="btnCancel" runat="server"  Text="Cancel"
            onclick="btnCancel_Click"  />
    </td></tr>
    </table>
    
      <table class="gvTable">
        <asp:GridView ID="gvReport" runat="server" AutoGenerateColumns="true">
        </asp:GridView>
      </table>
       </fieldset>
    </div>
    </form>
</body>
</html>



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;

public partial class Import_ImportExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { }
    }

    protected void ImportData(object sender, EventArgs e)
    {
        string strErrormessage = string.Empty;

        string strfullpathName = string.Empty;

        tdMsg.Visible = true;
        gvReport.Visible = true;
        if (ffUpload.HasFile)
        {
            string strFileName = ffUpload.PostedFile.FileName;
            string strFilePath = Server.MapPath("~/Upload/");
            strfullpathName = strFilePath + strFileName;
            //save file
            ffUpload.SaveAs(strfullpathName);

            switch (chblExportOption.SelectedValue)
            {
                case "xlsx":
                    ImportSQLToExcel(strfullpathName, gvReport, "tblExcel_Import", out strErrormessage);
                    break;
            }

            try
            {
                if (File.Exists(strfullpathName))
                {
                    File.Delete(strfullpathName);
                }
            }
            catch (FileNotFoundException ex)
            {
                tdMsg.Style.Add("color", "red");
                tdMsg.InnerHtml = "ERROR: " + ex.Message;
            }
            catch (Exception ex)
            {
                tdMsg.Style.Add("color", "red");
                tdMsg.InnerHtml = "ERROR: " + ex.Message;
            }
            //show Error or successfull message
            if (strErrormessage.Length > 0)
            {
                tdMsg.Style.Add("color", "red");
                tdMsg.InnerHtml = "ERROR: " + strErrormessage;
            }
            else
            {
                tdMsg.Style.Add("color", "green");
                tdMsg.InnerHtml = "Data imported successfully !";
            }
        }
    }

    #region [Import From Excel]
    public void ImportSQLToExcel(string strFilePath, GridView gvReport, string strDestinationTable, out string strErrorMessage)
    {
        strErrorMessage = string.Empty; //to show error message when export failed
        DataSet Ds;
        DataTable Dt;

        try
        {
            OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + HttpContext.Current.Server.MapPath("~/" + strFilePath) + ";Extended Properties=Excel 12.0;");
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
            Ds = new DataSet();
            objAdapter1.Fill(Ds);
            Dt = Ds.Tables[0];

              //Finally Insert the Data After validations
            if (Dt.Rows.Count >0)
            {
                InsertData(Dt, gvReport, strDestinationTable);
            }
        }
        catch (Exception ex)
        {
            strErrorMessage = ex.Message;
        }
    }

    private bool ValidateDate(string date)
    {
        try
        {
            string[] dateParts = date.Split('/');
            DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
            return true;
        }
        catch
        {
            return false;
        }
    }

    private void InsertData(DataTable Dt, GridView gv, string strDestinationTable)
    {
        String[] columnArray = new String[Dt.Columns.Count];
        for (int col = 0; col < Dt.Columns.Count; col++)
        {
            string strColumnName = Dt.Columns[col].ColumnName.ToString();
            columnArray[col] = strColumnName;
        }

        SqlConnection myConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ToString());

        using (SqlConnection dbConnection = myConn)
        {
            dbConnection.Open();
            using (SqlBulkCopy sqlBulk = new SqlBulkCopy(dbConnection))
            {
                sqlBulk.DestinationTableName = strDestinationTable;

                if (columnArray.Length > 0)
                {
                    for (int desColumn = 0; desColumn < columnArray.Length; desColumn++)
                    {
                        sqlBulk.ColumnMappings.Add(columnArray[desColumn], columnArray[desColumn]);
                    }
                }
                sqlBulk.WriteToServer(Dt);
            }
        }

        //show imported data into grid

        gv.DataSource = Dt;
        gv.DataBind();

    }
    #endregion
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        //reset
    }
}

Please feel free to send your feedback/comment.
Cheers,

Ved Pathak

Comments

Post a Comment