将许多表转换为Excel列 [英] Convert many table to excel columns
问题描述
我创建了用于文章审阅的网络应用.
I create web app for article review.
我有一个名为Article的表，每个Article都有一些ArticleReview:
I have table named Article and each Article have some ArticleReview:
文章
ArticleId
ArticleTitle
NumberOfComment
NumberOfView
...
ICollection<ArticleReview> Reviews
管理员可以添加许多用户(我称他们为JueryUser)并将其发送给他们进行审阅，我使用名为ArticlePoint的表来添加/删除可以审阅每篇文章的用户:
Admin can add many user (I call them JueryUser) and send article to them for review, I use table named ArticlePoint for adding/removing User's that can review each Article:
ArticlePoint
public int ArticlePointId { get; set; }
public DateTime CreateOn { get; set; }
public string Id { get; set; } // Id of User that review Article
public ApplicationUser JuryUser { get; set; }
public string UserId { get; set; } // Id of User that write Article
public int ArticleId { get; set; }
public string JuryReview { get; set; }
当陪审团用户评论文章时，他/她给文章提供了一些要点，这些要点是基于Admin提供的问题，此评论的内容存储在ArticleReview表中.
When Jury user review article he/she gave article some point, this points is based on question's that provided by Admin, this review's are stored in ArticleReview table.
文章评论
ArticleReviewId
ReviewPoint
ArticleId
ReviewerId
正如我在中说的那样我想从每个评审团给予文章的观点出发，获得出色的出口.
As I said in my previous question I want to get excel export from summery of point that gave to articles by each juery.
我使用下面的代码生成我的excel文件:
I use this below code to generate my excel file:
public static class ExcelExportHelper
{
public static string ExcelContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
public static DataTable ListToDataTable<T>(List<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable dataTable = new DataTable();
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor property = properties[i];
dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
}
object[] values = new object[properties.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = properties[i].GetValue(item);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
workSheet.View.RightToLeft = true;
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return result;
}
public static byte[] ExportExcel<T>(List<T> data, string Heading = "", bool showSlno = false, params string[] ColumnsToTake)
{
return ExportExcel(ListToDataTable<T>(data), Heading, showSlno, ColumnsToTake);
}
}
并在我的操作方法中使用以下代码:
and use this code in my action method:
public ActionResult ExportToExcel()
{
byte[] filecontent;
try
{
var juryLists = from user in db.Users
where user.Roles.Any(r => r.RoleId == "IdOfRole")
select user;
var articles = db.Articles.Include(r=>r.ArticleReview).Where(r2=>r2.ArticleReviews.Any()).ToList();
List<string> cmnList = new List<string>();
cmnList.Add("Article Title");
cmnList.Add("Point Avarage");
var juryListNames = juryLists.OrderBy(x=>x.Id).Select(x => "JuryPoints : " + x.FullName).ToList();
cmnList.AddRange(juryListNames);
string[] columns = cmnList.ToArray();
var heading = $"Results";
var dt = new DataTable();
foreach (var column in columns)
{
dt.Columns.Add(column, typeof(string));
}
foreach (var item in articles)
{
var _obj = new object[] { item.ArticleTitle, item.ArticleReviews.OrderBy(x=>x.JuryUserId).Select(x=>x.ArticlePoint).Average() }.Concat(
item.ArticleReviews.OrderBy(x => x.JuryUserId).GroupBy(x => x.JuryUserId).Select(x => x.Average(y => y.ReviewPoint))
.Cast<object>()).ToArray();
dt.Rows.Add(_obj);
}
filecontent = ExcelExportHelper.ExportExcel(dt, heading, true, columns);
}
catch (Exception e)
{
return RedirectToAction("Details",new {id= id });
}
return File(filecontent, ExcelExportHelper.ExcelContentType, "Results.xlsx");
}
我的问题:
我为我的excel创建了标头，但问题是某些陪审团的用户还没有指出文章，而当我生成excel时，另一个陪审团的观点就放在了另一个陪审团的列中.
例如:送给5个陪审团1,2,5的一篇文章指向该文章，但在第3陪审团专栏的第5陪审团出口之后
我该如何解决?
My Problem:
I create header for my excel but problem is some jury user wont gave point to article yet and when I generate excel, point of another jury placed in column of another one.
ex: one article sent to 5 jury jury 1,2 and 5 give point to article but after export point of 5th jury place in 3th jury column
how can I resolve this?
推荐答案
要包括那些尚未添加或预期由评论者填写的评论，您可以在评审团列表和计算每个进行评审的陪审团的平均值.如果陪审团缺少评论，则left outer join将导致空值平均值.因此，将填充一个简单的占位符，例如连字符，空字符串或零.为了实现所有这些，您需要在操作方法中更改第二个for loop，如下所示:
In order to include those reviews that are not yet added or expected to be filled by the reviewer, you can use a simple left outer join between the jury list and calculated averages for each of the juries who gave reviews. If reviews are missing for a jury, the left outer join will result an empty value average side. Therefore, a simple placeholder like a hyphen,an empty string, or a zero is populated. To achieve all these, you need to change the second for loop in the action method as below:
foreach (var item in articles)
{
//Prepare list of averages for this article by grouping by individual jury
//Resulting listcontains objects with two attributes, i.e. JuryUserId and Avg
//JuryUserId is needed here for later join with user(jury) list
var averages = item.ArticleReviews.GroupBy(x => x.JuryUserId).Select(x => new { JuryUserId = x.Key, Avg = x.Average(y => y.ReviewPoint) });
//Left outer join of juries with list of averages
//If reviews are not found or yet to be added, a hyphen will be populated as a placeholder
var joinedAvg = from jury in juryLists
join avg in averages on jury.Id equals avg.JuryUserId into j
from result in j.DefaultIfEmpty()
orderby jury.Id
select (result?.Avg.ToString() ?? "-"); //Use any placeholder here
var _obj = new object[] { item.ArticleTitle, item.ArticleReviews.Select(x => x.ReviewPoint).Average() }.Concat(joinedAvg.Cast<object>()).ToArray();
dt.Rows.Add(_obj);
}
这篇关于将许多表转换为Excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
