/// <summary>
/// 匯出Issue資料為Excel檔案
/// </summary>
/// <param name="sourceFilePath">issue來源檔案路徑檔名</param>
/// <param name="resultFilePath">匯出檔案路徑檔名</param>
public void Export(string sourceFilePath, string resultFilePath)
{
if (String.IsNullOrEmpty(sourceFilePath))
{
throw new ArgumentNullException(nameof(sourceFilePath));
}
if (!File.Exists(sourceFilePath))
{
throw new InvalidOperationException($"File not found.{sourceFilePath}");
}
Console.WriteLine("");
Console.WriteLine($"Issue檔格式: v1");
Console.WriteLine($"載入來源檔案: {sourceFilePath}");
IssueDb issueData = JsonConvert.DeserializeObject <IssueDb>(File.ReadAllText(sourceFilePath));
Console.WriteLine($"載入來源檔案完成，開始資料解析...");
if (issueData.Issues != null &&
issueData.Logs != null)
{
foreach (var issue in issueData.Issues.Where(x => x.Status == "resolved" || x.Status == "closed"))
{
var resolvedLog = issueData.Logs.Where(y => y.Issue == issue.Id && y.Field == "status" && y.ChangedTo == "resolved")
.OrderBy(y => y.CreatedOn).LastOrDefault();
if (resolvedLog != null)
{
issue.Resolveder = resolvedLog.User;
issue.ResolvedOn = resolvedLog.CreatedOn;
}
var closedLog = issueData.Logs.Where(y => y.Issue == issue.Id && y.Field == "status" && y.ChangedTo == "closed")
.OrderBy(y => y.CreatedOn).LastOrDefault();
if (closedLog != null)
{
issue.Closeder = closedLog.User;
issue.ClosedOn = closedLog.CreatedOn;
}
}
foreach (var issue in issueData.Issues.Where(x => x.Status == "invalid"))
{
issue.ResolvedOn = issueData.Logs.Where(y => y.Issue == issue.Id && y.Field == "status" && y.ChangedTo == "invalid")
.OrderBy(y => y.CreatedOn).LastOrDefault()?.CreatedOn;
}
foreach (var issue in issueData.Issues.Where(x => x.Status == "duplicate"))
{
issue.ResolvedOn = issueData.Logs.Where(y => y.Issue == issue.Id && y.Field == "status" && y.ChangedTo == "duplicate")
.OrderBy(y => y.CreatedOn).LastOrDefault()?.CreatedOn;
}
}
// ExcelDefinition
// Issue
var excelIssueColumns = new List <ExcelCellDefinition <Issue> >();
excelIssueColumns.Add("Issue編號", item => item.Id.ToString());
excelIssueColumns.Add("重要程度", item => item.Priority ?? "");
excelIssueColumns.Add("種類", item => item.Kind ?? "");
excelIssueColumns.Add("模組", item => item.Component ?? "");
excelIssueColumns.Add("標題", item => item.Title ?? "");
excelIssueColumns.Add("問題描述", item => item.Content ?? "");
excelIssueColumns.Add("起案人", item => item.Reporter ?? "");
excelIssueColumns.Add("處理人", item => item.Assignee ?? "");
excelIssueColumns.Add("狀態", item => item.Status ?? "");
excelIssueColumns.Add("QA版號", item => item.Version ?? "");
excelIssueColumns.Add("建立日期", item => item.CreatedOn.HasValue ? item.CreatedOn.Value.ToString("yyyy-MM-dd") : "");
excelIssueColumns.Add("更新日期", item => item.UpdatedOn.HasValue ? item.UpdatedOn.Value.ToString("yyyy-MM-dd") : "");
excelIssueColumns.Add("修正日期", item => item.ResolvedOn.HasValue ? item.ResolvedOn.Value.ToString("yyyy-MM-dd") : "");
excelIssueColumns.Add("結案日期", item => item.ClosedOn.HasValue ? item.ClosedOn.Value.ToString("yyyy-MM-dd") : "");
var excel = ExcelFactory.CreateExcelPackage(excelIssueColumns, issueData.Issues.OrderBy(x => x.Id), "Issue", null);
// Comment
var excelCommentColumns = new List <ExcelCellDefinition <Comment> >();
excelCommentColumns.Add("編號", item => item.Id.ToString());
excelCommentColumns.Add("Issue編號", item => item.Issue.ToString());
excelCommentColumns.Add("備註內容", item => item.Content ?? "");
excelCommentColumns.Add("人員", item => item.User ?? "");
excelCommentColumns.Add("建立日期", item => item.CreatedOn.HasValue ? item.CreatedOn.Value.ToString("yyyy-MM-dd HH:mm:ss") : "");
excelCommentColumns.Add("更新日期", item => item.UpdatedOn.HasValue ? item.UpdatedOn.Value.ToString("yyyy-MM-dd HH:mm:ss") : "");
excel = ExcelFactory.CreateExcelPackage(excelCommentColumns, issueData.Comments.OrderBy(x => x.Issue).ThenBy(x => x.CreatedOn), "Comment", excel);
Console.WriteLine($"匯出結果檔案: {resultFilePath}");
File.WriteAllBytes(resultFilePath, excel.GetAsByteArray());
Console.WriteLine($"匯出檔案完成...");
excel = null;
}