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