Example #1
0
        /// <summary>
        /// Gets the property configuration by the specified property expression for the specified <typeparamref name="TModel"/> and its <typeparamref name="TProperty"/>.
        /// </summary>
        /// <returns>The <see cref="PropertyConfiguration"/>.</returns>
        /// <param name="propertyExpression">The property expression.</param>
        /// <typeparam name="TProperty">The type of parameter.</typeparam>
        public PropertyConfiguration Property <TProperty>(Expression <Func <TModel, TProperty> > propertyExpression)
        {
            var propertyInfo = GetPropertyInfo(propertyExpression);

            if (!_propertyConfigurations.TryGetValue(propertyInfo.Name, out var pc))
            {
                pc = new PropertyConfiguration();
                _propertyConfigurations[propertyInfo.Name] = pc;
            }

            return(pc);
        }
Example #2
0
        /// <summary>
        /// Gets the property configuration by the specified property info for the specified <typeparamref name="TModel"/>.
        /// </summary>
        /// <param name="propertyInfo">The property information.</param>
        /// <returns>The <see cref="PropertyConfiguration"/>.</returns>
        public PropertyConfiguration Property(PropertyInfo propertyInfo)
        {
            if (propertyInfo.DeclaringType != typeof(TModel))
            {
                throw new InvalidOperationException($"Property does not belong to {nameof(TModel)}");
            }

            if (!_propertyConfigurations.TryGetValue(propertyInfo.Name, out var pc))
            {
                pc = new PropertyConfiguration();
                _propertyConfigurations[propertyInfo.Name] = pc;
            }

            return(pc);
        }
Example #3
0
        /// <summary>
        /// Configures the ignored properties for the specified <typeparamref name="TModel"/>.
        /// </summary>
        /// <param name="propertyExpressions">The a range of the property expression.</param>
        /// <returns>The <see cref="FluentConfiguration{TModel}"/>.</returns>
        public FluentConfiguration <TModel> HasIgnoredProperties(params Expression <Func <TModel, object> >[] propertyExpressions)
        {
            foreach (var propertyExpression in propertyExpressions)
            {
                var propertyInfo = GetPropertyInfo(propertyExpression);

                if (!_propertyConfigurations.TryGetValue(propertyInfo.Name, out var pc))
                {
                    pc = new PropertyConfiguration();
                    _propertyConfigurations[propertyInfo.Name] = pc;
                }

                pc.IsIgnored(true, true);
            }

            return(this);
        }
