/// <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>
/// Creates the action for a primitive.
/// </summary>
/// <param name="type">The type of primitive to create the action for.</param>
protected virtual void CreateActionForPrimitive(Type type)
{
var recordParameter = Expression.Parameter(type, "record");
Expression fieldExpression = Expression.Convert(recordParameter, typeof(object));
var typeConverter = TypeConverterFactory.GetConverter(type);
var typeConverterExpression = Expression.Constant(typeConverter);
var method = typeConverter.GetType().GetMethod("ConvertToString");
var typeConverterOptions = TypeConverterOptionsFactory.GetOptions(type);
if (typeConverterOptions.CultureInfo == null)
{
typeConverterOptions.CultureInfo = configuration.CultureInfo;
}
fieldExpression = Expression.Call(typeConverterExpression, method, Expression.Constant(typeConverterOptions), fieldExpression);
fieldExpression = Expression.Call(Expression.Constant(this), "WriteField", new[] { typeof(string) }, fieldExpression);
var actionType = typeof(Action <>).MakeGenericType(type);
typeActions[type] = Expression.Lambda(actionType, fieldExpression, recordParameter).Compile();
}
public void WriteRecordsAppliedWhenMappedTest()
{
var options = new TypeConverterOptions {
Format = "c"
};
TypeConverterOptionsFactory.AddOptions <int>(options);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var csvWriter = new CsvWriter(writer))
{
var list = new List <Test>
{
new Test {
Number = 1234, NumberOverridenInMap = 5678
},
};
csvWriter.Configuration.HasHeaderRecord = false;
csvWriter.Configuration.RegisterClassMap <TestMap>();
csvWriter.WriteRecords(list);
writer.Flush();
stream.Position = 0;
var record = reader.ReadToEnd();
Assert.AreEqual("\"$1,234.00\",\"5,678.00\"\r\n", record);
}
}
private void SetCovertSettings(List <TypeConverter> converters)
{
foreach (var converter in converters)
{
TypeConverterOptionsFactory.AddOptions(converter.Type, converter.TypeConverterOptions);
}
}
/// <summary>
/// Creates the action for an object.
/// </summary>
/// <param name="type">The type of object to create the action for.</param>
protected virtual void CreateActionForObject(Type type)
{
var recordParameter = Expression.Parameter(type, "record");
// Get a list of all the properties so they will
// be sorted properly.
var properties = new CsvPropertyMapCollection();
AddProperties(properties, configuration.Maps[type]);
if (properties.Count == 0)
{
throw new CsvWriterException(string.Format("No properties are mapped for type '{0}'.", type.FullName));
}
var delegates = new List <Delegate>();
foreach (var propertyMap in properties)
{
if (!CanWrite(propertyMap))
{
continue;
}
if (propertyMap.Data.TypeConverter == null || !propertyMap.Data.TypeConverter.CanConvertTo(typeof(string)))
{
// Skip if the type isn't convertible.
continue;
}
var fieldExpression = CreatePropertyExpression(recordParameter, configuration.Maps[type], propertyMap);
var typeConverterExpression = Expression.Constant(propertyMap.Data.TypeConverter);
if (propertyMap.Data.TypeConverterOptions.CultureInfo == null)
{
propertyMap.Data.TypeConverterOptions.CultureInfo = configuration.CultureInfo;
}
var typeConverterOptions = TypeConverterOptions.Merge(TypeConverterOptionsFactory.GetOptions(propertyMap.Data.Property.PropertyType), propertyMap.Data.TypeConverterOptions);
var typeConverterOptionsExpression = Expression.Constant(typeConverterOptions);
var method = propertyMap.Data.TypeConverter.GetType().GetMethod("ConvertToString");
fieldExpression = Expression.Convert(fieldExpression, typeof(object));
fieldExpression = Expression.Call(typeConverterExpression, method, typeConverterOptionsExpression, fieldExpression);
if (type.IsClass)
{
var areEqualExpression = Expression.Equal(recordParameter, Expression.Constant(null));
fieldExpression = Expression.Condition(areEqualExpression, Expression.Constant(string.Empty), fieldExpression);
}
var writeFieldMethodCall = Expression.Call(Expression.Constant(this), "WriteField", new[] { typeof(string) }, fieldExpression);
var actionType = typeof(Action <>).MakeGenericType(type);
delegates.Add(Expression.Lambda(actionType, writeFieldMethodCall, recordParameter).Compile());
}
typeActions[type] = CombineDelegates(delegates);
}
/// <summary>
/// Creates options using the given <see cref="CsvConfiguration"/>.
/// </summary>
/// <param name="configuration"></param>
public AutoMapOptions(CsvConfiguration configuration)
{
IgnoreReferences = configuration.IgnoreReferences;
PrefixReferenceHeaders = configuration.PrefixReferenceHeaders;
IncludePrivateProperties = configuration.IncludePrivateMembers;
MemberTypes = configuration.MemberTypes;
TypeConverterOptionsFactory = configuration.TypeConverterOptionsFactory;
}
/// <summary>
/// Writes a cell to the Excel file.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="row">Row to write the field to.</param>
/// <param name="col">Column to write the field to.</param>
/// <param name="field">The field to write.</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
public void WriteCell <T>(
int row,
int col,
T field,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
// Clear the cell if the field is null
var cell = _sheet.Cell(row + 1, col + 1);
if (field == null)
{
cell.SetValue((object)null);
return;
}
// Find the type conversion options
var type = typeof(T);
var converter = TypeConverterFactory.GetConverter(type);
var options = TypeConverterOptions.Merge(TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo));
// Set the formatting options to override the defaults
numberFormat = numberFormat ?? options.NumberFormat;
dateFormat = dateFormat ?? options.DateFormat;
// Apply the style to this cell if defined
if (numberFormat != null || dateFormat != null || fontStyle != null || fontSize != null || fontName != null)
{
UpdateStyle(cell.Style, numberFormat, dateFormat, fontStyle, fontSize, fontName);
}
// Now write the cell contents
if (field.GetType() == typeof(string))
{
var s = (string)(object)field;
if (s != null && s.StartsWith("="))
{
// Write as a formula if it starts with an equals sign
cell.FormulaA1 = s;
}
else
{
cell.SetValue(s);
}
}
else if (converter.AcceptsNativeType)
{
cell.SetValue(field);
}
else
{
cell.SetValue(converter.ConvertToExcel(options, field));
}
}
/// <summary>
/// Reads a cell from the Excel file.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="index">Column index to reead the value from.</param>
/// <returns>The value from the column converted to the specific type</returns>
public T GetColumn <T>(
int index)
{
var type = typeof(T);
var converter = TypeConverterFactory.GetConverter(type);
var typeConverterOptions = TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo);
return((T)converter.ConvertFromExcel(typeConverterOptions, _sheet[_row, index].Value));
}
/// <summary>
/// Reads a cell from the Excel file.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="index">Column index to read the value from.</param>
/// <returns>The value from the column converted to the specific type</returns>
public T GetColumn <T>(
int index)
{
var type = typeof(T);
var converter = TypeConverterFactory.GetConverter(type);
var typeConverterOptions = TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo);
var value = type == typeof(string) ? FormatValueAsString(index, false) : _reader.GetValue(index);
return((T)converter.ConvertFromExcel(typeConverterOptions, value));
}
public virtual void WriteField(Type type, object field, ITypeConverter converter)
{
CheckDisposed();
var options = TypeConverterOptionsFactory.GetOptions(type);
if (options.CultureInfo == null)
{
options.CultureInfo = configuration.CultureInfo;
}
WriteField(converter.ConvertToString(options, field));
}
public BeaconEventsUtils(SQLiteConnection sqlConnection, INotificationService notificationService)
{
_sqlConnection = sqlConnection;
_notificationService = notificationService;
sqlConnection.CreateTable <BeaconEvent> ();
var options = new TypeConverterOptions
{
Format = "MM/d/yyyy HH:mm:ss tz",
};
TypeConverterOptionsFactory.AddOptions <DateTime>(options);
}
/// <summary>
/// Writes the field to the CSV file.
/// When all fields are written for a record,
/// <see cref="ICsvWriter.NextRecord" /> must be called
/// to complete writing of the current record.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="field">The field to write.</param>
/// <param name="converter">The converter used to convert the field into a string.</param>
public virtual void WriteField <T>(T field, ITypeConverter converter)
{
CheckDisposed();
var typeConverterOptions = TypeConverterOptionsFactory.GetOptions <T>();
if (typeConverterOptions.CultureInfo == null)
{
typeConverterOptions.CultureInfo = configuration.CultureInfo;
}
var fieldString = converter.ConvertToString(typeConverterOptions, field);
WriteField(fieldString);
}
/// <summary>
/// Writes the field to the CSV file.
/// When all fields are written for a record,
/// <see cref="ICsvWriter.NextRecord" /> must be called
/// to complete writing of the current record.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="field">The field to write.</param>
/// <param name="converter">The converter used to convert the field into a string.</param>
/// <param name="forceQuote">True to force quote the field, otherwise evaluate if quote is needed</param>
public virtual void WriteField <T>(T field, ITypeConverter converter, bool forceQuote = false)
{
CheckDisposed();
var typeConverterOptions = TypeConverterOptionsFactory.GetOptions <T>();
if (typeConverterOptions.CultureInfo == null)
{
typeConverterOptions.CultureInfo = configuration.CultureInfo;
}
var fieldString = converter.ConvertToString(typeConverterOptions, field);
var shouldQuote = ShouldQuote(ref fieldString) || forceQuote; // order matters ... the should quote might change the field, smells bad
WriteField(fieldString, shouldQuote);
}
static void Main(string[] args)
{
// open a file
if (!args.Any() || string.IsNullOrWhiteSpace(args[0]))
{
Console.WriteLine($"usage: {AppDomain.CurrentDomain.FriendlyName} input_file");
Console.ReadLine();
Environment.Exit(-1);
}
string inputString = File.ReadAllText(args[0]);
// parse data to Document object (camt_053_001_02)
Document document = null;
XmlSerializer serializer = new XmlSerializer(typeof(Document));
using (StringReader reader = new StringReader(inputString))
{
document = (Document)(serializer.Deserialize(reader));
}
// convert data from camt_053_001_02 format to YNAB format
var transactions = new List <Transaction>();
foreach (var entry in document.BkToCstmrStmt.Stmt.First().Ntry.ToList())
{
transactions.Add(new Transaction(entry));
}
// write CSV file to disk
var options = new TypeConverterOptions
{
Format = "yyyy-MM-dd",
};
TypeConverterOptionsFactory.AddOptions <DateTime>(options);
var newFileName = Path.ChangeExtension(args[0], "csv");
using (TextWriter writer = File.CreateText(newFileName))
{
var csv = new CsvWriter(writer);
csv.WriteRecords(transactions);
}
// done
Console.WriteLine($"Done! Records converted: {transactions.Count}");
Console.Read();
}
/// <summary>
/// Creates the action for an object.
/// </summary>
/// <param name="type">The type of object to create the action for.</param>
protected virtual void CreateActionForObject(Type type)
{
var parameterExpression = Expression.Parameter(type, "record");
var csvPropertyMapCollections = new CsvPropertyMapCollection();
AddProperties(csvPropertyMapCollections, configuration.Maps[type]);
if (csvPropertyMapCollections.Count == 0)
{
throw new CsvWriterException(string.Format("No properties are mapped for type '{0}'.", type.FullName));
}
var delegates = new List <Delegate>();
foreach (var csvPropertyMapCollection in csvPropertyMapCollections)
{
if (!CanWrite(csvPropertyMapCollection) || csvPropertyMapCollection.Data.TypeConverter == null ||
!csvPropertyMapCollection.Data.TypeConverter.CanConvertTo(typeof(string)))
{
continue;
}
var expression = CreatePropertyExpression(parameterExpression, configuration.Maps[type],
csvPropertyMapCollection);
var constantExpression = Expression.Constant(csvPropertyMapCollection.Data.TypeConverter);
if (csvPropertyMapCollection.Data.TypeConverterOptions.CultureInfo == null)
{
csvPropertyMapCollection.Data.TypeConverterOptions.CultureInfo = configuration.CultureInfo;
}
var constantExpression1 =
Expression.Constant(
TypeConverterOptions.Merge(
TypeConverterOptionsFactory.GetOptions(csvPropertyMapCollection.Data.Property.PropertyType),
csvPropertyMapCollection.Data.TypeConverterOptions));
var method = csvPropertyMapCollection.Data.TypeConverter.GetType().GetMethod("ConvertToString");
expression = Expression.Convert(expression, typeof(object));
expression = Expression.Call(constantExpression, method, constantExpression1, expression);
if (type.GetTypeInfo().IsClass)
{
expression = Expression.Condition(Expression.Equal(parameterExpression, Expression.Constant(null)),
Expression.Constant(string.Empty), expression);
}
var methodCallExpression = Expression.Call(Expression.Constant(this), "WriteField",
new[] { typeof(string) }, expression);
var type1 = typeof(Action <>).MakeGenericType(type);
delegates.Add(Expression.Lambda(type1, methodCallExpression, parameterExpression).Compile());
}
typeActions[type] = CombineDelegates(delegates);
}
public void AddGetRemoveTest()
{
var customOptions = new TypeConverterOptions
{
Format = "custom",
};
TypeConverterOptionsFactory.AddOptions <string>(customOptions);
var options = TypeConverterOptionsFactory.GetOptions <string>();
Assert.AreEqual(customOptions.Format, options.Format);
TypeConverterOptionsFactory.RemoveOptions <string>();
options = TypeConverterOptionsFactory.GetOptions <string>();
Assert.AreNotEqual(customOptions.Format, options.Format);
}
public void AddGetRemoveTest()
{
var customOptions = new TypeConverterOptions
{
Formats = new string[] { "custom" },
};
var typeConverterOptionsFactory = new TypeConverterOptionsFactory();
typeConverterOptionsFactory.AddOptions <string>(customOptions);
var options = typeConverterOptionsFactory.GetOptions <string>();
Assert.AreEqual(customOptions.Formats, options.Formats);
typeConverterOptionsFactory.RemoveOptions <string>();
options = typeConverterOptionsFactory.GetOptions <string>();
Assert.AreNotEqual(customOptions.Formats, options.Formats);
}
public void GetRecordsTest()
{
var options = new TypeConverterOptions {
NumberStyle = NumberStyles.AllowThousands
};
TypeConverterOptionsFactory.AddOptions <int>(options);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var csvReader = new CsvReader(reader))
{
writer.WriteLine("\"1,234\",\"5,678\"");
writer.Flush();
stream.Position = 0;
csvReader.Configuration.HasHeaderRecord = false;
csvReader.GetRecords <Test>().ToList();
}
}
public void WriteFieldTest()
{
var options = new TypeConverterOptions {
Format = "c"
};
TypeConverterOptionsFactory.AddOptions <int>(options);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var csvWriter = new CsvWriter(writer))
{
csvWriter.WriteField(1234);
csvWriter.NextRecord();
writer.Flush();
stream.Position = 0;
var record = reader.ReadToEnd();
Assert.AreEqual("\"$1,234.00\"\r\n", record);
}
}
public void GetFieldTest()
{
var options = new TypeConverterOptions {
NumberStyle = NumberStyles.AllowThousands
};
TypeConverterOptionsFactory.AddOptions <int>(options);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var csvReader = new CsvReader(reader))
{
writer.WriteLine("\"1,234\",\"5,678\"");
writer.Flush();
stream.Position = 0;
csvReader.Configuration.HasHeaderRecord = false;
csvReader.Read();
Assert.AreEqual(1234, csvReader.GetField <int>(0));
Assert.AreEqual(5678, csvReader.GetField(typeof(int), 1));
}
}
/// <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>
/// Creates the action for a primitive.
/// </summary>
/// <param name="type">The type of primitive to create the action for.</param>
protected virtual void CreateActionForPrimitive(Type type)
{
var recordParameter = Expression.Parameter(type, "record");
Expression fieldExpression = Expression.Convert(recordParameter, typeof(object));
var typeConverter = TypeConverterFactory.GetConverter(type);
var typeConverterExpression = Expression.Constant(typeConverter);
var method = typeConverter.GetType().GetMethod("ConvertToString");
var propertyMapData = new CsvPropertyMapData(null)
{
Index = 0,
TypeConverter = typeConverter,
TypeConverterOptions = { CultureInfo = configuration.CultureInfo }
};
propertyMapData.TypeConverterOptions = TypeConverterOptions.Merge(propertyMapData.TypeConverterOptions, TypeConverterOptionsFactory.GetOptions(type));
fieldExpression = Expression.Call(typeConverterExpression, method, fieldExpression, Expression.Constant(this), Expression.Constant(propertyMapData));
fieldExpression = Expression.Call(Expression.Constant(this), "WriteConvertedField", null, fieldExpression);
var actionType = typeof(Action <>).MakeGenericType(type);
typeActions[type] = Expression.Lambda(actionType, fieldExpression, recordParameter).Compile();
}
/// <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]);
}
/// <summary>
/// Writes the field to the CSV file.
/// When all fields are written for a record,
/// <see cref="ICsvWriter.NextRecord" /> must be called
/// to complete writing of the current record.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="field">The field to write.</param>
/// <param name="converter">The converter used to convert the field into a string.</param>
public virtual void WriteField <T>(T field, ITypeConverter converter)
{
CheckDisposed();
var type = field == null ? typeof(string) : field.GetType();
var propertyMapData = new CsvPropertyMapData(null)
{
TypeConverter = converter,
TypeConverterOptions = { CultureInfo = configuration.CultureInfo }
};
propertyMapData.TypeConverterOptions = TypeConverterOptions.Merge(propertyMapData.TypeConverterOptions, TypeConverterOptionsFactory.GetOptions(type));
var fieldString = converter.ConvertToString(field, this, propertyMapData);
WriteConvertedField(fieldString);
}
/// <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>
/// Writes a cell to the Excel file.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="row">Row to write the field to.</param>
/// <param name="col">Column to write the field to.</param>
/// <param name="field">The field to write.</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
public void WriteCell <T>(
int row,
int col,
T field,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
// Find the type conversion options
var type = typeof(T);
var converter = TypeConverterFactory.GetConverter(type);
var options = TypeConverterOptions.Merge(TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo));
// Set the formatting options to override the defaults
var format = numberFormat ?? dateFormat;
if (converter.AcceptsNativeType)
{
// Convert the options to Excel format
if (format != null)
{
format = XLStyle.FormatDotNetToXL(format, converter.ConvertedType, options.CultureInfo);
}
else
{
// If no formatting is provided, see if the native type requires it (mostly for DateTime)
format = converter.ExcelFormatString(options);
}
}
else
{
// Override the formatting for the formatter, and do not format the Excel cell
if (numberFormat != null)
{
options.NumberFormat = format;
}
else if (dateFormat != null)
{
options.DateFormat = format;
}
format = null;
}
// Find the default style to use for this cell based on the row and column styles
var cellStyle = _sheet.Rows[row].Style ?? _sheet.Columns[col].Style;
// Clone the style so it does not modify the entire row or column
cellStyle = cellStyle?.Clone();
// Set up cell formatting for this cell
UpdateStyle(ref cellStyle, format, fontStyle, fontSize, fontName);
// Apply the style to this cell if defined
if (cellStyle != null)
{
_sheet[row, col].Style = cellStyle;
}
// Now write the cell contents
object value;
if (converter.AcceptsNativeType)
{
value = field;
}
else
{
value = converter.ConvertToExcel(options, field);
}
var s = value as string;
if (s != null && s.StartsWith("="))
{
// Write as a formula if it starts with an equals sign
_sheet[row, col].Value = "";
_sheet[row, col].Formula = s;
}
else
{
_sheet[row, col].Value = value;
}
}
