ASHIS KUMAR DAS: Read and Display Data From an Given Sheet of Excel File (.xsl or .xlsx) and Save in SQL Database in ASP.NET

The page design :

<%@ 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 td, tr.sectiontableentry2 td
        {
            padding: 4px;
        }
        tr.sectiontableentry1 td
        {
            padding: 8px 5px;
            background: url(hline.gif) repeat-x bottom;
        }
        tr.sectiontableentry2 td
        {
            padding: 8px 5px;
            background: url(hline.gif) repeat-x bottom #F2F2F2;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table style="padding: 5px; font-size: 11px;" align="center" border="0">
            <tbody>
                <tr>
                    <td>
                        <strong>Please Select Excel File Containing The Details…</strong>
                    </td>
                </tr>
                <tr>
                    <td>
                        <div style="background: url(hline.gif) repeat-x bottom #F2F2F2; padding: 8px 5px;
                            border-bottom: 1px solid #ccc;">
                            <asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>  
                            <asp:Label ID="TextBox1" Text="Sheet Name: " Height="20px" runat="server"></asp:Label>  
                            <asp:TextBox ID="txtSheetName" runat="server" MaxLength="100"></asp:TextBox>  <asp:RequiredFieldValidator
                                ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtSheetName"
                                ErrorMessage="*" ForeColor="Red" SetFocusOnError="True"></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" />
                            <AlternatingRowStyle CssClass="sectiontableentry1" />
                        </asp:GridView>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    </form>
</body>
</html>

Code Behind File :

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

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        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$]";

                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);

            // create SqlConnection
            SqlConnection myConnection = new SqlConnection(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,string sheetName)
        {
            //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)
                {
//Here you can iterate through all the sheets as well if you don't want to specify a sheetname
                    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;
        }
}
}

SQL Script :

Table Creation :

/****** Object: Table [dbo].[CodeReview]    Script Date: 11/17/2011 11:27:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CodeReview](
    [QID] [int] IDENTITY(1,1) NOT NULL,
    [Question_Description] [varchar](250) NULL,
    [Category] [varchar](50) NULL,
    [Reviewed] [varchar](10) NULL,
    [ReviewerComments] [varchar](500) NULL,
    [DevelopmentStatus] [varchar](10) NULL,
    [DeveloperComments] [varchar](500) NULL,
    [CreartedDate] [datetime] NULL,
    [Createdby] [varchar](50) NULL,
    [dcmVer] [varchar](5) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CodeReview] ADD CONSTRAINT [DF_CodeReview_CreartedDate] DEFAULT (getdate()) FOR [CreartedDate]
GO

Procedure :

/****** Object: StoredProcedure [dbo].[usp_AddExcelData]    Script Date: 11/17/2011 11:28:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[usp_AddExcelData]
(
@ExcelData varchar(max),
@Createdby varchar(50),
@dcmVer varchar(5)
)
AS
BEGIN
DECLARE @idoc int

DECLARE @MyTableVar table(
ID INT identity(1,1),
[Question_Description] nvarchar(255),
[Category] nvarchar(255),
[Reviewed] nvarchar(255),
[ReviewerComments] nvarchar(255),
[DevelopmentStatus] nvarchar(255),
[DeveloperComments] nvarchar(255),
CreatedBy Nvarchar(50),
dcmVer nvarchar(5)
)

EXEC sp_xml_preparedocument @idoc OUTPUT, @ExcelData
print @idoc

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

EXEC sp_xml_removedocument @idoc

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

Update @MyTableVar set CreatedBy=@Createdby,dcmVer=@dcmVer

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 from @MyTableVar where id=@i

    SET @i = @i + 1
END

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

END

GO

ASHIS KUMAR DAS

Welcome

Thursday 17 November 2011

Read and Display Data From an Given Sheet of Excel File (.xsl or .xlsx) and Save in SQL Database in ASP.NET

1 comment:

Important Links