private void button1_Click(object sender, System.EventArgs e)
{
string str_Sql;
useExcel = true;
int i = 0, j = 0;
excelApp = new Excel.ApplicationClass();
Excel.Workbook excelBook = excelApp.Workbooks.Add(1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//设置表头
String str_SchoolName = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
str_Sql = "Select * from Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
string errorstring = conn.Fill(str_Sql);
excelSheet.Cells[1, 1] = str_SchoolName + " 共有教师 " + conn.ds.Tables[0].Rows.Count.ToString() + " 个";
excelSheet.Cells[2, 1] = "学校代码";
excelSheet.Cells[2, 2] = "学校名称";
excelSheet.Cells[2, 3] = "学历";
excelSheet.Cells[2, 4] = "人数";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 4]).Font.Bold = true;
str_Sql = "Select * from View_Teacher_Statistics WHERE View_Teacher_Statistics.学校代码='" + comboBox_School.SelectedValue.ToString() + "'";
errorstring = conn.Fill(str_Sql);
DataGrid1.SetDataBinding(conn.ds, "TableIn");
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
excelApp.Visible = true;
//excelApp.Quit();
}
catch
{
throw new Exception("Excel error");
}
}
//生成Excel文件,以便打印
private void button1_Click(object sender, System.EventArgs e)
{
useExcel = true;
int i = 0, j = 0;
excelApp = new Excel.ApplicationClass();
Excel.Workbook excelBook = excelApp.Workbooks.Add(1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//设置表头
excelSheet.Cells[1, 1] = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
excelSheet.Cells[2, 1] = "班级代码";
excelSheet.Cells[2, 2] = "班级类别";
excelSheet.Cells[2, 3] = "班级名";
excelSheet.Cells[2, 4] = "班主任";
excelSheet.Cells[2, 5] = "学生人数";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 5]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 5]).Font.Bold = true;
string str_Sql = "SELECT View_Class.Class_ID AS 班级代码,View_Class.Class_Type_Name AS 班级类别,View_Class.Class_Name AS 班级名,View_Class.TeacherInCharge AS 班主任,Student_Count AS 学生人数 FROM View_Class WHERE View_Class.School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
string errorstring = conn.Fill(str_Sql);
DataGrid1.SetDataBinding(conn.ds, "TableIn");
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
excelApp.Visible = true;
//excelApp.Quit();
}
catch
{
throw new Exception("Excel error");
}
}
//根据当前选中的单元格,根据当前行的情况设定当前选中的学校的原有信息,此时修改按钮有效
private void DataGrid1_CurrentCellChanged(object sender, System.EventArgs e)
{
DataGrid1.Select(DataGrid1.CurrentRowIndex);
textBox_School_ID.Text = DataGrid1[DataGrid1.CurrentRowIndex, 0].ToString();
textBox2_School_Name.Text = conn.School_IDtoWhat(textBox_School_ID.Text, "School_Name");
comboBox1_School_Type.SelectedValue = int.Parse(conn.School_IDtoWhat(textBox_School_ID.Text, "School_Type_ID"));
textBox_Schoolmaster.Text = conn.School_IDtoWhat(textBox_School_ID.Text, "Schoolmaster");
textBox_School_Tel.Text = conn.School_IDtoWhat(textBox_School_ID.Text, "School_Tel");
textBox_School_Zip.Text = conn.School_IDtoWhat(textBox_School_ID.Text, "School_Zip");
textBox_School_Address.Text = conn.School_IDtoWhat(textBox_School_ID.Text, "School_Address");
button1.Enabled = true;
}
public BrowseStudent()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
conn.BindComboBox("SELECT QuXian_Code,QuXian_Name FROM QuXian", comboBox_QuXian, "QuXian_Code", "QuXian_Name");
conn.BindComboBox("SELECT School_Type_ID,School_Type_Name FROM School_Type", comboBox_School_Type, "School_Type_ID", "School_Type_Name");
string str_Sql, errorstring;
str_Sql = "Select * from Class";
if (conn.GetRowCount(str_Sql) == 0)
{
MessageBox.Show("请先输入班级信息！", "警告！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
//绑定下拉列表显示数据库中所有的学校
conn.BindComboBox("Select * FROM School WHERE School_Type_ID=" + comboBox_School_Type.SelectedValue + " AND School_ID Like '____" + comboBox_QuXian.SelectedValue + "%' Order By School_Type_ID,School_ID", comboBox_School, "School_ID", "School_Name");
if (comboBox_School.Items.Count == 0)
{
return;
}
else
{
//根据选择的学校绑定下拉列表显示该学校对应的所有班级类型
conn.BindComboBox("Select * FROM Class_Type WHERE School_Type_ID_Class_Belong=" + conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Type_ID"), comboBox_Class_Type, "Class_Type_ID", "Class_Type_Name");
//根据选择的学校和选择的班级类型绑定下拉列表显示该学校的这种班级类型对应的班级
conn.BindComboBox("Select * FROM Class WHERE Class_Type_ID=" + comboBox_Class_Type.SelectedValue.ToString() + "AND School_ID='" + comboBox_School.SelectedValue.ToString() + "'", comboBox_Class, "Class_ID", "Class_Name");
}
if (comboBox_Class.Items.Count == 0)
{
return;
}
else
{
//绑定DataGrid显示
DataGrid1.CaptionText = comboBox_Class_Type.Text.ToString() + "-"
+ comboBox_Class.Text.ToString() + " "
+ "现有学生信息";
str_Sql = "SELECT Student_ID As 身份证号码,Name As 姓名,Sex AS 性别,convert(char(10),Birthday,120) As 出生日期,Father AS 父亲,Mother AS 母亲,Keeper AS 监护人,StudentTel AS 联系电话,Student_Address AS 家庭住址,Father_Job AS 父亲职业,Father_XueLi AS 父亲学历,Mother_Job AS 母亲职业,Mother_XueLi AS 母亲学历,QuXian_Name AS 区县,Office_Name AS 办事处,Committee_Name AS 居委会 FROM View_StudentClass_Detail_City4 WHERE Class_ID=" + comboBox_Class.SelectedValue + " AND School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
conn.Fill(str_Sql);
DataGrid1.SetDataBinding(conn.ds, "TableIn");
DataGridTableStyle MyStyle = new DataGridTableStyle();
MyStyle.MappingName = "TableIn";
DataGrid1.TableStyles.Add(MyStyle);
MyStyle.GridColumnStyles["身份证号码"].Width = 120;
MyStyle.GridColumnStyles["性别"].Width = 30;
MyStyle.GridColumnStyles["家庭住址"].Width = 200;
MyStyle.GridColumnStyles["联系电话"].Width = 80;
}
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
private void button1_Click(object sender, System.EventArgs e)
{
useExcel = true;
excelApp = new Excel.ApplicationClass();
//Excel.Workbook
excelBook = excelApp.Workbooks.Add(1);
//Excel.Worksheet
excelSheet = (Excel.Worksheet)excelBook.Worksheets[1];
if (conn.ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("无可打印内容！", "提醒！", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
try
{
//创建应用程序,工作表,工作簿
int i, j = 0;
excelApp.Visible = true;
//设置表头
excelSheet.Cells[1, 1] = conn.School_IDtoWhat(comboBox_School.SelectedValue.ToString(), "School_Name");
excelSheet.Cells[2, 1] = "身份证号码";
excelSheet.Cells[2, 2] = "姓名";
excelSheet.Cells[2, 3] = "性别";
excelSheet.Cells[2, 4] = "出生日期";
excelSheet.Cells[2, 5] = "参加工作时间";
excelSheet.Cells[2, 6] = "学历";
excelSheet.Cells[2, 7] = "职称";
excelSheet.Cells[2, 8] = "职务";
excelSheet.Cells[2, 9] = "毕业院校";
excelSheet.Cells[2, 10] = "毕业日期";
excelSheet.Cells[2, 11] = "所学专业";
excelSheet.Cells[2, 12] = "是否在编";
excelSheet.Cells[2, 13] = "是否专任教师";
excelSheet.Cells[2, 14] = "担任课程";
//设置表头格式
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelSheet.Cells[2, 1], excelSheet.Cells[2, 14]).Font.Bold = true;
string str_Sql = "SELECT Teacher_ID As 身份证号码,Name As 姓名,Sex AS 性别,convert(char(10),Birthday,120) As 出生日期,convert(char(10),WorkTime,120) As 参加工作时间,SchoolType AS 学历,PostCan AS 职称,PostIn AS 职务,SchoolGrad AS 毕业院校,convert(char(10),GradTime,120) 毕业日期,SpecialIn AS 所学专业,InWorkSheet As 在编,IsFullTime AS 专任教师,LessonTeach AS 担任课程 FROM Teacher WHERE School_ID='" + comboBox_School.SelectedValue.ToString() + "'";
string errorstring = conn.Fill(str_Sql);
if (errorstring != "OK")
{
MessageBox.Show("请检查数据库！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
//填充表中各单元格
for (i = 1; i <= conn.ds.Tables[0].Rows.Count; i++)
{
for (j = 1; j <= conn.ds.Tables[0].Columns.Count; j++)
{
excelSheet.Cells[i + 2, j] = "'" + conn.ds.Tables[0].Rows[i - 1][j - 1].ToString();
}
}
//设置报表表格为最适应宽度
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Select();
excelSheet.get_Range(excelSheet.Cells[1, 1], excelSheet.Cells[i + 1, j]).Columns.AutoFit();
}
catch
{
throw new Exception("Excel error");
}
}
private void button1_Click(object sender, System.EventArgs e)
{
button1.Enabled = false;
string str_Sql, errorstring = "OK", str_School_ID_In;
//导入学校信息
conn.ds.Reset();
try
{
conn.ds.ReadXmlSchema(textBox_LocationDBOut.Text + "\\School.xsd");
conn.ds.ReadXml(textBox_LocationDBOut.Text + "\\School.xml");
}
catch
{ //对话框
MessageBox.Show("School文件打开失败！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
conn.dr = conn.ds.Tables[0].Rows[0];
str_School_ID_In = conn.dr["School_ID"].ToString();
DialogResult result;
if (conn.School_ID_Had(str_School_ID_In))
{
result = MessageBox.Show(this, "真的要导入学校代码为 " + str_School_ID_In + " 的学校吗?"
+ "其学校名称为" + conn.School_IDtoWhat(str_School_ID_In, "School_Name")
+ "操作会替换该学校原有的所有信息", "提醒！",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
}
else
{
result = MessageBox.Show(this, "真的要导入吗?", "提醒！",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
}
if (result == DialogResult.OK)
{
//删除该学校代码对应的所有信息,先删除学生,再删除班级,最后删除学校,以防外键冲突
str_Sql = "DELETE from Student WHERE School_ID='" + str_School_ID_In + "'";
conn.ExeSql(str_Sql);
str_Sql = "DELETE from Class WHERE School_ID='" + str_School_ID_In + "'";
conn.ExeSql(str_Sql);
str_Sql = "DELETE from Teacher WHERE School_ID='" + str_School_ID_In + "'";
conn.ExeSql(str_Sql);
str_Sql = "DELETE from School WHERE School_ID='" + str_School_ID_In + "'";
conn.ExeSql(str_Sql);
conn.ds.Reset();
conn.ds.ReadXmlSchema(textBox_LocationDBOut.Text + "\\School.xsd");
conn.ds.ReadXml(textBox_LocationDBOut.Text + "\\School.xml");
conn.dr = conn.ds.Tables[0].Rows[0];
str_Sql = "insert into School (School_ID,School_Name,School_Type_ID,Schoolmaster,School_Tel,School_Zip,School_Address,QuXian_Code) values ('"
+ str_School_ID_In + "','"
+ conn.dr["School_Name"].ToString() + "',"
+ conn.dr["School_Type_ID"].ToString() + ",'"
+ conn.dr["Schoolmaster"].ToString() + "','"
+ conn.dr["School_Tel"].ToString() + "','"
+ conn.dr["School_Zip"].ToString() + "','"
+ conn.dr["School_Address"].ToString() + "','"
+ conn.dr["QuXian_Code"].ToString() + "')";
errorstring = conn.ExeSql(str_Sql);
if (errorstring != "OK")
{
MessageBox.Show("未成功导入学校信息！请检查数据库！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
MessageBox.Show("成功导入学校信息！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
//导入教师信息
conn.ds.Reset();
try
{
conn.ds.ReadXmlSchema(textBox_LocationDBOut.Text + "\\Teacher.xsd");
conn.ds.ReadXml(textBox_LocationDBOut.Text + "\\Teacher.xml");
}
catch
{ //对话框
MessageBox.Show("Teacher文件打开失败！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
for (int i = 0; i < conn.ds.Tables[0].Rows.Count; i++)
{
string str_InWorkSheet, str_IsFullTime;
conn.dr = conn.ds.Tables[0].Rows[i];
if (conn.dr["InWorkSheet"].ToString() == "True")
{
str_InWorkSheet = "1";
}
else
{
str_InWorkSheet = "0";
}
if (conn.dr["IsFullTime"].ToString() == "True")
{
str_IsFullTime = "1";
}
else
{
str_IsFullTime = "0";
}
str_Sql = "INSERT INTO Teacher (School_ID,Teacher_ID,Name,Sex,Birthday,WorkTime,SchoolType,PostCan,PostIn,SchoolGrad,GradTime,SpecialIn,LessonTeach,InWorkSheet,IsFullTime) values('"
+ conn.dr["School_ID"].ToString()
+ "','" + conn.dr["Teacher_ID"].ToString()
+ "','" + conn.dr["Name"].ToString()
+ "','" + conn.dr["Sex"].ToString()
+ "','" + conn.dr["Birthday"].ToString()
+ "','" + conn.dr["WorkTime"].ToString()
+ "','" + conn.dr["SchoolType"].ToString()
+ "','" + conn.dr["PostCan"].ToString()
+ "','" + conn.dr["PostIn"].ToString()
+ "','" + conn.dr["SchoolGrad"].ToString()
+ "','" + conn.dr["GradTime"].ToString()
+ "','" + conn.dr["SpecialIn"].ToString()
+ "','" + conn.dr["LessonTeach"].ToString()
+ "'," + str_InWorkSheet
+ "," + str_IsFullTime + ")";
errorstring = conn.ExeSql(str_Sql);
}
if (errorstring != "OK")
{
MessageBox.Show("未成功导入教师信息！请检查数据库！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
MessageBox.Show("成功导入教师信息！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk); conn.ds.Reset();
//导入班级信息
conn.ds.Reset();
try
{
conn.ds.ReadXmlSchema(textBox_LocationDBOut.Text + "\\Class.xsd");
conn.ds.ReadXml(textBox_LocationDBOut.Text + "\\Class.xml");
}
catch
{ //对话框
MessageBox.Show("Class文件打开失败！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
for (int i = 0; i < conn.ds.Tables[0].Rows.Count; i++)
{
conn.dr = conn.ds.Tables[0].Rows[i];
str_Sql = "INSERT INTO Class (Class_ID,School_ID,Class_Type_ID,Class_Name,TeacherInCharge) values("
+ conn.dr["Class_ID"].ToString() + ",'"
+ conn.dr["School_ID"].ToString() + "',"
+ conn.dr["Class_Type_ID"].ToString() + ",'"
+ conn.dr["Class_Name"].ToString() + "','"
+ conn.dr["TeacherInCharge"].ToString() + "')";
errorstring = conn.ExeSql(str_Sql);
}
if (errorstring != "OK")
{
MessageBox.Show("未成功导入班级信息！请检查数据库！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
MessageBox.Show("成功导入班级信息！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk); conn.ds.Reset();
//导入学生信息
conn.ds.Reset();
try
{
conn.ds.ReadXmlSchema(textBox_LocationDBOut.Text + "\\Student.xsd");
conn.ds.ReadXml(textBox_LocationDBOut.Text + "\\Student.xml");
}
catch
{ //对话框
MessageBox.Show("Student文件打开失败！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
for (int i = 0; i < conn.ds.Tables[0].Rows.Count; i++)
{
conn.dr = conn.ds.Tables[0].Rows[i];
str_Sql = "insert into Student (School_ID,Student_ID,Class_ID,Name,Birthday,Sex,Father,Mother,Keeper,StudentTel,QuXian_ID,Office_ID,Committee_ID,Student_Address,Father_Job,Father_XueLi,Mother_Job,Mother_XueLi) values ('"
+ conn.dr["School_ID"].ToString() + "','"
+ conn.dr["Student_ID"].ToString() + "',"
+ conn.dr["Class_ID"].ToString() + ",'"
+ conn.dr["Name"].ToString() + "','"
+ conn.dr["Birthday"].ToString() + "','"
+ conn.dr["Sex"].ToString() + "','"
+ conn.dr["Father"].ToString() + "','"
+ conn.dr["Mother"].ToString() + "','"
+ conn.dr["Keeper"].ToString() + "','"
+ conn.dr["StudentTel"].ToString() + "',"
+ conn.dr["QuXian_ID"].ToString() + ","
+ conn.dr["Office_ID"].ToString() + ",'"
+ conn.dr["Committee_ID"].ToString() + "','"
+ conn.dr["Student_Address"].ToString() + "','"
+ conn.dr["Father_Job"].ToString() + "','"
+ conn.dr["Father_XueLi"].ToString() + "','"
+ conn.dr["Mother_Job"].ToString() + "','"
+ conn.dr["Mother_XueLi"].ToString() + "')";
errorstring = conn.ExeSql(str_Sql);
}
if (errorstring != "OK")
{
MessageBox.Show("未成功导入学生信息！请检查数据库！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
button1.Enabled = false;
return;
}
MessageBox.Show("成功导入学生信息！" + errorstring, "提醒！",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
conn.Close();
this.Dispose();
}
else
{
return;
}
} /**//**/
