private static SubProblem GetSubProblem(Excel.Worksheet source, int row)
{
string problemName;
var problemCell = source.GetCell(row, SourceConfig.ProblemNameColumn);
try
{
problemName = problemCell.Value2;
}
catch
{
throw new Exception($"문제 제목을 확인하세요. \n시트: {source.Name} \n셀: {problemCell.Address}");
}
string description;
var descriptionCell = source.GetCell(row, SourceConfig.DescriptionColumn);
try
{
description = descriptionCell.Value2;
}
catch
{
throw new Exception($"문제 설명을 확인하세요. \n시트: {source.Name} \n셀: {descriptionCell.Address}");
}
double score;
var scoreCell = source.GetCell(row, SourceConfig.ScoreColumn);
try
{
score = scoreCell.Value2;
double.Parse(score.ToString());
}
catch
{
throw new Exception($"배점을 확인하세요. 숫자로 입력하세요. \n시트: {source.Name} \n셀: {scoreCell.Address}");
}
return(new SubProblem
{
ProblemName = problemName,
Description = description,
Score = score,
Row = row,
});
}
private static List <(int Column, string UserNumber)> GetUserNumberList(Excel.Worksheet source)
{
var beginColumn = SourceConfig.BeginColumn;
var endColumn = source.GetCell(SourceConfig.BeginRow - 1, beginColumn).End[XlDirection.xlToRight].Column;
return(Enumerable.Range(beginColumn, endColumn - beginColumn + 1)
.Select(column => source.GetCell(SourceConfig.BeginRow - 1, column))
.Select(cell =>
{
var value = cell.Value2;
if (value is string valueStr)
{
return (cell.Column, valueStr);
}
else
{
return (cell.Column, ((object)value).ToString());
}
})
.ToList());
}
private static List <Problem> GetProblems(Excel.Worksheet source)
{
var beginRow = SourceConfig.BeginRow;
var endRow = source.GetCell(beginRow, 1).End[Excel.XlDirection.xlDown].Row;
return(Enumerable.Range(beginRow, endRow - beginRow + 1)
.Select(row => GetSubProblem(source, row))
.GroupBy(x => new { x.ProblemName })
.Select(x => new Problem
{
ProblemName = x.First().ProblemName,
SubProblems = x.ToList(),
})
.ToList());
}
private static UserData GetUserData(int column, string userNumber, List <Problem> problems, Excel.Worksheet source)
{
var scores = problems
.SelectMany(x => x.SubProblems)
.Select(x => new { SubProblem = x, Cell = source.GetCell(x.Row, column) })
.Select(x =>
{
var value = x.Cell.Value2;
if (value is double valueDouble)
{
return(new
{
x.SubProblem,
Score = valueDouble,
Cell = x.Cell,
Error = false,
ErrorMessage = string.Empty,
});
}
else
{
return(new
{
x.SubProblem,
Score = 0.0,
Cell = x.Cell,
Error = true,
ErrorMessage = $"점수가 숫자로 변환되지 않습니다. 시트: {source.Name}, 셀: {x.Cell.Address}",
});
}
})
.ToList();
var errorMessage = scores.Where(x => x.Error)
.Select(x => x.ErrorMessage)
.StringJoin(Environment.NewLine);
if (scores.Any(x => x.Error))
{
throw new Exception(errorMessage);
}
return(new UserData
{
Number = userNumber,
Scores = scores.Select(x => (x.SubProblem, x.Score, x.Cell)).ToList(),
});
static void Main(string[] args)
{
Random r = new Random();
Excel.Workbook myWb = MyExcel.GetWorkBook(System.AppDomain.CurrentDomain.BaseDirectory + "demo.xlsx");
try
{
MessageFilter.Register();
#region 写excel
myWb.Application.ScreenUpdating = true; //控制excel刷新暂停，false之后等到true才刷新，不暂停将会导致写一个格刷新一次
//这里写入excel
Excel.Worksheet ws = (Excel.Worksheet)myWb.Sheets["Sheet1"]; //想要搞的workbook的名字
string sample = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD";
string[] samples = sample.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in samples)
{
for (int i = 1; i < 10; i++)
{
ws.GetCell(item, i).Value = r.Next(1, 100);
}
}
myWb.Save();
myWb.Application.ScreenUpdating = true;
#endregion
MessageFilter.Revoke();
}
catch (Exception ex)
{
throw ex;
}
finally
{
myWb.Application.Quit();
}
}
