Asp.Net, Mvc, C#, Vb.Net, DataBase, JavaScript, Jquery: How to export and import in mvc?

EXPORT
_______________________________________________________________________________

public ActionResult ExportVendor(int id = 0, string check = "")
{
VendorItemModel model = new VendorItemModel();
IEnumerable<VendorItemModel> vendorItem = new List<VendorItemModel>();

List<GetVendorItems_Result> vendorList;

model.Checked = false;
vendorList = objVendor.GetAllVendorItems("", "", false, "ROCK, KBJ, TRIPPS", "NC, SC, VA").Where(x => x.IsDeleted == false).ToList();

var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("VendorExport");
var columns = new[] { "ItemNumber", "ItemDescription", "VendorItemAlias", "Vendor", "LinkBrgItem","ShiptooUom","PriceByUom",
"VendorCaseDescription","CurrentPrice","Taxable","Status","Percentage","Freight","Brand","Pack",
"BinLocation","InventoryLocation","InventoryStatus","PhysicallyInventoryUomId",
"PhysicalInventoryConversionFactor","StatesWhereUsed","ConceptWhereUsed"};
var headerRow = sheet.CreateRow(0);

//create header
for (int i = 0; i < columns.Length; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(columns[i]);
}

//fill content
string data = string.Empty;
for (int i = 0; i < vendorList.Count; i++)
{

var rowIndex = i + 1;
var row = sheet.CreateRow(rowIndex);
var cell0 = row.CreateCell(0);
var cell1 = row.CreateCell(1);
var cell2 = row.CreateCell(2);
var cell3 = row.CreateCell(3);
var cell4 = row.CreateCell(4);
var cell5 = row.CreateCell(5);
var cell6 = row.CreateCell(6);
var cell7 = row.CreateCell(7);
var cell8 = row.CreateCell(8);
var cell9 = row.CreateCell(9);
var cell10 = row.CreateCell(10);
var cell11 = row.CreateCell(11);
var cell12 = row.CreateCell(12);
var cell13 = row.CreateCell(13);
var cell14 = row.CreateCell(14);
var cell15 = row.CreateCell(15);
var cell16 = row.CreateCell(16);
var cell17 = row.CreateCell(17);
var cell18 = row.CreateCell(18);
var cell19 = row.CreateCell(19);
var cell20 = row.CreateCell(20);
var cell21 = row.CreateCell(21);

string ItemNumber = vendorList[i].VendorItemNumber != null ? vendorList[i].VendorItemNumber.ToString() : "Null";
string ItemDescription = vendorList[i].VendorItemDescription != null ? vendorList[i].VendorItemDescription.ToString() : ""; //string ItemDescriptionAlias = vendorList[i].VendorItemDescriptionAlias != null ? vendorList[i].VendorItemDescriptionAlias.ToString() : "";
string VendorItemAlias = vendorList[i].VendorItemDescriptionAlias != null ? vendorList[i].VendorItemDescriptionAlias.ToString() : "";
string Vendor = vendorList[i].VendorId != null ? vendorList[i].VendorId.ToString() : Convert.ToString(0);
string LinkBrgItem = vendorList[i].BrgItemId != null ? vendorList[i].BrgItemId.ToString() : Convert.ToString(0);

string ShiptooUom = vendorList[i].OrderByUomId != null ? vendorList[i].OrderByUomId.ToString() : Convert.ToString(0);
string PriceByUom = vendorList[i].PriceByUomId != null ? vendorList[i].PriceByUomId.ToString() : Convert.ToString(0);
string VendorCaseDescription = vendorList[i].VendorCaseDescription != null ? vendorList[i].VendorCaseDescription.ToString() : "";
string CurrentPrice = vendorList[i].CurrentPrice != null ? vendorList[i].CurrentPrice.ToString() : Convert.ToString(0);
string Taxable = vendorList[i].Taxable != null ? Convert.ToString(vendorList[i].Taxable) : "";

string Status = vendorList[i].Status != null ? vendorList[i].Status : "";
string Percentage = vendorList[i].VendorPercent != null ? vendorList[i].VendorPercent.ToString() : Convert.ToString(0);
string Freight = vendorList[i].VendorFreight != null ? vendorList[i].VendorFreight.ToString() : Convert.ToString(0);
string Brand = vendorList[i].BrandId != null ? vendorList[i].BrandId.ToString() : Convert.ToString(0);
string Pack = vendorList[i].Pack != null ? vendorList[i].Pack.ToString() : Convert.ToString(0);

string BinLocation = vendorList[i].BinLocation != null ? vendorList[i].BinLocation : "";
string InventoryLocation = vendorList[i].InventoryLocation != null ? vendorList[i].InventoryLocation : "";
string InventoryStatus = vendorList[i].InventoryStatus != null ? vendorList[i].InventoryStatus : "S";
string PhysicallyInventoryUomId = vendorList[i].PhysicalInventoryUomId != null ? vendorList[i].PhysicalInventoryUomId.ToString() : Convert.ToString(0);
string PhysicalInventoryConversionFactor = vendorList[i].PhysicalInventoryConversionFactor != null ? vendorList[i].PhysicalInventoryConversionFactor.ToString() : Convert.ToString(0);
string StatesWhereUsed = vendorList[i].StatesWhereUsed != null ? vendorList[i].StatesWhereUsed : "";
string ConceptWhereUsed = vendorList[i].ConceptWhereUsed != null ? vendorList[i].ConceptWhereUsed : "";

cell0.SetCellValue(ItemNumber);
cell1.SetCellValue(ItemDescription);
cell2.SetCellValue(VendorItemAlias);
cell3.SetCellValue(Vendor);
cell4.SetCellValue(LinkBrgItem);
cell5.SetCellValue(ShiptooUom);
cell6.SetCellValue(PriceByUom);
cell7.SetCellValue(VendorCaseDescription);
cell8.SetCellValue(CurrentPrice);
cell9.SetCellValue(Taxable);
cell10.SetCellValue(Status);
cell11.SetCellValue(Percentage);

cell12.SetCellValue(Freight);
cell13.SetCellValue(Brand);
cell14.SetCellValue(Pack);
cell15.SetCellValue(BinLocation);
cell16.SetCellValue(InventoryLocation);
cell17.SetCellValue(InventoryStatus);

cell18.SetCellValue(PhysicallyInventoryUomId);
cell19.SetCellValue(PhysicalInventoryConversionFactor);
cell20.SetCellValue(StatesWhereUsed);
cell21.SetCellValue(ConceptWhereUsed);

}
var stream = new MemoryStream();
workbook.Write(stream);
stream.Close();

return File(stream.ToArray(), "application/vnd.ms-excel", "VendorExport.xls");
}

