What we want to achieve?
- We want to upload product and its features using Excel Sheet.
- Excel sheet should be validated as per our sample data.
- If the Excel sheet is invalid list of errors should be display with link to that particular error so that user can reach there by clicking on link itself.
- Also we should be able to correct invalid excel sheet on the screen upload it again.
Sample Excel :
Screen 01 : When we upload Invalid Excel Sheet.
Screen 02 : When we click on error link then that particular error will be focused.
Screen 03 : When we click on error link then that particular error will be focused.
Screen 04: When we correct all errors and upload again.
Step 01 : We have two tables : Products and ProductFeatures
Table Products
CREATE TABLE [dbo].[Products](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](40) NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar](20) NULL,
[UnitPrice] [money] NULL,
[UnitsInStock] [smallint] NULL,
[UnitsOnOrder] [smallint] NULL,
[ReorderLevel] [smallint] NULL,
[Discontinued] [bit] NOT NULL,
[CreatedOn] [date] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table ProductFeatures
CREATE TABLE [dbo].[ProductFeatures](
[FeatureId] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NULL,
[ProductFeature] [nvarchar](max) NULL,
[Status] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[CreatedOn] [date] NULL,
CONSTRAINT [PK_ProductFeatures] PRIMARY KEY CLUSTERED
(
[FeatureId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Step 02 : We have got function and Procedures
Function to Split string using delimiter.
CREATE FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
@Delimiter varchar(100)
)
RETURNS @tblArray TABLE
(
ElementID int IDENTITY(1,1), -- Array index
Element varchar(1000) -- Array element contents
)
AS
BEGIN
-- Local Variable Declarations
-- ---------------------------
DECLARE @Index smallint,
@Start smallint,
@DelSize smallint
SET @DelSize = LEN(@Delimiter)
-- Loop through source string and add elements to destination table array
-- ----------------------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
IF @Index = 0
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(@DelimitedString)))
BREAK
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Element)
VALUES
(LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))
SET @Start = @Index + @DelSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
RETURN
END
Procedure 01:
CREATE PROC [dbo].[Usp_InsertProductAndFeatures]
@ProductName NVARCHAR(MAX),
@CategoryId INT,
@ProductFeatures VARCHAR(MAX)
AS
BEGIN
DECLARE @PId INT
DECLARE @Feature VARCHAR(MAX)
INSERT Products
(
ProductName,
CategoryID,
CreatedOn
)
VALUES
(
@ProductName,
@CategoryId,
GETDATE()
);
SET @PId= @@IDENTITY;
DECLARE @ExecQuery NVARCHAR(MAX);
SELECT @ExecQuery = COALESCE(@ExecQuery +';' ,'') + 'EXEC [dbo].[Usp_AddProductFeatures] '+CAST(@PId AS VARCHAR)+', '''+ CAST(GETDATE() AS VARCHAR) +''', ''' + Element+''', ''+|+'''
FROM dbo.func_split(@ProductFeatures, '*|*');
EXECUTE sp_executesql @ExecQuery;
END
Procedure 02:
CREATE PROCEDURE [dbo].[Usp_AddProductFeatures]
(
@PId INT,
@CreatedDate DATETIME,
@element VARCHAR(MAX),
@delimiter VARCHAR(MAX)
)
AS
BEGIN
DECLARE @result BIT = 0;
;WITH prodFeature AS(
SELECT * FROM dbo.func_split(@element, '+|+')
)
INSERT INTO ProductFeatures
(
ProductId,
CreatedOn,
ProductFeature
)
VALUES
(
@PId,
@createdDate,
(select Element from prodFeature where elementId = 1)
)
RETURN @result
END
1.) Product.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SampleMvc.Models
{
public class Product
{
public int ProductId { get; set; }
public int CategoryId { get; set; }
public string ProductName { get; set; }
public string ProductFeature { get; set; }
public DateTime CreateOn { get; set; }
}
}
2.) ProductExcel.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SampleMvc.Models
{
public class ProductExcel
{
public int ProductId { get; set; }
public string PNameAndFeature { get; set; }
}
}
3) ProductExcelRowCheck.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SampleMvc.Models
{
public class ProductExcelRowCheck
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public List<ExcelProductFeatureChk> PFeature { get; set; }
public bool IsBlockDone { get; set; }
}
}
4) ExcelProductFeatureChk.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SampleMvc.Models
{
public class ExcelProductFeatureChk
{
public int FID { get; set; }
public string ProductFreature { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SampleMvc.Models;
using System.Data.SqlClient;
namespace SampleMvc.DA
{
public class ProductDA : SQLHelper
{
public void AddProductAndFeatures(Product product, string productFeature)
{
try
{
int i = ExecNonQueryProc("Usp_InsertProductAndFeatures",
new SqlParameter("@ProductName", product.ProductName),
new SqlParameter("@CategoryId", product.CategoryId),
new SqlParameter("@ProductFeatures", productFeature));
}
catch
{
throw;
}
finally
{
CloseConnection();
}
}
}
}
FlowHelper.cs :
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Serialization;
namespace SampleMvc.Helpers
{
public static class FlowHelper
{
public static bool CreateDirectoryIfNotExists(string folderPath)
{
try
{
bool folderExists = Directory.Exists((folderPath));
if (!folderExists)
{
Directory.CreateDirectory((folderPath));
return true;
}
else
{
return true;
}
}
catch
{
throw;
}
}
public static bool TryToDeleteFile(string filePath)
{
try
{
if (File.Exists(filePath))
{
File.Delete(filePath);
return true;
}
else
{
return false;
}
}
catch
{
throw;
}
}
}
}
SessionHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using SampleMvc.Models;
namespace SampleMvc.Helpers
{
public class SessionHelper
{
public Product Product
{
get
{
return HttpContext.Current.Session["Productinfo"] as Product;
}
set
{
HttpContext.Current.Session["Productinfo"] = value;
}
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SampleMvc.Helpers;
using SampleMvc.Models;
using SampleMvc.DA;
using ExcelDataReader;
namespace SampleMvc.Controllers
{
public class ProductController : Controller
{
public ActionResult Product()
{
return View();
}
[HttpPost]
public ActionResult Product(HttpPostedFileBase postedFile)
{
string ext = Path.GetExtension(postedFile.FileName);
if (ext == ".xls" || ext == ".xlsx")
{
string folderPath = Server.MapPath("/BulkUpload");
FlowHelper.CreateDirectoryIfNotExists(folderPath);
string filePath = "";
filePath = "/BulkUpload/" + "QA" + ext;
string path = Server.MapPath(filePath);
postedFile.SaveAs(path);
// Install ExcelDataReader 3.1.0 and ExcelDataReader.DataSet 3.1.0 : Use command to do so as given below.
// Install-Package ExcelDataReader -Version 3.1.0
// Install-Package ExcelDataReader.DataSet -Version 3.1.0
// Reading excel file using excel data reader.
using (var stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet();
DataTable dt = result.Tables[0];
if (ValidateExcelFile(dt))
{
// Save Product and features.
CreateValidatedExcelFile(dt);
}
else
{
// Invalid excel action.
return View(dt);
}
}
}
// Delete uploaded excel file after bulk upload.
bool fileDeleted = FlowHelper.TryToDeleteFile(path);
TempData["ErrorLists"] = null;
}
return View();
}
[HttpPost]
public JsonResult ValidatedProductExcelUpload(List<ProductExcel> bulkExcelList)
{
DataTable dt = new DataTable();
dt = ConvertListToDataTable(bulkExcelList);
if (ValidateExcelFile(dt))
{
// Save Product and features.
CreateValidatedExcelFile(dt);
}
string json = string.Empty;
List<string> errList = TempData["ErrorLists"] as List<string>;
if (errList != null && errList.Count > 0)
{
json = string.Format("<h3 id=\"errList\"><i class=\"fa fa-minus-square\"></i> List(s) of Errors in Excel Sheet.</h3><ul class=\"ulerrlist\">{0}</ul>", string.Join(string.Empty, errList.Select(i => string.Concat("<li>", i, "</li>")).ToList()));
}
return Json(json);
}
/// <summary>
/// Method to validate excel data
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
///
private bool ValidateExcelFile(DataTable dt)
{
List<ProductExcelRowCheck> eRowCheck = new List<ProductExcelRowCheck>();
List<string> errList = new List<string>();
int excelColumnCount = dt.Columns.Count;
int excelRowCount = dt.Rows.Count;
for (int row = 0; row < excelRowCount; row++)
{
// Id Check
if (IsNumber(Convert.ToString(dt.Rows[row][0])))
{
int id = Convert.ToInt32(dt.Rows[row][0]);
var lastExcelRow = eRowCheck.LastOrDefault();
ProductExcelRowCheck ExcelRow = eRowCheck.FirstOrDefault(q => q.ProductId == id && q.IsBlockDone == true);
if (ExcelRow == null)
{
if (lastExcelRow != null && lastExcelRow.IsBlockDone == false)
{
// Title Check
if (Convert.ToString(dt.Rows[row][1]) == string.Empty)
{
errList.Add("<a href='javascript:;' class='exErrLnk' rc='" + row + "1'>Product name or Product feature can not be blank at [" + row + "][B]</a>");
}
else
{
if (lastExcelRow != null)
{
if (lastExcelRow.ProductId == id)
{
if (lastExcelRow.PFeature == null)
{
lastExcelRow.PFeature = new List<ExcelProductFeatureChk>();
}
// Adding Options
lastExcelRow.PFeature.Add(new ExcelProductFeatureChk
{
ProductFreature = Convert.ToString(dt.Rows[row][1])
});
// Duplicate Options Check
List<ExcelProductFeatureChk> dublicateFeature = lastExcelRow.PFeature.GroupBy(o => o.ProductFreature).SelectMany(grp => grp.Skip(1)).ToList();
if (dublicateFeature != null && dublicateFeature.Count > 0)
{
errList.Add("<a href='javascript:;' class='exErrLnk' t='f' pid='" + lastExcelRow.ProductId + "' col='1'>Duplicate features</a>");
//lastExcelRow.IsBlockDone = true;
}
}
else
{
lastExcelRow.IsBlockDone = true;
// Adding Question
eRowCheck.Add(new ProductExcelRowCheck
{
ProductId = Convert.ToInt32(dt.Rows[row][0]),
ProductName = Convert.ToString(dt.Rows[row][1]),
IsBlockDone = false
});
// Duplicate Question Check
List<ProductExcelRowCheck> dublicateProduct = eRowCheck.GroupBy(o => o.ProductName)
.Where(c => c.Count() > 1)
.SelectMany(grp => grp.Skip(1)).ToList();
if (dublicateProduct != null && dublicateProduct.Count > 0)
{
var lastdublicateProduct = dublicateProduct.LastOrDefault();
errList.Add("<a href='javascript:;' class='exErrLnk' t='p' pid='" + lastdublicateProduct.ProductId + "' col='1'>Duplicate Products</a>");
}
}
}
}
}
else
{
eRowCheck.Add(new ProductExcelRowCheck
{
ProductId = Convert.ToInt32(dt.Rows[row][0]),
ProductName = Convert.ToString(dt.Rows[row][1]),
IsBlockDone = false
});
}
}
else
{
errList.Add("<a href='javascript:;' class='exErrLnk' t='p' tr='" + row + "'>Duplicate ProductId[" + ExcelRow.ProductId + "] at [" + row + "][A]</a>");
}
}
else
{
errList.Add("Product Id must be numberic value at <a href='javascript:;' class='exErrLnk' rc='" + row + "0'>[" + row + "][A]</a>");
}
}
TempData["ErrorLists"] = errList;
return !(errList.Count > 0);
//return false;
}
private bool IsNumber(string v)
{
int result;
bool isNumeric = int.TryParse(v, out result);
return isNumeric;
}
private static DataTable ConvertListToDataTable(List<ProductExcel> list)
{
DataTable table = new DataTable();
// Get max columns.
int columns = 2;
// Add columns.
for (int i = 0; i < columns; i++)
{
table.Columns.Add();
}
// Add rows.
foreach (var array in list)
{
table.Rows.Add(array.ProductId, array.PNameAndFeature);
}
return table;
}
private void CreateValidatedExcelFile(DataTable dt)
{
//SessionHelper sessionHelper = new SessionHelper();
//if (sessionHelper.Product != null)
//{
int prodColumnCount = dt.Columns.Count;
int prodRowCount = dt.Rows.Count;
int prodID = Convert.ToInt32(dt.Rows[0][0]);
Product product = new Product();
ProductDA productDA = new ProductDA();
bool isFeature = false;
string ProductFeature = string.Empty;
for (int row = 0; row < prodRowCount; row++)
{
if (prodID == Convert.ToInt32(dt.Rows[row][0]))
{
if (!isFeature)
{
product.ProductName = Convert.ToString(dt.Rows[row][1]);
//product.CategoryId = sessionHelper.Product.CategoryId;
//Get CategoryId from session or Where ever you want.
product.CategoryId = 2;
isFeature = true;
}
else
{
ProductFeature += Convert.ToString(dt.Rows[row][1]) + "*|*";
if (row == prodRowCount - 1)
{
//Call function to save last set of record before exiting loop.
ProductFeature = ProductFeature.Substring(0, ProductFeature.Length - 3);
productDA.AddProductAndFeatures(product, ProductFeature);
TempData["message"] = "Bulk upload successful.";
}
}
}
else
{
//If Pid differs then call function to save data.
ProductFeature = ProductFeature.Substring(0, ProductFeature.Length - 3);
productDA.AddProductAndFeatures(product, ProductFeature);
prodID = Convert.ToInt32(dt.Rows[row][0]);
ProductFeature = "";
row--;
isFeature = false;
}
}
//}
}
}
}
@{
Layout = null;
}
@model System.Data.DataTable
@using System.Data
<!DOCTYPE html>
<html lang="en">
<head>
<title>Product and feature upload in bulk using Excel Sheet.</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="~/Contents/Bootstrap/bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/Bootstrap/jquery.min.js"></script>
<script src="~/Scripts/Bootstrap/bootstrap.min.js"></script>
<link href="~/Contents/CSS/Site.css" rel="stylesheet" />
<link href="~/Contents/CSS/ProductExcel.css" rel="stylesheet" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script type="text/javascript">
$(document).ready(function () {
$('div[id^="err"]').html("");
var columns = ["A", "B", "C", "D", "E", "F", "G"];
$(".readExcel table th").each(function () {
$(this).html(columns[parseInt($(this).text())]);
});
ApplyEvents();
});
function ApplyEvents() {
$("#btnUpload").click(function () {
if ($("#flExcel").val() != "") {
}
else {
$("#errflExcel").html("Please select file for bulk upload.");
return false;
}
});
$('input[type="text"]').on('focus', function () {
$(this).parent().addClass('activeCell');
});
$('input[type="text"]').on('blur', function () {
$(this).parent().removeClass('activeCell');
});
$("#btnVExcelUpload").on('click', function () {
$(".successMsg").html("");
var bulkExcelList = [];
var counter = 0;
$(".readExcel table tr").each(function (i, tr) {
var bulkExcel = { ProductId: 0, PNameAndFeature: "" };
if (counter++ > 0) {
bulkExcel.ProductId = $(tr).find("td:eq(0) input:text").val();
bulkExcel.PNameAndFeature = $(tr).find("td:eq(1) input:text").val();
bulkExcelList.push(bulkExcel);
}
});
bulkExcelList = JSON.stringify({ 'bulkExcelList': bulkExcelList });
$.ajax({
contentType: 'application/json; charset=utf-8',
dataType: 'json',
type: 'POST',
url: '/Product/ValidatedProductExcelUpload',
data: bulkExcelList,
success: function (data) {
$("#divErrLists").html(data);
if (!data) {
$("#readExcel").hide();
$(".successMsg").html("Bulk upload successful.");
}
ApplyErrorLinks();
}
});
});
ApplyErrorLinks();
}
function ApplyErrorLinks() {
$(".exErrLnk").on('click', function () {
$('.activeCell').removeClass("activeCell");
var exEle = $(this).attr("rc");
if (exEle) {
$("#" + exEle).focus();
$("#txt" + exEle).focus();
} else {
var type = $(this).attr("t");
var pid = $(this).attr("pid");
var col = $(this).attr("col");
var tr = $(this).attr("tr");
if (type == "f") {
if (pid && col) {
var counter = 0;
$(".readExcel table tr").each(function (i, tr) {
if ($(tr).find("td:eq(0) input:text").val() == pid) {
if (counter++ > 0) {
$(tr).find("td:eq(" + col + ")").addClass('activeCell');
}
}
});
}
}
if (type == "p") {
if (pid && col) {
$(".readExcel table tr").each(function (i, tr) {
if ($(tr).find("td:eq(0) input:text").val() == pid) {
$(tr).find("td:eq(" + col + ")").addClass('activeCell');
var product = $(tr).find("td:eq(" + col + ") input:text").val();
$(".readExcel table tr").each(function (i, tr) {
if ($(tr).find("td:eq(1) input:text").val() == product)
$(tr).find("td:eq(1)").addClass('activeCell');
});
}
});
}
}
if (tr) {
alert("");
tr++
$(".readExcel table tr:eq(" + tr + ")").addClass('activeCell');
}
}
});
$("#errList").on('click', function () {
if ($(".ulerrlist").css("display") == "none") {
$(this).find("i").attr("class", "fa fa-minus-square");
$(".ulerrlist").slideDown();
} else {
$(this).find("i").attr("class", "fa fa-plus-square");
$(".ulerrlist").slideUp();
}
});
}
</script>
</head>
<body>
<div class="container-fluid">
<div class="row content">
<div class="col-sm-3 sidenav">
<h4>Add Product</h4>
<ul class="nav nav-pills nav-stacked">
<li class="active"><a href="#section1">Home</a></li>
<li><a href="/Product/Product">Bulk Product Upload</a></li>
<li><a href="#section3">Family</a></li>
<li><a href="#section3">Photos</a></li>
</ul>
<br>
<div class="input-group">
<input type="text" class="form-control" placeholder="Search Blog..">
<span class="input-group-btn">
<button class="btn btn-default" type="button">
<span class="glyphicon glyphicon-search"></span>
</button>
</span>
</div>
</div>
<div class="col-sm-9">
<h4><small>Product Bulk Upload with its features.</small></h4>
<hr>
<div class="col-sm-12">
<div class="col-sm-3">
Select File :
</div>
<div class="col-sm-9">
<div id="divExcel" class="divExcelUP">
@using (Html.BeginForm("Product", "Product", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="editor-field">
<input type="file" id="flExcel" name="postedFile" />
<div id="errflExcel" class="divErr"></div>
</div>
<br />
<div class="editor-field">
<input class="Button" id="btnUpload" type="submit" value="Upload" />
</div>
<br />
<div class="editor-field">
<div class="successMsg">
@TempData["Msg"]
</div>
</div>
}
</div>
</div>
</div>
<div class="col-sm-12">
<div class="clearfix"></div>
<div id="divErrLists" class="errlists">
@if (TempData["ErrorLists"] != null)
{
<h3 id="errList"><i class="fa fa-minus-square"></i>List(s) of Errors in Excel Sheet.</h3>
<ul class="ulerrlist">
@foreach (var errlist in (List<string>)TempData["ErrorLists"])
{
<li>
@Html.Raw(errlist)
</li>
}
</ul>
}
</div>
<div class="clearfix"></div>
<div id="readExcel" class="readExcel">
@if (Model != null)
{
<input class="Button floatright marginbottom" id="btnVExcelUpload" type="submit" value="Upload Excel" />
<table class="table table-responsive table-bordered">
<thead>
<tr>
@foreach (DataColumn column in Model.Columns)
{
<th>@column.Ordinal</th>
}
</tr>
</thead>
<tbody>
@{
for (int r = 0; r < Model.Rows.Count; r++)
{
DataRow row = Model.Rows[r];
<tr class="beRow" id="@r">
@for (int c = 0; c < Model.Columns.Count; c++)
{
<td id="@string.Concat(r, c)">
<input type="text" id="@string.Concat("txt", r, c)" class="txtExcel" value="@row[c]" />
</td>
}
</tr>
}
}
</tbody>
</table>
}
</div>
<div class="clearfix"></div>
</div>
</div>
</div>
</div>
</body>
</html>
No comments:
Post a Comment