C# Excel 操作

将Excel 文件导入到dataGridView中

/// Excel数据导入DataGridView
        public void EcxelToDataGridView(string filePath, DataGridView dgv)
        {
            //根据路径打开一个Excel文件并将数据填充到DataSet中
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名

            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select  * from   [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");

            //conn.Close();
            //dgv.DataMember = "[sheet1$]";
            //dgv.DataSource = ds;
            //return;

            //根据DataGridView的列构造一个新的DataTable
            System.Data.DataTable tb = new System.Data.DataTable();

            tb = ds.Tables["table1"];

            //foreach (DataGridViewColumn dgvc in dgv.Columns)
            //{
            //    if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
            //    {
            //        DataColumn dc = new DataColumn();
            //        dc.ColumnName = dgvc.DataPropertyName;
            //        //dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
          
            dgv.Columns.Clear();
            //在DataGridView中显示导入的数据
            dgv.DataSource = tb;

            dgv.DefaultCellStyle.BackColor = Color.AliceBlue; //单元格背景颜色
            dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSkyBlue; //奇数行的背景色
            //datagridview backcolor 
            dgv.DefaultCellStyle.SelectionBackColor = Color.LimeGreen; //选中单元格颜色
            dgv.DefaultCellStyle.SelectionForeColor = Color.Blue; //选中单元格内字体颜色

            dgv.DefaultCellStyle.Font = new System.Drawing.Font("Arial", 9, FontStyle.Regular);//单元格内文本的字体设置
            dgv.DefaultCellStyle.ForeColor = Color.Black; //单元格内字体颜色 
            dgv.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single; //列标题格式
            dgv.ColumnHeadersVisible = true;   //列标题可见
            dgv.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Arial", 9, FontStyle.Bold); //列标题字体设置
            dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.LimeGreen;  //列标头背景色
            dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Blue;

            dgv.Columns[0].Width = 100;
            dgv.Columns[1].Width = 100;
            dgv.Columns[2].Width = 280;

            dgv.RowHeadersVisible = false;
            dgv.AllowUserToAddRows = false;
            dgv.AllowUserToResizeColumns = false;
            dgv.AllowUserToResizeRows = false;
            //dgv.ReadOnly = true;

            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }

将dataGridView保存为Excel文件

 /// <summary>
        /// DataGridView数据保存为Excel
        /// </summary>
        private void ExportExc()
        {
            SaveFileDialog dg = new SaveFileDialog();//保存文件对话框，选择导出文件的存放位置
            dg.Filter = "xls files(*.xls)|*.xls";//保存为xls格式
            if (dg.ShowDialog() == DialogResult.OK)
            {
                string filepath = dg.FileName.ToString();//保存文件的路径

                Microsoft.Office.Interop.Excel.Workbooks objBooks;//接口 workbooks
                Microsoft.Office.Interop.Excel.Sheets objSheets;// 接口 sheets
                Microsoft.Office.Interop.Excel._Worksheet objSheet;//接口 worksheet
                excel = new Microsoft.Office.Interop.Excel.Application();
                objBooks = excel.Workbooks;
                Object miss = System.Reflection.Missing.Value;
                objBook = objBooks.Add(miss);
                objSheets = objBook.Sheets;
                objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1];
                try
                {

                    if (dataGridView1.Rows.Count == 0) //没有数据的话就不往下执行   
                        return;


                    excel.Visible = false; //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写   

                    for (int i = 0; i < dataGridView1.Columns.Count; i++) //生成Excel中列头名称   
                    {
                        objSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                    }

                    for (int i = 0; i < dataGridView1.Rows.Count; i++) //把DataGridView当前页的数据保存在Excel中   
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                        {
                            if (dataGridView1[j, i].ValueType == typeof(string))
                            {
                                objSheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                            }
                            else
                            {
                                objSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                            }
                        }
                    }
                    objBook.SaveCopyAs(filepath);
                    //设置禁止弹出保存和覆盖的询问提示框   
                    excel.DisplayAlerts = false;
                    excel.AlertBeforeOverwriting = false;

                    //确保Excel进程关闭   
                    objBooks.Close();
                    excel.Workbooks.Close();
                    excel.Quit();
                    excel = null;
                    GC.Collect();
                    MessageBox.Show("数据导出完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    if (System.IO.File.Exists(filepath))
                        System.Diagnostics.Process.Start(filepath); //保存成功后打开此文件

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "错误提示");
                }
            }
        }