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);
}
