public ActionResult StorageJP(Guid id, string fileName = "")
{
var StorageJP = db.StorageJPs.Find(id);
//upload file
HttpPostedFileBase file = Request.Files[0];
fileName = file.FileName.DoiTenFile();
file.SaveAs(Server.MapPath("~/Uploads/StorageJP/" + fileName));
//read file
// Opening the Excel template...
FileStream fs = new FileStream(Server.MapPath("~/Uploads/StorageJP/" + fileName), FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
ISheet sheet = null;
if (fileName.Contains(".xlsx"))
{
XSSFWorkbook templateWorkbook = new XSSFWorkbook(Server.MapPath("~/Uploads/StorageJP/" + fileName));
sheet = templateWorkbook.GetSheet("例");
}
else if (fileName.Contains(".xls"))
{
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
sheet = templateWorkbook.GetSheet("例");
}
//read excel
WebsiteHelpers websiteHelper = new WebsiteHelpers();
List <StorageItemJP> lstStorageItemJP = new List <StorageItemJP>();
List <TrackingDetail> lstTrackingDetail = new List <TrackingDetail>();
Guid IdTracking = Guid.NewGuid();
#region read excel
//check column
string urlReport = ConfigurationManager.AppSettings["url_report_storejp"];
for (int row = 4; row <= sheet.LastRowNum; row++)
{
double price = 0;
try
{
price = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "GIA")).NumericCellValue;
}
catch (Exception ex) { }
string subcode = "";
try
{
subcode = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "SO KIEN")).StringCellValue;
try
{
subcode = int.Parse(subcode).ToString("00");
}
catch (Exception ex) { }
}
catch (Exception ex) { subcode = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "SO KIEN")).NumericCellValue.ToString("00"); }
if (price == 0 && subcode != "")
{
IdTracking = Guid.NewGuid();
TrackingDetail detail = new TrackingDetail()
{
CreatedAt = DateTime.Now,
CreatedBy = user.Staff.UserName,
Id = IdTracking,
StoregeJPId = StorageJP.Id,
Weigh = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "KG")).NumericCellValue,
UpdatedAt = DateTime.Now,
UpdatedBy = user.Staff.UserName,
StatusId = StorageJP.StatusId,
TrackingSubCode = subcode
};
lstTrackingDetail.Add(detail);
}
else if (price != 0)
{
string namejp = "";
string linkweb = ""; string ImageUrl = "";
string ImageBase64 = null;
try
{
namejp = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "TEN HANG_JP")).CellFormula;
namejp = namejp.Replace("HYPERLINK(", "").Replace(")", "").Replace("\"", "");
linkweb = namejp.Split(',')[0];
namejp = namejp.Split(',')[1];
}
catch (Exception ex)
{
namejp = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "TEN HANG_JP")).StringCellValue;
linkweb = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "WEBLINK")).StringCellValue;
}
if (linkweb.ToLower().Contains(urlReport.ToLower()))
{
string idItem = linkweb.Split('/')[linkweb.Split('/').Length - 1];
try
{
var item = db.StorageItemJPs.Find(Guid.Parse(idItem));
ImageBase64 = item.ImageBase64;
linkweb = item.LinkWeb;
ImageUrl = item.ImageLinkWeb;
}
catch (Exception ex) { }
}
else
{
try
{
ImageUrl = websiteHelper.GetImage(linkweb);
ImageBase64 = ImageUtils.Images(ImageUrl);
}
catch (Exception ex) { }
}
string component = "", material = "", JanCode = "";
try
{
component = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "THANH PHAN")).StringCellValue;
}
catch (Exception ex) { }
try
{
material = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "CHAT LIEU")).StringCellValue;
}
catch (Exception ex) { }
try
{
JanCode = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "Jancode,mavach")).StringCellValue;
}
catch (Exception ex) { JanCode = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "Jancode,mavach")).NumericCellValue + ""; }
StorageItemJP storejpItem = new StorageItemJP()
{
Amount = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "THANH TIEN")).NumericCellValue,
CategoryId = getCategory(sheet.GetRow(row).GetCell(getIndexColumn(sheet, "LOAI HANG")).StringCellValue).Id,
CategoryName = getCategory(sheet.GetRow(row).GetCell(getIndexColumn(sheet, "LOAI HANG")).StringCellValue).Name,
Component = component,
Id = Guid.NewGuid(),
CreatedAt = DateTime.Now,
CreatedBy = user.Staff.UserName,
ImageBase64 = ImageBase64,
ImageLinkWeb = ImageUrl,
JanCode = JanCode,
Material = material,
LinkWeb = linkweb,
MadeIn = getMadeIn(sheet.GetRow(row).GetCell(getIndexColumn(sheet, "XUAT XU")).StringCellValue).Id + "",
NameEN = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "TEN HANG_EN")).StringCellValue,
NameJP = namejp,
PriceTax = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "GIA")).NumericCellValue,
ProductCode = "",
Quantity = sheet.GetRow(row).GetCell(getIndexColumn(sheet, "SO LUONG")).NumericCellValue,
UpdatedAt = DateTime.Now,
StoregeJPId = StorageJP.Id,
UpdatedBy = user.Staff.UserName,
TrackingDetailId = IdTracking
};
lstStorageItemJP.Add(storejpItem);
}
}
#endregion
//delete all data form db
var details = StorageJP.TrackingDetails.ToList();
foreach (var item in details)
{
var stores = item.StorageItemJPs.ToList();
foreach (var st in stores)
{
db.StorageItemJPs.Remove(st);
}
db.TrackingDetails.Remove(item);
}
//Insert database
foreach (var item in lstTrackingDetail)
{
db.TrackingDetails.Add(item);
foreach (var store in lstStorageItemJP.Where(n => n.TrackingDetailId == item.Id))
{
db.StorageItemJPs.Add(store); db.SaveChanges();
}
}
db.SaveChanges();
return(View());
}
static void Main1(string[] args)
{
string[] fileArray = Directory.GetFiles(@"C:\FILES\");
foreach (var itemFile in fileArray)
{
try
{
// Opening the Excel template...
FileStream fs = new FileStream(itemFile, FileMode.Open, FileAccess.Read);
// Getting the complete workbook...
ISheet sheet = null;
string flighCode = "";
if (itemFile.Contains(".xlsx"))
{
XSSFWorkbook templateWorkbook = new XSSFWorkbook(itemFile);
sheet = templateWorkbook.GetSheet("INVOICE");
flighCode = templateWorkbook.GetSheet("INFO").GetRow(4).Cells[8].StringCellValue.Trim();
}
else if (itemFile.Contains(".xls"))
{
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
sheet = templateWorkbook.GetSheet("INVOICE");
flighCode = templateWorkbook.GetSheet("INFO").GetRow(4).Cells[8].StringCellValue.Trim();
}
WebsiteHelpers websiteHelper = new WebsiteHelpers();
string namejp = "";
List <ProductItem> lst = new List <ProductItem>();
for (int row = 11; row <= sheet.LastRowNum; row++)
{ // Ignoring first row as headers.
namejp = sheet.GetRow(row).GetCell(1).StringCellValue;
if (namejp != "" && namejp != null && namejp.Trim().Length > 0)
{
ProductItem p = new ProductItem();
p.NameJP = namejp;
p.NameEN = sheet.GetRow(row).GetCell(2).StringCellValue;
p.CategoryName = sheet.GetRow(row).GetCell(3).StringCellValue;
p.Link = sheet.GetRow(row).GetCell(4).StringCellValue;
p.Price = sheet.GetRow(row).GetCell(5).NumericCellValue;
try
{
string ImageUrl = websiteHelper.GetImage(p.Link);
p.ImageUrl = ImageUrl;
p.ImageBase64 = ImageUtils.Images(ImageUrl);
}
catch { }
p.ShippingMark = sheet.GetRow(row).GetCell(6).StringCellValue;
try
{
p.JanCode = sheet.GetRow(row).GetCell(7).NumericCellValue + "";
}
catch
{
p.JanCode = sheet.GetRow(row).GetCell(7) == null ? "" : sheet.GetRow(row).GetCell(7).StringCellValue;
}
p.Quantity = (int)sheet.GetRow(row).GetCell(8).NumericCellValue;
p.MadeIn = sheet.GetRow(row).GetCell(9).StringCellValue;
try
{
p.Note1 = sheet.GetRow(row).GetCell(10).NumericCellValue + "";
}
catch
{
p.Note1 = sheet.GetRow(row).GetCell(10).StringCellValue;
}
try
{
p.Note2 = sheet.GetRow(row).GetCell(11).NumericCellValue + "";
}
catch
{
p.Note2 = sheet.GetRow(row).GetCell(11).StringCellValue;
}
p.Amount = sheet.GetRow(row).GetCell(12).NumericCellValue;
lst.Add(p);
}
}
foreach (var item in lst)
{
WareHouseItem warehouse = new WareHouseItem()
{
Amount = item.Amount,
CategoryId = getCategory(item.CategoryName).Id,
CategoryName = item.CategoryName,
CreatedAt = DateTime.Now,
CreatedBy = "blf",
Id = Guid.NewGuid(),
Image = item.ImageUrl,
JanCode = item.JanCode,
LinkWeb = item.Link,
ImageBase64 = item.ImageBase64,
Component = "",
ComponentImage = "",
MadeIn = getMadeIn(item.MadeIn).Id + "",
Material = item.Note1,
NameEN = item.NameEN,
NameJP = item.NameJP,
Notes = item.Note1,
PriceTax = item.Price,
ProductCode = item.JanCode,
Quantity = item.Quantity,
UpdatedAt = DateTime.Now,
UpdatedBy = "blf",
ProductTypeId = 1,
IsDeny = false,
FlightCode = flighCode
};
db.WareHouseItems.Add(warehouse);
}
db.SaveChanges();
}
catch { }
}
Console.WriteLine("finished");
Console.ReadLine();
}