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