baseoperate.cs

程序设计语言上机考试系统

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication1.baseclass
{
    class BaseOperate
    {
        SqlConnection cn = new SqlConnection("SERVER=(local);UID=sa;PWD=sa;Trusted_Connection=True;DATABASE=test1");
        #region  建立数据库连接
        /// <summary>
        /// 建立数据库连接.
        /// </summary>
        /// <returns>返回SqlConnection对象</returns>
        public SqlConnection getcon()
        {
            SqlConnection cn = new SqlConnection("SERVER=(local);UID=sa;PWD=sa;Trusted_Connection=True;DATABASE=test1");
            return cn;
        }
        #endregion

        #region  执行SqlCommand命令
        /// <summary>
        /// 执行SqlCommand
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        public void getcom(string M_str_sqlstr)
        {
            try
            {
                SqlConnection sqlcon = this.getcon();
                //打开连接
                if (sqlcon.State == ConnectionState.Closed)
                    sqlcon.Open();
                SqlCommand sqlcom = new SqlCommand(M_str_sqlstr, sqlcon);
                sqlcom.ExecuteNonQuery();
                sqlcom.Dispose();
                //关闭连接
                if (sqlcon.State == ConnectionState.Open)
                    sqlcon.Close();
                sqlcon.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            }
        }
        #endregion

        #region  创建DataSet对象
        /// <summary>
        /// 创建一个DataSet对象
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <param name="M_str_table">表名</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet getds(string M_str_sqlstr, string M_str_table)
        {
            SqlConnection sqlcon = this.getcon();
            SqlDataAdapter sqlda = new SqlDataAdapter(M_str_sqlstr, sqlcon);
            DataSet myds = new DataSet();
            sqlda.Fill(myds, M_str_table);
            return myds;
        }
        #endregion

        #region  导出DataGridView中的数据
        /// <summary>
        /// 将DataGridView控件中的数据导出到Excel
        /// </summary>
        /// <param name="gridView">DataGridView对象</param>
        /// <param name="isShowExcle">是否显示Excel界面</param>
        /// <returns></returns>
        public void ExportDataGridview(DataGridView gridView, bool isShowExcle, string file)
        {
            string filename = "";
            //将模板文件复制到一个新文件中   
            SaveFileDialog mySave = new SaveFileDialog();
            mySave.Filter = "Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
            if (mySave.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                filename = mySave.FileName;
                //将模板文件copy到新位置，建议实际开发时用相对路径，如Application.StartupPath.Trim()+"\\report\\normal.xls" 
                FileInfo mode = new FileInfo(Application.StartupPath.Trim() + @"\template\" + file);
                try
                {
                    //复制模板
                    mode.CopyTo(filename, true);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            //打开复制后的文件   
            object missing = Missing.Value;
            Excel.Application myExcel = new Excel.Application();
            myExcel.Visible = false;

            string templetFile = filename;
            // 打开模板文件，得到WorkBook对象 
            Excel.Workbook workBook = myExcel.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Range range;
            // 获取要写入数据的WorkSheet对象，并重命名 
            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
            try
            {
                if (gridView.ColumnCount == 0)
                {
                    MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                //生成字段名称
                for (int i = 0; i < gridView.ColumnCount; i++)
                {
                    sheet.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
                }
                //填充数据
                int rowCount = gridView.Rows.Count;		//DataTable行数
                int colCount = gridView.Columns.Count;	//DataTable列数

                //利用二维数组批量写入
                string[,] arr = new string[rowCount, 5];  //存放学生基本信息
                Double[,] arr1 = new Double[rowCount, 4]; //存放学生成绩
                for (int j = 0; j < rowCount; j++)
                {
                    for (int k = 0; k < 5; k++)
                    {
                        arr[j, k] = gridView.Rows[j].Cells[k].Value.ToString().Trim();
                    }
                    for (int i = 5; i < colCount; i++)
                    {
                        arr1[j, i - 5] = Convert.ToDouble(gridView.Rows[j].Cells[i].Value.ToString());
                    }
                }

                //添加学生基本信息
                range = (Excel.Range)sheet.Cells[2, 1];
                range = range.get_Resize(rowCount, 5);
                range.Value2 = arr;
                //添加学生成绩
                range = (Excel.Range)sheet.Cells[2, 6];
                range = range.get_Resize(rowCount, 4);
                range.Value2 = arr1;
                //如果是成绩表，要为“名称”列添加公式
                if (file == "mark.xls")
                {
                    for (int i = 0; i < gridView.RowCount; i++)
                    {
                        //给“名次”列设置公式
                        sheet.Cells[i + 2, gridView.ColumnCount + 1] = "=IF(A" + (i + 2).ToString() + "=" + "\"" + "\"" + "," + "\"" + "\"" + ",RANK(I" + (i + 2).ToString() + ",I2:I" + (gridView.RowCount + 1).ToString() + "))";
                    }
                }
                //如果是成绩表，须添加sheet2（分析表）
                if (file == "mark.xls")
                {
                    Excel.Worksheet sheet2 = (Excel.Worksheet)workBook.Worksheets.get_Item(2);
                    getDepart(sheet2, gridView.RowCount + 1, range);
                }
                myExcel.Visible = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                //退出excel程序
                myExcel.Quit();
            }
        }
        #endregion


        #region  导出DataGridView中的数据
        /// <summary>
        /// 将DataGridView控件中的数据导出到Excel
        /// </summary>
        /// <param name="gridView">DataGridView对象</param>
        /// <param name="isShowExcle">是否显示Excel界面</param>
        /// <returns></returns>
        public void ExportStuExcel(DataGridView gridView, bool isShowExcle, string file)
        {
            string filename = "";
            //将模板文件复制到一个新文件中   
            SaveFileDialog mySave = new SaveFileDialog();
            mySave.Filter = "Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
            if (mySave.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                filename = mySave.FileName;
                //将模板文件copy到新位置，建议实际开发时用相对路径，如Application.StartupPath.Trim()+"\\report\\normal.xls" 
                FileInfo mode = new FileInfo(Application.StartupPath.Trim() + @"\template\" + file);
                try
                {
                    //复制模板
                    mode.CopyTo(filename, true);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            //打开复制后的文件   
            object missing = Missing.Value;
            Excel.Application myExcel = new Excel.Application();
            myExcel.Visible = false;

            string templetFile = filename;
            // 打开模板文件，得到WorkBook对象 
            Excel.Workbook workBook = myExcel.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Range range;
            // 获取要写入数据的WorkSheet对象，并重命名 
            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
            try
            {
                if (gridView.ColumnCount == 0)
                {
                    MessageBox.Show("表格中没有数据，无法导出数据！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                //生成字段名称
                for (int i = 0; i < gridView.ColumnCount; i++)
                {
                    sheet.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
                }
                //填充数据
                int rowCount = gridView.Rows.Count;		//DataTable行数
                int colCount = gridView.Columns.Count;	//DataTable列数

                //利用二维数组批量写入
                string[,] arr = new string[rowCount, colCount];  //存放学生基本信息
                for (int j = 0; j < rowCount; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        arr[j, k] = gridView.Rows[j].Cells[k].Value.ToString().Trim();
                    }
                }

                //添加学生基本信息
                range = (Excel.Range)sheet.Cells[2, 1];
                range = range.get_Resize(rowCount, colCount);
                range.Value2 = arr;
                myExcel.Visible = true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                //退出excel程序
                myExcel.Quit();
            }
        }
        #endregion

        #region 给mark表sheet2的院系，专业，班级赋值
        public void getDepart(Excel.Worksheet sheet,int total,Excel.Range range)
        {
            //取出院系名称
            DataSet myds = getds("select distinct department from student","student");
            DataTable dt = myds.Tables[0];
            int i = 0;
            foreach(DataRow row in dt.Rows)
            {
                //取出专业名称
                DataSet myds1= getds("select distinct profession from student where department ='"+ row["department"].ToString()+"'","student1");
                DataTable dt1 = myds1.Tables[0];
                foreach (DataRow row1 in dt1.Rows)
                {
                    //取出班级名称
                    DataSet myds2 = getds("select distinct class from student where profession ='" +row1["profession"].ToString() + "'", "student2");
                    DataTable dt2 = myds2.Tables[0];
                    foreach (DataRow row2 in dt2.Rows)
                    {
                        //院系