/// <summary> /// 实体化一个工具类根据类型,初始化spreadsheetcell自定义属性 /// </summary> /// <param name="type"></param> public ConvertToCellValueHelper(Type type) { TPorperties.Clear(); TPorperties.AddRange(GetCellAttributes(type)); Helpers.Clear(); foreach (var proInfo in TPorperties) { if (proInfo.PropertyType == typeof(string)) { continue; } if (proInfo.PropertyType.GetInterfaces().Any(x => x.Name == typeof(IEnumerable <>).Name)) { var helper = new ConvertToCellValueHelper(proInfo.PropertyType.GetGenericArguments()[0]); Helpers.Add(proInfo, helper); } else if (proInfo.PropertyType.IsClass) { var helper = new ConvertToCellValueHelper(proInfo.PropertyType); Helpers.Add(proInfo, helper); } } }
/// <summary> /// 填充数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sheet"></param> /// <param name="range"></param> /// <param name="dataSource"></param> /// <param name="isver"></param> /// <returns></returns> public static Range BindDataSource <T>( this Worksheet sheet, Range range, IEnumerable <T> dataSource, bool isver = false, bool isCpyStyle = false) { if (dataSource.Count() == 0) { return(range); } int topRowIndex = range.TopRowIndex; int leftColumnIndex = range.LeftColumnIndex; int rightColumnIndex = range.LeftColumnIndex; int bottomRowIndex = range.TopRowIndex; var helper = new ConvertToCellValueHelper(typeof(T)); var lstCellValues = dataSource.Select(w => helper.GetCellValue(w)).ToList(); if (isver == false) { var rowIndex = range.TopRowIndex; var styleRange = sheet.Range.FromLTRB( leftColumnIndex, topRowIndex, leftColumnIndex + lstCellValues.FirstOrDefault().Count - 1, topRowIndex); if (isCpyStyle == false) { styleRange.SetDefaultStyle(); } foreach (var rowItem in lstCellValues) { var nowRange = sheet.Range.FromLTRB(leftColumnIndex, rowIndex, leftColumnIndex + rowItem.Count - 1, rowIndex); nowRange.CopyFrom(styleRange, PasteSpecial.Formats); var columnIndex = range.LeftColumnIndex; foreach (var valueItem in rowItem) { if (sheet[rowIndex, columnIndex].HasFormula == false) { sheet[rowIndex, columnIndex].Value = valueItem; } columnIndex++; bottomRowIndex = rowIndex; } rowIndex++; rightColumnIndex = columnIndex - 1; } } else { var columnIndex = range.LeftColumnIndex; var styleRange = sheet.Range.FromLTRB( leftColumnIndex, topRowIndex, leftColumnIndex, topRowIndex + lstCellValues.FirstOrDefault().Count - 1); if (isCpyStyle == false) { styleRange.SetDefaultStyle(); } foreach (var rowItem in lstCellValues) { var nowRange = sheet.Range.FromLTRB(columnIndex, topRowIndex, columnIndex, topRowIndex + rowItem.Count - 1); nowRange.CopyFrom(styleRange, PasteSpecial.Formats); var rowIndex = range.TopRowIndex; foreach (var valueItem in rowItem) { if (sheet[rowIndex, columnIndex].HasFormula == false) { sheet[rowIndex, columnIndex].Value = valueItem; } rowIndex++; } columnIndex++; bottomRowIndex = rowIndex - 1; } rightColumnIndex = columnIndex - 1; } return(sheet.Range.FromLTRB( leftColumnIndex, topRowIndex, rightColumnIndex, bottomRowIndex)); }