public TradelanePackageDetailExcel GetPiecesDetailFromExcel() { TradelanePackageDetailExcel frayteShipmentDetailexcel = new TradelanePackageDetailExcel(); var httpRequest = HttpContext.Current.Request; List <TradelanePackage> _shipmentdetail = new List <TradelanePackage>(); if (httpRequest.Files.Count > 0) { HttpFileCollection files = httpRequest.Files; HttpPostedFile file = files[0]; int ShipmentId = Convert.ToInt32(httpRequest.Form["ShipmentId"].ToString()); if (!string.IsNullOrEmpty(file.FileName)) { //int ShipmentId = Convert.ToInt32(httpRequest.Form["ShipmentId"].ToString()); string connString = ""; string filename = DateTime.Now.ToString("MM_dd_yyyy_hh_mm_ss_") + file.FileName; string filepath = HttpContext.Current.Server.MapPath("~/UploadFiles/Tradelane/" + filename); file.SaveAs(filepath); connString = new DirectShipmentRepository().getExcelConnectionString(filename, filepath); string fileExtension = ""; fileExtension = new DirectShipmentRepository().getFileExtensionString(filename); try { if (!string.IsNullOrEmpty(fileExtension)) { var ds = new DataSet(); if (fileExtension == FrayteFileExtension.CSV) { using (var conn = new OleDbConnection(connString)) { conn.Open(); var query = "SELECT * FROM [" + Path.GetFileName(filename) + "]"; using (var adapter = new OleDbDataAdapter(query, conn)) { adapter.Fill(ds, "Pieces"); } } } else { var excel = new ExcelQueryFactory("excelFileName"); var oldCompanies = (from c in excel.Worksheet <TradelanePackage>("sheet1") select c); using (var conn = new OleDbConnection(connString)) { conn.Open(); DataTable dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString(); var query = "SELECT * FROM " + "[" + firstSheetName + "]"; using (var adapter = new OleDbDataAdapter(query, conn)) { adapter.Fill(ds, "Pieces"); } } } var exceldata = ds.Tables[0]; // Step 1: Validate for excel column string PiecesColumnList = "CartonNumber,CartonQTY,Length,Width,Height,Weight"; bool IsExcelValid = UtilityRepository.CheckUploadExcelFormat(PiecesColumnList, exceldata); if (!IsExcelValid) { frayteShipmentDetailexcel.Message = "Columns are not matching with provided template columns. Please check the column names."; } else { if (exceldata.Rows.Count > 0) { frayteShipmentDetailexcel.TotalUploaded = exceldata.Rows.Count; _shipmentdetail = new TradelaneBookingRepository().GetPiecesDetail(exceldata); frayteShipmentDetailexcel.Packages = new List <TradelanePackage>(); frayteShipmentDetailexcel.Packages = _shipmentdetail; frayteShipmentDetailexcel.Message = "OK"; frayteShipmentDetailexcel.SuccessUploaded = _shipmentdetail.Count; new TradelaneBookingRepository().SavePackagedetailShipment(frayteShipmentDetailexcel.Packages, ShipmentId); } else { frayteShipmentDetailexcel.Message = "No records found."; } } if ((System.IO.File.Exists(filepath))) { System.IO.File.Delete(filepath); } } else { frayteShipmentDetailexcel.Message = "Excel file not valid"; } } catch (Exception ex) { Elmah.ErrorSignal.FromCurrentContext().Raise(ex); if (ex != null && !string.IsNullOrEmpty(ex.Message) && ex.Message.Contains("Sheet1$")) { frayteShipmentDetailexcel.Message = "Sheet name is invalid."; } else { frayteShipmentDetailexcel.Message = "Error while uploading the excel."; } return(frayteShipmentDetailexcel); } } } return(frayteShipmentDetailexcel); }