//public FileResult Export([DataSourceRequest]DataSourceRequest request)
//{
// ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/DanhMucAnPham.xlsx")));
// ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
// if (userAsset["Export"])
// {
// string whereCondition = "";
// if (request.Filters.Count > 0)
// {
// whereCondition = new KendoApplyFilter().ApplyFilter(request.Filters[0]);
// }
// IDbConnection db = new OrmliteConnection().openConn();
// var lstResult = db.Select<Products>(whereCondition).ToList();
// int rowNum = 2;
// foreach (var item in lstResult)
// {
// ws.Cells["A" + rowNum].Value = item.Code;
// ws.Cells["B" + rowNum].Value = item.Name;
// ws.Cells["C" + rowNum].Value = item.Size;
// ws.Cells["D" + rowNum].Value = item.VATPrice;
// ws.Cells["E" + rowNum].Value = item.Type;
// ws.Cells["F" + rowNum].Value = item.Unit;
// ws.Cells["G" + rowNum].Value = item.WHID;
// ws.Cells["H" + rowNum].Value = item.WHLID;
// ws.Cells["I" + rowNum].Value = item.ShapeTemplate;
// if (item.Status == true)
// {
// ws.Cells["J" + rowNum].Value = "Đang hoạt động";
// }
// else
// {
// ws.Cells["J" + rowNum].Value = "Ngưng hoạt động";
// }
// ws.Cells["K" + rowNum].Value = item.CreatedBy;
// ws.Cells["L" + rowNum].Value = item.CreatedAt;
// ws.Cells["M" + rowNum].Value = item.UpdatedBy;
// if (item.UpdatedAt != DateTime.Parse("1900-01-01"))
// {
// ws.Cells["N" + rowNum].Value = item.UpdatedAt;
// }
// else
// {
// ws.Cells["N" + rowNum].Value = "";
// }
// rowNum++;
// }
// db.Close();
// }
// else
// {
// ws.Cells["A2:E2"].Merge = true;
// ws.Cells["A2"].Value = "Bạn không có quyền";
// }
// MemoryStream output = new MemoryStream();
// pck.SaveAs(output);
// return File(output.ToArray(), //The binary data of the XLS file
// "application/vnd.ms-excel", //MIME type of Excel files
// "DanhMucAnPham_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); //Suggested file name in the "Save as" dialog which will be displayed to the end user
//}
public ActionResult ImportData()
{
try
{
if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xlsx" || fileExtension == ".xls")
{
IDbConnection dbConn = new OrmliteConnection().openConn();
using (var dbTrans = dbConn.OpenTransaction(IsolationLevel.ReadCommitted))
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + Request.Files["FileUpload"].FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName);
string linkerror = "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
System.IO.File.Delete(fileLocation);
Request.Files["FileUpload"].SaveAs(fileLocation);
var rownumber = 2;
var total = 0;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
//FileInfo template = new FileInfo(Server.MapPath(errorFileLocation));
//template.CopyTo(errorFileLocation);
//FileInfo _fileInfo = new FileInfo(errorFileLocation);
//var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["Data"];
//ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["Data"];
ExcelPackage pck = new ExcelPackage(new FileInfo(errorFileLocation));
ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
int totalRows = oSheet.Dimension.End.Row;
for (int i = 2; i <= totalRows; i++)
{
string ID = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "";
string Name = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : "";
string Size = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : "";
string Priece = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString() : "0";
string Type = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString() : "";
string Unit = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString() : "";
string[] UnitID = Unit.Split('/');
string WH = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString() : "";
string[] WHID = WH.Split('/');
string WHL = oSheet.Cells[i, 8].Value != null ? oSheet.Cells[i, 8].Value.ToString() : "";
string[] WHLID = WHL.Split('/');
string Templete = oSheet.Cells[i, 9].Value != null ? oSheet.Cells[i, 9].Value.ToString() : "";
//string Status = oSheet.Cells[i, 9].Value != null ? oSheet.Cells[i, 9].Value.ToString() : "Ngưng hoạt động";
string Status = "false";
if (oSheet.Cells[i, 10].Value != null)
{
if (oSheet.Cells[i, 10].Value.ToString() == "Đang hoạt động")
{
Status = "true";
}
}
try
{
if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Size) || string.IsNullOrEmpty(Priece))
{
ws.Cells["A" + 2].Value = Name;
ws.Cells[rownumber, 14].Value = "Vui lòng nhập (*).";
rownumber++;
}
else
{
var checkexists = dbConn.SingleOrDefault<Products>("SELECT * FROM Products WHERE Code = '" + ID + "'");
if (checkexists != null)
{
checkexists.Code = ID;
checkexists.Name = Name;
checkexists.Size = Name;
checkexists.Price = int.Parse(Priece)/1.1;
checkexists.VATPrice = int.Parse(Priece);
checkexists.Type = Type;
checkexists.Unit = Unit != null ? UnitID[UnitID.Count() - 1] : "";
checkexists.WHID = WH != null ? WHID[WHID.Count() - 1] : "";
checkexists.WHLID = WHL != null ? WHLID[WHLID.Count() - 1] : "";
checkexists.ShapeTemplate = Templete;
checkexists.Status = Boolean.Parse(Status);
checkexists.UpdatedAt = DateTime.Now;
checkexists.UpdatedBy = currentUser.UserID;
dbConn.Update<Products>(checkexists);
}
else
{
string id = "";
var checkID = dbConn.SingleOrDefault<Products>("SELECT Code, Id FROM dbo.Products ORDER BY Id DESC");
if (checkID != null)
{
var nextNo = int.Parse(checkID.Code.Substring(2, checkID.Code.Length - 2)) + 1;
id = "AD" + String.Format("{0:00000000}", nextNo);
}
else
{
id = "AD00000001";
}
var item = new Products();
item.Code = ID;
item.Name = Name;
item.Size = Name;
item.Price = int.Parse(Priece) / 1.1;
item.VATPrice = int.Parse(Priece);
item.Type = Type;
item.Unit = Unit != null ? UnitID[UnitID.Count() - 1] : "";
item.WHID = WH != null ? WHID[WHID.Count() - 1] : "";
item.WHLID = WHL != null ? WHLID[WHLID.Count() - 1] : "";
item.ShapeTemplate = Templete;
item.Status = Boolean.Parse(Status);
item.CreatedAt = DateTime.Now;
item.CreatedBy = currentUser.UserID;
item.UpdatedAt = DateTime.Parse("1900-01-01");
item.UpdatedBy = "";
item.Status = Boolean.Parse(Status);
dbConn.Insert<Products>(item);
}
total++;
}
}
catch (Exception e)
{
return Json(new { success = false, message = e.Message });
}
}
return Json(new { success = true, total = total, totalError = rownumber - 2, link = linkerror });
}
}
else
{
return Json(new { success = false, message = "Không phải là file Excel. *.xlsx" });
}
}
else
{
return Json(new { success = false, message = "Không có file hoặc file không phải là Excel" });
}
}
catch (Exception ex)
{
return Json(new { success = false, message = ex.Message });
}
}