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