上一篇文章介绍了将datatable 内容导出到excel 文件，这里介绍如何将一个excel 文件读取出来，并保持到datatable 中，实际这样的应用场景也是经常遇到的。
这里继续使用了Microsoft.Office.Interop.Excel 类库。具体的一个示例代码如下：
/// <summary>
/// 读取excel 文件中的内容，并保存为datatable, 最后显示出来
/// </summary>
public static void ReadFromExcel()
{ try
{
string fileName = @"F:\excelTest\Items.xlsx";
Application app = new Microsoft.Office.Interop.Excel.Application();
// app.Visible = true;
Workbook wb = app.Workbooks.Open(fileName,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value
); // 定义datatable,用来保存excel 中读取的内容
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Columns.Add("Category");
dt.Columns.Add("Price"); string name;
string description;
string Category;
double price; foreach (Worksheet sheet in wb.Sheets)
{
Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
for (int i = ; i <= range.Rows.Count; i++)
{ // name 列取值
var cell1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, ];
name = cell1.Value; // description 列取值
var cell2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, ];
description = cell2.Value; //category 列取值
var cell3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, ];
Category = cell3.Value; //Price 取值
var cell4 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, ];
price = Convert.ToDouble(cell4.Value); dt.Rows.Add(name, description, Category, price);
} // 显示读取得到的excel 值
Console.WriteLine("The Excel Content:");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(item["Name"].ToString() + "\t" + item["Description"].ToString() + "\t" + item["Price"].ToString() + "\t" + item["Category"].ToString());
} }
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
}
}
如下是运行结果的截图：