نحوه وارد کردن داده های اکسل به SQL Server با استفاده از ASP.Net
با سلام در یکی از مقاله های قبلی توضیح وارد کردن برگه اکسل را در کنترل GridView ASP دادیم
در اینجا توضیح می دهیم که چگونه می توان ردیف های Excel Sheet را در جدول پایگاه داده SQL Server با استفاده از ADO.Net در یک برنامه وب ASP.Net وارد کرد.
مفهوم
1. کاربر پرونده Excel را آپلود می کند.
2. با توجه به پسوند تصمیم گرفته می شود که آیا این فرمت Excel 97 - 2003 یا Excel 2007 است.
3. کاربر می تواند انتخاب کند که آیا صفحه Excel دارای سطر هدر است و یا از دکمه های رادیویی استفاده نکند
4- فایل اکسل بارگذاری می شود و سپس برگه های موجود در Excel workbook در DropDownList خوانده می شوند.
5- کاربر مجبور است اکنون برگه را از کشویی که داده های وی را می خواهد وارد کند انتخاب کند.
6. کاربر مجبور است نام جدول را که می خواهد داده ها وارد شود ، مشخص کند.
7. کاربر دکمه OK را فشار می دهد و داده ها در جدول بانک اطلاعات سرور SQL وارد می کند و کاربر با وضعیت به روز می شود.
Stored Procedures
برای این مقاله دو Stored Procedures ایجاد کرده ایم که یکی از آنها می توانید فرمت Excel 97 - 2003 و سایر قالب های Excel 2007 را بخوانید. اگرچه درایور Microsoft Ace می تواند هر دو را بخواند اما هنوز از فرمت های Jet برای Excel 97 - 2003 استفاده کرده ایم.
فرمت Excel 97 - 2003
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
فرمت 2007 اکسل
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
در Stored Procedures فوق از چهار پارامتر ورودی استفاده کرده ایم
1. @ SheetName - نام برگه اکسل که باید خوانده شود.
2.FilePath - مسیر فایل اکسل
3. @ HDR - مشخص می کند که ردیف اول در برگه اکسل به عنوان ردیف سربرگ در نظر گرفته خواهد شد یا نه.
4.TableName - نام جدول که در صورت عدم وجود جدول ، داده های Excel Sheet منتقل می شود ، ایجاد میکند.
هنگامی که اولین بار Stored Procedures فوق را اجرا کردید ، ممکن است پیام خطای زیر را دریافت کنید.
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.
برای اجرای روش ذخیره شده فوق باید به روش زیر پیکربندی Surface Area را در SQL Server خود فعال کنید
چهار عبارت زیر را یک به یک در SQL Server Query Analyzer انجام دهید
sp_configure 'show advanced options', 1
sp_configure 'Ad Hoc Distributed Queries', 1
همچنین برای استفاده از درایور مایکروسافت OLEDB Ace شما نیاز به نصب درایور سیستم آفیس 2007 دارید: اجزاء اتصال داده که برای کار درایور Microsoft OLEDB ACE 12.0 ضروری است با استفاده از لینک 2007 درایور سیستم آفیس: مؤلفه های اتصال داده میتوانید دریافت کنید
هنگام اجرای 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)".
طراحی Front End
در زیر کد HTML از صفحه وب سایت asp.net است. 2 پنل وجود دارد. ابتدا با یک دکمه آپلود ، کنترل FileUpload و یک برچسب را نشان دهید تا وضعیت مورد استفاده برای آپلود فایل اکسل را نمایش دهد. دوم با برچسب برای نمایش نام فایل آپلود شده اکسل ، DropDownList که حاوی اسامی برگه های Excel workbook است ، یک RadioPuttonList برای ثبت اینکه آیا Sheet دارای سطر هدر است و در نهایت دو دکمه یکی برای وارد کردن سطرهای Excel Sheet در جدول پایگاه داده SQL Server و دیگری برای لغواست
<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>
فضاهای نام
شما باید نامهای زیر را وارد کنید
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
از یک کلید AppSettings FolderPath برای ذخیره مسیر فایلی که فایل اکسل آپلود شده در آن ذخیره می شود استفاده کرده ایم.
سه رشته اتصالی وجود دارد که در زیر شرح داده شده است
1. Excel03ConString - رشته اتصالی برای قالبهای Excel 97 - 2003
2. Excel07ConString - رشته اتصال برای قالب Excel 2007
3. conString - رشته اتصال برای 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>
آپلود excel Workbook
کاربر ابتدا باید فایل Excel را که داده های مورد نظر خود را برای انتقال به پایگاه داده SQL Server بارگذاری می کند ، آپلود کند . از کنترل FileUpload و یک دکمه بارگذاری روی سرور استفاده کنید . کد دکمه آپلود در زیر آورده شده است.
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
کد بالا به سادگی Excel Workbook را در مسیری که در کلید Web.Config تعریف شده است آپلود می کند. شکل زیر رابط کاربری برای بارگذاری فایل اکسل را نشان می دهد.

متوجه خواهید شد که عملکرد GetExcelSheets با کلیک روی دکمه آپلود فراخوانی می شود. همانطور که از نام آن پیداست ، این تابع اسامی تمام برگه های موجود در Excel Workbook را می خواند و نتیجه را به DropDownList متصل می کند. عملکرد کامل در زیر آورده شده است
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
وارد کردن سطرها از برگه اکسل به جدول پایگاه داده
پس از ثبت برگه ها در DropDownList ، رابط کاربری مانند تصویر زیر ظاهر می شود.

همانطور که مشاهده می کنید نام فایل اکسل به همراه تمام برگه ها در DropDownList نمایش داده می شود. RadioButtonList اطلاعات مربوط به سطر هدر در صفحه Excel را ذخیره می کند. سپس هنگامی که کاربر دکمه ذخیره را فشار داد ، تمام ردیف ها در جدول دیتابیس که کاربر در جعبه متن وارد کرده است ، خوانده می شود و اگر جدول موجود نباشد ، ایجاد می شود
کد زیر برای دکمه ذخیره آورده شده است
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
کد بالا به سادگی مراحل ذخیره شده مربوطه را بر اساس پسوند فراخوانی می کند و وضعیت مطابق شکل زیر برای کاربر نمایش داده می شود
