/// <summary> /// Read the data records. /// </summary> /// <remarks> /// Reads the heading row, if specified, and also inspects several data rows to see what columns we can find. /// </remarks> /// <returns>Enumeration of record readers.</returns> public SheetMetadata ReadMetadata( ) { const int dataRowsToInspect = 10; // Dictionary of columns and their headings Dictionary <string, string> columns = new Dictionary <string, string>( ); // Heading cells bool getHeadings = _settings.HeadingRowNumber > 0; if (getHeadings) { Row headingRow = GetRow(_settings.HeadingRowNumber); if (headingRow != null) { IEnumerable <Cell> headingCells = headingRow.Elements <Cell>( ); foreach (Cell headingCell in headingCells) { string column = ExcelHelpers.GetColumnPart(headingCell.CellReference?.Value); string title = ExcelHelpers.GetCellText(_document, headingCell); if (string.IsNullOrEmpty(title)) { continue; } columns[column] = title; } } } // Then inspect several rows of data cells - to pick up any loose data values that don't have a heading // (but just use the cell reference as the title) IEnumerable <Row> rows = GetRows(_settings.FirstDataRowNumber, _settings.LastDataRowNumber).Take(dataRowsToInspect); foreach (Row row in rows) { IEnumerable <Cell> cells = row.Elements <Cell>( ); foreach (Cell cell in cells) { string column = ExcelHelpers.GetColumnPart(cell.CellReference?.Value); if (!columns.ContainsKey(column)) { string content = ExcelHelpers.GetCellText(_document, cell); if (string.IsNullOrEmpty(content)) { continue; } columns [column] = column; } } } // Build result SheetMetadata result = new SheetMetadata( ); result.Fields = columns.Keys .OrderBy(colRef => colRef.Length).ThenBy(colRef => colRef) // make sure that 'Z' appears before 'AA', etc. .Select(colRef => new FieldMetadata { Key = colRef, Title = columns[colRef]?.Trim() }) .ToList( ); return(result); }
/// <summary> /// Locate a single cell by column reference and return its text content. /// </summary> /// <param name="key">The column reference. An Excel alphabetical column reference.</param> /// <returns>The text data.</returns> private string GetCellText(string key) { Cell cell = GetCell(key); return(ExcelHelpers.GetCellText(_document, cell)); }