读取excel 文件到datatable

上一篇文章介绍了将datatable 内容导出到excel 文件，这里介绍如何将一个excel 文件读取出来，并保持到datatable 中，实际这样的应用场景也是经常遇到的。

这里继续使用了Microsoft.Office.Interop.Excel 类库。具体的一个示例代码如下：

        /// <summary>
        /// 读取excel 文件中的内容，并保存为datatable, 最后显示出来
        /// </summary>
        public static void ReadFromExcel()
        {

            try
            {
                string fileName = @"F:excelTestItems.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 = 2; i <= range.Rows.Count; i++)
                    {


                        // name 列取值
                        var cell1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 1];
                        name = cell1.Value;

                        // description 列取值
                        var cell2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 2];
                        description = cell2.Value;

                        //category 列取值
                        var cell3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 3];
                        Category = cell3.Value;

                        //Price 取值
                        var cell4 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[i, 4];
                        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() + "	" + item["Description"].ToString() + "	" + item["Price"].ToString() + "	" + item["Category"].ToString());
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
            }
        }

  如下是运行结果的截图：