/// <summary> /// Loading <see cref="IEnumerable{T}"/> from specified excel file. /// /// </summary> /// <typeparam name="T">The type of the model.</typeparam> /// <param name="excelFile">The excel file.</param> /// <param name="startRow">The row to start read.</param> /// <param name="sheetIndex">Which sheet to read.</param> /// <param name="valueConverter">The cell value convert.</param> /// <returns>The <see cref="IEnumerable{T}"/> loading from excel.</returns> public static IEnumerable <T> Load <T>(string excelFile, int startRow = 1, int sheetIndex = 0, ValueConverter valueConverter = null) where T : class, new() { if (!File.Exists(excelFile)) { throw new FileNotFoundException(); } var workbook = InitializeWorkbook(excelFile); // currently, only handle sheet one (or call side using foreach to support multiple sheet) var sheet = workbook.GetSheetAt(sheetIndex); // get the writable properties var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty); bool fluentConfigEnabled = false; // get the fluent config if (Excel.Setting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig)) { fluentConfigEnabled = true; } var cellConfigs = new CellConfig[properties.Length]; for (var j = 0; j < properties.Length; j++) { var property = properties[j]; if (fluentConfigEnabled && fluentConfig.PropertyConfigs.TryGetValue(property, out var pc)) { // fluent configure first(Hight Priority) cellConfigs[j] = pc.CellConfig; } else { var attrs = property.GetCustomAttributes(typeof(ColumnAttribute), true) as ColumnAttribute[]; if (attrs != null && attrs.Length > 0) { cellConfigs[j] = attrs[0].CellConfig; } else { cellConfigs[j] = null; } } } var statistics = new List <StatisticsConfig>(); if (fluentConfigEnabled) { statistics.AddRange(fluentConfig.StatisticsConfigs); } else { var attributes = typeof(T).GetCustomAttributes(typeof(StatisticsAttribute), true) as StatisticsAttribute[]; if (attributes != null && attributes.Length > 0) { foreach (var item in attributes) { statistics.Add(item.StatisticsConfig); } } } 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; } var item = new T(); var itemIsValid = true; for (int i = 0; i < properties.Length; i++) { var prop = properties[i]; int index = i; var config = cellConfigs[i]; if (config != null) { 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); if (valueConverter != null) { value = valueConverter(row.RowNum, 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 = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; var safeValue = Convert.ChangeType(value, propType, CultureInfo.CurrentCulture); prop.SetValue(item, safeValue, null); } if (itemIsValid) { list.Add(item); } } return(list); }
/// <summary> /// Initializes a new instance of the <see cref="ColumnAttribute"/> class. /// </summary> public ColumnAttribute() { CellConfig = new CellConfig(); }
internal static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, string excelFile, string sheetName) { // 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 (Excel.Setting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig)) { fluentConfigEnabled = true; } // find out the configs var cellConfigs = new CellConfig[properties.Length]; for (var j = 0; j < properties.Length; j++) { var property = properties[j]; // get the property config if (fluentConfigEnabled && fluentConfig.PropertyConfigs.TryGetValue(property, out var pc)) { // fluent configure first(Hight Priority) cellConfigs[j] = pc.CellConfig; } else { var attrs = property.GetCustomAttributes(typeof(ColumnAttribute), true) as ColumnAttribute[]; if (attrs != null && attrs.Length > 0) { cellConfigs[j] = attrs[0].CellConfig; } else { cellConfigs[j] = null; } } } // init work book. var workbook = InitializeWorkbook(excelFile); // new sheet var sheet = workbook.CreateSheet(sheetName); // cache cell styles var cellStyles = new Dictionary <int, ICellStyle>(); // title row cell style var titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.Center; titleStyle.VerticalAlignment = VerticalAlignment.Center; titleStyle.FillPattern = FillPattern.Bricks; titleStyle.FillBackgroundColor = HSSFColor.Grey40Percent.Index; titleStyle.FillForegroundColor = HSSFColor.White.Index; 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 = cellConfigs[i]; if (config != null) { if (config.IsIgnored) { continue; } index = config.Index; } // 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); titleCell.CellStyle = titleStyle; titleCell.SetCellValue(title); } var value = property.GetValue(item, null); if (value == null) { continue; } var cell = row.CreateCell(index); if (cellStyles.TryGetValue(i, out var cellStyle)) { cell.CellStyle = cellStyle; var unwrapType = property.PropertyType.UnwrapNullableType(); if (unwrapType == typeof(bool)) { cell.SetCellValue((bool)value); } else if (unwrapType == typeof(DateTime)) { cell.SetCellValue(Convert.ToDateTime(value)); } else if (unwrapType == typeof(double)) { cell.SetCellValue(Convert.ToDouble(value)); } else if (value is IFormattable) { var fv = value as IFormattable; cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture)); } else { cell.SetCellValue(value.ToString()); } } else if (value is IFormattable) { var fv = value as IFormattable; cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture)); } else { cell.SetCellValue(value.ToString()); } } rowIndex++; } // merge cells var mergableConfigs = cellConfigs.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); 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) { var statistics = new List <StatisticsConfig>(); var filterConfigs = new List <FilterConfig>(); var freezeConfigs = new List <FreezeConfig>(); if (fluentConfigEnabled) { statistics.AddRange(fluentConfig.StatisticsConfigs); freezeConfigs.AddRange(fluentConfig.FreezeConfigs); filterConfigs.AddRange(fluentConfig.FilterConfigs); } else { var attributes = typeof(T).GetCustomAttributes(typeof(StatisticsAttribute), true) as StatisticsAttribute[]; if (attributes != null && attributes.Length > 0) { foreach (var item in attributes) { statistics.Add(item.StatisticsConfig); } } var freezes = typeof(T).GetCustomAttributes(typeof(FreezeAttribute), true) as FreezeAttribute[]; if (freezes != null && freezes.Length > 0) { foreach (var item in freezes) { freezeConfigs.Add(item.FreezeConfig); } } var filters = typeof(T).GetCustomAttributes(typeof(FilterAttribute), true) as FilterAttribute[]; if (filters != null && filters.Length > 0) { foreach (var item in filters) { filterConfigs.Add(item.FilterConfig); } } } // 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); 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 for (int i = 0; i < properties.Length; i++) { sheet.AutoSizeColumn(i); } return(workbook); }