将具有不同关系值的表转换为Excel列 [英] Convert a table with different relational values to excel columns
问题描述
我有这些表:
类别
CategoryId分类标题...ICollection< Article>文章
每个类别可以包含几篇文章:
文章
ArticleId文章标题评论数视点数...ICollection< ArticleReview>评论
每篇文章都有一些用户评论:
文章评论
ArticleReviewId评论点ArticleId评论者编号
我正在尝试使用
我的问题之一是Reviewer Point列是动态更改的,
对于一篇文章,可能有3列(如上图),但在另一篇文章中可能有4或5个Reviewer Point.
Edit2
我忘了说每个文章都有一个问题,每个问题都有一个审稿人回答,所以如果我们有3个问题,而有2个审稿人,则文章有6个ArticleReview,我应该为每个审稿人获取ArticleReview的平均值并将其放在单个单元格中
为简单起见,我假设您使用的是以下简化模型,并将描述解决方案.您可以轻松地将其适应您的模型:
公共类文章{公共字符串Title {get;放;}公开的DateTime日期{放;}公共列表<评论>评论{得到;放;}}公共课评{public int Points {获取;放;}}
现在,我们将根据输入数据生成以下输出,其中具有动态的审阅者列数:
解决方案
创建一个足以将 List< Article>
转换为 DataTable
的函数就足够了.要创建这样的 DataTable
,请为 Article
的每个属性添加一个新列.然后找到 Reviews
列表的最大数量并添加该列数.然后在一个循环中,为每个 Article
(包括其 Review
列表)创建一个对象数组,并将其添加到 DataTable
中.显然,您还可以对字段进行计算.
这是函数:
public DataTable GetData(List< Article>列表){var dt = new DataTable();dt.Columns.Add("Title",typeof(string));dt.Columns.Add("Date",typeof(DateTime));var max = list.Max(x => x.Reviews.Count());对于(int i = 0; i< max; i ++)dt.Columns.Add($"Reviewer {i +1} Points",typeof(int));foreach(列表中的可变项)dt.Rows.Add(new object [] {item.Title,item.Date} .Concat(item.Reviews.Select(x => x.Points).Cast< object>()).ToArray());返回dt}
测试数据
这是我的测试数据:
var list = new List< Article>{新文章(){标题=第1条",日期=新的DateTime(2018,1,1),评论=新列表<评论>{新的Review(){Points = 10},},},新文章(){标题=第2条",日期=新的DateTime(2018,1,2),评论=新列表<评论>{新Review(){Points = 10},新Review(){Points = 9},新Review(){Points = 8},},},新文章(){标题=第3条",日期=新的DateTime(2018,1,3),评论=新列表<评论>{新的Review(){Points = 9},},},};
I have these tables:
Category
CategoryId
CategoryTitle
...
ICollection<Article> Articles
Each category can have several articles:
Article
ArticleId
ArticleTitle
NumberOfComment
NumberOfView
...
ICollection<ArticleReview> Reviews
And each article has several reviews by some user:
ArticleReview
ArticleReviewId
ReviewPoint
ArticleId
ReviewerId
i am trying to export excel report using EPPlus package
Here is my ExcelExport
class :
public class excelExport
{
public string ArticleTitle { get; set; }
public int NumberOfComment { get; set; }
public int NumberOfReviews { get; set; }
public List<ResearchReviewReport> Reviews { get; set; }
}
public class ArticleReviewReport
{
public string Reviewer { get; set; }
public int ReviewPoint { get; set; }
}
Note: Since the number of reviews for an article is different, I use one-to-many relationship, but in final result all of them should be flatten in single row. Now I create new class that does not belong to database and I pass this class to ExcelPackage
class to generate xlsx as output :
ExcelExport
ArticleTitle
Reviewer1Point
Reviewer2Point
............
ReviewerNPoint
ReviewersAvaragePoint
NumberOfComment
NumberOfView
How can I populate the ExcelExport
class by using another 3 classes?
Edit
here is my expected excel output
One of my problems is Reviewer Point column is dynamically changed,
for one article may be there 3 column (like upper image) but in another may be 4 or 5 Reviewer Point.
Edit2
i forgot to say there is some question for each article and Reviewer answer to each question ,so if we have 3 question and there is 2 Reviewer,article hase 6 ArticleReview and i should get average of ArticleReview for each Reviewer and put it in single cell
To keep things simple, I assume you are using the following simplified models and will describe the solution. You can easily adapt it to your models:
public class Article
{
public string Title { get; set; }
public DateTime Date { get; set; }
public List<Review> Reviews { get; set; }
}
public class Review
{
public int Points { get; set; }
}
Now we are going to generate the following output, having dynamic number of reviewer columns, depending to the input data:
Solution
It's enough to create a function to convert a List<Article>
to a DataTable
. To create such DataTable
, for each property of the Article
, add a new column. Then find the maximum count of Reviews
list and add that number of columns. Then in a loop, for each Article
including its Review
list, create an array of objects and add to the DataTable
. Obviously you also can perform calculation on fields.
Here is the function:
public DataTable GetData(List<Article> list)
{
var dt = new DataTable();
dt.Columns.Add("Title", typeof(string));
dt.Columns.Add("Date", typeof(DateTime));
var max = list.Max(x => x.Reviews.Count());
for (int i = 0; i < max; i++)
dt.Columns.Add($"Reviewer {i + 1} Points", typeof(int));
foreach (var item in list)
dt.Rows.Add(new object[] { item.Title, item.Date }.Concat(
item.Reviews.Select(x => x.Points).Cast<object>()).ToArray());
return dt;
}
Test Data
Here is my test data:
var list = new List<Article>
{
new Article(){
Title = "Article 1", Date = new DateTime(2018,1,1),
Reviews = new List<Review> {
new Review(){Points=10},
},
},
new Article(){
Title = "Article 2", Date = new DateTime(2018,1,2),
Reviews = new List<Review> {
new Review(){Points=10}, new Review(){Points=9}, new Review(){Points=8},
},
},
new Article(){
Title = "Article 3", Date = new DateTime(2018,1,3),
Reviews = new List<Review> {
new Review(){Points=9},
},
},
};
这篇关于将具有不同关系值的表转换为Excel列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!