/// <summary>
/// Writes out the column styles for the record
/// </summary>
/// <param name="properties">Properties for the record</param>
private void WriteColumnStyles(
ExcelPropertyMapCollection properties)
{
// Write the column styles for all the columns
for (var col = 0; col < properties.Count; col++)
{
// Determine if this property is written
var propertyMap = properties[col];
if (!CanWrite(propertyMap))
{
continue;
}
// Now get the property converter and options
var data = propertyMap.Data;
var typeConverterOptions = TypeConverterOptions.Merge(
TypeConverterOptionsFactory.GetOptions(data.Property.PropertyType, _configuration.CultureInfo),
data.TypeConverterOptions);
// Write the cell formatting style if defined for this type
var format = data.TypeConverter.ExcelFormatString(typeConverterOptions);
if (format != null)
{
_sheet.Columns[col].Style = new XLStyle(_book)
{
Format = format,
};
}
}
}
/// <summary>
/// Adds the properties from the mapping. This will recursively
/// traverse the mapping tree and add all properties for
/// reference maps.
/// </summary>
/// <param name="properties">The properties to be added to.</param>
/// <param name="mapping">The mapping where the properties are added from.</param>
protected void AddProperties(
ExcelPropertyMapCollection properties,
ExcelClassMap mapping)
{
properties.AddRange(mapping.PropertyMaps);
foreach (var refMap in mapping.ReferenceMaps)
{
AddProperties(properties, refMap.Mapping);
}
}
/// <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>
/// Writes the header record from the given properties.
/// </summary>
/// <param name="properties">The properties for the records.</param>
private void WriteHeader(
ExcelPropertyMapCollection properties)
{
// Write the header fields
foreach (var property in properties)
{
if (CanWrite(property))
{
_sheet.Cell(_row, _col++).SetValue(property.Data.Names.FirstOrDefault());
}
}
// Set the style for the header to bold if desired
if (_configuration.HeaderIsBold)
{
var xlRow = _sheet.Row(_row);
xlRow.Style.Font.SetBold(true);
}
// Move to the next record
NextRecord();
}
/// <summary>
/// Writes out the column styles for the record
/// </summary>
/// <param name="properties">Properties for the record</param>
private void WriteColumnStyles(
ExcelPropertyMapCollection properties)
{
// Write the column styles for all the columns
for (var col = 0; col < properties.Count; col++)
{
// Determine if this property is written
var propertyMap = properties[col];
if (!CanWrite(propertyMap))
{
continue;
}
// Now get the property converter and options
var data = propertyMap.Data;
var typeConverterOptions = TypeConverterOptions.Merge(
TypeConverterOptionsFactory.GetOptions(data.Property.PropertyType, _configuration.CultureInfo),
data.TypeConverterOptions);
// Write the cell formatting style if defined for this type
var isDate = data.TypeConverter.ConvertedType == typeof(DateTime);
var format = isDate ? typeConverterOptions.DateFormat : typeConverterOptions.NumberFormat;
if (format != null)
{
using (var xlColumn = _sheet.Column(col + 1)) {
if (isDate)
{
xlColumn.Style.DateFormat.Format = format;
}
else
{
xlColumn.Style.NumberFormat.Format = format;
}
}
}
}
}
/// <summary>
/// Writes the header record from the given properties.
/// </summary>
/// <param name="properties">The properties for the records.</param>
private void WriteHeader(
ExcelPropertyMapCollection properties)
{
// Write the header fields
foreach (var property in properties)
{
if (CanWrite(property))
{
_sheet[_row, _col++].Value = property.Data.Names.FirstOrDefault();
}
}
// Set the style for the header to bold if desired
if (_configuration.HeaderIsBold)
{
_sheet.Rows[_row].Style = new XLStyle(_book)
{
Font = new Font(_book.DefaultFont, FontStyle.Bold),
};
}
// Move to the next record
NextRecord();
}
/// <summary>
/// Adds a <see cref="MemberBinding"/> for each property for it's field.
/// </summary>
/// <param name="properties">The properties to add bindings for.</param>
/// <param name="bindings">The bindings that will be added to from the properties.</param>
private void AddPropertyBindings(
ExcelPropertyMapCollection properties,
List <MemberBinding> bindings)
{
foreach (var propertyMap in properties)
{
// Ignore properties that are not read
if (!CanRead(propertyMap))
{
continue;
}
// Find the index of this field in the row
var index = -1;
var data = propertyMap.Data;
if (data.IsIndexSet)
{
// If an index was explicitly set, use it.
index = data.Index;
}
else
{
// Fallback to the default name.
index = GetFieldIndex(data.OptionalRead, data.Names.ToArray(), data.NameIndex);
}
// Skip if the index was not found. This can happen if not all fields are included in the
// import file, and we are not in strict reading mode or the field was marked as optional read.
// Very useful if you want missing fields to be imported with default values. The optional read mode
// is useful to make sure critical fields are always present.
if (index == -1)
{
continue;
}
// Get the field using the field index
var property = data.Property;
var propertyType = property.PropertyType;
var method = GetType().GetMethod("GetField", BindingFlags.NonPublic | BindingFlags.Instance);
Expression fieldExpression = Expression.Call(Expression.Constant(this),
method,
Expression.Constant(index, typeof(int)),
Expression.Constant(propertyType, typeof(Type)));
// Get the type conversion information we need
var typeConverterExpression = Expression.Constant(data.TypeConverter);
var typeConverterOptions = TypeConverterOptions.Merge(
TypeConverterOptionsFactory.GetOptions(propertyType, _configuration.CultureInfo),
data.TypeConverterOptions);
var typeConverterOptionsExpression = Expression.Constant(typeConverterOptions);
// Store the mapped property in our list of properties
_importedColumns.Add(property);
// If a default value is set, check for an empty record and set the field to the default if it is empty
Expression expression;
if (data.IsDefaultSet)
{
// Creating an expression to hold the local field variable
var field = Expression.Parameter(typeof(object), "field");
// Handle strings differently, so we can compare strings to the empty string as well as null
Expression checkFieldEmptyExpression = propertyType == typeof(string) ?
checkFieldEmptyExpression = Expression.Call(typeof(string), "IsNullOrEmpty", null, Expression.Convert(field, typeof(string))) :
checkFieldEmptyExpression = Expression.Equal(field, Expression.Constant(null));
// Expression to assign the default value
var defaultValueExpression = Expression.Assign(field, Expression.Convert(Expression.Constant(data.Default), typeof(object)));
// Expression to convert the field value and store it back in the variable
var convertExpression = Expression.Assign(field, Expression.Call(typeConverterExpression, "ConvertFromExcel", null, typeConverterOptionsExpression, field));
// Create a block to execute so GetField won't be called twice
expression = Expression.Block(
// Local variable
new[] { field },
// Assign the result of GetField() to a local variable
Expression.Assign(field, fieldExpression),
// Conditionally set the field to the default value, or the converted value
Expression.IfThenElse(checkFieldEmptyExpression, defaultValueExpression, convertExpression),
// Finally convert the field local variable and return it
Expression.Convert(field, propertyType));
}
else
{
// Convert the field from Excel format to the native type directly
expression = Expression.Convert(
Expression.Call(typeConverterExpression, "ConvertFromExcel", null, typeConverterOptionsExpression, fieldExpression),
propertyType);
}
// Now add the binding to bind the expression result to the property
bindings.Add(Expression.Bind(property, expression));
}
}
/// <summary>
/// Gets the action delegate used to write the custom
/// class object to the writer.
/// </summary>
/// <param name="type">The type of the custom class being written.</param>
/// <param name="properties">Properties for the record</param>
/// <returns>The action delegate.</returns>
private Delegate GetWriteRecordAction(
Type type,
ExcelPropertyMapCollection properties)
{
if (!_typeActions.ContainsKey(type))
{
// Define the parameter to the action to pass in the record
var recordParameter = Expression.Parameter(type, "record");
// Build delegates to write every property out
var delegates = new List <Delegate>();
foreach (var propertyMap in properties)
{
// Ignore properties that are not written
if (!CanWrite(propertyMap))
{
continue;
}
// Get the type converter and converter options for this type
var data = propertyMap.Data;
var typeConverter = data.TypeConverter;
var typeConverterOptions = TypeConverterOptions.Merge(
TypeConverterOptionsFactory.GetOptions(data.Property.PropertyType, _configuration.CultureInfo),
data.TypeConverterOptions);
// Create an expression to extract the field from the record
var fieldExpression = CreatePropertyExpression(recordParameter, _configuration.Maps[type], propertyMap);
Expression actionExpression;
if (typeConverterOptions.IsFormula)
{
// Define an expression to call WriteFieldFormula(property)
actionExpression = Expression.Call(
Expression.Constant(this),
GetType().GetMethod("WriteFieldFormula", BindingFlags.NonPublic | BindingFlags.Instance),
Expression.Convert(fieldExpression, typeof(object)));
}
else if (typeConverter.AcceptsNativeType)
{
// Define an expression to call WriteFieldNative(property)
actionExpression = Expression.Call(
Expression.Constant(this),
GetType().GetMethod("WriteFieldNative", BindingFlags.NonPublic | BindingFlags.Instance),
Expression.Convert(fieldExpression, typeof(object)));
}
else
{
// Define an expression to call WriteFieldConverted(property, typeConverter, typeConverterOptions)
actionExpression = Expression.Call(
Expression.Constant(this),
GetType().GetMethod("WriteFieldConverted", BindingFlags.NonPublic | BindingFlags.Instance),
Expression.Convert(fieldExpression, typeof(object)),
Expression.Constant(typeConverter),
Expression.Constant(typeConverterOptions));
}
// Now create a lambda expression and compile it
var actionType = typeof(Action <>).MakeGenericType(type);
delegates.Add(Expression.Lambda(actionType, actionExpression, recordParameter).Compile());
}
// Combine all the delegates together so they are executed in order
_typeActions[type] = Delegate.Combine(delegates.ToArray());
}
return(_typeActions[type]);
}
