internal ImportCompetenciesResult ProcessCompetenciesTable(IXLTable table, int adminUserId, int frameworkId, int maxFrameworkCompetencyId, int maxFrameworkCompetencyGroupId)
{
var competenciesRows = table.Rows().Skip(1).Select(row => new CompetencyTableRow(table, row)).ToList();
foreach (var competencyRow in competenciesRows)
{
maxFrameworkCompetencyGroupId = ProcessCompetencyRow(adminUserId, frameworkId, maxFrameworkCompetencyId, maxFrameworkCompetencyGroupId, competencyRow);
}
return(new ImportCompetenciesResult(competenciesRows));
}
internal BulkUploadResult ProcessDelegatesTable(IXLTable table, int centreId, DateTime?welcomeEmailDate = null)
{
var jobGroupIds = jobGroupsDataService.GetJobGroupsAlphabetical().Select(item => item.id).ToList();
var delegateRows = table.Rows().Skip(1).Select(row => new DelegateTableRow(table, row)).ToList();
foreach (var delegateRow in delegateRows)
{
ProcessDelegateRow(centreId, welcomeEmailDate, delegateRow, jobGroupIds);
}
return(new BulkUploadResult(delegateRows));
}
/// <summary>
/// Get an ADO.Net DataTable object from an Excel .xlsx file
/// </summary>
/// <param name="fileName">File to use</param>
/// <returns>ADO.Net DataTable</returns>
public static DataTable GetDataTableFromExcel(string fileName)
{
DataTable newDt = new DataTable();
XLWorkbook wb = new XLWorkbook(fileName);
IXLWorksheet ws = wb.Worksheet(1);
var xlRange = ws.RangeUsed();
IXLTable compTable = xlRange.AsTable();
foreach (IXLRangeColumn hello in compTable.Columns())
{
newDt.Columns.Add(hello.FirstCell().Value.ToString(), typeof(string));
}
bool headerFound = false;
foreach (IXLRangeRow row in compTable.Rows())
{
if (headerFound)
{
DataRow dr = newDt.NewRow();
int itemCt = 0;
foreach (IXLCell cell in row.Cells())
{
dr[itemCt] = row.Cell(itemCt + 1).Value.ToString();
itemCt++;
}
newDt.Rows.Add(dr);
}
else
{
headerFound = true;
}
}
return(newDt);
}
