Exporting / Importing Excel data into SQL Server Database using ASP.net

Introduction

In certain occasions you may need to export / import large excel spreadsheet to SQL server data base. In this small and simple article i would like to demonstrate the process of exporting / importing Excel Data into SQL server database.

Step 1:

I am assuming you have created a folder and uploaded your Microsoft Excel worksheet in that folder.

Step 2:

You can create class for creating an export function

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
/// /// Class for Exporting Excel Data to SQL server/// public class clsExcelToSqlServer
{
public clsExcelToSqlServer()
{
//// TODO: Add constructor logic here//}
private string _FilePath;
public String FilePath
{
get { return _FilePath; }
set { _FilePath = value; }
}
public DataTable getDataFromExcelSheet()
{
try{
//File path of Excel Spread sheetFilePath = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath) +
"/ExcelFolder/ExcelAppliance.xls";
//Connection string to connect Excel datastring strConnectionString = string.Empty; strConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ FilePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
cnCSV.Open();
//Selecting all rows from excel sheetOleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
OleDbDataAdapter daCSV = new OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
DataTable dtCSV = new DataTable();
//Filling excel data into data tabledaCSV.Fill(dtCSV);
cnCSV.Close();
daCSV = null;
return dtCSV;
}
catch (Exception ex)
{
return null;
}
finally { }
}
}

 getDataFromExcelSheet() function returns a Data Table and you can use this Data table to export data into to SQL Server.