如何使用c#从excel文件中读取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15793442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read data from excel file using c#
提问by TutuGeorge
My application needs to read data from an excel file. I am using .Net and c# for development. I cannot install MS office in the system. Because of that the my application fails to read excel file and throws an error while loading the dll for excel.
我的应用程序需要从 excel 文件中读取数据。我正在使用 .Net 和 c# 进行开发。我无法在系统中安装 MS Office。因此，我的应用程序无法读取 excel 文件并在为 excel 加载 dll 时引发错误。
How can i access excel file in my application in a system where ms office is not installed?
如何在未安装 ms office 的系统中访问我的应用程序中的 excel 文件？
采纳答案by Steve
There is the option to use OleDBand use the Excel sheets like datatables in a database...
可以选择使用OleDB和使用 Excel 工作表，如数据库中的数据表......
Just an example.....
只是一个例子.....
string con =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";
using(OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
using(OleDbDataReader dr = command.ExecuteReader())
{
while(dr.Read())
{
var row1Col0 = dr[0];
Console.WriteLine(row1Col0);
}
}
}
This example use the Microsoft.Jet.OleDb.4.0provider to open and read the Excel file. However, if the file is of type xlsx (from Excel 2007 and later), then you need to download the Microsoft Access Database Enginecomponents and install it on the target machine.
此示例使用Microsoft.Jet.OleDb.4.0提供程序打开并读取 Excel 文件。但是，如果文件类型为 xlsx（来自 Excel 2007 及更高版本），则您需要下载Microsoft Access 数据库引擎组件并将其安装在目标计算机上。
The provider is called Microsoft.ACE.OLEDB.12.0;. Pay attention to the fact that there are two versions of this component, one for 32bit and one for 64bit. Choose the appropriate one for the bitness of your application and what Office version is installed (if any). There are a lot of quirks to have that driver correctly working for your application. See this question for example.
提供者称为Microsoft.ACE.OLEDB.12.0;。注意这个组件有两个版本，一个是32bit的，一个是64bit的。根据您的应用程序的位数和安装的 Office 版本（如果有）选择合适的版本。要使该驱动程序为您的应用程序正常工作，有很多怪癖。例如，请参阅此问题。
Of course you don't need Office installed on the target machine.
当然你不需要在目标机器上安装Office。
While this approach has some merits, I think you should pay particular attention to the link signaled by a comment in your question Reading excel files from C#. There are some problems regarding the correct interpretation of the data types and when the length of data, present in a single excel cell, is longer than 255 characters
虽然这种方法有一些优点，但我认为您应该特别注意问题Read excel files from C# 中的注释所表示的链接。关于正确解释数据类型以及当单个 Excel 单元格中的数据长度超过 255 个字符时存在一些问题
回答by Robert Harvey
Save the Excel file to CSV, and read the resulting file with C# using a CSV reader library like FileHelpers.
将 Excel 文件保存为 CSV，然后使用 C# 使用FileHelpers等 CSV 读取器库读取生成的文件。
回答by petko_stankoski
Convert the excel file to .csvfile (comma separated valuefile) and now you can easily be able to read it.
将 excel 文件转换为.csv文件（逗号分隔值文件），现在您可以轻松阅读它。
回答by Obama
CSharpJExcel for reading Excel 97-2003 files (xls): http://sourceforge.net/projects/jexcelapi/
用于读取 Excel 97-2003 文件 (xls) 的 CSharpJExcel：http: //sourceforge.net/projects/jexcelapi/
and ExcelPackage for reading Excel 2007/2010 files (Office Open XML format, xlsx): http://excelpackage.codeplex.com/
和 ExcelPackage 用于读取 Excel 2007/2010 文件（Office Open XML 格式，xlsx）：http://excelpackage.codeplex.com/
and ExcelDataReader, that seems to have the ability to handle both formats: https://github.com/ExcelDataReader/ExcelDataReader
和 ExcelDataReader，似乎有能力处理这两种格式：https: //github.com/ExcelDataReader/ExcelDataReader
Good luck!
祝你好运！
回答by Mark Kram
I don't have a machine available to test this but it should work. First you will probably need to install the either the 2007 Office System Driver: Data Connectivity Componentsor the Microsoft Access Database Engine 2010 Redistributable. Then try the following code, note you will need to change the name of the Sheet in the Select statement below to match sheetname in your excel file:
我没有可用于测试的机器，但它应该可以工作。首先，您可能需要安装2007 Office System Driver: Data Connectivity Components或Microsoft Access Database Engine 2010 Redistributable。然后尝试以下代码，注意您需要在下面的 Select 语句中更改 Sheet 的名称以匹配 Excel 文件中的 sheetname：
using System.Data;
using System.Data.OleDb;
namespace Data_Migration_Process_Creator
{
class Class1
{
private DataTable GetDataTable(string sql, string connectionString)
{
DataTable dt = null;
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
return dt;
}
}
}
}
private void GetExcel()
{
string fullPathToExcel = "<Path to Excel file>"; //ie C:\Temp\YourExcel.xls
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel);
DataTable dt = GetDataTable("SELECT * from [SheetName$]", connString);
foreach (DataRow dr in dt.Rows)
{
//Do what you need to do with your data here
}
}
}
}
Note: I don't have an environment to test this in (One with Office installed) so I can't say if it will work in your environment or not but I don't see why it shouldn't work.
注意：我没有一个环境可以在（安装了 Office 的一个）中进行测试，所以我不能说它是否可以在您的环境中工作，但我不明白为什么它不应该工作。
