/// <summary> /// Does a quick check on the validity of the spreasheet. /// </summary> /// <param name="teamInfoSpreadsheet"></param> /// <param name="cell1">Value of cell at [0,0]</param> /// <param name="minRows">Minimum number of rows</param> /// <param name="minCols">Minimum number of columns</param> /// <returns></returns> private static bool quickSpreadsheetCheck(SimpleSpreadsheetReader teamInfoSpreadsheet, string cell1, int minRows, int minCols) { minRows = Math.Max(minRows, 2); minCols = Math.Max(minCols, 1); if (teamInfoSpreadsheet.getNumRows() < minRows || teamInfoSpreadsheet.getNumCols() < minCols) { Trace.WriteLine("Spreadsheet dimensions too small."); return(false); } String[] cells = teamInfoSpreadsheet.getRowCells(0, 0, 0); String c1 = stripEndBlanks(cells[0]); if (!c1.Equals(cell1)) { Trace.WriteLine("Cell 1 (signature) is incorrect."); return(false); } return(true); }
/// <summary> /// Get's the team info (id, name) associated with the machine, if found. Returns null otherwise. /// Throws ArgumentException if there is an issue with the spreadsheet data. /// </summary> /// <param name="teamInfoSpreadsheet"></param> /// <returns></returns> public static TeamInfo getTeamInfoForMachine(SimpleSpreadsheetReader teamInfoSpreadsheet) { String machineName = normalizeMachineName(Environment.MachineName); TeamInfo info = null; // We expect the order of info in the spreadsheet to be: // TeamID, TeamName, MachineName, ... // Also, the 1st row is expected to be properties and the 2nd row to be the header. if (!quickSpreadsheetCheck(teamInfoSpreadsheet, "PTD", 2, 3)) { String message = "Does not appear to be a valid team info spreadsheet - signature missing or invalid"; ErrorReport.logError(message); throw new ArgumentException(message); } // Start at 1 to skip header row. for (int r = 1; r < teamInfoSpreadsheet.getNumRows(); r++) { String[] row = teamInfoSpreadsheet.getRowCells(r, 0, 2); String machineName_r = normalizeMachineName(row[2]); if (machineName.Equals(machineName_r)) { // Found it! String teamId = stripEndBlanks(row[0]); String teamName = stripEndBlanks(row[1]); // We expect team ID to be T followed by a digit sequence. if (!Regex.IsMatch(teamId, "^T[0-9]+$")) { String message = String.Format("Team Info: matched Team ID [{0}] doesn't appear to be a valid team ID (row={1})", teamId, r + 1); ErrorReport.logError(message); throw new ArgumentException(message); } info = new TeamInfo(teamId, teamName); break; } } return(info); }
/// <summary> /// Adds all puzzles in the specified spreadsheet. Individual puzzles may not /// be added for a variety of reasons including: /// - Duplicate Puzzle ID (only first one gets added) /// - Empty answer field. /// - Invalid REGEX field in answer or hints. /// Errors are reported as Trace output. /// TODO: Have a systematic way to report skipped puzzles. /// </summary> /// <param name="sr"></param> private void addPuzzles(SimpleSpreadsheetReader sr) { // We expect that the first row contains the signature ("POD") followed by "version:1.0" followed by // additional properties (which we ignore for now) const int HEADER_ROWS = 2; const int MIN_COLS = 3; const String FILE_SIGNATURE = "POD"; const String PROP_ENCRYPTED = "encrypted"; int sheet = 0; bool encrypted = false; // whether answers and hints are encrypted or not. int numRows = sr.getNumRows(sheet); int numCols = sr.getNumCols(sheet); if (numRows < HEADER_ROWS || numCols < MIN_COLS) { ErrorReport.logError("Puzzle data spreadsheet is too small!"); throw new ArgumentException(); } String[] propertyRow = sr.getRowCells(0, 0, numCols - 1, sheet); String[] header = sr.getRowCells(1, 0, numCols - 1, sheet); // We expect the first property cell to be POD (all caps) if (!Utils.stripEndBlanks(propertyRow[0]).Equals(FILE_SIGNATURE)) { ErrorReport.logError("Puzzle data spreadsheet has an invalid/missing signature."); throw new ArgumentException(); } // Read rest of properties readProperties(propertyRow); // Check if answer keys are encrypted. if (properties.ContainsKey(PROP_ENCRYPTED)) { encrypted = true; } this.sourceIsEncrypted = encrypted; // We expect the first header cell to be "id" String headerId = Utils.stripEndBlanks(header[0]); if (!headerId.Equals(SPREADSHEET_LABEL_ID, StringComparison.CurrentCultureIgnoreCase)) { ErrorReport.logError("Puzzle data spreadsheet does not have the ID field."); throw new ArgumentException(); } int startRow = HEADER_ROWS; // First row of puzzle data int startCol = 0; // First col of puzzle data //int puzzleCount = numRows - HEADER_ROWS; // could be zero; it's valid to have 0 puzzles. for (int i = startRow; i < numRows; i++) { String[] sRow = sr.getRowCells(i, startCol, numCols - 1, sheet); const String REGEX_ID = @"^[0-9][0-9][0-9]$"; // For now, IDs must be 3-digit numbers. String id = Utils.stripBlanks(sRow[0]); if (!Regex.IsMatch(id, REGEX_ID)) { Trace.WriteLine(String.Format("Skipping row {0}: invalid ID", i)); continue; } // We got the ID, if needed decrypt remaining fields after Name if (encrypted) { decryptCells(id, sRow, 2, numCols - 1); // 2 == skip Id and Name. False == descrypt } // Now let's get the remaining columns. First, get the first two: Name and Answer. String name = Utils.stripEndBlanks(sRow[1]); String answer = Utils.stripEndBlanks(sRow[2]); // Neither should be blank. if (name.Equals("") || answer.Equals("")) { Trace.WriteLine(String.Format("Skipping row {0}: blank Name or Answer", i)); continue; } PuzzleResponse pAnswerResponse = buildPuzzleResponse(answer, PuzzleResponse.ResponseType.Correct); if (pAnswerResponse == null) { Trace.WriteLine(String.Format("Skipping row {0}: Invalid Answer", i)); continue; } PuzzleInfo pi = new PuzzleInfo(id, name); pi.addResponse(pAnswerResponse); // Add hints, if any... for (int j = 3; j < sRow.Length; j++) { String field = Utils.stripEndBlanks(sRow[j]); if (field.Length > 0) { PuzzleResponse pr = buildPuzzleResponse(field, PuzzleResponse.ResponseType.Incorrect); if (pr == null) { Trace.WriteLine(String.Format("Ignoring hint {0} on row {1}: Invalid hint content", j, i)); continue; } pi.addResponse(pr); } } try { puzzles.Add(id, pi); puzzleIDs.Add(id); Trace.WriteLine(String.Format("Adding row {0}: Puzzle ID {1}, Answer {2}", i, id, answer)); } catch (ArgumentException) { Trace.WriteLine(String.Format("Ignoring row {0}: Duplicate ID {1}", i, id)); } } }