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