public ActionResult UploadPricingDetails(PricingList pricingList, HttpPostedFileBase FileUpload)
{
if (User.Identity.IsAuthenticated)
{
if (isAdminUser())
{
StringBuilder ErrorMessages = new StringBuilder();
StringBuilder SuccessMessages = null;
if (FileUpload != null)
{
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
string targetpath = Server.MapPath("~/Doc/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var pricingLists = from a in excelFile.Worksheet <PricingList>(sheetName) select a;
StringBuilder ImportValues = new StringBuilder();
foreach (var a in pricingLists)
{
try
{
if (a.ProductId != "")
{
PricingList PL = new PricingList();
PL.ProductId = a.ProductId;
PL.ProductName = a.ProductName;
PL.BuyPrice = a.BuyPrice;
PL.SellPrice = a.SellPrice;
PL.Profit = a.Profit;
PL.MRP = a.MRP;
string value = "('" + Guid.NewGuid() + "','" + PL.ProductId + "','" + PL.ProductName + "','" + PL.BuyPrice + "','" + PL.SellPrice + "','" + PL.Profit + "','" + PL.MRP + "',1,getdate(),suser_sname(),null,null),";
ImportValues.Append(value);
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
ErrorMessages.Append("\nProperty: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
string ImportData = ImportValues.ToString();
ImportData = ImportData.Substring(0, ImportData.Length - 1);
PricingDetailsProxy.InsertBulkPricingDetails(ImportData);
SuccessMessages = new StringBuilder();
SuccessMessages.Append(UploadConstants.UploadSuccessMessage);
}
else
{
ErrorMessages.Append(UploadConstants.InvalidFileFormat);
}
}
else
{
ErrorMessages.Append(UploadConstants.FileNotFound);
}
if (!String.IsNullOrEmpty(SuccessMessages.ToString()))
{
TempData[UploadConstants.UploadSuccess] = SuccessMessages.ToString();
}
if (!String.IsNullOrEmpty(ErrorMessages.ToString()))
{
TempData[UploadConstants.UploadError] = ErrorMessages.ToString();
}
return(RedirectToAction("Index", "ManagePricing"));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
public JsonResult UploadPricing(PricingList pricingList, HttpPostedFileBase FileUpload)
{
List <string> data = new List <string>();
if (FileUpload != null)
{
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
string targetpath = Server.MapPath("~/Doc/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var pricingLists = from a in excelFile.Worksheet <PricingList>(sheetName) select a;
StringBuilder ImportValues = new StringBuilder();
foreach (var a in pricingLists)
{
try
{
if (a.ProductId != "")
{
PricingList PL = new PricingList();
PL.ProductId = a.ProductId;
PL.ProductName = a.ProductName;
PL.BuyPrice = a.BuyPrice;
PL.SellPrice = a.SellPrice;
PL.Profit = a.Profit;
PL.MRP = a.MRP;
string value = "('" + Guid.NewGuid() + "','" + PL.ProductId + "','" + PL.ProductName + "','" + PL.BuyPrice + "','" + PL.SellPrice + "','" + PL.Profit + "','" + PL.MRP + "',1,getdate(),suser_sname(),null,null),";
ImportValues.Append(value);
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
string ImportData = ImportValues.ToString();
ImportData = ImportData.Substring(0, ImportData.Length - 1);
PricingDetailsProxy.InsertBulkPricingDetails(ImportData);
return(Json("success", JsonRequestBehavior.AllowGet));
}
else
{
//alert message for invalid file format
data.Add("<ul>");
data.Add("<li>Only Excel file format is allowed</li>");
data.Add("</ul>");
data.ToArray();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
else
{
data.Add("<ul>");
if (FileUpload == null)
{
data.Add("<li>Please choose Excel file</li>");
}
data.Add("</ul>");
data.ToArray();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}