Example #4
0
        /// <summary>
        /// Adjust the auto index value for all the has auto index configuration properties of specified <typeparamref name="TModel"/>.
        /// </summary>
        /// <returns>The <see cref="FluentConfiguration{TModel}"/>.</returns>
        public FluentConfiguration <TModel> AdjustAutoIndex()
        {
            var index      = 0;
            var properties = typeof(TModel).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);

            foreach (var property in properties)
            {
                if (!_propertyConfigurations.TryGetValue(property.Name, out var pc))
                {
                    if (_propertyConfigurations.Values.Any(c => c.Index == index))
                    {
                        // the default index had been used, so calculate a new one for it.
                        _propertyConfigurations[property.Name] = pc = new PropertyConfiguration
                        {
                            Title     = property.Name,
                            AutoIndex = true,
                            Index     = -1
                        };
                    }
                    else
                    {
                        // the default index not be used, 'I' will use it.
                        index++;

                        continue;
                    }
                }

                if (pc.AutoIndex && !pc.IsExportIgnored && pc.Index == -1)
                {
                    while (_propertyConfigurations.Values.Any(c => c.Index == index))
                    {
                        index++;
                    }

                    pc.HasExcelIndex(index++);
                }
            }

            return(this);
        }
        public static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, IWorkbook workbook, ExcelSetting excelSetting, string sheetName = "sheet0", bool overwrite = false)
            where T : class
        {
            if (null == source)
            {
                throw new ArgumentNullException(nameof(source));
            }
            if (null == workbook)
            {
                throw new ArgumentNullException(nameof(workbook));
            }
            if (string.IsNullOrWhiteSpace(sheetName))
            {
                throw new ArgumentException($"sheet name cannot be null or whitespace", nameof(sheetName));
            }

            // TODO: can static properties or only instance properties?
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);

            bool fluentConfigEnabled = false;

            // get the fluent config
            if (excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig))
            {
                fluentConfigEnabled = true;

                // adjust the auto index.
                (fluentConfig as FluentConfiguration <T>)?.AdjustAutoIndex();
            }

            // find out the configurations
            var propertyConfigurations = new PropertyConfiguration[properties.Length];

            for (var i = 0; i < properties.Length; i++)
            {
                var property = properties[i];

                // get the property config
                if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc))
                {
                    propertyConfigurations[i] = pc;
                }
                else
                {
                    propertyConfigurations[i] = null;
                }
            }

            // TODO check the sheet's name is valid
            var sheet = workbook.GetSheet(sheetName);

            if (sheet == null)
            {
                sheet = workbook.CreateSheet(sheetName);
            }
            else
            {
                // doesn't override the exist sheet if not required
                if (!overwrite)
                {
                    sheet = workbook.CreateSheet();
                }
            }

            // cache cell styles
            var cellStyles = new Dictionary <int, ICellStyle>();

            // title row cell style
            ICellStyle titleStyle = null;

            if (excelSetting.TitleCellStyleApplier != null)
            {
                titleStyle = workbook.CreateCellStyle();
                var font = workbook.CreateFont();
                excelSetting.TitleCellStyleApplier(titleStyle, font);
            }

            var titleRow = sheet.CreateRow(0);
            var rowIndex = 1;

            foreach (var item in source)
            {
                var row = sheet.CreateRow(rowIndex);
                for (var i = 0; i < properties.Length; i++)
                {
                    var property = properties[i];

                    int index  = i;
                    var config = propertyConfigurations[i];
                    if (config != null)
                    {
                        if (config.IsExportIgnored)
                        {
                            continue;
                        }

                        index = config.Index;

                        if (index < 0)
                        {
                            throw new Exception($"The excel cell index value cannot be less then '0' for the property: {property.Name}, see HasExcelIndex(int index) methods for more informations.");
                        }
                    }

                    // this is the first time.
                    if (rowIndex == 1)
                    {
                        // if not title, using property name as title.
                        var title = property.Name;
                        if (!string.IsNullOrEmpty(config?.Title))
                        {
                            title = config.Title;
                        }

                        if (!string.IsNullOrEmpty(config?.Formatter))
                        {
                            try
                            {
                                var style = workbook.CreateCellStyle();

                                var dataFormat = workbook.CreateDataFormat();

                                style.DataFormat = dataFormat.GetFormat(config.Formatter);

                                cellStyles[i] = style;
                            }
                            catch (Exception ex)
                            {
                                // the formatter isn't excel supported formatter
                                System.Diagnostics.Debug.WriteLine(ex.ToString());
                            }
                        }

                        var titleCell = titleRow.CreateCell(index);
                        if (titleStyle != null)
                        {
                            titleCell.CellStyle = titleStyle;
                        }
                        titleCell.SetCellValue(title);
                    }

                    var unwrapType = property.PropertyType.UnwrapNullableType();

                    var value = property.GetValue(item, null);

                    // give a chance to the value converter even though value is null.
                    if (config?.CellValueConverter != null)
                    {
                        value = config.CellValueConverter(rowIndex, index, value);
                        if (value == null)
                        {
                            continue;
                        }

                        unwrapType = value.GetType().UnwrapNullableType();
                    }

                    if (value == null)
                    {
                        continue;
                    }

                    var cell = row.CreateCell(index);
                    if (cellStyles.TryGetValue(i, out var cellStyle))
                    {
                        cell.CellStyle = cellStyle;
                    }
                    else if (!string.IsNullOrEmpty(config?.Formatter) && value is IFormattable fv)
                    {
                        // the formatter isn't excel supported formatter, but it's a C# formatter.
                        // The result is the Excel cell data type become String.
                        cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture));

                        continue;
                    }

                    if (unwrapType == typeof(bool))
                    {
                        cell.SetCellValue((bool)value);
                    }
                    else if (unwrapType == typeof(DateTime))
                    {
                        cell.SetCellValue(Convert.ToDateTime(value));
                    }
                    else if (unwrapType.IsInteger() ||
                             unwrapType == typeof(decimal) ||
                             unwrapType == typeof(double) ||
                             unwrapType == typeof(float))
                    {
                        cell.SetCellValue(Convert.ToDouble(value));
                    }
                    else
                    {
                        cell.SetCellValue(value.ToString());
                    }
                }

                rowIndex++;
            }

            // merge cells
            var mergableConfigs = propertyConfigurations.Where(c => c != null && c.AllowMerge).ToList();

            if (mergableConfigs.Any())
            {
                // merge cell style
                var vStyle = workbook.CreateCellStyle();
                vStyle.VerticalAlignment = VerticalAlignment.Center;

                foreach (var config in mergableConfigs)
                {
                    object previous = null;
                    int    rowspan = 0, row = 1;
                    for (row = 1; row < rowIndex; row++)
                    {
                        var value = sheet.GetRow(row).GetCellValue(config.Index, _formulaEvaluator);
                        if (object.Equals(previous, value) && value != null)
                        {
                            rowspan++;
                        }
                        else
                        {
                            if (rowspan > 1)
                            {
                                sheet.GetRow(row - rowspan).Cells[config.Index].CellStyle = vStyle;
                                sheet.AddMergedRegion(new CellRangeAddress(row - rowspan, row - 1, config.Index, config.Index));
                            }
                            rowspan  = 1;
                            previous = value;
                        }
                    }

                    // in what case? -> all rows need to be merged
                    if (rowspan > 1)
                    {
                        sheet.GetRow(row - rowspan).Cells[config.Index].CellStyle = vStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(row - rowspan, row - 1, config.Index, config.Index));
                    }
                }
            }

            if (rowIndex > 1 && fluentConfigEnabled)
            {
                var statistics    = fluentConfig.StatisticsConfigurations;
                var filterConfigs = fluentConfig.FilterConfigurations;
                var freezeConfigs = fluentConfig.FreezeConfigurations;

                // statistics row
                foreach (var item in statistics)
                {
                    var lastRow = sheet.CreateRow(rowIndex);
                    var cell    = lastRow.CreateCell(0);
                    cell.SetCellValue(item.Name);
                    foreach (var column in item.Columns)
                    {
                        cell = lastRow.CreateCell(column);

                        // set the same cell style
                        cell.CellStyle = sheet.GetRow(rowIndex - 1)?.GetCell(column)?.CellStyle;

                        // set the cell formula
                        cell.CellFormula = $"{item.Formula}({GetCellPosition(1, column)}:{GetCellPosition(rowIndex - 1, column)})";
                    }

                    rowIndex++;
                }

                // set the freeze
                foreach (var freeze in freezeConfigs)
                {
                    sheet.CreateFreezePane(freeze.ColSplit, freeze.RowSplit, freeze.LeftMostColumn, freeze.TopRow);
                }

                // set the auto filter
                foreach (var filter in filterConfigs)
                {
                    sheet.SetAutoFilter(new CellRangeAddress(filter.FirstRow, filter.LastRow ?? rowIndex, filter.FirstCol, filter.LastCol));
                }
            }

            // autosize the all columns
            if (excelSetting.AutoSizeColumnsEnabled)
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }

            return(workbook);
        }
