Search This Blog

Thursday, December 1, 2011

Display Data from a Given Sheet of Excel File (.xsl or .xlsx) in Grid View and Save in SQL Database -ASP.NET


Importing data from Excel file into database is repeatedly and frequently used task by an end user.
But from database side its required a technical expertise of DBA, we can achieve it by DTS or SSIS package in SQL Server 2005/2008.
In this article I am going to create a small web application using ASP.net for end user to browse a excel file on his/her system and upload it to back end database table, which is going to help a lot the developer as well as the end user, here I am going to explain step by step procedure to performed the above scenario.

Scenario:
Imports excel file sheet or desired sheet to database and display it on browser;

Solution:
My application has some fileupload control through which the excel file is loaded into the server. Then clicking on button will transfer the Excel file sheet into the Database table.

To transfer the data from excel to database it can be achieved by the following way

1.)  Using the sql Bulk Copy command and
2.)  Using the Dataset and XML ADO.net feature

Step 1
Create your Table like this.




Create you excel file

Step 2;
Create your web application :- Create a new Web application project in you VS2005/2008 and paste the following code in you default. Aspx page;

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true"CodeBehind="Default.aspx.cs"
    Inherits="ExcelApplication._Default" %>

<!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 id="Head1" runat="server">
    <title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
    <style type="text/css">
        tr.sectiontableentry1 tdtr.sectiontableentry2 td
        {
            padding4px;
        }
        tr.sectiontableentry1 td
        {
            padding8px 5px;
            backgroundurl(hline.gif) repeat-x bottom;
        }
        tr.sectiontableentry2 td
        {
            padding8px 5px;
            backgroundurl(hline.gif) repeat-x bottom #F2F2F2;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table style="padding5pxfont-size11px;" align="center"border="0">
            <tbody>
                <tr>
                    <td>
                        <strong>Please Select Excel File Containing The Details…</strong>
                    </td>
                </tr>                
                <tr>
                    <td>
                        <div style="backgroundurl(hline.gif) repeat-xbottom #F2F2F2padding8px 5px;
                            border-bottom1px solid #ccc;">
                            <asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>&nbsp;&nbsp;
                            <asp:Label ID="lblSheet" Text="Sheet Name: "Height="20px" runat="server"></asp:Label>&nbsp;&nbsp;
                            <asp:TextBox ID="txtSheetName" runat="server"MaxLength="100"></asp:TextBox>&nbsp;&nbsp;<asp:RequiredFieldValidator
                                ID="RequiredFieldValidator1" runat="server"ControlToValidate="txtSheetName"
                                ErrorMessage="*" ForeColor="Red"SetFocusOnError="True"></asp:RequiredFieldValidator>
                            &nbsp;<asp:Label ID="lblProject" Text="Project: " Height="20px" runat="server"></asp:Label>&nbsp;
                            <asp:DropDownList ID="ddlProject" runat="server"Height="20px">
                            </asp:DropDownList>
                            <asp:RequiredFieldValidator ID="rfvProject"runat="server" ControlToValidate="ddlProject"
                                ErrorMessage="*" ForeColor="Red"SetFocusOnError="true" InitialValue="0"></asp:RequiredFieldValidator>
                            <asp:Button ID="btnUpload" runat="server"Text="Upload" OnClick="btnUpload_Click" /><br />
                            <asp:Label ID="lblMessage" runat="server"Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>
                        </div>
                    </td>
                </tr>
               
                <tr>
                    <td>
                        <asp:GridView ID="grvExcelData" runat="server"EmptyDataText="No Record Found!!">
                            <RowStyle CssClass="sectiontableentry2" />
                            <AlternatingRowStyleCssClass="sectiontableentry1" />
                        </asp:GridView>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    </form>
</body>
</html>



Here I am going to explain
1.)  Using the Dataset and XML ADO.net feature

The advantage of using this method over sqlbulkcopy is
We can call a procedure and add extra value to it at runtime we can shuffle the table field at run time.
It’s a faster technique and we can do it any database because it’s using xml;

Step 3:

Create your procedure like this.

