private void SearchName(string qry) { grd1.Children.Clear(); mBoard.vExaminee.Clear(); SqlCommand cmd = new SqlCommand(); cmd.Connection = GetDBConnection(); if (cmd.Connection == null) { return; } cmd.CommandText = qry; SqlDataReader reader = null; try { reader = cmd.ExecuteReader(); } catch (SqlException ex) { System.Windows.MessageBox.Show(ex.ToString(), "SQL cmd error"); return; } if (reader != null && reader.HasRows) { while (reader.Read()) { Examinee nee = new Examinee(); int i = 1; nee.mIndex = reader.GetInt16(++i); nee.mName = reader.GetString(++i); nee.mBirthdate = reader.GetDateTime(++i); nee.mBirthplace = reader.GetString(++i); nee.mGrade1 = (float)reader.GetDouble(++i); mBoard.vExaminee.Add(nee); } GridShowExaminee(grd1); } }
public string Load(string filepath) { vExaminee.Clear(); string fn = Path.GetFileNameWithoutExtension(filepath); //todo check fn for length if (DT.To_(fn.Substring(0, 10), DT._, out mDate)) { return("File name must represent test date in format yyyy-MM-dd."); } if ((int)TestFormat.IT_1 < mDate.Year) { mFormat = TestFormat.IT_2; } else { mFormat = TestFormat.IT_1; } string msg = "ok"; // Open the spreadsheet document for read-only access. using (SpreadsheetDocument document = SpreadsheetDocument.Open(filepath, false)) { // Retrieve a reference to the workbook part. WorkbookPart wbPart = document.WorkbookPart; // Find the sheet with the supplied name, and then use that // Sheet object to retrieve a reference to the first worksheet. Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().FirstOrDefault(); // Throw an exception if there is no sheet. if (theSheet == null) { return("No sheet"); } if (!TestType.TryParse(theSheet.Name.ToString().Substring(0, 4), out mType)) { return("Sheet name must represent test type."); } // Retrieve a reference to the worksheet part. WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); // Use its Worksheet property to get a reference to the cell // whose address matches the address you supplied. IEnumerable <Row> vRow = wsPart.Worksheet.Descendants <Row>(); if (vRow.Count() < 1) { return("No row."); } //birthdate or given name bool bName = true; string v = LoadExamineeAttr(vRow.First().ChildElements.ElementAt(2) as Cell, wbPart); foreach (char c in v.ToCharArray()) { if ('0' <= c && c <= '9') { bName = false; break; } } //determine the number of cells of each row int nCellOfRow = 5; if (bName) { ++nCellOfRow; } if (mType < TestType.IT_A) { nCellOfRow += 2; } else if (mFormat == TestFormat.IT_2) { ++nCellOfRow; } int li = -1; foreach (Row r in vRow) { ++li; IEnumerable <Cell> cells = r.ChildElements.OfType <Cell>(); List <Cell> vCell = cells.ToList(); if (vCell.Count < nCellOfRow) { msg = "Line " + li + ": The number of columns is " + vCell.Count + " < " + nCellOfRow; break; } Examinee nee = new Examinee(); int i = -1; //index string value = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); if (!nee.TryParseIdx(value)) { msg = "Line " + li + ": Attr " + i + " is error"; break; } //name nee.mName = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); //given name if (bName) { nee.mName += LoadExamineeAttr(vCell.ElementAt(++i), wbPart); } //birthdate value = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); int val; if (int.TryParse(value, out val)) //birthdate is stored as number { if (val < 2018) //birthyear only { nee.mBirthdate = new DateTime(val, 1, 1, 8, 8, 8); } else { nee.mBirthdate = DateTime.FromOADate(val); } } else if (!nee.TryParseBirdate(value))//birthdate is stored as string { msg = "Line " + li + ": Attr " + i + " is error"; break; } //birthplace nee.mBirthplace = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); //grade 1 float grade; value = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); if (!float.TryParse(value, out grade)) { if (value.ToLower().Trim() == "v") { grade = -1.0f; } else { msg = "Line " + li + ": Attr " + i + " is error"; break; } } nee.mGrade1 = grade; if (mType < TestType.IT_A || mFormat == TestFormat.IT_2) { //grade 2 value = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); if (!float.TryParse(value, out grade)) { if (value.ToLower().Trim() == "v") { grade = -1.0f; } else { msg = "Line " + li + ": Attr " + i + " is error"; break; } } nee.mGrade2 = grade; } if (mType < TestType.IT_A) { //grade 3 value = LoadExamineeAttr(vCell.ElementAt(++i), wbPart); if (!float.TryParse(value, out grade)) { if (value.ToLower().Trim() == "v") { grade = -1.0f; } else { msg = "Line " + li + ": Attr " + i + " is error"; break; } } nee.mGrade3 = grade; } vExaminee.Add(nee); } } return(msg); }