/// <summary>
/// Read the data records.
/// </summary>
/// <returns>Enumeration of record readers.</returns>
public SheetMetadata ReadMetadata()
{
string[] values = null;
SheetMetadata result = new SheetMetadata();
bool getHeadings = _settings.HeadingRowNumber > 0;
// Heading row specified
int startingRow = getHeadings ? _settings.HeadingRowNumber : _settings.FirstDataRowNumber;
AdvanceToLine(startingRow);
// Read row
if (!_parser.EndOfData)
{
long lineNumber;
values = ReadFields(out lineNumber);
result.Fields = new List <FieldMetadata>( );
}
if (values == null)
{
result.Fields = new List <FieldMetadata>( );
}
// Create metadata
if (values == null)
{
result.Fields = new List <FieldMetadata>();
}
else
{
result.Fields =
values.Select((value, index) => new FieldMetadata
{
Key = ColumnIndexToKey(index),
Title = getHeadings ? value : ColumnIndexToKey(index)
}).ToList();
}
return(result);
}
/// <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);
}
