توضیحات و دانلود

نحوه وارد کردن داده های اکسل به SQL Server با استفاده از ASP.Net
0 0
نحوه وارد کردن داده های اکسل به 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

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

همچنین برای استفاده از درایور مایکروسافت 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

 کد بالا به سادگی مراحل ذخیره شده مربوطه را بر اساس پسوند فراخوانی می کند و وضعیت مطابق شکل زیر برای کاربر نمایش داده می شود

 

 


دانلود
  • لینک های دانلود دوره های آموزشی تا پایان دوره قابل دانلود می باشد.
  • برای خارج کردن فایل ها از حالت فشرده از ورژن جدید نرم افزار winrar استفاده کنید.
  • برای خارج کردن فایل ها از حالت فشرده لینک های دانلودی که چندین قسمت می باشند فقط قسمت اول را از حالت فشرده خارج کنید.
  • لطفا توضیحات نوشته شده برای مطالب را با دقت بخوانید.
  • برای نمایش فیلم ها می توانید از نرم افزار هایی مانند Km Player , VLC Player یا Media Player Classic استفاده کنید.

ارسال نظر
ارسال پیام به :