/// <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
ReadRow();
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
while (!IsEmptyRecord())
{
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 = _sheet[_row, _currentIndex].Value,
};
// 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>
/// Writes the list of typed records to the Excel file.
/// </summary>
/// <param name="records">The list of records to write.</param>
public void WriteRecords <T>(
IEnumerable <T> records)
where T : class
{
// 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));
}
// Get a list of all the properties so they will be sorted properly.
var properties = new ExcelPropertyMapCollection();
AddProperties(properties, _configuration.Maps[type]);
if (properties.Count == 0)
{
throw new ExcelWriterException($"No properties are mapped for type '{type.FullName}'.");
}
// Write the header
WriteHeader(properties);
// Write all the column styles
WriteColumnStyles(properties);
// Get the action method for writing the records out
Delegate writeRecord = null;
try {
writeRecord = GetWriteRecordAction(type, properties);
} catch (Exception ex) {
ExceptionHelper.AddExceptionDataMessage(ex, type);
throw;
}
// Now process each record
foreach (var record in records)
{
writeRecord.DynamicInvoke(record);
NextRecord();
}
}
/// <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.IsNullOrWhiteSpace(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. Also check that the next column is
// also empty and throw and exception if not.
if (i + 1 < _columnCount && !string.IsNullOrWhiteSpace(_sheet[_row, i + 1].Value as string))
{
throw new Exception($"Blank column header found at column {ColumnHelper.ColumnIndexToColumnLetter(i + 1)}");
}
_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];
record.Add(headers[i], FormatValueAsString(cell, _row, i, true));
} 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,
};
// Use the inner exception if we have one so the message is more clear
ex = ex.InnerException ?? ex;
// 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 <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),
};
// Use the inner exception if we have one so the message is more clear
ex = ex.InnerException ?? ex;
// 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);
}
}