C# 操作Excel进程与获取Excel表格数据

最近做了一下Excel相关的程序，学到了不少东西，在此感谢博客园中无私奉献的园友们。很多资料和信息都是从博客园获取的！

1、完美关闭Excel进程

思路：只把EXCEL进程kill是不够的，还必须把全局变量中的所有与Excel操作相关的excel变量赋值为null才可以！

代码如下：

View Code

 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         }