从 IQueryable 对象将列和行插入电子表格
如何从 Queryable 对象插入数据的列和行？下面列出了我到目前为止所拥有的。似乎我可以将列名放入电子表格中，但我不确定如何使用我编写的方法插入值。
private IQueryable<ShippingRequest> GetRecordsFromDatabase()
{
var CurrentUserId = (int)Session["UserId"];
var results = db.ShippingRequests
.Where(r => r.UserId == CurrentUserId);
return results;
}
//Create the WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("FedEx Rates");
//get columns of table
var columnNames = typeof(ShippingRequest).GetProperties()
.Select(x => x.Name)
.ToArray();
int i = 0;
//Adding column name to worksheet
foreach (var col in columnNames)
{
i++;
worksheet.Cells[1, i].Value = col;
}
//Adding records to worksheet
int j;
for (i = 0; i<columnNames.Length; i++)
{
foreach (var item in db)
{
worksheet.Cells[i + 2, j + 1].Value = ???; //Not sure how to get this value
}
}
呼如林浏览 46回答 1
1回答
-
温温酱
所以你获取了一些数据作为一个序列，你希望这个序列的每个元素都作为一行添加到你的表中。这些列都是 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);
随时随地看视频慕课网APP
相关分类
C#