Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Import An Excel File in ASP.NET

Article:
Viewed:  1450 
Posted On:  11/09/2014 07:49:34 
How we can import an excel file in asp.net c#? How we can read data from an excel and insert it into SQL Server? 

 

In this article I am going to show how we can import Excel data into SQL Server. Like in below excel sheet we have Employee: Manager Data. We will read this excel and insert data in SQL Server table.

Here I am going to read data from excel by cell and I am using pure excel functionality to import data.

1.png

Image 1.

Below is my output: Select an excel file to import

2.png

Image 2.

Now click import.

3.png

Image 3.

Now see your data in SQL Server Table.

4.png

Image 4.

To achieve this I add some Excel reference in my solution

5.png

Image 5.

My ASPX is:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportExcel.aspx.cs" Inherits="ImportExcelToSql.ImportExcel" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table style="width: 100%; height: 250px; background: #f1e6e6; border: solid 9px red; padding: 30px;">
 
                <tr>
                    <td><span style="font-family: Arial; font-size: 12pt;">Select An Excel File To Import</span> <span style="color: red;">*</span></td>
                    <td style="text-align: left">
                        <span>
                            <input style="width: 300px;" id="ExcelFileUpload" type="file" runat="server" />
                        </span>
                    </td>
                </tr>
                <tr>
                    <td style="text-align: left; vertical-align: top"></td>
                    <td style="text-align: left; vertical-align: top">
                        <asp:Button ID="btnImportExcel" Text="Import" runat="server" OnClick="btnImportExcel_Click" Width="120px" />
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Label ID="lblMessage" runat="server" ForeColor="Red" Text="" /></td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>
 

My ASPX.CS is

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace ImportExcelToSql
{
    public partial class ImportExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
 
        protected void btnImportExcel_Click(object sender, EventArgs e)
        {
            var path = "";
            var fileExtension = "";
            Guid guid;
            guid = Guid.NewGuid();          

            if (ExcelFileUpload.PostedFile != null)
            {
               if (Path.GetExtension(ExcelFileUpload.PostedFile.FileName).ToString() == ".xls" || Path.GetExtension(ExcelFileUpload.PostedFile.FileName).ToString()
==
".xlsx")
                {
                    fileExtension = Path.GetExtension(ExcelFileUpload.PostedFile.FileName).ToString();
                    path = Path.Combine(Server.MapPath("~/ExcelFileToImport/"), guid.ToString() + fileExtension);
                    ExcelFileUpload.PostedFile.SaveAs(path);
                    ManageExcelData(path);
                    lblMessage.Text = "Excel Data Imported Successfully.";
                    lblMessage.Visible = true;
 
                }
            }
        }
 
        public void ManageExcelData(string path)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;
 
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 
            range = xlWorkSheet.UsedRange;
 
            DataTable dtEmployee = new DataTable("TBL_EMPLOYEE");
            dtEmployee.Columns.Add("CompanyName");
            dtEmployee.Columns.Add("EmployeeSupervisorCode");
            dtEmployee.Columns.Add("EmployeeCode");
            dtEmployee.Columns.Add("EmployeeName");
            dtEmployee.Columns.Add("ProjectName");
            dtEmployee.Columns.Add("JoiningDate");
            dtEmployee.Columns.Add("Experience");
            dtEmployee.Columns.Add("Mobile");
            dtEmployee.Columns.Add("Address");
            dtEmployee.Columns.Add("CreatedDate");
 
            int rowNum = 0;
            for (rowNum = 8; rowNum <= 11; rowNum++)
            {
                DataRow dr = dtEmployee.NewRow();
                string CompanyName = Convert.ToString((range.Cells[1, 1] as Excel.Range).Value2);
                dr[0] = CompanyName.TrimStart();
                dr[1] = Convert.ToString((range.Cells[4, 9] as Excel.Range).Value2);
                dr[2] = Convert.ToString((range.Cells[rowNum, 1] as Excel.Range).Value2);
                dr[3] = Convert.ToString((string)(range.Cells[rowNum, 2] as Excel.Range).Value2);
                dr[4] = Convert.ToString((string)(range.Cells[rowNum, 4] as Excel.Range).Value2);
                dr[5] = DateTime.FromOADate(Convert.ToDouble((range.Cells[rowNum, 6] as Excel.Range).Value2));
                dr[6] = Convert.ToString((string)(range.Cells[rowNum, 8] as Excel.Range).Value2);
                dr[7] = Convert.ToString((range.Cells[rowNum, 9] as Excel.Range).Value2);
                dr[8] = Convert.ToString((range.Cells[rowNum, 10] as Excel.Range).Value2);
                dr[9] = DateTime.FromOADate(Convert.ToDouble((range.Cells[14, 8] as Excel.Range).Value2));
                dtEmployee.Rows.Add(dr);
            }
 
            SqlBulkCopy sqlBulkInsert = new SqlBulkCopy(@"Data Source=MyPC\SqlServer2k8;Initial Catalog=Test;Integrated Security=True");
            sqlBulkInsert.DestinationTableName = "Employee";
            sqlBulkInsert.WriteToServer(dtEmployee);
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();
            xlWorkSheet = null;
            xlWorkBook = null;
            xlApp = null;
        } 
    }
}

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us