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