private static string GetPropName <T>(int column, ExcelSheet <T> results) { // if the header has not been registered if (!results.Headers.HasSlotAndValue(column)) { // if its a dynamic type, generate column name from column index /*if (typeof(T).IsDynamicType()) { * while ((results.Headers.Count - 1) < column) { * results.Headers.Add("Column" + (results.Headers.Count + 1)); * } * }*/ // otherwise mark that we cannot support this prop return(null); } // return the registered header return(results.Headers[column]); }
/// <summary> /// Parse an XLSX file and convert it into a List of strongly typed Objects. /// Never returns null. /// If the first line is not headers, and you don't supply any columnProps, then the names of the columns are assumed. /// </summary> /// <param name="excelPath">Path of the XLSX file</param> /// <param name="headers">Read the first line of each sheet as the column headers</param> /// <param name="columnProps">Provide the properties per column, if known</param> /// <param name="onlySheetsNamed">Only returns the sheets with the given name.</param> /// <param name="skipBlankRows">Skips rows where all the cells are blank.</param> /// <returns></returns> public static ExcelResults <T> ImportXlsx <T>(string excelPath, ExcelHeaders headers, List <string> columnProps = null, List <string> onlySheetsNamed = null, bool skipBlankRows = true) { var results = new ExcelResults <T>(); // fail if file does not exist if (!excelPath.FileExists()) { return(results); } var workbook = new ExcelPackage(new FileInfo(excelPath)).Workbook; // fail if workbook cannot be read if (workbook == null) { return(results); } // per sheet foreach (var sheet in workbook.Worksheets) { // filter by worksheet name if wanted if (onlySheetsNamed != null && !onlySheetsNamed.Contains(sheet.Name)) { continue; } // skip the sheet if it is blank if (sheet.Dimension == null) { continue; } // new sheet var sheetResult = new ExcelSheet <T>(); results.Sheets.Add(sheetResult); // if props given, take those if (columnProps.Exists()) { sheetResult.Headers = columnProps; } // calc starting row based on header config var startRow = headers == ExcelHeaders.None ? 1 : 2; // get the size of the sheet int rowCount = sheet.Dimension.End.Row; int colCount = sheet.Dimension.End.Column; // read the headers of the first row if wanted and not given if (headers == ExcelHeaders.FirstRow && !sheetResult.Headers.Exists()) { // per cell for (int c = 1; c <= colCount; c++) { // register the header based on the cell text var text = sheet.Cells[1, c].Text.Trim(); if (text.Exists()) { sheetResult.Headers.Add(text); } } } // per row for (int r = startRow; r <= rowCount; r++) { // create a new data object for the row var row = Activator.CreateInstance <T>(); // check for blank rows var isBlankRow = true; // per cell for (int c = 1; c <= colCount; c++) { var prop = GetPropName(c - 1, sheetResult); var text = sheet.Cells[r, c].Text.Trim(); // skip if column name not given if (prop == null) { continue; } // store this value in the row object row.SetPropValue(prop, text); // check for blank rows if (text.Exists()) { isBlankRow = false; } } // skip blank rows if wanted if (skipBlankRows && isBlankRow) { } else { sheetResult.Data.Add(row); } } } // all ok if some sheets were read results.Success = results.Sheets.Exists(); return(results); }