C#操作Excel数据导入和dataGridView重绘

一 C# 操作excel导入和datagridviewview重绘

   代码部分：

                  (1)  判断本机是否存在excel；

       /// <summary>
       /// 判断本机是否有excel
       /// </summary>
      /// <returns></returns>
      private bool codeboolisExcelInstalled()
      {
            Type type = Type.GetTypeFromProgID("Excel.Application");
             return type != null;
       }

                  

                  (2) 找出需要导入的excel表中的相应sheet，不支持多个sheet的导入；                           

        /// <summary>
        /// 获取要导入的excel中的sheet表名称，这里支持sheet表名的重命名
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static string[] GetExcelSheetNames(string filePath)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            int count = wb.Worksheets.Count;
            string[] names = new string[count];
            for (int i = 1; i <= count; i++)
            {
                names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;
            }

            wb.Close(null, null, null);
            excelApp.Quit();
            wbs.Close();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
            excelApp = null;
            wbs = null;
            wb = null;
            return names;
        }

 

                    (3) 导入操作代码；

        /// <summary>
        /// 导入Excel计划表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                //打开要导入的文件
                OpenFileDialog open = new OpenFileDialog();
                if (open.ShowDialog() == DialogResult.OK)
                {
                    string fileName = open.FileName;
                    ReadExcel(fileName);
                    label13.Visible = true;
                    label13.Text = textBox1.Text.ToString() + "鉴定计划表";
                    fileName = "";
                    return;
                }
                else
                {
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

                   

        #region 考评员计划表中的考评员所在的列
        int[] sum1 = new int[5] { 7, 8, 9, 10, 11 };
        int[] sum2 = new int[5] { 15, 16, 17, 18, 19 };
        int[] sum3 = new int[5] { 23, 24, 25, 26, 27 };
        int[] sum4 = new int[5] { 31, 32, 33, 34, 35 };
        int[] sum5 = new int[5] { 39, 40, 41, 42, 43 };
        int[] sum6 = new int[5] { 47, 48, 49, 50, 51 };
        int[] sum7 = new int[5] { 55, 56, 57, 58, 59 };
        int[] sum8 = new int[5] { 63, 64, 65, 66, 67 };
        int[] sum9 = new int[5] { 71, 72, 73, 74, 75 };
        int[] sum10 = new int[5] { 79, 80, 81, 82, 83 };
        #endregion

        string[] titleName1 = new string[10];//记录Excel表中考核题目信息
        int abc = 0;
        private void ReadExcel(string sPath)
        {
            //dataGridView1.Columns.Clear();
            //HDR = YES 代表第一行是标题，不做为数据使用；如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
            //参数Excel 8.0 对于Excel 97以上到2003版本都用Excel 8.0，2007或2010的都用Extended Properties=Excel 12.0
            //当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。1 是读取 2 是写入和读取 
            try
            {
                #region 导入计划表，重绘显示
                if (codeboolisExcelInstalled())
                {
                    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
                    //string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                    excelConnection = new OleDbConnection(connectionString);
                    excelConnection.Open();
                    string SheetName = GetExcelSheetNames(sPath)[0]; ;

                    string strSQL = "SELECT * FROM [" + SheetName + "$]";
                    OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
                    System.Data.DataTable dTable = new System.Data.DataTable();
                    dataAdapter.Fill(dTable);

                    if (dTable.Rows.Count != 0)
                    {
                        abc = dTable.Rows.Count;
                        #region 选获取考评员计划表中的题目信息
                        titleName1[0] = dTable.Rows[0][6].ToString();
                        titleName1[1] = dTable.Rows[0][14].ToString();
                        titleName1[2] = dTable.Rows[0][22].ToString();
                        titleName1[3] = dTable.Rows[0][30].ToString();
                        titleName1[4] = dTable.Rows[0][38].ToString();
                        titleName1[5] = dTable.Rows[0][46].ToString();
                        titleName1[6] = dTable.Rows[0][54].ToString();
                        titleName1[7] = dTable.Rows[0][62].ToString();
                        titleName1[8] = dTable.Rows[0][70].ToString();
                        titleName1[9] = dTable.Rows[0][78].ToString();
                        #endregion

                        #region 获得excel考评员计划表后在进行表格重绘
                        dataGridView2.Visible = true;
                        for (int k = 0; k < dataGridView2.Columns.Count; k++)
                        {
                            dataGridView2.Columns[k].SortMode = DataGridViewColumnSortMode.NotSortable;
                        }
                        dataGridView2.Rows.Clear();
                        #endregion

                        #region 显示在表格
                        RowS = dTable.Rows.Count - 2;//计划表的前两行为表头，表头的第一行和第二行合并，所以表头也被加载到dataTable中，所以得减去前两行，需要的数据从第三行开始
                        int i = 0, j = 2;
                        for (i = dataGridView2.Rows.Count; i < RowS; i++)
                        {
                            if (dTable.Rows[j][2].ToString() != "")
                            {
                                #region excel导入到表格
                                dataGridView2.Rows.Add();
                                dataGridView2.Rows[i].Cells[0].Value = dTable.Rows[j][0].ToString();
                                dataGridView2.Rows[i].Cells[1].Value = dTable.Rows[j][1].ToString();
                                dataGridView2.Rows[i].Cells[2].Value = dTable.Rows[j][2].ToString();
                                dataGridView2.Rows[i].Cells[3].Value = dTable.Rows[j][3].ToString();
                                dataGridView2.Rows[i].Cells[4].Value = dTable.Rows[j][4].ToString();
                                dataGridView2.Rows[i].Cells[5].Value = dTable.Rows[j][5].ToString();
                                dataGridView2.Rows[i].Cells[6].Value = dTable.Rows[j][6].ToString();
                                dataGridView2.Rows[i].Cells[7].Value = dTable.Rows[j][7].ToString();
                                dataGridView2.Rows[i].Cells[8].Value = dTable.Rows[j][8].ToString();
                                dataGridView2.Rows[i].Cells[9].Value = dTable.Rows[j][9].ToString();
                                dataGridView2.Rows[i].Cells[10].Value = dTable.Rows[j][10].ToString();
                                dataGridView2.Rows[i].Cells[11].Value = dTable.Rows[j][11].ToString();
                                dataGridView2.Rows[i].Cells[12].Value = dTable.Rows[j][12].ToString();
                                dataGridView2.Rows[i].Cells[13].Value = dTable.Rows[j][13].ToString();
                                dataGridView2.Rows[i].Cells[14].Value = dTable.Rows[j][14].ToString();
                                dataGridView2.Rows[i].Cells[15].Value = dTable.Rows[j][15].ToString();
                                dataGridView2.Rows[i].Cells[16].Value = dTable.Rows[j][16].ToString();
                                dataGridView2.Rows[i].Cells[17].Value = dTable.Rows[j][17].ToString();
                                dataGridView2.Rows[i].Cells[18].Value = dTable.Rows[j][18].ToString();
                                dataGridView2.Rows[i].Cells[19].Value = dTable.Rows[j][19].ToString();
                                dataGridView2.Rows[i].Cells[20].Value = dTable.Rows[j][20].ToString();
                                dataGridView2.Rows[i].Cells[21].Value = dTable.Rows[j][21].ToString();
                                dataGridView2.Rows[i].Cells[22].Value = dTable.Rows[j][22]