public ActionResult Register(LoadProjectsViewModel model, HttpPostedFileBase imageFile) { JSonResult objResult = new JSonResult(); string response = string.Empty; try { Session objSession = new Session() { UserId = AutenticationManager.GetUser().IdUsuario, }; List <MProject> entList = new List <MProject>(); List <ModelProjectResult> entListData = new List <ModelProjectResult>(); entListData = (List <ModelProjectResult>)Session["ListProjects"]; foreach (ModelProjectResult item in entListData) { MProject mProject = new MProject(); mProject.ProjectCode = item.ProjectCode; mProject.Description = item.Description; mProject.Type = item.Type; mProject.Status = item.Status; mProject.StartDate = Convert.ToInt32(Extension.ToFormatDateYYYYMMDD(item.StartDateStr)); mProject.EndDate = Convert.ToInt32(Extension.ToFormatDateYYYYMMDD(item.EndDateStr));//item.EndDate; mProject.Title = item.Title; mProject.AwardId = item.AwardId; mProject.AwardStatus = item.AwardStatus; entList.Add(mProject); } response = new WebApiProject().InsertProject(entList, objSession); string statusCode = response.Split('|')[0]; string statusMessage = response.Split('|')[1]; objResult.isError = statusCode.Equals("2"); objResult.message = string.Format(MessageResource.SaveSuccess, "Projects");; } catch (Exception ex) { objResult.message = string.Format(MessageResource.SaveError + "Error :" + ex.Message, "Projects"); } return(Json(objResult)); }
public ActionResult LoadFile(LoadProjectsViewModel model, HttpPostedFileBase imageFile) { JSonResult objResult = new JSonResult(); string fileName = string.Empty; string Ext = string.Empty; byte[] imgData = null;//; new byte[0]; string path = string.Empty; string basePath; // basePath = "E:\\TFS_Fuentes\\UnitLite\\Fuentes CMS Net\\CMSWeb\\File"; basePath = Server.MapPath("~/File"); DataTable dt; var include = new[] { "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O" }; try { if (imageFile != null) { fileName = imageFile.FileName; Ext = Path.GetExtension(imageFile.FileName); // imgData = Extension.FileToByteArray(imageFile); path = string.Format("{0}\\{1}", basePath, imageFile.FileName); } if (!Directory.Exists(basePath)) { Directory.CreateDirectory(basePath); } imageFile.SaveAs(path); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false)) { //Read the first Sheet from Excel file. Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>(); //Get the Worksheet instance. Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet; //Fetch all the rows present in the Worksheet. IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>(); dt = new DataTable(); //Loop through the Worksheet rows. foreach (Row row in rows) { //Use the first row to add columns to DataTable. if (row.RowIndex.Value == 2) { foreach (Cell cell in row.Descendants <Cell>()) { string cel = cell.CellReference; cel = cel.Substring(0, 1); if (include.Any(x => cel.Contains(x))) {//Continue adding the row to the table dt.Columns.Add(OpenXMLUtil.GetValue(doc, cell)); } } } else if (row.RowIndex.Value > 2) { //Add rows to DataTable. dt.Rows.Add(); int i = 0; foreach (Cell cell in row.Descendants <Cell>()) { string cel2 = cell.CellReference; cel2 = cel2.Substring(0, 1); if (include.Any(x => cel2.Contains(x))) { string val = OpenXMLUtil.GetValue(doc, cell); if (cel2.Contains("K") || cel2.Contains("L")) { try { DateTime date = DateTime.FromOADate(Convert.ToDouble(val)); val = date.ToString("dd/MM/yyyy"); } catch (Exception ex) { objResult.isError = true; objResult.message = string.Format("Error: Error in the date field please use format DD/MM/YYYY", "Project ERP "); return(Json(objResult)); } } dt.Rows[dt.Rows.Count - 1][i] = val; i++; } } } } } } catch (Exception ex) { objResult.isError = true; objResult.data = null; objResult.message = string.Format("Error: Please check the template for this upload ", "Projects"); return(Json(objResult)); } if (dt.Rows.Count <= 0) { objResult.isError = true; objResult.data = null; objResult.message = string.Format("The uploaded file has no rows", "Projects"); return(Json(objResult)); } try { var dtResultado = dt.Rows.Cast <DataRow>().Where(row => !Array.TrueForAll(row.ItemArray, value => { return(value.ToString().Length == 0); })); dt = dtResultado.CopyToDataTable(); List <ModelProjectResult> entlist = new List <ModelProjectResult>(); for (int i = 0; i < dt.Rows.Count; i++) { ModelProjectResult ent = new ModelProjectResult(); //ent.Department = Extension.ToEmpty(dt.Rows[i][0].ToString());//Convert.ToInt32(dt.Rows[i]["StudentId"]); ent.ProjectCode = Extension.ToEmpty(dt.Rows[i][1].ToString());//Convert.ToInt32(dt.Rows[i]["StudentId"]); ent.Description = Extension.ToEmpty(dt.Rows[i][2].ToString()); ent.Type = Extension.ToEmpty(dt.Rows[i][3].ToString()); ent.Status = Extension.ToEmpty(dt.Rows[i][4].ToString()); ent.StartDateStr = Extension.ToEmpty(dt.Rows[i][9].ToString()); ent.EndDateStr = Extension.ToEmpty(dt.Rows[i][10].ToString()); ent.AwardId = Extension.ToEmpty(dt.Rows[i][11].ToString()); ent.Title = Extension.ToEmpty(dt.Rows[i][12].ToString()); ent.AwardStatus = Extension.ToEmpty(dt.Rows[i][13].ToString()); ent.AlertMessage = string.Empty; ent.WithAlert = "N"; //if (ent.ProjectCode.Length > 10) //{ // ent.AlertMessage += "<tr><td> - the Project Code column must not must not exceed 10 characters </td></tr> "; //} //if (ent.ProjectCode.Length == 0) //{ // ent.AlertMessage += "<tr><td> - the Project Code column is required </td></tr> "; //} //if (ent.Description.Length > 255) //{ // ent.AlertMessage += "<tr><td> - the Description column must not must not exceed 255 characters </td></tr> "; //} //if (ent.Description.Length == 0) //{ // ent.AlertMessage += "<tr><td> - the Description column is required </td></tr> "; //} //if (ent.AlertMessage.Length > 0) //{ // ent.AlertMessage = "<table>" + ent.AlertMessage + "</table>"; // ent.WithAlert = "S"; //} entlist.Add(ent); } Session["ListProjects"] = entlist; objResult.data = entlist; } catch (Exception ex) { objResult.isError = true; objResult.data = null; objResult.message = "Projects :" + "Format error, check records"; return(Json(objResult)); } objResult.isError = false; objResult.message = null; // string.Format(MessageResource.SaveSuccess, "Load File save"); } catch (Exception ex) { objResult.isError = true; objResult.data = null; objResult.message = "Error loading Projects"; } return(Json(objResult)); }