importExcel.aspx Page
===================================================================== <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="importExcel.aspx.cs" Inherits="Eisk.Web.admin.importExcel" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Upload Excel File to Database </title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload ID="fluInput" runat="server" />
<asp:Button ID="btnImport" Text="Import" runat="server" OnClick="btnUploadFile_Click"
CssClass="button medium green" />
</form>
</body>
</html>
=====================================================================
Code Behind importExcel.aspx.cs
=====================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Eisk.BusinessEntities;
using Eisk.BusinessLogicLayer;
namespace Eisk.Web.admin
{
public partial class importExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUploadFile_Click(object sender, EventArgs e)
{
ConvertFile();
}
protected void ConvertFile()
{
if (fluInput.HasFile)
{
string strFileName = Server.HtmlEncode(fluInput.FileName);
string strExtension = Path.GetExtension(strFileName);
if (strExtension != ".xls" && strExtension != ".xlsx")
{
Response.Write("<script>alert('Please select a Excel spreadsheet to import!');</script>");
return;
}
string strUploadFileName = "~/admin/Excell/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
strUploadFileName = Server.MapPath(strUploadFileName);
fluInput.SaveAs(strUploadFileName);
string connectionString = "";
if (strExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 8.0;HDR=YES;'";
}
else
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";
}
try
{
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
string sheetName = "Sheet1";
string query = "SELECT * FROM [" + sheetName + "$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
adapter.Fill(ds);
dt = ds.Tables[0];
adapter.Fill(dt);
connection.Close();
int number_of_columns = dt.Columns.Count;
int number_of_rows = ds.Tables[0].Rows.Count / 2;
string[,] sheetEntries = new string[number_of_rows, number_of_columns];
string[] columnNames = new string[number_of_columns];
int startingColumn = 1;
for (int i = startingColumn; i < number_of_rows; i++)
{
for (int j = 0; j < number_of_columns; j++)
sheetEntries[i, j] = dt.Rows[i].ItemArray.GetValue(j).ToString();
}
List<ActivationCode> activationCodeList = CovertToList(sheetEntries, number_of_columns, number_of_rows);
ExportToSQLServerActivationCode(activationCodeList);
if (File.Exists(strUploadFileName))
{
File.Delete(strUploadFileName);
}
}
catch (Exception ex)
{
}
}
}
private List<ActivationCode> CovertToList(string[,] sheetEntries, int number_of_columns, int number_of_rows)
{
List<ActivationCode> aclist = new List<ActivationCode>();
ActivationCode aCode = null;
DateTime toDay = DateTime.Now;
for (int i = 0; i < number_of_rows; i++)
{
aCode = new ActivationCode();
string activationCodeName = StringNullOrEmptyChecking(sheetEntries[i, 2]);
string servicePlanSKU = StringNullOrEmptyChecking(sheetEntries[i, 5]);
if (!string.IsNullOrEmpty(activationCodeName) && !string.IsNullOrEmpty(servicePlanSKU))
{
aCode.ActivationCodeName = activationCodeName;
aCode.ServicePlanSKU = servicePlanSKU;
aCode.MinProductsNo = 1;
string maxProductsNo = StringNullOrEmptyChecking(sheetEntries[i, 11]);
if (!string.IsNullOrEmpty(maxProductsNo))
{
aCode.MaxProductsNo = Convert.ToInt16(maxProductsNo);
}
else
{
aCode.MaxProductsNo = 1;
}
aCode.IsUsed = false;
aCode.UploadDate = toDay;
aclist.Add(aCode);
}
}
return aclist;
}
private string StringNullOrEmptyChecking(object value)
{
if (value == null)
return null;
string returnValue = null;
if (value != null)
{
returnValue = value.ToString();
}
if (value == "")
{
returnValue = null;
}
return returnValue;
}
private void ExportToSQLServerActivationCode(List<ActivationCode> activationCodeList)
{
ActivationCodeBLL activationCodeBLL = new ActivationCodeBLL();
ActivationCodeRejectedBLL activationCodeRejectedBLL = new ActivationCodeRejectedBLL();
List<ActivationCode> allActivationCodeList = activationCodeBLL.GetAllActivationCodes();
List<ActivationCode> allActivationCodeListSelected = new List<ActivationCode>();
List<ActivationCodeRejected> activationCodeRejectedList = new List<ActivationCodeRejected>();
foreach (ActivationCode item in activationCodeList)
{
ActivationCode isExists = allActivationCodeList.Find(x => x.ActivationCodeName == item.ActivationCodeName);
if (isExists == null)
{
allActivationCodeListSelected.Add(item);
}
else
{
ActivationCodeRejected activationCodeRejected = new ActivationCodeRejected();
activationCodeRejected.ActivationCodeName = item.ActivationCodeName;
activationCodeRejected.ServicePlanSKU = item.ServicePlanSKU;
activationCodeRejected.MinProductsNo = item.MinProductsNo;
activationCodeRejected.MaxProductsNo = item.MaxProductsNo;
activationCodeRejected.IsUsed = item.IsUsed;
activationCodeRejected.UploadDate = item.UploadDate;
activationCodeRejectedList.Add(activationCodeRejected);
}
}
foreach (var item in allActivationCodeListSelected)
{
activationCodeBLL.CreateNewActivationCode(item);
}
foreach (var item in activationCodeRejectedList)
{
activationCodeRejectedBLL.CreateNewActivationCodeRejected(item);
}
}
}
}
