/// <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);
}
// 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);
text = ((IFormattable)value).ToString(fmt, CultureInfo.CurrentCulture);
}
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);
}
}
// convert excel styles into grid styles
private static ExcelCellStyle GetCellStyle(XLStyle x)
{
// look it up in the cache
ExcelCellStyle s = default(ExcelCellStyle);
if (_cellStyles.TryGetValue(x, out s))
{
return(s);
}
// not found, create style now
s = new ExcelCellStyle();
// alignment
switch (x.AlignHorz)
{
case XLAlignHorzEnum.Left:
s.HorizontalAlignment = HorizontalAlignment.Left;
break;
case XLAlignHorzEnum.Center:
s.HorizontalAlignment = HorizontalAlignment.Center;
break;
case XLAlignHorzEnum.Right:
s.HorizontalAlignment = HorizontalAlignment.Right;
break;
}
switch (x.AlignVert)
{
case XLAlignVertEnum.Top:
s.VerticalAlignment = VerticalAlignment.Top;
break;
case XLAlignVertEnum.Center:
s.VerticalAlignment = VerticalAlignment.Center;
break;
case XLAlignVertEnum.Bottom:
s.VerticalAlignment = VerticalAlignment.Bottom;
break;
}
s.TextWrapping = x.WordWrap;
// colors
if (x.BackPattern == XLPatternEnum.Solid && IsColorValid(x.BackColor))
{
s.Background = new SolidColorBrush(x.BackColor);
}
if (IsColorValid(x.ForeColor))
{
s.Foreground = new SolidColorBrush(x.ForeColor);
}
// font
dynamic font = x.Font;
if (font != null)
{
s.FontFamily = new FontFamily(font.FontName);
s.FontSize = PointsToPixels(font.FontSize);
if (font.Bold)
{
s.FontWeight = FontWeights.Bold;
}
if (font.Italic)
{
s.FontStyle = FontStyles.Italic;
}
if (font.Underline != XLUnderlineStyle.None)
{
s.TextDecorations = TextDecorations.Underline;
}
}
// format
if (!string.IsNullOrEmpty(x.Format))
{
s.Format = XLStyle.FormatXLToDotNet(x.Format);
}
// borders
s.CellBorderThickness = new Thickness(GetBorderThickness(x.BorderLeft), GetBorderThickness(x.BorderTop), GetBorderThickness(x.BorderRight), GetBorderThickness(x.BorderBottom));
s.CellBorderBrushLeft = GetBorderBrush(x.BorderColorLeft);
s.CellBorderBrushTop = GetBorderBrush(x.BorderColorTop);
s.CellBorderBrushRight = GetBorderBrush(x.BorderColorRight);
s.CellBorderBrushBottom = GetBorderBrush(x.BorderColorBottom);
// save in cache and return
_cellStyles[x] = s;
return(s);
}
// convert Excel style into FlexGrid style
private CellStyle StyleFromExcel(C1FlexGrid flex, XLStyle style)
{
// sanity
if (style == null)
{
return(null);
}
// look it up on list
if (_styles.Contains(style))
{
return(_styles[style] as CellStyle);
}
// create new flex style
CellStyle cs = flex.Styles.Add(_styles.Count.ToString());
// set up new style
if (style.Font != null)
{
cs.Font = style.Font;
}
if (style.ForeColor != Color.Transparent)
{
cs.ForeColor = style.ForeColor;
}
if (style.BackColor != Color.Transparent)
{
cs.BackColor = style.BackColor;
}
if (style.Rotation == 90)
{
cs.TextDirection = TextDirectionEnum.Up;
}
if (style.Rotation == 180)
{
cs.TextDirection = TextDirectionEnum.Down;
}
if (style.Format != null && style.Format.Length > 0)
{
cs.Format = XLStyle.FormatXLToDotNet(style.Format);
}
switch (style.AlignHorz)
{
case XLAlignHorzEnum.Center:
cs.WordWrap = style.WordWrap;
switch (style.AlignVert)
{
case XLAlignVertEnum.Top:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.CenterTop;
break;
case XLAlignVertEnum.Center:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.CenterCenter;
break;
default:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.CenterBottom;
break;
}
break;
case XLAlignHorzEnum.Right:
cs.WordWrap = style.WordWrap;
switch (style.AlignVert)
{
case XLAlignVertEnum.Top:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.RightTop;
break;
case XLAlignVertEnum.Center:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.RightCenter;
break;
default:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.RightBottom;
break;
}
break;
case XLAlignHorzEnum.Left:
cs.WordWrap = style.WordWrap;
switch (style.AlignVert)
{
case XLAlignVertEnum.Top:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.LeftTop;
break;
case XLAlignVertEnum.Center:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.LeftCenter;
break;
default:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.LeftBottom;
break;
}
break;
default:
cs.WordWrap = style.WordWrap;
switch (style.AlignVert)
{
case XLAlignVertEnum.Top:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.GeneralTop;
break;
case XLAlignVertEnum.Center:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.GeneralCenter;
break;
default:
cs.TextAlign = C1.Win.C1FlexGrid.TextAlignEnum.GeneralBottom;
break;
}
break;
}
// save it
_styles.Add(style, cs);
// return it
return(cs);
}
