1 回答
TA贡献1752条经验 获得超4个赞
所以你获取了一些数据作为一个序列,你希望这个序列的每个元素都作为一行添加到你的表中。这些列都是 ShippingRequests 的可读公共属性。
让我们创建一个通用解决方案,它将添加任何列序列并显示某个类的任何对象序列。
通常,列的名称不必与所有属性的名称一一对应。有时您只想显示某些属性。有时您想要创建不同的列名或显示不同的值。也许您不想将数据显示到 Excel 工作表,而是显示到不同类型的表格?
从某个表类定义列的可重用类可能类似于:
class Column<TSource>
{
public int Index {get; set;}
public string Name {get; set;}
public Func<TSource, object> PropertyValueSelector {get; set;}
public object GetValue(TSource source)
{
return this.PropertyValueSelector(source);
}
... // possible other properties, like: IsVisible, IsSortable, DisplayFormat?
}
显然,您想为包含 ShippingRequest 的每个公共属性的 ShippingRequests 创建一系列列。列的名称是属性的标识符。索引并不重要。
以下函数将创建您的列序列:
public static IEnumerable<Column<TSource>> CreateColumns<TSource>()
where TSource : class
{
return typeof(TSource).GetProperties()
.Where(property => property.CanRead) // they must be at least readable
.Select( (propertyInfo, index) => new Column<TSource>
{
Index = index,
Name = propertyInfo.Name,
PropertyValueSelector = source => propertyInfo.GetValue(source);
});
}
一旦我们获得数据和列,我们就可以填写您的工作表:
void Fill<TSource>(this ExcelWorkSheet workSheet,
IEnumerable<Column<TSource>> columns,
IEnumerable<TSource> sourceData)
{
// TODO: clear worksheet?
//Add column names to worksheet
foreach (var column in columns)
{
worksheet.Cells[1, column.Index].Value = column.Name;
}
// add the source data
int nextRowIndex = 2;
foreach (var rowData in sourceData)
{
AddRow(workSheet, nextRowIndex, columns, rowData);
++nextRowIndex;
}
}
void AddRow<TSource> AddRow<TSource>(this ExcelWorkSheet workSheet,
int rowIndex,
IEnumerable<Column<TSource>> columns,
TSource rowData)
{
foreach (var column in columns)
{
var value = column.GetValue(rowData);
worksheet.Cells[rowIndex, column.Index].Value = value;
}
}
现在你已经有了这个,你的代码将很容易:
var workSheet = ...
var columns = ...
var data = ...
worksheet.Fill(columns, data);
在你的情况下:
var worksheet = excelPackage.Workbook.Worksheets.Add("FedEx Rates");
var columns = CreateColumns<ShippingRequest>().ToList();
var shippingRequests = GetShippingRequests();
worksheet.Fill(columns, shippingRequests);
// Bam! Done!
好处是您可以使用代码将来自任何类的数据填充到工作表中。
例如,我有一个学生班级,我想显示 100 名最年轻学生的一些列。
// I only want to show the following columns of students:
var studentColumns = new Column<Student>
{
new Column {Index = 1, Name = "Id", PropertyValueSelector = student => student.Id },
new Column {Index = 3, Name = "Birthday", PropertyValueSelector = student => student.Id }
new Column {Index = 2, Name = "Student Name", PropertyValueSelector = student =>
String.Format("{0} {1} {2}", student.FirstName,
student.MiddleName,
student.FamilyName} },
};
// I only want 100 youngest students:
var studentsToDisplay = GetStudents()
.OrderByDescending(student => student.BirthDay)
.Take(100)
.ToList();
// filling the worksheet is only two lines:
var worksheet = excelPackage.Workbook.Worksheets.Add("Young Students");
worksheet.Fill(studentColumns, studentsToDisplay);
- 1 回答
- 0 关注
- 45 浏览
添加回答
举报