public FileReaderResults AddManagerSkillsReport(SkillsMatrixDB database, string filePath)
{
FileReaderResults ret = FileReaderResults.FileReaderUnknownError;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Excel._Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int currentRow = 1;
int matches = 0;
//iterate over the rows and columns
//excel is not zero based!!
for (int col = 1; col <= colCount; col++)
{
Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];
if (cell.Value2 != null)
{
if (ManagerColumns.Contains(cell.Value2))
{
matches++;
}
}
Marshal.FinalReleaseComObject(cell);
}
if (matches == ManagerColumns.Count)
{
ret = FileReaderResults.FileReaderSuccess;
}
else
{
ret = FileReaderResults.FileReaderWrongFormat;
}
try
{
if (ret == FileReaderResults.FileReaderSuccess)
{
for (currentRow++; currentRow <= rowCount; currentRow++)
{
SkillsModel skill = new SkillsModel();
PersonModel person = new PersonModel();
for (int col = 1; col <= colCount; col++)
{
Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];
ManagerItem colIdx = (ManagerItem)(col - 1);
switch (colIdx)
{
case ManagerItem.ManagerName:
person.ManagerName = cell.Value2.ToString();
break;
case ManagerItem.FullName:
person.Name = cell.Value2.ToString();
break;
case ManagerItem.PersonnelNo:
person.PersonnelNo = Int32.Parse(cell.Value2.ToString());
break;
case ManagerItem.Email:
person.Email = cell.Value2.ToString();
break;
case ManagerItem.SkillName:
skill.SkillName = cell.Value2.ToString();
break;
case ManagerItem.SkillGroupName:
skill.GroupName = cell.Value2.ToString();
break;
case ManagerItem.SkillGroupParentName:
skill.GroupParentName = cell.Value2.ToString();
break;
case ManagerItem.RequiredSkill:
if (cell.Value2 != null)
{
skill.RequiredSkillLevel = cell.Value2.ToString();
}
else
{
skill.RequiredSkillLevel = "Not Set";
}
break;
case ManagerItem.HeldSkill:
if (cell.Value2 != null)
{
skill.HeldSkillLevel = cell.Value2.ToString();
}
else
{
skill.HeldSkillLevel = "Not Set";
}
break;
}
Marshal.FinalReleaseComObject(cell);
}
if (database.SaveManagerSkill(person, skill) != 1)
{
ret = FileReaderResults.FileReaderErrorProcessingFile;
}
}
}
}
catch (Exception ex)
{
ret = FileReaderResults.FileReaderUnknownError;
}
finally
{
//close and release
Marshal.FinalReleaseComObject(xlRange);
Marshal.FinalReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.FinalReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
// take the trash out
CleanupExcel();
}
return(ret);
}
