private int InsertExcelFileDate(TblBankStatHeader header, List <Models.Excel.CellModel> detailList, string company) { List <string> errors = new List <string>(); using (var entities = new ccnewEntities(SharedOperation.GetSqlConnectionString(company))) { var template = entities.TblBankStatExcelTemplates.FirstOrDefault(t => t.TblBank == header.TblBank); if (template == null) { throw new ArgumentNullException("Template", "Cannot find Bank Template"); } string templatePath = string.Format("{0}\\BankStatExcelTemplates\\{1}", "..", template.TemplatePath).Replace("\\\\", "\\"); string workingPath = string.Format("{0}\\BankStatExcelTemplates\\{1}\\{2}\\{3}", "..", DateTime.Now.ToString("yyyy-MM-dd--HH-mm-ss"), Guid.NewGuid(), template.TemplatePath).Replace("\\\\", "\\"); string workingDirectory = Path.GetDirectoryName(workingPath); if (!Directory.Exists(workingDirectory)) { Directory.CreateDirectory(workingDirectory); } File.Copy(templatePath, workingPath); var fs = new FileInfo(workingPath);//, FileMode.Create); using (ExcelPackage package = new ExcelPackage(fs)) { int baseColIndex = 3; var sheet = package.Workbook.Worksheets.First(); //Excel._Worksheet sheet = xlWorkbook.Sheets[1]; //sheet.Column(2).Style.Numberformat.Format = "dd-mm-yyyy"; foreach (var item in detailList) { //((Excel.Range)sheet.Cells[item.Row + 1, item.Column + 1]).Value = item.Value; if (item.Column + 1 == 2 && item.Value.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Length == 3) { sheet.Cells[item.Row + 1, 1].LoadFromText(item.Day); sheet.Cells[item.Row + 1, 2].LoadFromText(item.Mounth); sheet.Cells[item.Row + 1, 3].LoadFromText(item.Year); } else { //فيه مشكلة فى التاريخ بيبدل اليوم والشهر فى الاصلى مظبوط وبعد النسخ بتحصل المشكلة sheet.Cells[item.Row + 1, item.Column + baseColIndex + 1].LoadFromText(item.Value);//1-base } } package.Save(); //xlWorkbook.Save(); } Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(workingPath); List <ImportedBankStatement> importedList = new List <ImportedBankStatement>(); //using (ExcelPackage package = new ExcelPackage(fs)) { var sheet = xlWorkbook.Sheets[1]; // package.Workbook.Worksheets.First(); // try { FillList(importedList, sheet, template.StartRow); header.Approved = false; foreach (var item in importedList) { TblBankStatDetail detail = PrepareDetail(entities, header, item, errors); if (detail != null) { header.TblBankStatDetails.Add(detail); } } header.TblBank1 = null; header.TblCurrency1 = null; header.CreationDate = DateTime.Now; header.LastChangeDate = DateTime.Now; header.LastChangeUser = header.CreatedBy; //header.Approved = true; entities.TblBankStatHeaders.AddObject(header); entities.SaveChanges(); return(header.Iserial); } catch (Exception ex) { throw ex; } finally { var process = SharedOperation.GetExcelProcess(xlApp); if (process != null) { process.Kill(); } } } } }