Introduction
Here I will explain how to Import Excel in ASP.NET with C# and allow user to map Excel column with SQL table column before save excel data.
Database
I have made use of following table schema
Sql Connection String
<connectionStrings>
<add name="CommectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|ExcelDatabase.mdf;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Model
public class ImportModel
{
public string SqlColName { get; set; }
public List<string> ListExcelCols{ get; set; }
}
Here I will explain how to Import Excel in ASP.NET with C# and allow user to map Excel column with SQL table column before save excel data.
Database
I have made use of following table schema
Sql Connection String
<connectionStrings>
<add name="CommectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|ExcelDatabase.mdf;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Model
public class ImportModel
{
public string SqlColName { get; set; }
public List<string> ListExcelCols{ get; set; }
}
Home.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Import.aspx.cs" Inherits="ExcelImporting.Import" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload runat="server" ID="filecontrol" />
<asp:Button runat="server" ID="btnUpload" OnClick="btnUpload_Click" Text="Upload" />
<hr />
<table border="1">
<thead>
<tr>
<td>Parent Column</td>
<td width="10px"></td>
<td>Excel Column</td>
</tr>
</thead>
<tfoot>
<tr>
<td colspan="3"></td>
</tr>
</tfoot>
<tbody>
<asp:Repeater runat="server" ID="Repeater1" OnItemDataBound="Repeater1_ItemDataBound1">
<ItemTemplate>
<tr>
<td><asp:Label runat="server" ID="lblSqlColName" Text='<%#Eval("SqlColName") %>'></asp:Label></td>
<td></td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
<tr>
<td></td>
<td></td>
<td>
<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click"/>
</td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
Home.aspx.cs
using ExcelImporting.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace ExcelImporting
{
public partial class Import : System.Web.UI.Page
{
OleDbDataAdapter oleda;
OleDbCommand cmd;
static DataSet dsExcel;
DataSet ds;
OleDbConnection excelConnection;
SqlDataAdapter sqlda;
SqlCommand sqlcmd;
SqlConnection sqlconnection;
String _StrConnection = ConfigurationManager.ConnectionStrings["CommectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
// Excel import
oleda = new OleDbDataAdapter();
dsExcel = new DataSet();
cmd = new OleDbCommand();
string tmp = DateTime.Now.ToString("MMM-ddd-d-HH-mm-ss-yyyy") + "_" + filecontrol.PostedFile.FileName;
string path = System.IO.Path.GetFullPath(Server.MapPath(Convert.ToString("~/ExcelFiles/") + tmp));
filecontrol.SaveAs(Server.MapPath(Convert.ToString("~/ExcelFiles/") + tmp));
string excelConnectionString = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=") + path) + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';";
excelConnection = new OleDbConnection(excelConnectionString);
cmd.CommandText = "Select * from [Sheet1$]";
cmd.CommandType = CommandType.Text;
cmd.Connection = excelConnection;
excelConnection.Open();
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(dsExcel, "dataExcel");
List<string> list = new List<string>();
ExcelDataset = dsExcel;
list = BindExcelCols(dsExcel.Tables["dataExcel"]);
//Sql Table column fetch
if (list.Count > 0)
{
getSqlTableColumns(list);
}
}
private void getSqlTableColumns(List<string> excelCols)
{
ds = new DataSet();
sqlcmd = new SqlCommand();
sqlconnection = new SqlConnection(_StrConnection);
sqlcmd.CommandText = "Select * from TableToImportExcel";
sqlcmd.CommandType = CommandType.Text;
sqlcmd.Connection = sqlconnection;
sqlconnection.Open();
sqlda = new SqlDataAdapter(sqlcmd);
sqlda.Fill(ds, "TableToImportExcel");
var Importlist = BindSqlCols(ds.Tables["TableToImportExcel"], excelCols);
Repeater1.DataSource = Importlist;
Repeater1.DataBind();
}
private List<string> BindExcelCols(DataTable table)
{
List<string> list = new List<string>();
foreach (DataColumn column in table.Columns)
{
list.Add(column.ColumnName);
}
return list;
}
private List<ImportModel> BindSqlCols(DataTable table, List<string> excelCols)
{
List<ImportModel> list = new List<ImportModel>();
foreach (DataColumn column in table.Columns)
{
list.Add(new Models.ImportModel { ListExcelCols = excelCols, SqlColName = column.ColumnName });
}
return list;
}
protected void Repeater1_ItemDataBound1(object sender, RepeaterItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType ==ListItemType.AlternatingItem)
{
var Value = (ImportModel)e.Item.DataItem;
DropDownList List = new DropDownList();
List = (DropDownList)e.Item.FindControl("DropDownList1");
List.DataSource = Value.ListExcelCols;
List.DataBind();
}
}
public static DataSet ExcelDataset {
get { return dsExcel; }
set { dsExcel= value ; }
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlBulkCopy sqlBulk = new SqlBulkCopy(_StrConnection);
sqlBulk.BatchSize = 150;
sqlBulk.BulkCopyTimeout = 600;
//Give your Destination table name
sqlBulk.DestinationTableName = "dbo.TableToImportExcel";
sqlBulk.ColumnMappings.Clear();
foreach (RepeaterItem item in Repeater1.Items)//lblSqlColName
{
string key = ((Label)item.FindControl("lblSqlColName")).Text;
string value = ((DropDownList)item.FindControl("DropDownList1")).SelectedItem.Text;
sqlBulk.ColumnMappings.Add(value, key);
}
dsExcel = ExcelDataset;
sqlBulk.WriteToServer(dsExcel.Tables[0]);
}
}
}
No comments:
Post a Comment