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