private void CreateSourceButton_Click(object sender, EventArgs e)
{
if (ExcelApp.TryGetWorkbook(x => x.Name == TargetWorkbook.Text, out var targetWorkbook))
{
var target = new TargetWorkbook(targetWorkbook);
var subProblems = target.Worksheet
.SelectMany(x => x.SubProblems.Select(sub => (x.ProblemName, sub.Desc, sub.Max)))
.Distinct()
.ToList();
var wb = ExcelApp.Workbooks.Add();
if (wb.TryGetWorksheet(x => true, out var sheet))
{
var row = 0;
foreach (var sub in subProblems)
{
row++;
sheet.Cells[row, 1].Value2 = sub.ProblemName;
sheet.Cells[row, 2].Value2 = sub.Desc;
sheet.Cells[row, 3].Value2 = sub.Max;
}
}
}
}
public static void Execute(Excel.Worksheet sourceWorksheet, Excel.Workbook targetWorkbook, bool execute)
{
var source = new SourceWorksheet(sourceWorksheet);
var target = new TargetWorkbook(targetWorkbook);
foreach (var targetSheet in target.Worksheet)
{
targetSheet.Sheet.Activate();
var targetLeftTopCell = targetSheet.Sheet.Range[TargetConfig.ScoreLeftTopAddress];
var userDataList = targetSheet.UserData;
var minRow = userDataList.Min(x => x.Cell.Row);
var maxRow = userDataList.Max(x => x.Cell.Row);
var rowCount = maxRow - minRow + 1;
var subProblemCount = targetSheet.SubProblems.Count;
var scoreArray = new object[rowCount, subProblemCount];
foreach (var userData in userDataList)
{
var userScoreData = source.Users.First(x => x.Number == userData.Number);
int column = 0;
foreach (var subProblem in targetSheet.SubProblems)
{
var targetCell = targetSheet.Sheet.GetCell(userData.Cell.Row, subProblem.Column);
var userScores = userScoreData.Scores
.Where(x => x.SubProblem.ProblemName == targetSheet.ProblemName)
.Where(x => x.SubProblem.Description.StartsWith(subProblem.Desc))
.ToList();
if (userScores.Empty())
{
throw new Exception($"{targetCell.Address} 심사위원 채점표에서 \"{targetCell.Offset[-2, 0].Value2}\"를 찾을 수 없습니다.");
}
var userScore = userScores.First().UserScore;
// targetCell.Value2 = userScore.ToString();
scoreArray[userData.Cell.Row - minRow, column] = userScore.ToString();
column++;
}
}
if (execute)
{
var minCell = targetLeftTopCell;
var maxCell = minCell.Offset[rowCount - 1, subProblemCount - 1];
targetSheet.Sheet.Range[minCell, maxCell].Value2 = scoreArray;
}
}
}
public static bool Validate(Excel.Worksheet sourceWorksheet, Excel.Workbook targetWorkbook)
{
var source = new SourceWorksheet(sourceWorksheet);
//File.WriteAllText(@"D:\source.json", JsonConvert.SerializeObject(source.Problems, Formatting.Indented), Encoding.UTF8);
//File.WriteAllText(@"D:\score.json", JsonConvert.SerializeObject(source.Users, Formatting.Indented), Encoding.UTF8);
var target = new TargetWorkbook(targetWorkbook);
//File.WriteAllText(@"D:\target.json", JsonConvert.SerializeObject(target, Formatting.Indented), Encoding.UTF8);
// 문제 이름이 모두 같은가?
// TargetWorkbook 만들 때 체크하게 되어 있음.
//var nullProblems = target.Worksheet
// .Where(x => x.Problem == null)
// .ToList();
//if (nullProblems.Any())
//{
// MessageBox.Show($"문제 이름이 다릅니다.");
// return false;
//}
// 3. 선수 수가 맞는가? 양쪽이 정확히 같아야 함.
var targetUserList = target.Worksheet
.GroupBy(x => x.ProblemName)
.Select(x => x.SelectMany(e => e.UserData.Select(u => u.Number)).OrderBy(e => e).ToList())
.First()
.Distinct()
.ToList();
var sourceUserList = source.Users.Select(x => x.Number).Distinct().OrderBy(x => x).ToList();
if (sourceUserList.Count != targetUserList.Count())
{
MessageBox.Show($"선수 수가 다릅니다.\n심사위원 채점표: {sourceUserList.Count} 명\n공단채점표: {targetUserList.Count} 명");
return(false);
}
var zipped = sourceUserList.Zip(targetUserList, (a, b) => new { Source = a, Target = b })
.Where(x => x.Source != x.Target)
.ToList();
if (zipped.Any())
{
var sourceOnly = sourceUserList
.Where(x => targetUserList.Empty(e => x == e))
.ToList();
var targetOnly = targetUserList
.Where(x => sourceUserList.Empty(e => x == e))
.ToList();
MessageBox.Show($"양쪽 선수 목록이 다릅니다.\n심사위원 채점표: {sourceOnly.StringJoin("\n")}\n공단채점표: {targetOnly.StringJoin("\n")}");
return(false);
}
// 1. 문제수가 맞는가?
if (source.Problems.Count != target.Problems.Count)
{
MessageBox.Show($"문제 수가 다릅니다.\n심사위원채점표: {source.Problems.Count} 문제\n공단채점표: {target.Problems.Count} 문제");
return(false);
}
// 2. 세부 항목 수가 맞는가?
var sourceSubProblemsName = source.Problems
.SelectMany(x => x.SubProblems.Select(sub => (sub.ProblemName, sub.Description)))
.Distinct()
.ToList();
var targetSubProblemsName = target.Worksheet
.SelectMany(x => x.SubProblems.Select(sub => (x.ProblemName, sub.Desc)))
.Distinct()
.ToList();
List <string> missingSubProblems = new List <string>();
foreach (var targetSubProblem in targetSubProblemsName)
{
var t = targetSubProblem;
if (sourceSubProblemsName.Empty(x => x.ProblemName == t.ProblemName && x.Description.StartsWith(t.Desc)))
{
missingSubProblems.Add($"문제: {t.ProblemName}, 항목: {t.Desc}");
//MessageBox.Show($"심사위원 채점표에서 \"{t.Desc}\"를 찾을 수 없습니다.");
//return false;
}
}
if (missingSubProblems.Any())
{
var missing = missingSubProblems.Select(x => $"\"{x}\"")
.StringJoin(Environment.NewLine);
MessageBox.Show($"심사위원 채점표에서 \n{missing}\n 를 찾을 수 없습니다.");
return(false);
}
foreach (var problem in targetSubProblemsName.Select(x => x.ProblemName))
{
var s = sourceSubProblemsName.Where(x => x.ProblemName == problem).ToList();
var t = targetSubProblemsName.Where(x => x.ProblemName == problem).ToList();
if (s.Count != t.Count)
{
MessageBox.Show($"세부항목 수가 다릅니다.\n문제: {problem}\n심사위원채점표: {s.Count} 항목\n공단채점표: {t.Count} 항목");
return(false);
}
}
// 4. 점수 배점이 같은가?
var problems = target.Worksheet
.GroupBy(x => x.ProblemName)
.Select(x => x.First())
.ToList();
foreach (var targetProblem in problems)
{
var sourceSubProblems = source.Problems.First(x => x.ProblemName == targetProblem.ProblemName).SubProblems;
var targetSubProblems = targetProblem.SubProblems;
var subZip = targetProblem.SubProblems
.Select(tSub => new
{
Target = tSub,
Source = sourceSubProblems.First(s => s.Description == tSub.Desc),
})
.ToList();
var subDiffList = subZip
.Where(x => Math.Round(x.Source.Score, 3) != Math.Round(x.Target.Max, 3))
.ToList();
if (subDiffList.Any())
{
var errorMessage = subDiffList
.Select((s, i) => $"{i + 1}. 항목: {targetProblem.ProblemName} - {s.Source.Description}\n심사위원채점표: {s.Source.Score}\n공단채점표: {s.Target.Max}")
.StringJoin(Environment.NewLine);
MessageBox.Show($"세부항목 배점이 다릅니다.\n{errorMessage}");
return(false);
}
}
// 5. 득점이 점수 구간에 있는가?
foreach (var user in source.Users)
{
foreach (var score in user.Scores)
{
if (score.SubProblem.Score < score.UserScore)
{
sourceWorksheet.Activate();
score.Cell.Activate();
MessageBox.Show($"선수의 득점이 범위를 초과하였습니다.\n채점번호: {user.Number}\n항목: {score.SubProblem.ProblemName} - {score.SubProblem.Description}\n범위: {score.SubProblem.Score}\n득점: {score.UserScore}");
return(false);
}
}
}
// 실행해보자
Execute(sourceWorksheet, targetWorkbook, execute: false);
return(true);
}