学生管理系统

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
using MySql.Data.MySqlClient;


namespace WindowsFormsApplication5
{
    public partial class Form1 : Form
    {
        private string fileName = string.Empty;//定义保存读取文件路径的变量
        private List<string> objListStudent = new List<string>(); //定义List存储读取到的学生信息
        private List<string> objQueryList = new List<string>();//经过查询满足条件的学生信息
        private int actionFlag = 0;//如果其值判断是添加还是修改，如果为1是添加，如果为2是修改
        private Microsoft.Office.Interop.Excel.Application application;//这是个客户端
        private Microsoft.Office.Interop.Excel.Workbooks workbooks;//所有工作簿
        private Microsoft.Office.Interop.Excel.Worksheet worksheet;//工作表
        private Microsoft.Office.Interop.Excel.Workbook workbook;//所用到的工作表
        public Form1()
        {
            InitializeComponent();

            //禁用明细区域
            groupBox2.Enabled = false;
        }
        //数据的导入和展示 》选择文件...》将数据导入到DataGridView中...》将数据显示到学生明细中...》数据发生变化，学生明细发生变化
        //控件事件
        private void button8_Click(object sender, EventArgs e)//导入数据
        {
            //1 选择文件
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "CSV文件(*.csv)|*.csv|TXT文件(*.txt)|*.txt|所有文件(*.*)|*.*";
            if (openfile.ShowDialog() == DialogResult.OK)
            {
                fileName = openfile.FileName;//把选择文件路径赋值给全局变量filename
            }
            //2 把文件读取到list中
            try
            {
                //读取文件
                objListStudent = ReadFileToList(fileName);
            }
            catch (Exception ex) {
                //弹出错误窗口
                MessageBox.Show("读取文件出错，具体错误如下"+ex.Message,"系统消息",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            //3 把list数据展示到表中
            LoadDataToDateGrid(objListStudent);
            //4 把表中数据中的一行展示在groupbox明细中
            string currentSNo = dataGridView1.Rows[0].Cells[0].Value.ToString();//第一行，第一列的值
            string[] currentDetail = GetStudentByNO(currentSNo).Split(',');//将当前获得的信息直接按逗号分开
            //把当前学生信息展示在明细中
            LoadDataToDetail(currentDetail[0], currentDetail[1], currentDetail[2], currentDetail[3], currentDetail[4], currentDetail[5], currentDetail[6]);
        }
        private void dataGridView1_SelectionChanged(object sender, EventArgs e)//将改变的datagridview中的数据加载到学生详细信息中
        {
            if (dataGridView1.CurrentRow.Selected == false) return;
            else
            {
                string currentSNO = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                string[] currentDetail = GetStudentByNO(currentSNO).Split(',');
                LoadDataToDetail(currentDetail[0], currentDetail[1], currentDetail[2], currentDetail[3], currentDetail[4], currentDetail[5], currentDetail[6]);
            }
        }
        private void textBox1_TextChanged(object sender, EventArgs e)//根据学号查询
        {
            //清空查询存储结果的list
            objQueryList.Clear();

            foreach (string item in objListStudent)
            {
                if (item.StartsWith(textBox1.Text)) objQueryList.Add(item);
            }
            //清空datagridview中的数据
            dataGridView1.Rows.Clear();
            //展示在datagridvie中
            LoadDataToDateGrid(objQueryList);
        }
        private void textBox2_TextChanged(object sender, EventArgs e)//根据姓名查询
        {
            //清空查询存储结果的list
            objQueryList.Clear();

            foreach (string item in objListStudent)
            {
                if (item.Contains(textBox2.Text)) objQueryList.Add(item);
            }
            //清空datagridview中的数据
            dataGridView1.Rows.Clear();
            //展示在datagridvie中
            LoadDataToDateGrid(objQueryList);
        }
        private void textBox3_TextChanged(object sender, EventArgs e)//根据手机号码查询
        {
            //清空查询存储结果的list
            objQueryList.Clear();

            foreach (string item in objListStudent)
            {
                if (item.Contains(textBox3.Text)) objQueryList.Add(item);
            }
            //清空datagridview中的数据
            dataGridView1.Rows.Clear();
            //展示在datagridvie中
            LoadDataToDateGrid(objQueryList);
        }
        private void button2_Click(object sender, EventArgs e)//添加学生信息
        {
            //添加过程：点击添加按钮————》输入数据————》判断数据是否有效————》提交

            //控制启用和禁用
            DisableButton();

            //让明细窗体中文本框清空
            textBox4.Text = string.Empty;
            textBox5.Text = string.Empty;
            textBox6.Text = string.Empty;
            textBox7.Text = string.Empty;
            textBox8.Text = string.Empty;
            dateTimePicker1.Text = DateTime.Now.ToString();
            radioButton1.Checked = true;

            //为学号设置焦点focus
            textBox4.Focus();

            //修改actionFlag
            actionFlag = 1;
        }
        private void button3_Click(object sender, EventArgs e)//修改学生信息
        {
            //修改=删除旧的，添加新的
            DisableButton();
            //学号不允许修改
            textBox4.Enabled = false;

            //让学生姓名的文本框获得焦点
            textBox5.Focus();

            //修改actionFlag
            actionFlag = 2;
        }
        private void button4_Click(object sender, EventArgs e)//删除学生信息
        {
            if(dataGridView1.CurrentRow.Selected==false)
            {
                MessageBox.Show("删除数据前必须要选中某一行", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else
            {
                string info = "您确定要删除学生信息【学号 ："+dataGridView1.CurrentRow.Cells[0].Value.ToString()+"】";
                DialogResult result= MessageBox.Show(info, "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if(result==DialogResult.Yes)
                {
                    string currentStudent = GetStudentByNO(dataGridView1.CurrentRow.Cells[0].Value.ToString());

                    foreach (string item in objListStudent)
                    {
                        if (item.Equals(currentStudent))
                        {
                            objListStudent.Remove(item);
                            MessageBox.Show("学生信息删除成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            break;
                        }
                    }
                }
                else
                {
                    return;
                }
                dataGridView1.Rows.Clear();
                LoadDataToDateGrid(objListStudent);
            }
        }
        private void button6_Click(object sender, EventArgs e)//提交添加和修改的操作
        {
            if (ValiDate() == false) return;
            else
            {
                //组合数据，准备添加到list中
                string sno = textBox4.Text.Trim();
                string sname = textBox5.Text.Trim();
                string sex = radioButton1.Checked == true ? "男" : "女";
                string birthday = dateTimePicker1.Text;
                string mobile = textBox6.Text;
                string email = textBox7.Text;
                string homeadress = textBox8.Text;

                string currentStudent = sno + ',' + sname + ',' + sex + ',' + birthday + ',' + mobile + ',' + email + ',' + homeadress;

                switch (actionFlag)
                {
                    case 1://添加
                        objListStudent.Add(currentStudent);
                        //把添加后的list显示
                        dataGridView1.Rows.Clear();
                        LoadDataToDateGrid(objListStudent);

                        //显示添加成功
                        MessageBox.Show("学生信息添加成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        //启用按钮
                        EnableButton();
                        break;
                    case 2://修改
                        for(int i = 0; i < objListStudent.Count; i++)
                        {
                            if(objListStudent[i].StartsWith(sno))
                            {
                                //1.删除原有的
                                objListStudent.RemoveAt(i);
                                //2.插入添加的
                                objListStudent.Insert(i,currentStudent);
                                break;
                            }
                        }
                        dataGridView1.Rows.Clear();
                        LoadDataToDateGrid(objListStudent);
                        //提示修改完成
                        MessageBox.Show("学生信息修改成功", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //启用按钮
                        EnableButton();

                        break;
                    default:
                        break;
                }
            }
            
        }
        private void button7_Click(object sender, EventArgs e)//取消操作
        {
            //控制按钮
            EnableButton();
        }
        private void button1_Click(object sender, EventArgs e)//保存数据
        {
            DialogResult result = MessageBox.Show("是否要保存数据", "系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if(result==DialogResult.Yes)
            {
                string savefilename = string.Empty;
                SaveFileDialog savedialog = new SaveFileDialog();
                savedialog.DefaultExt = "xls";
                savedialog.Filter = "Excel文件|*.xls";
                savedialog.ShowDialog();
                savefilename = savedialog.FileName;
                application = new Microsoft.Office.Interop.Excel.Application();
                if(application==null)
                {
                    MessageBox.Show("无法创建Excel文件，可能您的机器未按照Excel");
                }
                workbooks = application.Workbooks;
                workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                for(int i=0;i<dataGridView1.ColumnCount;i++)
                {
                    worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }
                for(int j=0;j<dataGridView1.RowCount;j++)
                {
                    for(int m=0;m<dataGridView1.ColumnCount;m++)
                    {
                        worksheet.Cells[j + 2, m + 1] = dataGridView1.Rows[j].Cells[m].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                if(savefilename!="")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(savefilename);
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show("导出文件时出错\n" + ex.Message);
                    }
                }

            }
        }
        private void button9_Click(object sender, EventArgs e)//保存到数据库
        {
            string conStr = "server = localhost; uid = root; pwd = 693213; database = zymbest";
            MySqlConnection conn = new MySqlConnection(conStr);
            conn.Open();
            MySqlDataAdapter myadp = new MySqlDataAdapter("select*from new_table",conn);
            DataSet myds = new DataSet();
            MySqlCommandBuilder mycb = new MySqlCommandBuilder(myadp);

            myadp.Update(myds,"new_table");
            

        }
        //用户自定义方法
        private List<string> ReadFileToList(string filePath)//把一个文件读取，并以list方式返回给调用值
        {
            List<string> objlist = new List<string>();
            string line = string.Empty;
            try
            {
                StreamReader file = new StreamReader(filePath, Encoding.Default);
                while ((line = file.ReadLine()) != null)//先赋值，在循环，如果下一行为空，那就停止循环
                {
                    objlist.Add(line);//把这一行的数据加入list中
                }
                file.Close();//结束之后一定要关闭文件
            }
            catch (Exception ex) {
                throw ex;
            }
            return objlist;
        }
        private void LoadDataToDateGrid(List<string> objlist)//把list数据展示在DataGridView中
        {
            //依次读取list中的数据
            foreach (string item in objlist)
            {
                string[] studentArray = item.Split(',');

                //把读取的当前学生信息插入到DataGridView中
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dataGridView1);
                row.Cells[0].Value = studentArray[0];
                row.Cells[1].Value = studentArray[1];
                row.Cells[2].Value = studentArray[2];
                row.Cells[3].Value = studentArray[3];
                row.Cells[4].Value = studentArray[4];
                dataGridView1.Rows.Add(row);//加一行
            }
        }
        private void LoadDataToDetail(string sno,string name,string sex,string birthday,string mobile,string email,string homeadress)//把一个学生的明细展示在groupbox中
        { 
            textBox4.Text = sno;
            textBox5.Text = name;
            if (sex == "男")
            {
                radioButton1.Checked = true;
            }
            else
            {
                radioButton2.Checked = true;
            }
            dateTimePicker1.Text = birthday;
            textBox6.Text = mobile;
            textBox7.Text = email;
            textBox8.Text = homeadress;
            //if (photo == null) return;
        }
        private string GetStudentByNO(string sno)//通过学号来查询学生各项明细信息
        {
            string currentStudent = string.Empty;
            foreach(string item in objListStudent)
            {
                if (item.StartsWith(sno))
                {
                    currentStudent = item;
                    break;
                }
            }
            return currentStudent;
        }
        private void DisableButton()//禁用按钮
        {
            //禁用按钮
            button8.Enabled = false;
            button2.Enabled = false;
            button3.Enabled = false;
            button4.Enabled = false;

            //启用
            groupBox2.Enabled = true;
        }
        private void EnableButton()//启用按钮
        {
            //启用按钮
            button8.Enabled = true;
            button2.Enabled = true;
            button3.Enabled = true;
            button4.Enabled = true;

            //启用
            groupBox2.Enabled = false;
        }
        private bool ValiDate()//用户添加和修改后数据的校验
        {
            bool b = true;
            //学号和姓名不能为空
            if (string.IsNullOrWhiteSpace(textBox4.Text))//判断学号是否为空
            {
                MessageBox.Show("学生学号不能为空", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                textBox4.Focus();
                b=false;
            }
            if (string.IsNullOrWhiteSpace(textBox5.Text))//判断姓名是否为空
            {
                MessageBox.Show("学生姓名不能为空", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                textBox5.Focus();
                b=false;
            }
            if (actionFlag == 1)
            {
                if (GetStudentByNO(textBox4.Text.Trim()) != string.Empty)
                {
                    MessageBox.Show("该学生学号已经存在", "系统消息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    textBox4.Focus();
                    b=false;
                }
            }
            return b;
        }
        private void textBox4_TextChanged(object sender, EventArgs e)
        {
           
        }