Menu Bar

Wednesday, May 25, 2016

Excel to Database - ASP.NET, C#, Webforms ( 9 Steps)


Requirement (Simple POC):

  1. Upload Excel File to Server (ASP.net)
  2. Push the Excel Data to Database Server (MS SQL Server)
  3. Display the data in browser

Steps to accomplish this task:

1. Open Microsoft SQL Server and Create Database named "Excel_to_DB"





 2. Create a Table Schema (Follow anyone method below)

2.1 Method 1 - Creating Design by adding table






2.1 Method 2 - Creating a query and execute it



Copy the following code and paste in query window and execute it

USE [Excel_to_DB]
GO

/****** Object:  Table [dbo].[tb_CompanyFinance]    Script Date: 5/25/2016 4:10:22 PM ******/
DROP TABLE [dbo].[tb_CompanyFinance]
GO

/****** Object:  Table [dbo].[tb_CompanyFinance]    Script Date: 5/25/2016 4:10:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tb_CompanyFinance](
 [Segment] [varchar](50) NULL,
 [Country] [varchar](50) NULL,
 [Product] [varchar](50) NULL,
 [DiscountBand] [nchar](10) NULL,
 [UnitsSold] [int] NULL,
 [ManufacturingPrice] [money] NULL,
 [SalePrice] [money] NULL,
 [GrossSales] [money] NULL,
 [Discounts] [varchar](50) NULL,
 [Sales] [money] NULL,
 [COGS] [money] NULL,
 [Profit] [money] NULL,
 [Date] [date] NULL,
 [MonthNumber] [smallint] NULL,
 [MonthName] [nvarchar](50) NOT NULL,
 [Year] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

3. Open Visual Studio 2013 and Create a Webform Project



4. Create a Webform Named "ExcelUpload"






5. Add two connection string in Web.Config under <connecctionStrings> tag
  
    
    
    
  
6. Copy the following content into <body> tag im ExcelUpload.aspx

    
7. Copy the following content into   ExcelUpload.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Data;

public partial class ExcelUpload : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        MsgAlert.Text = "";
    }
    protected void btnupload_Click(object sender, EventArgs e)
    {
        // Configurable
        string DbName = "Excel_to_DB";
        string TableName = "tb_CompanyFinance";
        string connectionString ="";

        if (FileUploadProduct.HasFile)
        {
            try
            {
            
            string fileName = Path.GetFileName(FileUploadProduct.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUploadProduct.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/Excel/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + fileName);
            FileUploadProduct.SaveAs(fileLocation);
            
            //Check whether file extension is xls or xslx
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            else
            {
                File.Delete(fileLocation);
                MsgAlert.Text = "Unsupported File Name Extension. \n Upload only xlsx or xls file types.";
                GridView1.DataSource = "";
                GridView1.DataBind();
                return;
            }

            //Create OleDB Connection and OleDb Command
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            DbDataReader dr = cmd.ExecuteReader();
            string sqlConnectionString = @"Data Source=.;Initial Catalog=" + DbName + ";Integrated Security=True";

            // Clearing table contents
            SqlConnection sql_con = new SqlConnection(sqlConnectionString);
            sql_con.Open();
            string sql = @"DELETE FROM " + TableName +";";
            SqlCommand sql_cmd = new SqlCommand(sql, sql_con);
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();

            SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
            bulkInsert.DestinationTableName = TableName;
            bulkInsert.WriteToServer(dr);
            MsgAlert.Text = "Product uploaded successfully";
            con.Close();
            GridView1.DataSource = dtExcelRecords;
            GridView1.DataBind();
        }
         catch (Exception ex)
            {
                MsgAlert.Text = ex.Message;
            }
         
        }
    }
}

8. Create New folder named "Excel" for keeping the uploaded file into server



9. Build and Run the project. You should get the following output as below


Excel to DB Output
DB output

Sources:


Download the sample data from here
Excel Data - http://go.microsoft.com/fwlink/?LinkID=521962


No comments:

Post a Comment