Example #6
0
        public static IEnumerable <T> Load <T>(ISheet sheet, IFormulaEvaluator formulaEvaluator, ExcelSetting excelSetting, int startRow = 1) where T : class, new()
        {
            if (null == sheet)
            {
                throw new ArgumentNullException(nameof(sheet));
            }

            // get the writable properties
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);

            bool fluentConfigEnabled = false;

            // get the fluent config
            if (excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig))
            {
                fluentConfigEnabled = true;
            }

            var propertyConfigurations = new PropertyConfiguration[properties.Length];

            for (var j = 0; j < properties.Length; j++)
            {
                var property = properties[j];
                if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc))
                {
                    // fluent configure first(Hight Priority)
                    propertyConfigurations[j] = pc;
                }
                else
                {
                    propertyConfigurations[j] = null;
                }
            }

            var statistics = new List <StatisticsConfiguration>();

            if (fluentConfigEnabled)
            {
                statistics.AddRange(fluentConfig.StatisticsConfigurations);
            }

            var list = new List <T>();
            int idx  = 0;

            IRow headerRow = null;

            // get the physical rows
            var rows = sheet.GetRowEnumerator();

            while (rows.MoveNext())
            {
                var row = rows.Current as IRow;

                if (idx == 0)
                {
                    headerRow = row;
                }
                idx++;

                if (row.RowNum < startRow)
                {
                    continue;
                }

                // ignore whitespace rows if requested
                if (true == fluentConfig?.IgnoreWhitespaceRows)
                {
                    if (row.Cells.All(x =>
                                      CellType.Blank == x.CellType ||
                                      (CellType.String == x.CellType && string.IsNullOrWhiteSpace(x.StringCellValue))
                                      ))
                    {
                        continue;
                    }
                }

                var item        = new T();
                var itemIsValid = true;
                for (int i = 0; i < properties.Length; i++)
                {
                    var prop = properties[i];

                    int index  = i;
                    var config = propertyConfigurations[i];
                    if (config != null)
                    {
                        if (config.IsImportIgnored)
                        {
                            continue;
                        }

                        index = config.Index;

                        // Try to autodiscover index from title and cache
                        if (index < 0 && config.AutoIndex && !string.IsNullOrEmpty(config.Title))
                        {
                            foreach (var cell in headerRow.Cells)
                            {
                                if (!string.IsNullOrEmpty(cell.StringCellValue))
                                {
                                    if (cell.StringCellValue.Equals(config.Title, StringComparison.InvariantCultureIgnoreCase))
                                    {
                                        index = cell.ColumnIndex;

                                        // cache
                                        config.Index = index;

                                        break;
                                    }
                                }
                            }
                        }

                        // check again
                        if (index < 0)
                        {
                            throw new ApplicationException("Please set the 'index' or 'autoIndex' by fluent api or attributes");
                        }
                    }

                    var value = row.GetCellValue(index, formulaEvaluator);

                    // give a chance to the cell value validator
                    if (null != config?.CellValueValidator)
                    {
                        var validationResult = config.CellValueValidator(row.RowNum - 1, config.Index, value);
                        if (false == validationResult)
                        {
                            if (fluentConfig.SkipInvalidRows)
                            {
                                itemIsValid = false;
                                break;
                            }

                            throw new ArgumentException($"Validation of cell value at row {row.RowNum}, column {config.Title}({config.Index + 1}) failed! Value: [{value}]");
                        }
                    }

                    // give a chance to the value converter.
                    if (config?.CellValueConverter != null)
                    {
                        value = config.CellValueConverter(row.RowNum - 1, config.Index, value);
                    }

                    if (value == null)
                    {
                        continue;
                    }

                    // check whether is statics row
                    if (idx > startRow + 1 && index == 0
                        &&
                        statistics.Any(s => s.Name.Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)))
                    {
                        var st      = statistics.FirstOrDefault(s => s.Name.Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
                        var formula = row.GetCellValue(st.Columns.First()).ToString();
                        if (formula.StartsWith(st.Formula, StringComparison.InvariantCultureIgnoreCase))
                        {
                            itemIsValid = false;
                            break;
                        }
                    }

                    // property type
                    var propType = prop.PropertyType.UnwrapNullableType();

                    var safeValue = Convert.ChangeType(value, propType, CultureInfo.CurrentCulture);

                    prop.SetValue(item, safeValue, null);
                }

                if (itemIsValid)
                {
                    // give a chance to the row data validator
                    if (null != fluentConfig?.RowDataValidator)
                    {
                        var validationResult = fluentConfig.RowDataValidator(row.RowNum - 1, item);
                        if (false == validationResult)
                        {
                            if (fluentConfig.SkipInvalidRows)
                            {
                                itemIsValid = false;
                                continue;
                            }

                            throw new ArgumentException($"Validation of row data at row {row.RowNum} failed!");
                        }
                    }

                    list.Add(item);
                }
            }

            return(list);
        }