USE [RND]
GO
/****** Object:  StoredProcedure [dbo].[usp_AddExcelData]    Script Date: 12/01/2011 12:48:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[usp_AddExcelData]
(
@ExcelData varchar(max),
@Createdby varchar(50),
@dcmVer varchar(5),
@ProjID Varchar(10)
)
AS
BEGIN
DECLARE @idoc int

DECLARE @MyTableVar table(
 ID INT identity(1,1),
 [Question_Description] nvarchar(255),
 [Category] nvarchar(255),
 [Reviewed] Bit,
 [ReviewerComments] nvarchar(255),
 [DevelopmentStatus]Bit,
 [DeveloperComments] nvarchar(255),
 CreatedBy Nvarchar(50),
 dcmVer nvarchar(5),
 ProjID int
 )
   
EXEC sp_xml_preparedocument @idoc OUTPUT, @ExcelData
print @idoc

INSERT INTO @MyTableVar([Question_Description],[Category],[ReviewerComments],[DeveloperComments])
SELECT[Question_Description],[Category],[ReviewerComments],[DeveloperComments]
FROM OPENXML (@idoc, '/NewDataSet/Table', 1)
WITH
(Question_Description nvarchar(255) './Question_Description',
 Category nvarchar(255) './Category',
 ReviewerComments nvarchar(255) './ReviewerComments',
 DeveloperComments nvarchar(255) './DeveloperComments'
)

EXEC sp_xml_removedocument @idoc

Declare @TotalCount AS INT
Declare @i as INT
Declare @MaxID as INT

Update @MyTableVar setCreatedBy=@Createdby,dcmVer=@dcmVer,ProjID=@ProjID,[Reviewed]=0,[DevelopmentStatus]=0

SET @i=1
SET @TotalCount=(select count(*) from @MyTableVar)
SET @MaxID=(select max(QID) from CodeReview)

WHILE @i<=@TotalCount
BEGIN
      Insert CodeReview SELECT [Question_Description]
      ,[Category]
      ,[Reviewed]
      ,[ReviewerComments]
      ,[DevelopmentStatus]
      ,[DeveloperComments],GETDATE(),CreatedBy,dcmVer,ProjID from@MyTableVar where id=@i
     
      SET @i = @i + 1
END

Select * from CodeReview where QID > ISNULL(@MaxID,0)

END




Step 4:

The code behind part to upload the excel file to the server and transfer the data in the table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;
using Excel;
using System.Data.SqlClient;
using System.Configuration;

namespace ExcelApplication
{
    public partial class _Default : System.Web.UI.Page
    {
        string SheetNameExcel = null;

        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            BindProject();
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if ((txtFilePath.HasFile))
            {

                OleDbConnection conn = new OleDbConnection();
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
               
                string query = null;
                string connString = "";
                string strFileName =DateTime.Now.ToString("ddMMyyyy_HHmmss");
                string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

                //Check file type
                if (strFileType == ".xls" || strFileType == ".xlsx")
                {
                    //save the file on server
                    txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
                }
                else
                {
                    lblMessage.Text = "Only excel files allowed";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Visible = true;
                    return;
                }

                //Fetch the path of the file
                string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);

               
                //Connection String to Excel Workbook
                if (strFileType.Trim() == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                }
                else if (strFileType.Trim() == ".xlsx")
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                }

                //query = "SELECT * FROM [Sheet1$]";
                //query = "SELECT [Country],[Capital] FROM [Sheet1$] WHERE [Currency]=’Rupee’"
                //query = "SELECT [Country],[Capital] FROM [Sheet1$]"
                string SheetName = txtSheetName.Text.Trim() + "$";


                //Create the connection object
                conn = new OleDbConnection(connString);
                //Open connection
                if (conn.State == ConnectionState.Closed) conn.Open();

                //Check sheet name
                if (FindExcelSheetName(conn, SheetName))
                {

                    //Create the query dynamically
                    query = "SELECT * FROM [" + SheetNameExcel + "]";

                    //Create the command object
                    cmd = new OleDbCommand(query, conn);
                    da = new OleDbDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds);

                    //Get the xmldata in string
                    string excelData = ds.GetXml().ToString();

                    ds=AddExcelData(excelData);
                    if (ds.Tables[0].Rows.Count >0)
                    {
                        grvExcelData.DataSource = ds.Tables[0];
                        grvExcelData.DataBind();

                        lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count;
                        lblMessage.ForeColor = System.Drawing.Color.Green;
                        lblMessage.Visible = true;

                        //Destroy the adapter and close the connection
                        da.Dispose();
                        conn.Close();
                        conn.Dispose();

                        //Delete the file from server
                        if (File.Exists(strNewPath))
                            File.Delete(strNewPath);
                    }
                    else
                    {
                        lblMessage.Text = "Sheet Does Not Contain Any Records!!";
                        lblMessage.ForeColor = System.Drawing.Color.Red;
                        lblMessage.Visible = true;

                        //Destroy the adapter and close the connection
                        da.Dispose();
                        conn.Close();
                        conn.Dispose();

                        //Delete the file from server
                        if (File.Exists(strNewPath))
                            File.Delete(strNewPath);
                    }                   
                }
                else
                {
                    lblMessage.Text = "Sheet Name Does Not Exists!";
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Visible = true;

                    //Destroy the adapter and close the connection
                    da.Dispose();
                    conn.Close();
                    conn.Dispose();

                    //Delete the file from server
                    if (File.Exists(strNewPath))
                        File.Delete(strNewPath);
                }                               
            }
            else
            {
                lblMessage.Text = "Please select an excel file first!!";
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Visible = true;
            }
        }

        protected DataSet AddExcelData(string excelData)
        {
            SqlCommand myCommand = new SqlCommand("usp_AddExcelData");
            myCommand.CommandType = CommandType.StoredProcedure;


            //Creat and add the parameters
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@excelData";
            param.Value = excelData;           
            myCommand.Parameters.Add(param);

            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@Createdby";
            param1.Value = "Ashis";
            myCommand.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@dcmVer";
            param2.Value = "1";
            myCommand.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter();
            param3.ParameterName = "@ProjID";
            param3.Value = ddlProject.SelectedValue;
            myCommand.Parameters.Add(param3);

            // create SqlConnection
            SqlConnection myConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);

            myConnection.Open();

            myCommand.Connection = myConnection;
            SqlDataAdapter da = new SqlDataAdapter(myCommand);
            DataSet aus = new DataSet();
            da.Fill(aus);

            //Destroy the adapter and close the connection
            da.Dispose();
            myConnection.Close();
            myConnection.Dispose();

            return aus;
        }

        protected bool FindExcelSheetName(OleDbConnection conn,stringsheetName)
        {
            //Get the all sheet name
            DataTable dt = null;
            bool find=false;

            //Get the sheet name from schema
            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt != null)
            {
                foreach (DataRow row in dt.Rows)
                {
                    string excelSheetName = row["TABLE_NAME"].ToString();
                    //Matching the sheetname with input and schema
                    if (excelSheetName.ToUpper() == sheetName.ToUpper())
                    {
                        find = true;

                        //Setting the sheetname from schema
                        SheetNameExcel = excelSheetName;
                    }                   
                }
            }

            return find;
        }

        protected void BindProject()
        {
            SqlCommand myCommand = new SqlCommand("usp_GetProjects");
            myCommand.CommandType = CommandType.StoredProcedure;           

            // create SqlConnection
            SqlConnection myConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);

            myConnection.Open();

            myCommand.Connection = myConnection;
            SqlDataAdapter da = new SqlDataAdapter(myCommand);
            DataSet aus = new DataSet();
            da.Fill(aus);

            //Destroy the adapter and close the connection
            da.Dispose();
            myConnection.Close();
            myConnection.Dispose();

            if (aus.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow Dr in aus.Tables[0].Rows)
                {
                    ddlProject.Items.Add(newListItem(Dr["ProjectName"].ToString(), Dr["ProjectID"].ToString()));
                }
            }

            ddlProject.Items.Insert(0, "---Select---");
            ddlProject.Items[0].Value = "0";
        }

        protected DataTable ReadExcel(string filePath, string fileExtension)
        {
            FileStream stream = File.Open(filePath, FileMode.Open,FileAccess.Read);
            IExcelDataReader excelReader;

            if (fileExtension == ".xls")
            {
                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else
            {
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            //DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            ////5. Data Reader methods
            //while (excelReader.Read())
            //{
            //    //excelReader.GetInt32(0);
            //}

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            return result.Tables[0];
        }


    }
}