/// <summary> /// Creates an instance of the <see cref="iClickerQuizPts.EPPlusManager"/> /// class /// </summary> /// <param name="wbkFullNm">The full name (i.e., including path) of the /// Excel file containing the raw iClicker quiz points data.</param> /// <exception cref="iClickerQuizPts.AppExceptions.ReadingExternalWbkException"> /// The file is either a *.csv and *.xlx files (or a different kind /// of file entirely).</exception> /// <exception cref="iClickerQuizPts.AppExceptions.InalidAppConfigItemException"> /// An entry in the <code>appSettings</code> section in the <code>App.config</code> /// file could not be found. /// </exception> public EPPlusManager(string wbkFullNm) { if (wbkFullNm.EndsWith("xlsx")) { _wbkFullNm = wbkFullNm; try { ReadAppConfigDataIntoFields(); } catch (InalidAppConfigItemException ex) { throw ex; } } else { ReadingExternalWbkException ex = new ReadingExternalWbkException(); ex.ImportResult = ImportResult.NotExcel; 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 } } } }