Loop and insert data of all sheets in Excel file to database in ASP.Net

Last Reply on Apr 23, 2014 10:21 AM By Azim

Posted on Apr 23, 2014 04:26 AM

Dear,

I want to insert data in sql server from sheet within excelsheet ,there are three sheets in the file which i want to upload in three different tables,currently i am doing by selecting an individual sheet and the table to upload thus doing it three times for three sheets can i do it in one go,like passing all the shhets to a function or something like that or how can i do it in a better way and also i cannot directly upload sheet into table as i have to map one column of excel to different table of database to get its value like i need statecode but statename is provided in the sheet so i map statecode to statename in a staging table and then i upload the data,any guidance to better this approach?

 

Thanks 

Posted on Apr 23, 2014 10:21 AM Modified on on Apr 28, 2014 04:07 AM

Note: Download the sample and change the above button event code. In the files folder you will find excel files. Only one sheet is available in excel so you need to add more sheets and with some data. Refer the article and run the project.

C#

protected void btnSave_Click(object sender, EventArgs e)
{
    for (int i = 1; i < ddlSheets.Items.Count; i++)
    {
        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.Items[i].Text;
        cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = "D:/Excel/Excel03.xls";
        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;

        }
    }

}

SQL

Table

CREATE TABLE [dbo].[tblCustomers](
	[CustomerID] [nchar](5) NOT NULL,
	[City] [nvarchar](30) NULL,
	[Country] [nvarchar](30) NULL,
	[PostalCode] [nvarchar](20) NULL
) ON [PRIMARY]
GO

Stored Procedure

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

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

Screenshot