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

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

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.

sp_configure 'show advanced options', 1

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

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)".

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

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Configuration;

using System.Data.SqlClient;

Imports System.Data

Imports System.Data.OleDb

Imports System.IO

Imports System.Data.SqlClient

Imports System.Configuration

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

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");

    }

}

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

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;

}

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

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;

    }

}

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