public PropertyConfiguration() { CellConfig = new CellConfig(); }
internal static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, string excelFile, string sheetName) { var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty); bool fluentConfigEnabled = false; IFluentConfiguration fluentConfig; if (ExcelHelper.Setting.FluentConfigs.TryGetValue(typeof(T), out fluentConfig)) { fluentConfigEnabled = true; } var cellConfigs = new CellConfig[properties.Length]; for (var j = 0; j < properties.Length; j++) { var property = properties[j]; PropertyConfiguration pc; if (fluentConfigEnabled && fluentConfig.PropertyConfigs.TryGetValue(property, out pc)) { 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 workbook = InitializeWorkbook(excelFile); var sheet = workbook.CreateSheet(sheetName); var cellStyles = new Dictionary <int, ICellStyle>(); var titleStyle = workbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.Center; titleStyle.VerticalAlignment = VerticalAlignment.Center; titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.BorderBottom = BorderStyle.Thin; titleStyle.BorderLeft = BorderStyle.Thin; titleStyle.BorderRight = BorderStyle.Thin; titleStyle.FillForegroundColor = HSSFColor.Lime.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; } if (rowIndex == 1) { 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) { 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; } ICell cell = row.CreateCell(index, CellType.Numeric); ICellStyle cellStyle; if (cellStyles.TryGetValue(i, out 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++; } int recordIndex = 0; var mergableConfigs = cellConfigs.Where(c => c != null && c.AllowMerge).ToList(); if (mergableConfigs.Any()) { var vStyle = workbook.CreateCellStyle(); vStyle.VerticalAlignment = VerticalAlignment.Center; foreach (var config in mergableConfigs) { recordIndex++; 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) { int cellIndex = sheet.GetRow(row - rowspan).Cells.Count - 1; if (config.Index <= cellIndex) { 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; } } if (rowspan > 1) { int cellIndex = sheet.GetRow(row - rowspan).Cells.Count - 1; if (config.Index <= cellIndex) { 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); } } } 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++; } foreach (var freeze in freezeConfigs) { sheet.CreateFreezePane(freeze.ColSplit, freeze.RowSplit, freeze.LeftMostColumn, freeze.TopRow); } foreach (var filter in filterConfigs) { sheet.SetAutoFilter(new CellRangeAddress(filter.FirstRow, filter.LastRow ?? rowIndex, filter.FirstCol, filter.LastCol)); } } for (int i = 0; i < properties.Length; i++) { //sheet.AutoSizeColumn(i); } return(workbook); }
/// <summary> /// <see cref="ColumnAttribute"/> /// </summary> public ColumnAttribute() { CellConfig = new CellConfig(); }
/// <summary> /// 加载Excel文件数据到IEnumerable <see cref="IEnumerable{T}"/> /// /// </summary> public static IEnumerable <T> Load <T>(string excelFile, Stream stream = null, int startRow = 1, int sheetIndex = 0, ValueConverter valueConverter = null) where T : class, new() { if (stream == null && !File.Exists(excelFile)) { throw new FileNotFoundException(); } var workbook = stream != null?InitializeWorkbook(stream, excelFile) : InitializeWorkbook(excelFile); var sheet = workbook.GetSheetAt(sheetIndex); var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty); bool fluentConfigEnabled = false; if (Setting.FluentConfigs.TryGetValue(typeof(T), out IFluentConfiguration 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 PropertyConfiguration pc)) { cellConfigs[j] = pc.CellConfig; } else { #pragma warning disable IDE0019 // 使用模式匹配 ColumnAttribute[] attrs = property.GetCustomAttributes(typeof(ColumnAttribute), true) as ColumnAttribute[]; #pragma warning restore IDE0019 // 使用模式匹配 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 { #pragma warning disable IDE0019 // 使用模式匹配 StatisticsAttribute[] attributes = typeof(T).GetCustomAttributes(typeof(StatisticsAttribute), true) as StatisticsAttribute[]; #pragma warning restore IDE0019 // 使用模式匹配 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; 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) { if (!config.IsIgnored) { index = config.Index; 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; config.Index = index; break; } } } } if (index < 0) { throw new ApplicationException("未设置Excel起始索引index,或是autoIndex"); } } var value = row.GetCellValue(index); if (valueConverter != null) { value = valueConverter(row.RowNum, index, value); } if (value == null) { continue; } 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; } } 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); }