/// <summary> /// Gets all the records in the Excel file and converts each to <see cref="Type"/> T. /// </summary> /// <typeparam name="T">The <see cref="Type"/> of the record.</typeparam> /// <returns>An <see cref="IEnumerable{T}" /> of records.</returns> public IEnumerable <T> GetRecords <T>() { // Get the type of all the records var type = typeof(T); // Make sure it is mapped if (_configuration.Maps[type] == null) { _configuration.Maps.Add(_configuration.AutoMap(type)); } // First read the header record and parse it ParseHeaderRecord(); // Create the function to read the records outside the inner loop Delegate parseRecord; try { parseRecord = GetParseRecordFunc(type); } catch (Exception ex) { ExceptionHelper.AddExceptionDataMessage(ex, type); throw; } // Read each record one at a time and yield it var ignoreEmptyRows = Configuration.IgnoreEmptyRows; while (_reader.Read()) { // If we are not ignoring empty records, bail when we reach one. Otherwise we process all rows in the file. if (IsEmptyRecord()) { if (ignoreEmptyRows) { _row++; continue; } else { break; } } T record; try { _currentIndex = -1; record = (T)parseRecord.DynamicInvoke(); _row++; } catch (Exception ex) { // Build the details about the error so it can be logged var details = new ExcelReadErrorDetails { Row = _row + 1, // Show 1 based row to the user Column = _currentIndex + 1, // Show 1 based column to the user FieldName = (from pair in _namedIndexes from index in pair.Value where index == _currentIndex select pair.Key).SingleOrDefault(), FieldValue = _reader.GetValue(_currentIndex), }; // Add the details to the exception ExceptionHelper.AddExceptionDataMessage(ex, type, details); // If we are ignoring errors, optionally call the callback and continue if (_configuration.IgnoreReadingExceptions) { _configuration.ReadingExceptionCallback?.Invoke(ex, details); _row++; continue; } throw; } yield return(record); } }
/// <summary> /// Gets all the records in the Excel file and converts each to dictionary of strings to strings. /// </summary> /// <returns>An enumeration of dictionaries.</returns> public IEnumerable <Dictionary <string, string> > GetRecordsAsDictionary() { // We assume all columns contain headers by default _columnCount = _reader.FieldCount; // First make sure we have a header record if (!_reader.Read()) { throw new ExcelReaderException("No header record was found."); } // Process each column in the header row var headers = new List <string>(); for (var i = 0; i < _columnCount; i++) { // Get the header name var name = _reader.GetString(i); if (string.IsNullOrEmpty(name)) { // Header is null or empty, so we are done. This can happen if the file has more total columns // in it than header rows, which can happen if some white space ends up in a right column // or there are extra rows below the records _columnCount = i; break; } // Now store the named index for later for this header column if (!_configuration.IsHeaderCaseSensitive) { name = name.ToLower(); } headers.Add(name); } // Move to the next row _row++; // Read each record one at a time and yield it while (_reader.Read()) { var record = new Dictionary <string, string>(); for (var i = 0; i < _columnCount; i++) { try { var value = _reader.GetValue(i); if (value != null) { string text; var type = _reader.GetFieldType(i); if (type == typeof(bool)) { // For compatibility with old PHP code, format TRUE and FALSE for boolean values text = (bool)value ? "TRUE" : "FALSE"; } else if (type == typeof(DateTime)) { // To ensure DateTime values make sense in any culture, we render them in ISO 8601 format text = ((DateTime)value).ToString("o"); } else { text = value.ToString(); } record.Add(headers[i], text); } else { // Always return empty strings, not nulls record.Add(headers[i], ""); } } catch (Exception ex) { // Build the details about the error so it can be logged var details = new ExcelReadErrorDetails { Row = _row + 1, Column = i + 1, FieldName = headers[i], FieldValue = _reader.GetValue(i), }; // Add the details to the exception ExceptionHelper.AddExceptionDataMessage(ex, null, details); // If we are ignoring errors, optionally call the callback and continue if (_configuration.IgnoreReadingExceptions) { _configuration.ReadingExceptionCallback?.Invoke(ex, details); _row++; continue; } throw; } } _row++; yield return(record); } }
/// <summary> /// Gets all the records in the Excel file and converts each to dictionary of strings to strings. /// </summary> /// <returns>An enumeration of dictionaries.</returns> public IEnumerable <Dictionary <string, string> > GetRecordsAsDictionary() { // We assume all columns contain headers by default _columnCount = _sheet.Columns.Count; // First make sure we have a header record ReadRow(); if (IsEmptyRecord()) { throw new ExcelReaderException("No header record was found."); } // Process each column in the header row var headers = new List <string>(); for (var i = 0; i < _columnCount; i++) { // Get the header name var name = _sheet[_row, i].Value as string; if (string.IsNullOrEmpty(name)) { // Header is null or empty, so we are done. This can happen if the file has more total columns // in it than header rows, which can happen if some white space ends up in a right column // or there are extra rows below the records _columnCount = i; break; } // Now store the named index for later for this header column if (!_configuration.IsHeaderCaseSensitive) { name = name.ToLower(); } headers.Add(name.Trim()); } // Move to the next row _row++; // Read each record one at a time and yield it while (!IsEmptyRecord()) { var record = new Dictionary <string, string>(); for (var i = 0; i < _columnCount; i++) { try { var cell = _sheet[_row, i]; var value = cell.Value; if (value != null) { // Format value if cell has a style with format set string text; var style = cell.Style ?? (_sheet.Rows[_row].Style ?? _sheet.Columns[i].Style); if (value.GetType() == typeof(bool)) { // By default Excel formats boolean as uppercase if ((bool)value) { text = "TRUE"; } else { text = "FALSE"; } } else { if (style != null && style.Format.Length > 0 && value is IFormattable) { var fmt = XLStyle.FormatXLToDotNet(style.Format.ToUpperInvariant()); if (!string.IsNullOrEmpty(fmt)) { text = ((IFormattable)value).ToString(fmt, CultureInfo.CurrentCulture); } else { text = value.ToString(); } } else { text = value.ToString(); } } record.Add(headers[i], text); } else { // Always return empty strings, not nulls record.Add(headers[i], ""); } } catch (Exception ex) { // Build the details about the error so it can be logged var details = new ExcelReadErrorDetails { Row = _row + 1, Column = i + 1, FieldName = headers[i], FieldValue = _sheet[_row, i].Value, }; // Add the details to the exception ExceptionHelper.AddExceptionDataMessage(ex, null, details); // If we are ignoring errors, optionally call the callback and continue if (_configuration.IgnoreReadingExceptions) { _configuration.ReadingExceptionCallback?.Invoke(ex, details); _row++; continue; } throw; } } _row++; yield return(record); } }