Upload Excel sheet data to SQL Database [ Most Simple ]

In this post I will discuss another common requirement of today dynamic websites that is How to upload Excel sheet data to SQL database. In this post we will also learn how to get the excel sheet name from an already uploaded excel file.

import data from excel sheet to sql database

To get started first create an empty .aspx page and add a file upload tool. Also add a button named ‘Submit’ or ‘Upload’ whichever you like. Now create button click event for the button we have just created . Before writing functions in button click event of the button, we will discuss the function which gets the name of excel sheet in the excel file uploaded.

We need to use this function because after uploading the excel file to server we will use the excel file as Excel Database and will fetch data from excel sheets to upload it to our main SQL database.

So first create a new ‘functions.cs’ file in your solution under App_code ASP Folder and paste the code below in it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
/// <summary>
/// Summary description for functions
/// </summary>
public class functions
{
 public functions()
 {  //
  // TODO: Add constructor logic here
  //
 }
    public String[] GetExcelSheetNames(string excelFile)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {
            String connString = "provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0; Data Source=" + excelFile + ";";
            // Create connection object by using the preceding connection string.
            objConn = new OleDbConnection(connString);
            // Open connection with the database.
            objConn.Open();
            // Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            // Loop through all of the sheets if you want too...
            for (int j = 0; j < excelSheets.Length; j++)
            {
                // Query each excel sheet.
            }
            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }
}

Now, upto this step we have the function which can return excelsheet names. This function will take the excel file name (with full path) as input and will return a string array of excel sheet names.

Next, add following namespaces to your code page:

using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

Now add following code in button click event of upload button:

   protected void butUploadFile_Click(object sender, EventArgs e)
   {
       string excelConnectionString = string.Empty;
       if (Page.IsValid && FileUpldExcel.HasFile)
       {
           try
           {
               filename = Server.MapPath("exceluploaded/") + FileUpldExcel.FileName.ToString(); //getting complete path for saving the uploaded excel
               FileUpldExcel.SaveAs(filename); // saving the excel
               //Create connection string to Excel work book
               if ( FileUpldExcel.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                   excelConnectionString = "provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0; data source=" + filename + ";"; //conncection string for .xlsx file
               else
                   excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=\"Excel 8.0;HDR=YES\"; data source=" + filename + ";";
               
   //create connection to uploaded excel file treating it as database
               OleDbConnection excelCon = new OleDbConnection(excelConnectionString);
               //getting the worksheet name from uploaded excel file
               functions func = new functions();
               string[] excelsheet = func.GetExcelSheetNames(filename);
   
   //oleDB command to fetch data from excelsheet
               OleDbCommand excelCmd = new OleDbCommand("SELECT [rollno],[emailid] FROM [" + excelsheet[0].ToString() + "]", excelCon);
               //getting all data in a dataset
               OleDbDataAdapter ad = new OleDbDataAdapter(excelCmd);
               DataSet ds = new DataSet();
               ad.Fill(ds);
               //counting number of non-blanck rows in excelsheet
               int countRow = 0;
               for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
               {
                   if (ds.Tables[0].Rows[i][0].ToString() != "")
                   {
                       countRow++;
                   }
                   else
                       break;
               }
               //now storing data to database
               SqlCommand command = new SqlCommand();
               command.Connection = con;
               con.Open();
               for (int i = 0; i < countRow; i++)
               {
                    command.CommandText = "INSERT INTO student(rollno,emailid) VALUES('" + ds.Tables[0].Rows[i][0].ToString().Trim() + "','" + ds.Tables[0].Rows[i][1].ToString().Trim() + "')";
                    command.ExecuteNonQuery();
               }
               con.Close();
               //deleting the uploaded excel file
               FileInfo file = new FileInfo(filename);
               try
               {
                   if (file.Exists)
                       file.Delete();
               }
               catch
               {
                    //show error message
               }
  }
  catch
  {
  }
 }
}