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(); } }