IMPORT
______________________________________________________________________________
<div class="col-sm-3" style="margin-left: -135px;">
<button class="btn btn-default btn1" data-toggle="modal" data-backdrop="static" data-target="#FileUploadModel">
Import Vendor
</button>
</div>

<div class="modal fade" id="FileUploadModel" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title" id="ModalLabel">Vendor File Upload </h4>
</div>
<div class="clearfix"></div>
<div class="modal-body">
<div id="FileUpload">
@Html.Partial("VendorFileUpload")
</div>
<div class="clearfix"></div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</div>

In PartialPage (VendorFileUpload) -
______________________________

@using (Html.BeginForm("ImportXlVendorData", "VendorProduct", FormMethod.Post, new { enctype = "multipart/form-data" }))
{

<div id="status" style=" color: green; "></div>
<a class="file-input-wrapper btn btn-default ">
<input type="file" onchange="ValidateSingleInput(this);" name="file" id="fileInput" data-filename-placement="inside" style="left: -225.5px; top: 13px;">
</a>
<input type="submit" value="Upload file" />

}

<script type="text/javascript">
var _validFileExtensions = [".xls"];

function ValidateSingleInput(oInput) {
debugger;
if (oInput.type == "file") {
var sFileName = oInput.value;
if (sFileName.length > 0) {
var blnValid = false;
for (var j = 0; j < _validFileExtensions.length; j++) {
var sCurExtension = _validFileExtensions[j];
if (sFileName.substr(sFileName.length - sCurExtension.length, sCurExtension.length).toLowerCase() == sCurExtension.toLowerCase()) {
blnValid = true;
break;
}
}

if (!blnValid) {
alert("Sorry, " + sFileName + " is invalid, allowed extensions are: " + _validFileExtensions.join(", "));
oInput.value = "";
return false;
}
}
}
return true;
}

