sir i have done the repective code for
Import Data from Excel File to Database Table in ASP.NET MVC
using ajax.
<script type="text/javascript">
$("#btnUploadFile").click(function () {
if ($("#excelfile").val() != "") {
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
/*Checks whether the file is a valid excel file*/
if (!regex.test($("#excelfile").val().toLowerCase())) {
alert("Please upload a valid Excel file!");
return false;
}
else {
UploadSelectedExcelsheet();
}
}
else
{
alert("Please upload a Excel file!");
return false;
}
});
UploadSelectedImagesForPersonalisation();
function UploadSelectedExcelsheet() {
var data = new FormData();
var i = 0;
var fl = $("#excelfile").get(0).files[0];
if (fl != undefined) {
data.append("file", fl);
}
var Url = Path("ProductBulk", "UploadExcelsheet");
$.ajax({
type: "POST",
url: Url,
contentType: false,
processData: false,
data: data,
success: function (result) {
$("#Products").html(result);
$('.ddlRecipient').multiselect({
includeSelectAllOption: true
});
$('.ddlOccasionmaster').multiselect({
includeSelectAllOption: true
});
},
error: function (xhr, status, p3, p4) {
var err = "Error " + " " + status + " " + p3 + " " + p4;
if (xhr.responseText && xhr.responseText[0] == "{")
err = JSON.parse(xhr.responseText).Message;
alert(err);
return false;
}
});
}
</script>
[HttpPost]
public ActionResult UploadExcelsheet()
{
if (Request.Files.Count > 0)
{
var file = Request.Files[0];
List<ProductModel> _lstProductMaster = new List<ProductModel>();
string filePath = string.Empty;
if (Request.Files != null)
{
string path = Server.MapPath("~/Uploads/Product/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName("ProductUploadSheet-" + DateTime.Now.ToString("dd-MMM-yyyy-HH-mm-ss-ff") + Path.GetExtension(file.FileName));
string extension = Path.GetExtension("ProductUploadSheet-" + DateTime.Now.ToString("dd-MMM-yyyy-HH-mm-ss-ff") + Path.GetExtension(file.FileName));
file.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
int total = 0;
int entered = 0;
int failed = 0;
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
total++;
_lstProductMaster.Add(new ProductModel
{
ProductName = row["ProductName"].ToString().Replace("'", "''"),
ProductSKU = row["VendorSKU"].ToString().Trim() + "GL" + DateTime.Now.Year.ToString().Substring(2),
VendorSKU = row["VendorSKU"].ToString().Trim(),
DisplayText = row["DisplayText"].ToString().Trim(),
ProductSpecification = row["ProductSpecification"].ToString().Replace("'", "''"),
Description = row["Description"].ToString().Replace("'", "''"),
ShortDescription = row["ShortDescription"].ToString().Replace("'", "''"),
LongDescription = row["LongDescription"].ToString().Replace("'", "''"),
InventoryCount = row["InventoryCount"].ToString().Trim(),
ListPrice = row["ListPrice"].ToString().Trim(),
SellingPrice = row["SellingPrice"].ToString().Trim(),
// if (chkMultiple.Checked == true && ProductSKU != "")
//{
// ProductImage = Convert.ToString(VendorSKU).Trim() + "_1.jpg";
//}
//else if ((chkMultiple.Checked == false && details.SKU != ""))
//{
// ProductImage = Convert.ToString(VendorSKU).Trim() + ".jpg";
//}
});
entered++;
if (entered > 0)
{
GetOccassionRecipientMasters();
}
}
}
}
failed = total - entered;
if (failed > 0)
{
ViewBag.Fail = failed + " Records not entered";
}
else
{
ViewBag.Pass = entered + " Records entered";
ViewBag.Fail = failed + " Records not entered";
}
ViewBag.Total = total + " Total Records";
}
}
}
List<ProductMaster> _productmaster = new List<ProductMaster>();
ViewBag.maindata = _lstProductMaster;
//return Json(_lstProductMaster, JsonRequestBehavior.AllowGet);
return View("ImportProductsFromExcel", _lstProductMaster);
}
else
{
//return Json("", JsonRequestBehavior.AllowGet);
return View();
}
}
}
}
public class ProductModel
{
public string ProductName { get; set; }
public string ProductSKU { get; set; }
public string VendorSKU { get; set; }
public string ProductImage { get; set; }
public string DisplayText { get; set; }
public string ProductSpecification { get; set; }
public string Description { get; set; }
public string ShortDescription { get; set; }
public string LongDescription { get; set; }
public string InventoryCount { get; set; }
public string ListPrice { get; set; }
public string SellingPrice { get; set; }
public string TotalCount { get; set; }
public string TotalPass { get; set; }
public string TotalFail { get; set; }
}
