C# Excel 操作
将Excel 文件导入到dataGridView中
1 /// Excel数据导入DataGridView 2 public void EcxelToDataGridView(string filePath, DataGridView dgv) 3 { 4 //根据路径打开一个Excel文件并将数据填充到DataSet中 5 //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,第一行是字段名 6 7 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; 8 9 OleDbConnection conn = new OleDbConnection(strConn); 10 conn.Open(); 11 string strExcel = ""; 12 OleDbDataAdapter myCommand = null; 13 DataSet ds = null; 14 strExcel = "select * from [sheet1$]"; 15 myCommand = new OleDbDataAdapter(strExcel, strConn); 16 ds = new DataSet(); 17 myCommand.Fill(ds, "table1"); 18 19 //conn.Close(); 20 //dgv.DataMember = "[sheet1$]"; 21 //dgv.DataSource = ds; 22 //return; 23 24 //根据DataGridView的列构造一个新的DataTable 25 System.Data.DataTable tb = new System.Data.DataTable(); 26 27 tb = ds.Tables["table1"]; 28 29 //foreach (DataGridViewColumn dgvc in dgv.Columns) 30 //{ 31 // if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell)) 32 // { 33 // DataColumn dc = new DataColumn(); 34 // dc.ColumnName = dgvc.DataPropertyName; 35 // //dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable 36 37 dgv.Columns.Clear(); 38 //在DataGridView中显示导入的数据 39 dgv.DataSource = tb; 40 41 dgv.DefaultCellStyle.BackColor = Color.AliceBlue; //单元格背景颜色 42 dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.LightSkyBlue; //奇数行的背景色 43 //datagridview backcolor 44 dgv.DefaultCellStyle.SelectionBackColor = Color.LimeGreen; //选中单元格颜色 45 dgv.DefaultCellStyle.SelectionForeColor = Color.Blue; //选中单元格内字体颜色 46 47 dgv.DefaultCellStyle.Font = new System.Drawing.Font("Arial", 9, FontStyle.Regular);//单元格内文本的字体设置 48 dgv.DefaultCellStyle.ForeColor = Color.Black; //单元格内字体颜色 49 dgv.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single; //列标题格式 50 dgv.ColumnHeadersVisible = true; //列标题可见 51 dgv.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Arial", 9, FontStyle.Bold); //列标题字体设置 52 dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.LimeGreen; //列标头背景色 53 dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Blue; 54 55 dgv.Columns[0].Width = 100; 56 dgv.Columns[1].Width = 100; 57 dgv.Columns[2].Width = 280; 58 59 dgv.RowHeadersVisible = false; 60 dgv.AllowUserToAddRows = false; 61 dgv.AllowUserToResizeColumns = false; 62 dgv.AllowUserToResizeRows = false; 63 //dgv.ReadOnly = true; 64 65 dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; 66 }
将dataGridView保存为Excel文件
1 /// <summary> 2 /// DataGridView数据保存为Excel 3 /// </summary> 4 private void ExportExc() 5 { 6 SaveFileDialog dg = new SaveFileDialog();//保存文件对话框，选择导出文件的存放位置 7 dg.Filter = "xls files(*.xls)|*.xls";//保存为xls格式 8 if (dg.ShowDialog() == DialogResult.OK) 9 { 10 string filepath = dg.FileName.ToString();//保存文件的路径 11 12 Microsoft.Office.Interop.Excel.Workbooks objBooks;//接口 workbooks 13 Microsoft.Office.Interop.Excel.Sheets objSheets;// 接口 sheets 14 Microsoft.Office.Interop.Excel._Worksheet objSheet;//接口 worksheet 15 excel = new Microsoft.Office.Interop.Excel.Application(); 16 objBooks = excel.Workbooks; 17 Object miss = System.Reflection.Missing.Value; 18 objBook = objBooks.Add(miss); 19 objSheets = objBook.Sheets; 20 objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets[1]; 21 try 22 { 23 24 if (dataGridView1.Rows.Count == 0) //没有数据的话就不往下执行 25 return; 26 27 28 excel.Visible = false; //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 29 30 for (int i = 0; i < dataGridView1.Columns.Count; i++) //生成Excel中列头名称 31 { 32 objSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; 33 } 34 35 for (int i = 0; i < dataGridView1.Rows.Count; i++) //把DataGridView当前页的数据保存在Excel中 36 { 37 for (int j = 0; j < dataGridView1.Columns.Count; j++) 38 { 39 if (dataGridView1[j, i].ValueType == typeof(string)) 40 { 41 objSheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); 42 } 43 else 44 { 45 objSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); 46 } 47 } 48 } 49 objBook.SaveCopyAs(filepath); 50 //设置禁止弹出保存和覆盖的询问提示框 51 excel.DisplayAlerts = false; 52 excel.AlertBeforeOverwriting = false; 53 54 //确保Excel进程关闭 55 objBooks.Close(); 56 excel.Workbooks.Close(); 57 excel.Quit(); 58 excel = null; 59 GC.Collect(); 60 MessageBox.Show("数据导出完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 61 if (System.IO.File.Exists(filepath)) 62 System.Diagnostics.Process.Start(filepath); //保存成功后打开此文件 63 64 } 65 catch (Exception ex) 66 { 67 MessageBox.Show(ex.Message, "错误提示"); 68 } 69 } 70 }
