.NET[C#]如何读取Excel文件/表格数据文件，有哪些方式可以实现？

.NET[C#]如何读取Excel文件/表格数据文件，有哪些方式可以实现？本文来归纳一下，供大家参考。

方式一、使用OleDb

首先、将Excel表格数据通过 OleDbDataAdapter 读取到 DataTable 中：

var fileName = string.Format("{0}\\excel.xlsx", Directory.GetCurrentDirectory());
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

DataTable data = ds.Tables["anyNameHere"];

其中的：sheet1$替换成自己的sheet

接着、使用LINQ将 DataTable 中的表格数据转换成对象集合：

var query = data.Where(x => x.Field<string>("phoneNumber") != string.Empty).Select(x =>
                new MyContact
                    {
                        firstName= x.Field<string>("First Name"),
                        lastName = x.Field<string>("Last Name"),
                        phoneNumber =x.Field<string>("Phone Number"),
                    });

方式二、读取Excel 2003 文件

Dictionary<string, string> props = new Dictionary<string, string>();
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Data Source"] = repFile;
props["Extended Properties"] = "Excel 8.0";

StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
    sb.Append(prop.Key);
    sb.Append('=');
    sb.Append(prop.Value);
    sb.Append(';');
}
string properties = sb.ToString();

using (OleDbConnection conn = new OleDbConnection(properties))
{
    conn.Open();
    DataSet ds = new DataSet();
    string columns = String.Join(",", columnNames.ToArray());
    using (OleDbDataAdapter da = new OleDbDataAdapter(
        "SELECT " + columns + " FROM [" + worksheet + "$]", conn))
    {
        DataTable dt = new DataTable(tableName);
        da.Fill(dt);
        ds.Tables.Add(dt);
    }
}

方式三、使用第三库(ExcelDataReader)

ExcelDataReader的托管地址：https://github.com/ExcelDataReader/ExcelDataReader

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

方式四、使用EPPlus

EPPlus托管地址：https://github.com/JanKallman/EPPlus

使用Nuget安装，然后引入命名空间：

using OfficeOpenXml

简单的使用：

using (ExcelPackage p = new ExcelPackage())
{
    using (FileStream stream = new FileStream("excel.xlsx", FileMode.Open))
    {
        p.Load(stream);

        ExcelWorksheet ws = p.Workbook.Worksheets["Sample Sheet"];
        int rowIndex = 2;

        string text = ws.Cells[rowIndex, 1].Value.ToString(); //读取值
        MessageBox.Show("Text in [" + rowIndex + ",1]=" + text);

        string comment = ws.Comments[0].Text; // 读取批注
        MessageBox.Show("Comments = " + comment);

        string pictureName = ws.Drawings[0].Name;// 读取图片
        MessageBox.Show("Picture = " + pictureName);
    }
}

方式五、文件流读取

引入命名空间：

using System.Text;
using System.IO;

读取操作：

var fs = new FileStream("d:\\Customer.csv", FileMode.Open, FileAccess.Read, FileShare.None);
var sr = new StreamReader(fs, System.Text.Encoding.GetEncoding(936));
var str = "";
var s = Console.ReadLine();
while (str != null)
{    str = sr.ReadLine();
     var xu = new String[2];
     xu = str.Split(',');
     var ser = xu[0]; 
     var dse = xu[1];                
     if (ser == s)
     { 
         Console.WriteLine(dse);break;
     }
}   
sr.Close();

方式六、使用COM组件(不推荐)

此方式需要在应用程序的计算机上安装Office组件，实现代码：

private void OpenExcel(string strFileName)
{
    object missing = System.Reflection.Missing.Value;
    Application excel = new Application();
    if (excel == null)
    {
        //处理异常
    }
    else
    {
        excel.Visible = false; excel.UserControl = true;
        // 以只读的形式打开EXCEL文件
        Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
         missing, missing, missing, true, missing, missing, missing, missing, missing);
        //取得第一个工作薄
        Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);


        //取得总记录行数   (包括标题列)
        int rowsint = ws.UsedRange.Cells.Rows.Count; 


        //取得数据范围区域 (不包括标题列) 
        Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);  


        Range rng2 = ws.Cells.get_Range("K2", "K" + rowsint);
        object[,] arryItem= (object[,])rng1.Value2;   //get range's value
        object[,] arryCus = (object[,])rng2.Value2;   
        //将新值赋给一个数组
        string[,] arry = new string[rowsint-1, 2];
        for (int i = 1; i <= rowsint-1; i++)
        {
            //Item_Code列
            arry[i - 1, 0] =arryItem[i, 1].ToString();
            //Customer_Name列
            arry[i - 1, 1] = arryCus[i, 1].ToString();
        }
        Response.Write(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]);
    }
     excel.Quit(); excel = null;
    Process[] procs = Process.GetProcessesByName("excel");


    foreach (Process pro in procs)
    {
        pro.Kill();
    }
    GC.Collect();
}