Here
I am explaining how to import Excel Sheet Rows into SQL Server Database table using
ADO.Net in an ASP.Net Web Application.
Concept
1.
User uploads the Excel File.
2.
Based on the extension it is decided whether it is Excel 97 – 2003 or Excel
2007 format.
3.
User can select whether the Excel Sheet has header row or not using the Radio Buttons
4.
The Excel file is uploaded and then sheets in the Excel workbook are read into a
DropDownList.
5.
User has to now select the Sheet from the dropdown whose data he wants to import.
6.
User has to enter the name of the table which he wants the data to be imported.
7.
User presses OK Button and the data is imported into the SQL Server Database table
and the user is updated with the status.
Stored Procedures
For this article I have
created two stored procedures one to read the Excel 97 – 2003 format and other
Excel 2007 format. Though the Microsoft Ace Driver can read both still I have used
Jet for Excel 97 - 2003 formats.
Excel 97 –
2003 Format
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
spx_ImportFromExcel03
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(1000)
IF OBJECT_ID
(@TableName,'U')
IS NOT NULL
SET @SQL =
'INSERT INTO ' +
@TableName + ' SELECT
* FROM OPENDATASOURCE'
ELSE
SET @SQL =
'SELECT * INTO ' +
@TableName + ' FROM OPENDATASOURCE'
SET @SQL = @SQL
+ '(''Microsoft.Jet.OLEDB.4.0'',''Data
Source='
SET @SQL = @SQL
+ @FilePath +
';Extended Properties=''''Excel 8.0;HDR='
SET @SQL = @SQL
+ @HDR + ''''''')...['
SET @SQL = @SQL
+ @SheetName +
']'
EXEC sp_executesql
@SQL
END
GO
Excel 2007 Format
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
spx_ImportFromExcel07
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(1000)
IF OBJECT_ID
(@TableName,'U')
IS NOT NULL
SET @SQL =
'INSERT INTO ' +
@TableName + ' SELECT
* FROM OPENDATASOURCE'
ELSE
SET @SQL =
'SELECT * INTO ' +
@TableName + ' FROM OPENDATASOURCE'
SET @SQL = @SQL
+ '(''Microsoft.ACE.OLEDB.12.0'',''Data
Source='
SET @SQL = @SQL
+ @FilePath +
';Extended Properties=''''Excel 12.0;HDR='
SET @SQL = @SQL
+ @HDR + ''''''')...['
SET @SQL = @SQL
+ @SheetName +
']'
EXEC sp_executesql
@SQL
END
GO
In
the above stored procedures, I have used four input parameters
1.@SheetName
- Name of the Excel Sheet to be read.
2.@FilePath
- Path of the Excel File
3.@HDR - Indicates
whether first row in the excel sheet will be considered as Header row or not.
4.@TableName
- The name of the table in which the Excel Sheet data will be transferred if the
table is not present it will be created.
When you run the above
stored procedure first time you might get the following error message.
Msg 15281, Level
16, State 1, Line 1
SQL Server blocked
access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc
Distributed Queries' because this component is turned off as part of the security
configuration for this server. A system administrator can enable the use of 'Ad
Hoc Distributed Queries' by using sp_configure. For more information about enabling
'Ad Hoc Distributed Queries', see "Surface Area Configuration"
in SQL Server Books Online.
In
order to run the above stored procedure you will need to enable Surface Area Configuration
in your SQL Server in the following way
Execute
the below four statements one by one in the SQL Server Query Analyzer
sp_configure
'show advanced options',
1
sp_configure 'Ad
Hoc Distributed Queries', 1
Also
in order to use the Microsoft OLEDB Ace Driver you will need to install the
2007 Office System Driver: Data Connectivity Components which is necessary
for Microsoft OLEDB ACE 12.0 driver to work using the link below
2007 Office System Driver: Data Connectivity Components
I faced
the following Error while running the OLEDB Ace stored procedure
Msg 7399, Level
16, State 1, Line 2
The OLE DB provider
"Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported
an error. Access denied.
Msg 7350, Level
16, State 2, Line 2
Cannot get the
column information from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for
linked server "(null)".
You can found an article on the solutions for it. You can visit it using the link below
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)"
Front End Design
Below
is the markup of the HTML mark of the asp.net web page. There are 2 panels. First
with an upload button, Asp.Net FileUpload control and a label to display the status
used to upload the Excel File. Second one with label to display the uploaded Excel
file name, DropDownList which contain the names of Sheets of the Excel workbook,
an ASP.Net RadioButtonList to capture whether the Sheet has header row and finally
two buttons one to import the Excel Sheet rows into the SQL Server Database table
and other one to cancel.
<asp:Panel ID="Panel1"
runat="server">
<asp:FileUpload
ID="FileUpload1"
runat="server"
/>
<asp:Button
ID="btnUpload"
runat="server"
Text="Upload"
OnClick="btnUpload_Click"
/>
<br />
<asp:Label
ID="lblMessage"
runat="server"
Text=""
/>
asp:Panel>
<asp:Panel ID="Panel2"
runat="server"
Visible = "false" >
<asp:Label
ID="Label5"
runat="server"
Text="File Name"/>
<asp:Label
ID="lblFileName"
runat="server"
Text=""/>
<br />
<asp:Label
ID="Label2"
runat="server"
Text="Select Sheet"
/>
<asp:DropDownList
ID="ddlSheets"
runat="server"
AppendDataBoundItems
= "true">
asp:DropDownList>
<br />
<asp:Label
ID="Label3"
runat="server"
Text="Enter Source Table Name"/>
<asp:TextBox
ID="txtTable"
runat="server">asp:TextBox>
<br />
<asp:Label
ID="Label1"
runat="server"
Text="Has Header Row?"
/>
<br />
<asp:RadioButtonList
ID="rbHDR"
runat="server">
<asp:ListItem
Text = "Yes"
Value =
"Yes" Selected
= "True"
>
asp:ListItem>
<asp:ListItem
Text = "No"
Value =
"No">asp:ListItem>
asp:RadioButtonList>
<br />
<asp:Button
ID="btnSave"
runat="server"
Text="Save"
OnClick="btnSave_Click"
/>
<asp:Button
ID="btnCancel"
runat="server"
Text="Cancel"
OnClick="btnCancel_Click"
/>
asp:Panel>
Namespaces
You
will require to import the following namespaces
C#
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using
System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient
Imports
System.Configuration
Web.Config Configurations
I have
used a AppSettings key FolderPath to store the path of the folder
where the uploaded excel file will be stored.
There
are three connections strings as described below
1.
Excel03ConString - Conncection String for Excel
97 – 2003 formats
2.
Excel07ConString - Connection String for Excel
2007 format
3.
conString - Connection String for the SQL Server 2005 Express Database.
<appSettings>
<add key="FolderPath" value="Files/"/>
appSettings>
<connectionStrings>
<add
name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add
name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add
name="conString"
connectionString="Data Source=.\SQLEXPRESS;
database=ExcelImport;Integrated Security=true"/>
connectionStrings>
Uploading the excel Workbook
User
has to first upload the Excel File whose data he wants to transfer to the SQL Server
database onto the server using ASP.Net FileUpload Control and
a Upload button. The code snippet for the Upload Button is given below.
C#
protected void
btnUpload_Click(object sender,
EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName =
Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension =
Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath =
ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath
+ FileName);
FileUpload1.SaveAs(FilePath);
GetExcelSheets(FilePath, Extension, "Yes");
}
}
VB.Net
Protected Sub
btnUpload_Click(ByVal sender
As Object, ByVal
e As System.EventArgs)
If FileUpload1.HasFile Then
Dim FileName As
String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim Extension As
String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim FolderPath As
String = ConfigurationManager.AppSettings("FolderPath")
Dim FilePath As
String = Server.MapPath(FolderPath + FileName)
FileUpload1.SaveAs(FilePath)
GetExcelSheets(FilePath, Extension, "Yes")
End If
End Sub
The
above code snippet simply uploads the Excel Workbook into the path defined in the
Web.Config key. The figure below displays the User Interface for uploading the Excel
File.
You
will notice GetExcelSheets function being called on the click of Upload Button. As the name
suggests the function reads the names of all the sheets present in the Excel Workbook
and binds the result to DropDownList. The complete function is given below
C#
private void
GetExcelSheets(string FilePath,
string Extension, string isHDR)
{
string conStr="";
switch (Extension)
{
case ".xls":
//Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx":
//Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
//Get the Sheets in Excel WorkBoo
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel =
new OleDbConnection(conStr);
OleDbCommand cmdExcel = new
OleDbCommand();
OleDbDataAdapter oda = new
OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
//Bind the Sheets to DropDownList
ddlSheets.Items.Clear();
ddlSheets.Items.Add(new ListItem("--Select Sheet--",
""));
ddlSheets.DataSource=connExcel
.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
ddlSheets.DataTextField = "TABLE_NAME";
ddlSheets.DataValueField = "TABLE_NAME";
ddlSheets.DataBind();
connExcel.Close();
txtTable.Text = "";
lblFileName.Text = Path.GetFileName(FilePath);
Panel2.Visible = true;
Panel1.Visible = false;
}
VB.Net
Private Sub
GetExcelSheets(ByVal FilePath
As String, ByVal
Extension As String,
ByVal isHDR As
String)
Dim conStr As
String = ""
Select Case Extension
Case
".xls"
'Excel 97-03
conStr = ConfigurationManager.ConnectionStrings("Excel03ConString")
_
.ConnectionString
Exit Select
Case ".xlsx"
'Excel 07
conStr = ConfigurationManager.ConnectionStrings("Excel07ConString")
_
.ConnectionString
Exit Select
End Select
'Get the Sheets in Excel WorkBoo
conStr
= String.Format(conStr, FilePath, isHDR)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As
New OleDbDataAdapter()
cmdExcel.Connection
= connExcel
connExcel.Open()
'Bind the Sheets to DropDownList
ddlSheets.Items.Clear()
ddlSheets.Items.Add(New ListItem("--Select Sheet--",
""))
ddlSheets.DataSource
= connExcel.GetOleDbSchemaTable(OleDbSchemaGuid _
.Tables,
Nothing)
ddlSheets.DataTextField
= "TABLE_NAME"
ddlSheets.DataValueField
= "TABLE_NAME"
ddlSheets.DataBind()
connExcel.Close()
txtTable.Text
= ""
lblFileName.Text
= Path.GetFileName(FilePath)
Panel2.Visible
= True
Panel1.Visible
= False
End Sub
Importing the rows from the excel sheet to the database table
As
you can see the excel file name is displayed along with all the sheets in the DropDownList.
The RadioButtonList captures the information about the header row in the Excel Sheet.
Then once the user presses Save button all the rows are read into the database table
which the user has entered in the textbox if the table is not present it will be
created.
The
code snippet for the Save button is give below
&nbnbsp;
C#
protected void
btnSave_Click(object sender,
EventArgs e)
{
string FileName = lblFileName.Text;
string Extension = Path.GetExtension(FileName);
string FolderPath = Server.MapPath (ConfigurationManager
.AppSettings["FolderPath"]);
string CommandText = "";
switch (Extension)
{
case ".xls":
//Excel 97-03
CommandText = "spx_ImportFromExcel03";
break;
case ".xlsx":
//Excel 07
CommandText = "spx_ImportFromExcel07";
break;
}
//Read Excel Sheet using Stored Procedure
//And import the data into Database Table
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new
SqlConnection(strConnString);
SqlCommand cmd = new
SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandText;
cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value =
ddlSheets.SelectedItem.Text;
cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value =
FolderPath + FileName;
cmd.Parameters.Add("@HDR",
SqlDbType.VarChar).Value =
rbHDR.SelectedItem.Text;
cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value =
txtTable.Text;
cmd.Connection = con;
try
{
con.Open();
object count = cmd.ExecuteNonQuery();
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Text = count.ToString() + " records
inserted.";
}
catch (Exception
ex)
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = ex.Message;
}
finally
{
con.Close();
con.Dispose();
Panel1.Visible = true;
Panel2.Visible = false;
}
}
VB.Net
Protected Sub
btnSave_Click(ByVal sender
As Object, ByVal
e As EventArgs)
Dim FileName As String = lblFileName.Text
Dim Extension As String = Path.GetExtension(FileName)
Dim FolderPath As String = Server.MapPath( _
ConfigurationManager.AppSettings("FolderPath"))
Dim CommandText As String = ""
Select Case Extension
Case
".xls"
'Excel
97-03
CommandText = "spx_ImportFromExcel03"
Exit Select
Case ".xlsx"
'Excel 07
CommandText = "spx_ImportFromExcel07"
Exit
Select
End Select
'Read Excel Sheet using Stored Procedure
'And import the data into Database Table
Dim strConnString As
String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim con As
New SqlConnection(strConnString)
Dim cmd As
New SqlCommand()
cmd.CommandType
= CommandType.StoredProcedure
cmd.CommandText = CommandText
cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value =
_
ddlSheets.SelectedItem.Text
cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = _
FolderPath + FileName
cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = _
rbHDR.SelectedItem.Text
cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value =
_
txtTable.Text
cmd.Connection
= con
Try
con.Open()
Dim count
As Object = cmd.ExecuteNonQuery()
lblMessage.ForeColor = System.Drawing.Color.Green
lblMessage.Text = count.ToString() & " records
inserted."
Catch ex As Exception
lblMessage.ForeColor = System.Drawing.Color.Red
lblMessage.Text = ex.Message
Finally
con.Close()
con.Dispose()
Panel1.Visible = True
Panel2.Visible = False
End Try
End Sub
