private m_project FillDataFromExcel(m_project insertData,XSSFSheet sheet, int row, int colNum,ref List<string> messageList, ref bool allowSave) { bool test; var dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); //required non-foreign key Name (string) colNum=0; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.name = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Name", "Must be filled")); } } //optional foreign key Contractor Id colNum = 1; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { //insertData.contractor_id = RepoContractor.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue); //if(insertData.contractor_id == 0) //{ // allowSave = false; // messageList.Add(GetExcelMessage(row, "Contractor", "Does not exist")); //} } } //required non-foreign key Photo (string) colNum=2; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.photo = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Photo", "Must be filled")); } } //required non-foreign key Description (string) colNum=3; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.description = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Description", "Must be filled")); } } //required non-foreign key Start Date (System.DateTime) colNum=4; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { DateTime dateVal; double dateOA; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Start Date", "Invalid value")); } else { test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal); insertData.start_date = dateVal; } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Start Date", "Must be filled")); } } //required non-foreign key Finish Date (System.DateTime) colNum=5; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { DateTime dateVal; double dateOA; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Finish Date", "Invalid value")); } else { test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal); insertData.finish_date = dateVal; } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Finish Date", "Must be filled")); } } //optional non-foreign key Highlight (bool?) colNum=6; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue; if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no") { allowSave = false; messageList.Add(GetExcelMessage(row, "Highlight", "Must be filled with Yes or No")); } else { bool val = false; if(boolValue.ToLower() == "yes") { val = true; } insertData.highlight = val; } } } //required non-foreign key Project Stage (string) colNum=7; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.project_stage = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Project Stage", "Must be filled")); } } //optional non-foreign key Status (byte?) colNum=8; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { int val = 0; test = Int32.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Status", "Invalid value")); } else { insertData.status= (byte)val; } } } //optional non-foreign key Budget (double?) colNum=9; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { double val = 0; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Budget", "Invalid value")); } else { insertData.budget= val; } } } //required non-foreign key Currency (string) colNum=10; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.currency = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Currency", "Must be filled")); } } //optional non-foreign key Num (int?) colNum=11; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { int val = 0; test = Int32.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out val); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Num", "Invalid value")); } else { insertData.num= val; } } } //optional foreign key Pmc Id colNum = 12; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { //insertData.pmc_id = RepoPmc.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue); //if(insertData.pmc_id == 0) //{ // allowSave = false; // messageList.Add(GetExcelMessage(row, "Pmc", "Does not exist")); //} } } //required non-foreign key Summary (string) colNum=13; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.summary = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Summary", "Must be filled")); } } //optional foreign key Company Id colNum = 14; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { //insertData.company_id = RepoCompany.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue); //if(insertData.company_id == 0) //{ // allowSave = false; // messageList.Add(GetExcelMessage(row, "Company", "Does not exist")); //} } } //required non-foreign key Status Non Technical (string) colNum=15; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.status_non_technical = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Status Non Technical", "Must be filled")); } } //required non-foreign key Is Completed (bool) colNum=16; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue; if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no") { allowSave = false; messageList.Add(GetExcelMessage(row, "Is Completed", "Must be filled with Yes or No")); } else { bool val = false; if(boolValue.ToLower() == "yes") { val = true; } insertData.is_completed = val; } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Is Completed", "Must be filled")); } } //optional non-foreign key Completed Date (System.DateTime?) colNum=17; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { DateTime dateVal; double dateOA; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Completed Date", "Invalid value")); } else { test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal); insertData.completed_date = dateVal; } } } //required foreign key Project Id colNum = 18; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.project_id = RepoProject.GetIdByNameAndInsert(sheet.GetRow(row).GetCell(colNum).StringCellValue); if(insertData.project_id == 0) { allowSave = false; messageList.Add(GetExcelMessage(row, "Project", "Does not exist")); } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Project", "Must be filled")); } } //required non-foreign key Submit For Approval Time (System.DateTime) colNum=19; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { DateTime dateVal; double dateOA; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Submit For Approval Time", "Invalid value")); } else { test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal); insertData.submit_for_approval_time = dateVal; } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Submit For Approval Time", "Must be filled")); } } //required non-foreign key Approval Status (string) colNum=20; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.approval_status = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Approval Status", "Must be filled")); } } //optional non-foreign key Approval Time (System.DateTime?) colNum=21; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { DateTime dateVal; double dateOA; test = Double.TryParse(sheet.GetRow(row).GetCell(colNum).StringCellValue, out dateOA); if (test == false) { allowSave = false; messageList.Add(GetExcelMessage(row, "Approval Time", "Invalid value")); } else { test = DateTime.TryParse(DateTime.FromOADate(dateOA).ToString(), out dateVal); insertData.approval_time = dateVal; } } } //required non-foreign key Deleted (bool) colNum=22; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { string boolValue = sheet.GetRow(row).GetCell(colNum).StringCellValue; if(boolValue.ToLower() != "yes" && boolValue.ToLower() != "no") { allowSave = false; messageList.Add(GetExcelMessage(row, "Deleted", "Must be filled with Yes or No")); } else { bool val = false; if(boolValue.ToLower() == "yes") { val = true; } insertData.deleted = val; } } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Deleted", "Must be filled")); } } //required non-foreign key Approval Message (string) colNum=23; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.approval_message = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Approval Message", "Must be filled")); } } //required non-foreign key Status Technical (string) colNum=24; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.status_technical = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Status Technical", "Must be filled")); } } //required non-foreign key Scurve Data (string) colNum=25; dt = (XSSFCell)sheet.GetRow(row).GetCell(colNum); if (dt != null) { sheet.GetRow(row).GetCell(colNum).SetCellType(CellType.String); if (sheet.GetRow(row).GetCell(colNum).StringCellValue != String.Empty) { insertData.scurve_data = sheet.GetRow(row).GetCell(colNum).StringCellValue; } else { allowSave = false; messageList.Add(GetExcelMessage(row, "Scurve Data", "Must be filled")); } } return insertData; }
/** * Only for WithMoreVariousData.xlsx ! */ private static void doTestHyperlinkContents(XSSFSheet sheet) { Assert.IsNotNull(sheet.GetRow(3).GetCell(2).Hyperlink); Assert.IsNotNull(sheet.GetRow(14).GetCell(2).Hyperlink); Assert.IsNotNull(sheet.GetRow(15).GetCell(2).Hyperlink); Assert.IsNotNull(sheet.GetRow(16).GetCell(2).Hyperlink); // First is a link to poi Assert.AreEqual(HyperlinkType.Url, sheet.GetRow(3).GetCell(2).Hyperlink.Type); Assert.AreEqual(null, sheet.GetRow(3).GetCell(2).Hyperlink.Label); Assert.AreEqual("http://poi.apache.org/", sheet.GetRow(3).GetCell(2).Hyperlink.Address); // Next is an internal doc link Assert.AreEqual(HyperlinkType.Document, sheet.GetRow(14).GetCell(2).Hyperlink.Type); Assert.AreEqual("Internal hyperlink to A2", sheet.GetRow(14).GetCell(2).Hyperlink.Label); Assert.AreEqual("Sheet1!A2", sheet.GetRow(14).GetCell(2).Hyperlink.Address); // Next is a file Assert.AreEqual(HyperlinkType.File, sheet.GetRow(15).GetCell(2).Hyperlink.Type); Assert.AreEqual(null, sheet.GetRow(15).GetCell(2).Hyperlink.Label); Assert.AreEqual("WithVariousData.xlsx", sheet.GetRow(15).GetCell(2).Hyperlink.Address); // Last is a mailto Assert.AreEqual(HyperlinkType.Email, sheet.GetRow(16).GetCell(2).Hyperlink.Type); Assert.AreEqual(null, sheet.GetRow(16).GetCell(2).Hyperlink.Label); Assert.AreEqual("mailto:[email protected]?subject=XSSF Hyperlinks", sheet.GetRow(16).GetCell(2).Hyperlink.Address); }