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