public static string GetCellValue(this ExcelElement <Cell> cell, string nullValue = null)
{
Cell cellData = cell.Data;
string cellValue = cellData.CellValue?.InnerText ?? nullValue;
string cellTextValue = null;
if (cellValue != null && cellData.DataType != null && cellData.DataType.Value == CellValues.SharedString)
{
cellTextValue = cell.Doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(cellValue)).InnerText;
}
if (cellTextValue == null && cellValue != null && cellData.DataType == null)
{
var propertyParser = cell.GetMetadata <IPropertyParser>();
if (propertyParser != null)
{
if (propertyParser.TargetType == typeof(LocalDate))
{
DateTime dateTime = Prelude
.ParseDouble(cellValue)
.Match(FromExcelSerialDate, DateTime.MinValue);
cellTextValue = dateTime.ToString("yyyy-MM-dd");
}
else if (propertyParser.TargetType == typeof(LocalTime))
{
DateTime dateTime = Prelude
.ParseDouble(cellValue)
.Match(FromExcelSerialDate, DateTime.MinValue);
cellTextValue = dateTime.ToString("HH:mm:ss");
}
else if (propertyParser.TargetType == typeof(DateTime))
{
DateTime dateTime = Prelude
.ParseDouble(cellValue)
.Match(FromExcelSerialDate, DateTime.MinValue);
cellTextValue = dateTime.ToString("yyyy-MM-ddTHH:mm:ss");
}
}
}
return(cellTextValue ?? cellValue);
}
/// <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));
}
private static string GetFormatedValue(this ExcelElement <Cell> cell)
{
var cellformat = cell.GetCellFormat();
string value;
if (cellformat.NumberFormatId != 0)
{
var elements = cell.Doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements <NumberingFormat>().ToList();
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);
}
public static string[] GetRowValues(
this ExcelElement <Row> row,
ExcelElement <Column>[] 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);
}
public Column(ExcelElement <Cell> cell)
{
Cell = cell;
Name = cell.GetCellValue();
}
public TableDataRow(IReadOnlyDictionary <string, string> data, ExcelElement <Row> row)
{
Data = data;
Row = row;
}