Programing Solutions on OOPS-Concept: 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">

<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>

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>

<div>

<tbody>

<tr>

<td>

<strong>Please Select Excel File Containing The Details…</strong>

</td>

</tr>

<tr>

<td>

<div style="background: url(hline.gif) repeat-xbottom #F2F2F2; padding: 8px 5px;

border-bottom: 1px solid #ccc;">

<asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>  

<asp:Label ID="lblSheet" 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:Label ID="lblProject" Text="Project: " Height="20px" runat="server"></asp:Label> 

<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!!">

<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]

/****** Object: StoredProcedure [dbo].[usp_AddExcelData] Script Date: 12/01/2011 12:48:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[usp_AddExcelData]

(

@ExcelData varchar(max),

@Createdby varchar(50),

@dcmVer varchar(5),

@ProjID Varchar(10)

)

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

}

download code in VS2010

Programing Solutions on OOPS-Concept

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

21 comments :

Labels

Popular Posts

Followers

Blog Archive