/// <summary>
/// Maps rows to entities with specified <paramref name="parserProvider"/>.
/// </summary>
/// <typeparam name="T">Entity type.</typeparam>
/// <param name="sheet">Sheet.</param>
/// <param name="parserProvider"><see cref="IParserProvider"/>.</param>
/// <param name="factory">Factory.</param>
/// <returns>Enumeration of <typeparamref name="T"/>.</returns>
public static IEnumerable <T> GetRowsAs <T>(
this ExcelElement <Sheet> sheet,
IParserProvider parserProvider,
Func <IReadOnlyList <IPropertyValue>, T>?factory = null)
{
if (factory == null)
{
factory = list => (T)Activator.CreateInstance(typeof(T), list);
}
if (sheet.IsEmpty())
{
return(Array.Empty <T>());
}
return(sheet
.GetRows()
.MapRows(parserProvider, factory));
}
/// <summary>
/// Gets value for each header.
/// <see cref="IPropertyParser"/> will be attached to cells where cell header name same as <see cref="IPropertyParser.SourceName"/>.
/// </summary>
public static string[] GetRowValues(
this ExcelElement <Row> row,
ExcelElement <HeaderCell>[] headers,
IParserProvider parserProvider,
string?nullValue = null)
{
if (row.IsEmpty())
{
return(Array.Empty <string>());
}
var cells = row.GetRowCells();
string[] rowValues = new string[headers.Length];
for (int i = 0; i < headers.Length; i++)
{
var header = headers[i];
// Find cell for the same column.
var cell = cells.FirstOrDefault(c => c.Data.CellReference.GetColumnReference() == header.Data.ColumnReference);
if (cell != null)
{
// Set propertyParser for cell according column name
var propertyParser = parserProvider.GetParsers().FirstOrDefault(parser => parser.SourceName == header.Data.Name);
if (propertyParser != null)
{
cell.SetMetadata(propertyParser);
}
rowValues[i] = cell.GetCellValue(nullValue);
}
else
{
rowValues[i] = nullValue;
}
}
return(rowValues);
}
private static string GetFormattedValue(this ExcelElement <Cell> cell)
{
string value;
var cellFormat = cell.GetCellFormat();
if (cellFormat.NumberFormatId != 0)
{
var elements = cell.Doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements <NumberingFormat>();
string format = elements.FirstOrDefault(i => i.NumberFormatId.Value == cellFormat.NumberFormatId.Value)?.FormatCode;
//Note: Look also: https://stackoverflow.com/questions/13176832/reading-a-date-from-xlsx-using-open-xml-sdk
format ??= "d/m/yyyy";
double number = double.Parse(cell.Data.InnerText);
value = number.ToString(format);
}
else
{
value = cell.Data.InnerText;
}
return(value);
}
/// <summary>
/// Gets rows from sheet.
/// </summary>
/// <param name="sheet">Source sheet.</param>
/// <returns>Sheet rows.</returns>
public static IEnumerable <ExcelElement <Row> > GetRows(this ExcelElement <Sheet> sheet)
{
return(GetOpenXmlRows(sheet)
.Zip(Enumerable.Repeat(sheet, int.MaxValue), (row, sh) => new ExcelElement <Row>(sh.Doc, row)));
}
public TableDataRow(IReadOnlyDictionary <string, string> data, ExcelElement <Row> row)
{
Data = data;
Row = row;
}
public HeaderCell(ExcelElement <Cell> cell)
{
Cell = cell;
Name = cell.GetCellValue();
}
