将具有不同关系值的表转换为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屋！