/// <summary> /// Obtains the session number, quiz date, and maximum points /// from a raw data file data column header. /// </summary> /// <param name="hdr">A column header from a raw quiz data file.</param> /// <param name="sessionNo">An out parameter to capture the session number.</param> /// <param name="qzDate">An out parameter to capture the date of the quiz.</param> /// <param name="maxPts">An out parameter to capture the maximum points for the quiz.</param> /// <exception cref="iClickerQuizPts.AppExceptions.InvalidQuizDataHeaderException">The header /// text from the raw data file is not in the expected format.</exception> private void ExtractSessionDataFromColumnHeader(string hdr, out string sessionNo, out DateTime qzDate, out byte maxPts) { try { hdr = hdr.Replace("Session ", string.Empty); hdr = hdr.Replace("Total ", string.Empty); // (char)91 = opening bracket (i.e., "[")... hdr = hdr.Replace(((char)91).ToString(), string.Empty); // (char)93 = closing bracket (i.e., "]")... hdr = hdr.Replace(((char)93).ToString(), string.Empty); hdr = hdr.Trim(); // Hdr will now be something like: "40 5/2/16 2.00"... int perSpace = hdr.IndexOf((char)46); // ...(char)46 = period (i.e., ".") hdr = hdr.Substring(0, perSpace); // ...remove trailing decimals int posSpace1 = hdr.IndexOf((char)32, 1); // ...(char)32 = space (i.e., " ") int posSpace2 = hdr.IndexOf((char)32, posSpace1 + 1); // Now extract our values... sessionNo = hdr.Substring(0, posSpace1); if (sessionNo.Length == 1) { sessionNo = string.Format($"0{sessionNo}"); // ...add leading zero, if necessary } qzDate = DateTime.Parse(hdr.Substring(posSpace1 + 1, posSpace2 - posSpace1 - 1)); maxPts = Byte.Parse(hdr.Substring(posSpace2 + 1)); } catch (Exception e) { InvalidQuizDataHeaderException ex = new InvalidQuizDataHeaderException( "Failure in ExtractSessionDataFromColumnHeader method.", e); ex.HeaderText = hdr; throw ex; } }
/// <summary> /// Utilizes EPPlus to create two <see cref="System.Data.DataTable"/>s. One /// comprises all the data from the quiz data worksheet, the other comprises /// Session number information. /// <exception cref="iClickerQuizPts.AppExceptions.ReadingExternalWbkException"> /// There are any number of problems with the format and/or /// structure of the workbook and/or the worksheet containing the quiz /// results data. The exact nature of the problem is specified in /// the exception's message property.</exception> /// <exception cref="iClickerQuizPts.AppExceptions.InvalidQuizDataHeaderException"> /// A data column header in a raw iClicker data file is not in the expected /// format. As such, there are problems extracting any of: /// <list type="bullet"> /// <item>session number</item> /// <item>quiz/session date</item> /// <item>maximum points for the quiz</item> /// </list> /// </exception> /// </summary> public virtual void CreateRawQuizDataTable() { _dtAllScores = new DataTable("RawQuizData"); using (ExcelPackage p = new ExcelPackage()) { using (FileStream stream = new FileStream(_wbkFullNm, FileMode.Open)) { // Read the workbook and it's 1st (& presumably only) worksheet... p.Load(stream); ExcelWorksheet ws = p.Workbook.Worksheets[1]; /* * * TRAP FOR PROBLEMS IN THE WORKSHEET... * */ if (ws == null) { ReadingExternalWbkException ex = new ReadingExternalWbkException("No worksheets in the workbook."); ex.ImportResult = ImportResult.WrongFormat; throw ex; } if (!ws.Cells["A1"].Value.ToString().Trim().Equals("Student ID") || !ws.Cells["B1"].Value.ToString().Trim().Equals("Student Name") || !ws.Cells["C3"].Value.ToString().Trim().EndsWith("TOTAL")) { string msg = "Incorrect column headings for columns A, B, and/or C"; ReadingExternalWbkException ex = new ReadingExternalWbkException(msg); ex.ImportResult = ImportResult.WrongFormat; throw ex; } /* * * GATHER WORKSHEET DIMENSIONS, TRAPPING FOR MISSING DATA... * */ // Find last col in wsh (header row should always have values)... while (_lastCol > 1) { ExcelRange c = ws.Cells[1, _lastCol]; if (c.Value != null) { break; } else { _lastCol--; } } // Trap for no data columns... if (_lastCol <= _firstDataCol) { string msg = "There are no columns of quiz data."; ReadingExternalWbkException ex = new ReadingExternalWbkException(msg); ex.ImportResult = ImportResult.WrongFormat; throw ex; } // Find last row of data in wsh. Student ID column should always have an entry... while (_lastRow > 1) { ExcelRange c = ws.Cells[_lastRow, _studentEmailCol]; if (c.Value != null) { break; } else { _lastRow--; } } // Trap for no data rows... if (_lastRow == 1) { string msg = "There are no rows of data."; ReadingExternalWbkException ex = new ReadingExternalWbkException(msg); ex.ImportResult = ImportResult.WrongFormat; throw ex; } /* * * CREATE COLUMNS & ADD TO DATATABLES... * */ // ALL SCORES DATA TABLE: // Create a primary key column... DataColumn colDataID = new DataColumn(_colNmID, typeof(int)); colDataID.AllowDBNull = false; colDataID.AutoIncrement = true; colDataID.ReadOnly = true; colDataID.Unique = true; _dtAllScores.Columns.Add(colDataID); // Create Student ID (email) column... DataColumn colStEml = new DataColumn(_colNmEmail, typeof(string)); colStEml.AllowDBNull = false; colStEml.ReadOnly = true; colStEml.Unique = true; _dtAllScores.Columns.Add(colStEml); // Create student Last Name column... DataColumn colLn = new DataColumn(_colNmLastNm, typeof(string)); colLn.AllowDBNull = false; _dtAllScores.Columns.Add(colLn); // Create & add student First Name column... DataColumn colFn = new DataColumn(_colNmFirstNm, typeof(string)); _dtAllScores.Columns.Add(colFn); // Create & add columns for quiz data... for (int i = _firstDataCol; i <= _lastCol; i++) { string rawColHdr = ws.Cells[1, i].Value.ToString().Trim(); DataColumn col = new DataColumn(rawColHdr, typeof(byte)); try { Session s = new Session(rawColHdr); if (!_blistSssnsAll.Contains(s)) { _blistSssnsAll.Add(s); } else // ...dupe entries { string msg = string.Format($"Multiple instances of Session {s.SessionNo} are in {_wbkFullNm}."); ReadingExternalWbkException ex = new ReadingExternalWbkException(msg); ex.ImportResult = ImportResult.WrongFormat; throw ex; } // Set extended properties of column, then add column... col.ExtendedProperties["Session Nmbr"] = s.SessionNo; col.ExtendedProperties["QuizDate"] = s.QuizDate.ToShortDateString(); col.ExtendedProperties["MaxQuizPts"] = s.MaxPts.ToString().PadLeft(2, '0'); col.ExtendedProperties["ComboBoxLbl"] = string.Format($"Session {s.SessionNo} - {s.QuizDate}"); _dtAllScores.Columns.Add(col); } catch { InvalidQuizDataHeaderException ex = new InvalidQuizDataHeaderException(); ex.HeaderText = rawColHdr; throw ex; } } /* * * POPULATE ROWS WITH DATA THEN ADD TO DATATABLE... * */ string studentFullNm; // Loop through each data row... for (int rowNo = 2; rowNo <= _lastRow; rowNo++) { DataRow r = _dtAllScores.NewRow(); // Populate student name & email fields... r[_colNmEmail] = ws.Cells[rowNo, _studentEmailCol].Value.ToString().Trim(); studentFullNm = ws.Cells[rowNo, _studentNameCol].Value.ToString(); r[_colNmLastNm] = _hdrParser.ExtractLastNameFromFullName(studentFullNm); r[_colNmFirstNm] = _hdrParser.ExtractFirstNameFromFullName(studentFullNm); // Loop through each quiz data column... for (int colNo = _firstDataCol; colNo <= _lastCol; colNo++) { // Populate quiz data fields... string colNm = ws.Cells[1, colNo].Value.ToString().Trim(); r[colNm] = ws.Cells[rowNo, colNo].Value; } _dtAllScores.Rows.Add(r); // ...add row to dataTable } } } }