从 C# 读取 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15828/
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
Reading Excel files from C#
提问by dbkk
Is there a free or open source library to read Excel files (.xls) directly from a C# program?
是否有免费或开源库可以直接从 C# 程序读取 Excel 文件 (.xls)？
It does not need to be too fancy, just to select a worksheet and read the data as strings. So far, I've been using Export to Unicode text function of Excel, and parsing the resulting (tab-delimited) file, but I'd like to eliminate the manual step.
不需要太花哨，只需选择一个工作表并将数据作为字符串读取即可。到目前为止，我一直在使用 Excel 的导出到 Unicode 文本功能，并解析生成的（制表符分隔的）文件，但我想消除手动步骤。
采纳答案by Robin Robinson
var fileName = string.Format("{0}\fileNameHere", 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 [workSheetNameHere$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "anyNameHere");
DataTable data = ds.Tables["anyNameHere"];
This is what I usually use. It is a little different because I usually stick a AsEnumerable() at the edit of the tables:
这是我通常使用的。这有点不同，因为我通常在编辑表格时使用 AsEnumerable()：
var data = ds.Tables["anyNameHere"].AsEnumerable();
as this lets me use LINQ to search and build structs from the fields.
因为这让我可以使用 LINQ 从字段中搜索和构建结构。
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"),
});
回答by Leon Bambrick
you could write an excel spreadsheet that loads a given excel spreadsheet and saves it as csv (rather than doing it manually).
您可以编写一个 excel 电子表格来加载给定的 excel 电子表格并将其保存为 csv（而不是手动执行）。
then you could automate that from c#.
那么你可以从 c# 自动化。
and once its in csv, the c# program can grok that.
一旦它在 csv 中，c# 程序就可以理解了。
(also, if someone asks you to program in excel, it's best to pretend you don't know how)
（另外，如果有人让你用excel编程，最好假装你不知道怎么做）
(edit: ah yes, rob and ryan are both right)
（编辑：啊，是的，rob 和 ryan 都是对的）
回答by Lars M?hlum
I know that people have been making an Excel "extension" for this purpose.
You more or less make a button in Excel that says "Export to Program X", and then export and send off the data in a format the program can read.
我知道人们为此目的一直在制作 Excel“扩展”。
您或多或少在 Excel 中创建一个按钮，上面写着“导出到程序 X”，然后以程序可以读取的格式导出和发送数据。
http://msdn.microsoft.com/en-us/library/ms186213.aspxshould be a good place to start.
http://msdn.microsoft.com/en-us/library/ms186213.aspx应该是一个不错的起点。
Good luck
祝你好运
回答by Rob Cooper
Forgive me if I am off-base here, but isn't this what the Office PIA'sare for?
如果我不在基地，请原谅我，但这不是办公室 PIA 的用途吗？
回答by Ryan Farley
If it is just simple data contained in the Excel file you can read the data via ADO.NET. See the connection strings listed here:
如果只是 Excel 文件中包含的简单数据，您可以通过 ADO.NET 读取数据。请参阅此处列出的连接字符串：
http://www.connectionstrings.com/?carrier=excel2007or http://www.connectionstrings.com/?carrier=excel
http://www.connectionstrings.com/?carrier=excel2007或 http://www.connectionstrings.com/?carrier=excel
-Ryan
-瑞安
Update: then you can just read the worksheet via something like select * from [Sheet1$]
更新：然后您可以通过类似的方式阅读工作表 select * from [Sheet1$]
回答by xanadont
Not free, but with the latest Office there's a verynice automation .Net API. (there has been an API for a long while but was nasty COM) You can do everything you want / need in code all while the Office app remains a hidden background process.
不是免费的，但是最新的 Office 有一个非常好的自动化 .Net API。（已经有一个 API 很长一段时间了，但是很讨厌 COM）你可以在代码中做你想要/需要的一切，而 Office 应用程序仍然是一个隐藏的后台进程。
回答by Christian Hagelid
Just did a quick demo project that required managing some excel files. The .NET component from GemBox software was adequate for my needs. It has a free version with a few limitations.
刚刚做了一个需要管理一些excel文件的快速演示项目。GemBox 软件中的 .NET 组件足以满足我的需求。它有一个有一些限制的免费版本。
回答by hitec
Here's some code I wrote in C# using .NET 1.1 a few years ago. Not sure if this would be exactly what you need (and may not be my best code :)).
这是我几年前使用 .NET 1.1 用 C# 编写的一些代码。不确定这是否正是您所需要的（并且可能不是我最好的代码:)）。
using System;
using System.Data;
using System.Data.OleDb;
namespace ExportExcelToAccess
{
/// <summary>
/// Summary description for ExcelHelper.
/// </summary>
public sealed class ExcelHelper
{
private const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FILENAME>;Extended Properties=\"Excel 8.0;HDR=Yes;\";";
public static DataTable GetDataTableFromExcelFile(string fullFileName, ref string sheetName)
{
OleDbConnection objConnection = new OleDbConnection();
objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
DataSet dsImport = new DataSet();
try
{
objConnection.Open();
DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if( (null == dtSchema) || ( dtSchema.Rows.Count <= 0 ) )
{
//raise exception if needed
}
if( (null != sheetName) && (0 != sheetName.Length))
{
if( !CheckIfSheetNameExists(sheetName, dtSchema) )
{
//raise exception if needed
}
}
else
{
//Reading the first sheet name from the Excel file.
sheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
}
new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConnection ).Fill(dsImport);
}
catch (Exception)
{
//raise exception if needed
}
finally
{
// Clean up.
if(objConnection != null)
{
objConnection.Close();
objConnection.Dispose();
}
}
return dsImport.Tables[0];
#region Commented code for importing data from CSV file.
// string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + System.IO.Path.GetDirectoryName(fullFileName) +";" +"Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
//
// System.Data.OleDb.OleDbConnection conText = new System.Data.OleDb.OleDbConnection(strConnectionString);
// new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(fullFileName).Replace(".", "#"), conText).Fill(dsImport);
// return dsImport.Tables[0];
#endregion
}
/// <summary>
/// This method checks if the user entered sheetName exists in the Schema Table
/// </summary>
/// <param name="sheetName">Sheet name to be verified</param>
/// <param name="dtSchema">schema table </param>
private static bool CheckIfSheetNameExists(string sheetName, DataTable dtSchema)
{
foreach(DataRow dataRow in dtSchema.Rows)
{
if( sheetName == dataRow["TABLE_NAME"].ToString() )
{
return true;
}
}
return false;
}
}
}
回答by Ian Nelson
The ADO.NET approach is quick and easy, but it has a few quirks which you should be aware of, especially regarding how DataTypes are handled.
ADO.NET 方法既快速又简单，但它有一些您应该注意的怪癖，尤其是关于如何处理数据类型。
This excellent article will help you avoid some common pitfalls: http://blog.lab49.com/archives/196
这篇优秀的文章将帮助您避免一些常见的陷阱：http: //blog.lab49.com/archives/196
回答by Carl Seleborg
I did a lot of reading from Excel files in C# a while ago, and we used two approaches:
前段时间我在 C# 中阅读了大量 Excel 文件，我们使用了两种方法：
- The COM API, where you access Excel's objects directly and manipulate them through methods and properties
- The ODBC driver that allows to use Excel like a database.
- COM API，您可以在其中直接访问 Excel 的对象并通过方法和属性操作它们
- 允许像使用数据库一样使用 Excel 的 ODBC 驱动程序。
The latter approach was muchfaster: reading a big table with 20 columns and 200 lines would take 30 seconds via COM, and half a second via ODBC. So I would recommend the database approach if all you need is the data.
后一种方法要快得多：通过 COM 读取一个包含 20 列和 200 行的大表需要 30 秒，通过 ODBC 需要半秒。因此，如果您只需要数据，我会推荐数据库方法。
Cheers,
干杯，
Carl
卡尔
