读取excel 文件到datatable

上一篇文章介绍了将datatable 内容导出到excel 文件，这里介绍如何将一个excel 文件读取出来，并保持到datatable 中，实际这样的应用场景也是经常遇到的。
这里继续使用了Microsoft.Office.Interop.Excel 类库。具体的一个示例代码如下：
 1         /// <summary>
 2         /// 读取excel 文件中的内容，并保存为datatable, 最后显示出来
 3         /// </summary>
 4         public static void ReadFromExcel()
 5         {
 6 
 7             try
 8             {
 9                 string fileName = @"F:excelTestItems.xlsx";
10                 Application app = new Microsoft.Office.Interop.Excel.Application();
11                 // app.Visible = true;
12                 Workbook wb = app.Workbooks.Open(fileName,
13                     System.Reflection.Missing.Value,
14                     System.Reflection.Missing.Value,
15                     System.Reflection.Missing.Value,
16                     System.Reflection.Missing.Value,
17                     System.Reflection.Missing.Value,
18                     System.Reflection.Missing.Value,
19                     System.Reflection.Missing.Value,
20                     System.Reflection.Missing.Value,
21                     System.Reflection.Missing.Value,
22                     System.Reflection.Missing.Value,
23                     System.Reflection.Missing.Value,
24                     System.Reflection.Missing.Value,
25                     System.Reflection.Missing.Value,
26                     System.Reflection.Missing.Value
27                     );
28 
29 
30               // 定义datatable,用来保存excel 中读取的内容
31                 System.Data.DataTable dt = new System.Data.DataTable();
32                 dt.Columns.Add("Name");
33                 dt.Columns.Add("Description");
34                 dt.Columns.Add("Category");
35                 dt.Columns.Add("Price");
36 
37                 string name;
38                 string description;
39                 string Category;
40                 double price;
41 
42 
43                 foreach (Worksheet sheet in wb.Sheets)
44                 {
45                     Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
46                     for (int i = 2; i <= range.Rows.Count; i++)
47                     {
48 
49 
50                         // name 列取值
51                         var cell1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 1];
52                         name = cell1.Value;
53 
54                         // description 列取值
55                         var cell2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 2];
56                         description = cell2.Value;
57 
58                         //category 列取值
59                         var cell3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 3];
60                         Category = cell3.Value;
61 
62                         //Price 取值
63                         var cell4 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 4];
64                         price = Convert.ToDouble(cell4.Value);
65 
66                         dt.Rows.Add(name, description, Category, price);
67                     }
68 
69 
70                     // 显示读取得到的excel 值
71                     Console.WriteLine("The Excel Content:");
72                     foreach (DataRow item in dt.Rows)
73                     {
74                         Console.WriteLine(item["Name"].ToString() + "	" + item["Description"].ToString() + "	" + item["Price"].ToString() + "	" + item["Category"].ToString());
75                     }
76 
77                 }
78             }
79             catch (Exception ex)
80             {
81                 Console.WriteLine(ex.StackTrace);
82             }
83         }
如下是运行结果的截图：