public void writeErrorToFile(ExcelWorksheet eSheet, int rownumber, CRM_FAQ_Topic item, string error)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
int i = 1;
eSheet.Cells[rownumber, i++].Value = item.Name;
eSheet.Cells[rownumber, i++].Value = item.Description;
eSheet.Cells[rownumber, i++].Value = item.CreatedAt;
eSheet.Cells[rownumber, i++].Value = item.CreatedBy;
eSheet.Cells[rownumber, i++].Value = item.UpdatedAt;
eSheet.Cells[rownumber, i++].Value = item.UpdatedBy;
eSheet.Cells[rownumber, i++].Value = error;
}
}
public ActionResult ImportFromExcel()
{
try
{
if (Request.Files["FileUpload"] != null && Request.Files["fileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["fileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["fileUpload"].FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "-Error]" + Request.Files["fileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["fileUpload"].SaveAs(fileLocation);
//Request.Files["fileUpload"].SaveAs(errorFileLocation);
var rownumber = 2;
var total = 0;
int n = 1;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\Deca_Topic.xlsx"));
template.CopyTo(errorFileLocation);
FileInfo _fileInfo = new FileInfo(errorFileLocation);
var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["FAQTopic"];
ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["FAQTopic"];
var data = WorksheetToDataTables(oSheet);
var listData = data.AsEnumerable().Select(s => new CRM_FAQ_Topic
{
Name = s["Name"] != null ? s["Name"].ToString() : "",
Description = s["Description"] != null ? s["Description"].ToString() : "",
}).ToList();
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
if (n == 1)
{
var required = listData.Where(s =>
String.IsNullOrEmpty(s.Name) ||
String.IsNullOrEmpty(s.Description));
foreach (var item in required)
{
writeErrorToFile(eSheet, rownumber, item, "Vui lòng nhập đầy đủ thông tin bắt buộc");
rownumber++;
}
listData = listData.Except(required).ToList();
if (listData.Count() > 0)
{
n = 1;
}
else
{
n = 0;
}
}
foreach (var item in listData)
{
try
{
var newData = new CRM_FAQ_Topic();
newData.Name = item.Name;
newData.Description = item.Description;
newData.CreatedAt = DateTime.Now;
newData.CreatedBy = currentUser.UserName;
dbConn.Insert(newData);
total++;
}
catch (Exception e)
{
writeErrorToFile(eSheet, rownumber, item, e.Message);
rownumber++;
}
}
}
_excelPkg.Save();
return(Json(new { success = true, total = total, totalError = rownumber - 2, link = errorFileLocation }));
}
}
}
catch (Exception e)
{
return(Json(new { success = false, error = "errors:" + e.Message }));
}
return(Json(new { success = true }));
}
