epplus保存为流_.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("phoneNumber") != string.Empty).Select(x =>

new MyContact

{

firstName= x.Field("First Name"),

lastName = x.Field("Last Name"),

phoneNumber =x.Field("Phone Number"),

});

方式二、读取Excel 2003 文件Dictionary props = new Dictionary();

props["Provider"] = "Microsoft.Jet.OLEDB.4.0";

props["Data Source"] = repFile;

props["Extended Properties"] = "Excel 8.0";

StringBuilder sb = new StringBuilder();

foreach (KeyValuePair 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)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

使用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();

}