C# 使用 OLEDB 数据提供程序读取 excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18511576/
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 file using OLEDB Data Provider
提问by Gajendra
I am using OLEDB Data Providerto read excel file, but the problem is that in excel sheet some cloumn has an invalid value for example instead of number string is there, When I read this invalid value I get an empty string instead of actual value.
我正在使用OLEDB 数据提供程序读取 excel 文件，但问题是在 excel 表中，某些 cloumn 具有无效值，例如存在无效值而不是数字字符串，当我读取此无效值时，我得到一个空字符串而不是实际值。
for above screenshot when i read value john getting empty string.
对于上面的屏幕截图，当我读取值 john 获取空字符串时。
So is there any way to read this invalid value?
那么有没有办法读取这个无效值呢？
Any help will be appreciated.
任何帮助将不胜感激。
The Codeis to read excel file
该代码是读取Excel文件
private DataTable ReadExcelFile(string sheetName, string path)
{
using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
string Import_FileName = path;
string fileExtension = Path.GetExtension(Import_FileName);
if (fileExtension == ".xls")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
if (fileExtension == ".xlsx")
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (OleDbCommand comm = new OleDbCommand())
{
comm.CommandText = "Select * from [" + sheetName + "$]";
comm.Connection = conn;
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.SelectCommand = comm;
da.Fill(dt);
return dt;
}
}
}
}
采纳答案by Yuriy Galanter
You need to set value for TypeGuessRowsRegistry key to 0, this way driver will set data type based on all column values instead of first 8 (default).
您需要将TypeGuessRows注册表项的值设置为0，这样驱动程序将根据所有列值而不是前 8（默认）设置数据类型。
The location of the key differs from version to version of driver, you can easily Google it based on your specific version. For example for Access Connectivity Engine 2007 it would be
密钥的位置因驱动程序版本而异，您可以根据您的特定版本轻松搜索。例如，对于 Access Connectivity Engine 2007，它将是
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office.0\Access Connectivity Engine\Engines\Excel
By the way, you do not need Jet to read XLS files, ACE is perfectly capable of this as well.
顺便说一句，您不需要Jet来读取XLS文件，ACE也完全有能力做到这一点。
回答by Adam
This worked for me
这对我有用
using (OleDbConnection conn = new OleDbConnection())
{
DataTable dt = new DataTable();
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
+ ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
using (OleDbCommand comm = new OleDbCommand())
{
comm.CommandText = "Select * from [" + sheetName + "$]";
comm.Connection = conn;
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
da.SelectCommand = comm;
da.Fill(dt);
return dt;
}
}
}
The MAXSCANROWS=0 overrides the registry default and scans all rows before determining types. IMEX=1 still needs to be included.
MAXSCANROWS=0 覆盖注册表默认值并在确定类型之前扫描所有行。IMEX=1 仍然需要包括在内。
For example, given this table:
例如，给定这个表：
Header | Header
------ | ------
Cell1 | 2456354
Cell2 | 2456354
Cell3 | 2456354
Cell4 | 2456354
Cell5 | 2456354
Cell6 | 2456354
Cell7 | 2456354
Cell8 | 2456354
Cell9 | A5341
The following connection strings will lose A5341
以下连接字符串将丢失 A5341
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
+ ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
+ ";Extended Properties='Excel 12.0 Xml;HDR=YES;MAXSCANROWS=0'"
But it works when it has both.
但是当两者兼而有之时它就起作用了。
