/// <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; } }