上一篇文章介绍了将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:\excelTest\Items.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() + "\t" + item["Description"].ToString() + "\t" + item["Price"].ToString() + "\t" + item["Category"].ToString()); 75 } 76 77 } 78 } 79 catch (Exception ex) 80 { 81 Console.WriteLine(ex.StackTrace); 82 } 83 }
如下是运行结果的截图：