</script>

public ActionResult ImportXlVendorData(HttpPostedFileBase file)
{
try
{
int result = 0;
string List = string.Empty;
//SiteUserBLL siteUserBLL = new SiteUserBLL();
VendorItemBL vendorBl = new VendorItemBL();
DataSet ds = new DataSet();
if (Request.Files["file"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["file"].FileName);

string FileName = Request.Files["file"].FileName;
string[] strFileName = FileName.Split('.');
FileName = strFileName[0];

//if (fileExtension == ".xls" || fileExtension == ".xlsx")
if (fileExtension == ".xls")
{
string fileLocation = Server.MapPath("~/Content/VendorImport/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
// excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();

DataTable dt = new DataTable();
OleDbDataAdapter MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + FileName + "$]", excelConnection);
ds = new System.Data.DataSet();

//Bind all excel data in to data set
//MyCommand.Fill(ds, "[Sheet1$]");

MyCommand.Fill(ds, "[" + FileName + "$]");

int dscoulmn = ds.Tables[0].Columns.Count;

string guid = Guid.NewGuid().ToString();

if (dscoulmn == 8)
{
dt = ds.Tables[0];
if (dt != null)
{
VendorItemModel model = new VendorItemModel();
foreach (DataRow dr in dt.Rows)
{
var vendorList = objVendor.GetAllVendorItems("", "", false, "ROCK, KBJ, TRIPPS", "NC, SC, VA").Where(x => x.VendorItemNumber == dr[0].ToString()).ToList();
if (vendorList.Count() > 0)
{
List += dr[0].ToString() + ",";
Session["Success"] = List + " all these vendor already exists and other vendor is successfully inserted .!";
}
else
{
model.VendorItemNumber = dr[0] == null ? "" : dr[0].ToString();
model.VendorItemDescription = dr[1].ToString();
model.VendorCaseDescription = dr[2] == null ? "" : dr[2].ToString();
//model.Weight = Convert.ToDecimal(dr[3]);
model.AverageWeight = Convert.ToDecimal(dr[3]);
model.CurrentPrice = Convert.ToDecimal(dr[4]);
if (dr[5].ToString() == "T")
{
model.Taxable = true;
}
else
{
model.Taxable = false;
}
model.Status = dr[6] == null ? "I" : dr[6].ToString();
model.LastUpdatedBy = 0;
model.ConceptsWhereUsed = dr[7] == null ? "" : dr[7].ToString();
model.LastUpdatedDate = DateTime.Now;

model.VendorId = 1;
model.PriceByUomId = 383;
model.InventoryStatus = "S";

if (List == "")
{
result = VendorItemBL.InsertVendorItem1(model.VendorItemNumber, model.VendorItemDescription, model.VendorId, model.PriceByUomId, model.VendorCaseDescription,
model.AverageWeight, model.CurrentPrice, model.Taxable, model.Status, model.LastUpdatedBy, model.ConceptsWhereUsed);

if (result != 0)
{
Session["Success"] = "successfully inserted";
}
}

}
}
}
}

excelConnection.Close();
}
else
{
TempData["errormsg"] = "Please select .xls/.xlsx file !";
}
}
}
catch (Exception ex)
{
throw ex;
//TempData["errormsg"] = "Please select xls file type in Correct format.";
//return RedirectToAction("SiteUser");
}

return RedirectToAction("Index");
}

Asp.Net, Mvc, C#, Vb.Net, DataBase, JavaScript, Jquery

Tuesday, 26 January 2016

How to export and import in mvc?

No comments:

Post a Comment