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 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-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!!">
<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];
}
}
}
