C# winform 编写一键排班软件遇到的程序问题

    下面咱们看图说话：

    1.规范输入。系统默认应输入“星期X”，如果用户输入特殊字符@#￥%！或其他字符，在这里会给个提醒，同时限制只能输入三个字符

    

            string str = txtWeek.Text.Trim();            
            string[] strchar = { "星期一","星期二","星期三","星期四","星期五","星期六","星期日" };
            bool exists = ((IList)strchar).Contains(str);
            if (exists == false)
            {
                MessageBox.Show("只能输入“星期X”这种格式！", "提示");
            }
            else
            {

            }

    当然，要添加引用

using System.Reflection;
using System.Collections;

    2.如何实现排班

    数据库里建立三张表，分别存放二日班可选人员、周四班可选人员、一三五六班可选人员    

    D层写三个方法，分别对应三个表，调用哪个方法，就从哪个表随机获取一名同学，并把它插入新值班表

    根据界面输入的星期X，依次调用D层方法

    (这时发现数据库设计有些不合规范……其实完全可以单表操作)

    3.如何将DataTable中的数据直接导出到Excel

    测试的时候，程序退出了，但进程里的EXCEL.exe不退出，网上的方法不奏效，暂时找不到其他方法，所以用提示框+杀死所有Excel进程的方式保证下次再运行程序不会弹出多个excel

        private void btnExcel_Click(object sender, EventArgs e)
        {
            if (System.Diagnostics.Process.GetProcessesByName("EXCEL").ToList().Count > 0)
            {
                //系统进程中存在Excel.exe
                MessageBox.Show("请保存并关闭其他已打开的Excel文件，否则[导出]不能成功！");
            }
            else
            {
                //不存在
                arrangeBAO abao = new arrangeBAO();
                DataTable dt = abao.ToExcel();
                string strFileName = @"C:\新值班表.xlsx";
                DataTabletoExcel(dt, strFileName);//调用函数

                EndReport();
                killProcessThread();//杀掉不死进程
                killExcel();
                open();
                btnExcel.Enabled = false;
            }
        }

        public Excel.Application m_xlApp = null;


        /// <summary>  
        /// 将DataTable数据导出到Excel表  
        /// </summary>  
        /// <param name="tmpDataTable">要导出的DataTable</param>  
        /// <param name="strFileName">Excel的保存路径及名称</param>  
        public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null)
            {
                return;
            }
            long rowNum = tmpDataTable.Rows.Count;//行数  
            int columnNum = tmpDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;

            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1  

            try
            {
                if (rowNum > 65536)//单张Excel表格最大行数  
                {
                    long pageRows = 65535;//定义每页显示的行数,行数必须小于65536  
                    int scount = (int)(rowNum / pageRows);//导出数据生成的表单数  
                    if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准  
                    {
                        scount = scount + 1;
                    }
                    for (int sc = 1; sc <= scount; sc++)
                    {
                        if (sc > 1)
                        {
                            object missing = System.Reflection.Missing.Value;
                            worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
                                        missing, missing, missing, missing);//添加一个sheet  
                        }
                        else
                        {
                            worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1  
                        }
                        string[,] datas = new string[pageRows + 1, columnNum];

                        for (int i = 0; i < columnNum; i++) //写入字段  
                        {
                            datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息  
                        }
                        Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
                        range.Interior.ColorIndex = 15;//15代表灰色  
                        range.Font.Bold = true;
                        range.Font.Size = 9;

                        int init = int.Parse(((sc - 1) * pageRows).ToString());
                        int r = 0;
                        int index = 0;
                        int result;
                        if (pageRows * sc >= rowNum)
                        {
                            result = (int)rowNum;
                        }
                        else
                        {
                            result = int.Parse((pageRows * sc).ToString());
                        }

                        for (r = init; r < result; r++)
                        {
                            index = index + 1;
                            for (int i = 0; i < columnNum; i++)
                            {
                                object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
                                datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                            }
                            System.Windows.Forms.Application.DoEvents();
                            //添加进度条  
                        }

                        Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
                        fchR.Value2 = datas;
                        worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                        m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化  
                        range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
                        //range.Interior.ColorIndex = 15;//15代表灰色  
                        range.Font.Size = 9;
                        range.RowHeight = 14.25;
                        range.Borders.LineStyle = 1;
                        range.HorizontalAlignment = 1;
                    }
                }//排班的应该走这儿，就百条数据
                else
                {
                    string[,] datas = new string[rowNum + 1, columnNum];
                    for (int i = 0; i < columnNum; i++) //写入字段  
                    {
                        datas[0, i] = tmpDataTable.Columns[i].Caption;
                    }
                    Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
                    range.Interior.ColorIndex = 15;//15代表灰色  
                    range.Font.Bold = true;
                    range.Font.Size = 9;

                    int r = 0;
                    for (r = 0; r < rowNum; r++)
                    {
                        for (int i = 0; i < columnNum; i++)
                        {
                            object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
                            datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                        }
                        System.Windows.Forms.Application.DoEvents();
                        //添加进度条  
                    }
                    Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
                    fchR.Value2 = datas;

                    worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                    m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;

                    range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
                    //range.Interior.ColorIndex = 15;//15代表灰色  
                    range.Font.Size = 9;
                    range.RowHeight = 14.25;
                    range.Borders.LineStyle = 1;
                    range.HorizontalAlignment = 1;
                }
                workbook.Saved = true;
                //保存文件
                workbook.SaveCopyAs(strFileName);
                //workbook.SaveAs(strFileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                EndReport();
            }
        }


        /// <summary>  
        /// 退出报表时关闭Excel和清理垃圾Excel进程  
        /// </summary>  
        public void EndReport()
        {
            object missing = System.Reflection.Missing.Value;
            try
            {
                m_xlApp.Workbooks.Close();
                m_xlApp.Workbooks.Application.Quit();
                m_xlApp.Application.Quit();
                m_xlApp.Quit();
            }
            catch { }
            finally
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
                    m_xlApp = null;

                }
                catch { }
                try
                {
                    //清理垃圾进程  
                    this.killProcessThread();
                }
                catch { }
                GC.Collect();
            }
        }
        /// <summary>  
        /// 杀掉不死进程  
        /// </summary>  
        public void killProcessThread()
        {
            ArrayList myProcess = new ArrayList();
            for (int i = 0; i < myProcess.Count; i++)
            {
                try
                {
                    System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
                }
                catch { }
            }
        }


        //保存后打开值班文件
        public void open()
        {
            //定义一个ProcessStartInfo实例

            System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();

            //设置启动进程的初始目录

            info.WorkingDirectory = Application.StartupPath;

            //设置启动进程的应用程序或文档名

            info.FileName = @"C:\新值班表.xlsx";

            //设置启动进程的参数

            info.Arguments = "";

            //启动由包含进程启动信息的进程资源

            try
            {
                System.Diagnostics.Process.Start(info);
            }
            catch (System.ComponentModel.Win32Exception we)
            {
                MessageBox.Show(this, we.Message);
                return;
            }
        }

        //杀掉任务管理器里的所有excel进程
        public static void killExcel()
        {
            foreach (Process p in Process.GetProcessesByName("EXCEL"))
            {
                try
                {
                    p.Kill();
                }
                catch
                {
                    //在杀死非应用程序开启的进程时,会抛出错误,直接抛弃错误,不用处理。
                }
            }
        }