Excel files are constantly encountered in web programming, especially during bulk update process. So, it’s often necessary to upload and process Excel files.

As developers, we often need to import data from Excel files and use it to fulfill our application and data management requirements. Here I will explain how to import exactly the data we need directly into a C# project and then manipulate it programmatically to update SQL database.

First, we need a plugin. Install the ClosedXML library from NuGet Package Manage

use it in your project

using ClosedXML.Excel;

In the web.config file we have to write the connection string for both xls and xlsx extension

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/> <add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

Now prepare your page using a fileupload and button control

<div class="form-group">

<label for="FileUpload1">Select the Excel File (.xls only)</label>

<asp:FileUpload ID="FileUpload1" CssClass="form-control" onchange="checkfile(this);" runat="server" />
<br/>

<asp:Button Text="Preview" ID="btnReview" runat="server" class="btn btn-primary btn-md" OnClick="btnPreview_Click" />
<asp:Button Text="Confirm And Update" ID="Button2" runat="server" class="btn btn-primary btn-md" OnClick="Button1_Click" Visible="False" />
</div>

In the preview button click event you may accept the excel file and read

protected void btnPreview_Click(object sender, EventArgs e){ //Upload and save the file
string excelPath = Server.MapPath("~/assets/images/uploads/bulkupload/") + DateTime.Now.ToString("yyyyMMddTHHmmss") + Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

//based on extension we can use different connection string
if (extension == “.xls” || extension == “.xlsx”)
{
switch (extension) { case ".xls":
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break;
case ".xlsx":
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break; }
conString = string.Format(conString, excelPath); using (OleDbConnection excel_con = new OleDbConnection(conString))

{ excel_con.Open(); //read the excel data string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); DataTable dtExcelData = new DataTable();

//add columns to a datatable which matches the columns in excel dtExcelData.Columns.Add("col1", typeof(int)); dtExcelData.Columns.Add("col2", typeof(int)); dtExcelData.Columns.Add("col3", typeof(int)); dtExcelData.Columns.Add("col4", typeof(string));

//read using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

{ oda.Fill(dtExcelData); } excel_con.Close(); //Get the sql server connection string string consString = ConfigurationManager.ConnectionStrings["myDBConString"].ConnectionString; using (SqlConnection con = new SqlConnection(consString))

{ //use SqlBulkCopy using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { sqlBulkCopy.DestinationTableName = "your table name"; // Now perform column mapping sqlBulkCopy.ColumnMappings.Add("col1", "orderRefNo"); sqlBulkCopy.ColumnMappings.Add("col2", "customerId"); sqlBulkCopy.ColumnMappings.Add("col3", "shippingAddressId"); sqlBulkCopy.ColumnMappings.Add("col4", "deliveryDate"); con.Open(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); } } } }