C# 操作Excel进程与获取Excel表格数据
最近做了一下Excel相关的程序,学到了不少东西,在此感谢博客园中无私奉献的园友们。很多资料和信息都是从博客园获取的!
1、完美关闭Excel进程
思路:只把EXCEL进程kill是不够的,还必须把全局变量中的所有与Excel操作相关的excel变量赋值为null才可以!
代码如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 private void QuitExcelApp() 2 { 3 if (excelApp != null)//excelApp全局变量 4 { 5 //关闭EXCEl进程,只把EXCEL进程kill是不够的, 6 //还必须把全局变量中的所有与Excel操作相关的excel变量 7 //赋值为null才可以 8 excelApp.DisplayAlerts = false;//不显示提示框 9 excelApp.Workbooks.Close(); 10 excelApp.Quit(); 11 KillSpecialExcel(excelApp); 12 excelApp = null; 13 workbook1 = null; 14 workSheetTemp = null; 15 } 16 } 17 [DllImport("user32.dll", SetLastError = true)] 18 static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); 19 20 public void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel) 21 { 22 try 23 { 24 if (m_objExcel != null) 25 { 26 int lpdwProcessId; 27 GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); 28 Process p = System.Diagnostics.Process.GetProcessById(lpdwProcessId); 29 if (!p.CloseMainWindow()) 30 { 31 p.Kill(); 32 } 33 } 34 } 35 catch (Exception ex) 36 { 37 Console.WriteLine("Delete Excel Process Error:" + ex.Message); 38 } 39 }
2、获取Excel表单数
1 try 2 { 3 if (tempListBox.Items.Count != 0)//清空原有显示的表单 4 { 5 tempListBox.Items.Clear(); 6 } 7 foreach (Worksheet wsheet in workbook1.Worksheets) 8 { 9 tempListBox.Items.Add(wsheet.Name); 10 } 11 } 12 catch (Exception ex) 13 { 14 MessageBox.Show(ex.ToString(), "信息提示"); 15 }
3、打开Excel工作簿
1 excelApp = new Microsoft.Office.Interop.Excel.Application(); 2 3 excelApp.DisplayAlerts = false;//不显示提示框 4 workbook1 = excelApp.Workbooks.Open(strFileName, Type.Missing, Type.Missing, Type.Missing, 5 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 6 Type.Missing, Type.Missing, Type.Missing, Type.Missing);
4、获取Excel中的表格,注意当Excel和C#自带的链接数据库获取表结合使用时,一定要先关闭Excel进程,再链接!
1 private void OpenExcelTabelByName(string tabelName) 2 { 3 try 4 { 5 string ss = tabelName; 6 //关闭Excel进程 7 QuitAndOpenExcelApp(); 8 string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName+ ";Extended Properties='Excel 8.0;HDR=YES; IMEX=1'"; 9 OleDbConnection con = new OleDbConnection(connStr); 10 con.Open(); 11 12 string strsql = "select * from [" + ss + "$]"; 13 14 OleDbDataAdapter oda = new OleDbDataAdapter(strsql, con); 15 DataSet ds = new DataSet(); 16 oda.Fill(ds); 17 daGridViewTem.DataSource = ds.Tables[0]; 18 for (int i = 0; i < daGridViewTem.Columns.Count; i++) 19 { 20 daGridViewTem.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; 21 } 22 oda.Dispose(); 23 con.Close(); 24 //重新新建Excel进程 25 QuitAndOpenExcelApp(); 26 workSheetTemp = (Worksheet)workbook1.Worksheets[ss]; 27 } 28 catch (Exception ex) 29 { 30 MessageBox.Show(ex.Message, "获取Excel文件失败"); 31 return; 32 } 33 }