将Excel读入数据表失败 [英] Read Excel into datatable fails
问题描述
我正在尝试将Excel文件读入数据表。因此我找到了一个例子： http：// www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-ASP.Net-GridView-Control.aspx [ ^ ]。
这很好用。但是，当我想创建自己的控制台应用程序时，我很难做到正确。即使我将de asp.net-website（示例）中的代码复制到我的控制台应用程序中......
我这样做：
我打开一个控制台应用程序。我使用示例中的连接字符串创建一个App.config
<？ xml version = 1.0 encoding = utf-8 < span class =code-summarycomment>？ >
< 配置 >
< connectionStrings >
< 添加 名称 = Excel03ConString connectionString = Provider = Microsoft.Jet.OLEDB.4.0;数据源= {0};扩展属性='Excel 8.0; HDR = {1}' / >
< add 名称 = Excel07ConString connectionString = Provider = Microsoft.A CE.OLEDB.12.0;数据源= {0};扩展属性='Excel 8.0; HDR = {1}' / >
< / connectionStrings >
< / configuration >
然后我复制了代码
class 程序
{
static void Main（ string [] args）
{
尝试
{
string conStr = ;
string Extension = Path.GetExtension（ TEST.XLS）;
switch （扩展名）
{
case 。xls： // Excel 97-03
conStr = ConfigurationManager.ConnectionStrings [ Excel03ConString ]的ConnectionString。
break ;
case 。xlsx： // Excel 07
conStr = ConfigurationManager.ConnectionStrings [ Excel07ConString]。ConnectionString;
break ;
}
conStr = 字符串 .Format（conStr， @ D：\， no）;
OleDbConnection connExcel = new OleDbConnection（conStr）;
OleDbCommand cmdExcel = new OleDbCommand（）;
OleDbDataAdapter oda = new OleDbDataAdapter（）;
DataTable dt = new DataTable（）;
cmdExcel.Connection = connExcel;
// 获取First Sheet的名称
connExcel。打开（）;
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， null ）;
string SheetName = dtExcelSchema.Rows [ 0 ] [ TABLE_NAME]。ToString（）;
connExcel.Close（）;
// 从第一张表中读取数据
connExcel.Open （）;
cmdExcel.CommandText = SELECT * From [ + SheetName + ];
oda.SelectCommand = cmdExcel;
oda.Fill（dt）;
connExcel.Close（）;
foreach （ var dtLine in dt.Rows）
{
}
}
catch （Exception ex ）
{
throw ex;
}
}
但结果我总是得到这个错误：'Microsoft.Jet.OLEDB.4.0'提供商未在本地计算机上注册。当代码点击
connExcel.Open（）;
我做错了什么？ ： - /因为在示例（asp.net网站）中它在本地运行时有效...： - /
提前致谢！
Greetz
Cas
这个问题是已在这里回答。请看看
'' Microsoft.Jet.OLEDB.4.0'提供程序未在本地计算机上注册 [ ^ ]
Microsoft.Jet.OLEDB.4.0提供程序未在本地计算机上注册 [ ^ ]
'Microsoft.Jet.OLEDB.4.0'提供商不是reg在本地计算机上输入。 [ ^ ]
引用：您可以将Microsoft.ACE.OLEDB.12.0用于64位系统。您可以检查它是32位还是64位系统。如果32位然后使用.JET.OLEDB，则使用ACE.OLEDB。
希望，你会在那里找到解决方案。 ：）
但是如果它是一个32/64位的问题，为什么它适用于给定的例子，它也在本地运行。
当我尝试使用Google解决问题时，我发现了类似的解决方案，但没有一个能够解决问题。 ： - /
Hi,
I'm trying to read an Excel file into a datatabe. Therefore I found an example: http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-ASP.Net-GridView-Control.aspx[^].
This works great. But, when I want to create my own console application, I struggle to get it right. Even when I copy the code from de asp.net-website (example) into my console application...
So what I do:
I open a console app. I create an App.config with the connectionstrings like in the example
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings >
</configuration>
And then I copy the code
class Program
{
static void Main(string[] args)
{
try
{
string conStr = "";
string Extension = Path.GetExtension("test.xls");
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, @"D:\", "no");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
foreach (var dtLine in dt.Rows)
{
}
}
catch (Exception ex)
{
throw ex;
}
}
But as a result I always get this error: "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine." When the code hits the
connExcel.Open();
What am I doing wrong? :-/ Because in the example (asp.net website) it works when I run it locally... :-/
Thanks in advance!
Greetz
Cas
This question is already answered here. Please have a look
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine[^]
Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine[^]
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.[^]
Quote:You can use Microsoft.ACE.OLEDB.12.0 for 64 bit system. You can check is it 32 bit or 64 bit system. If 32 bit then use .JET.OLEDB else use ACE.OLEDB.
Hope, you'll find the solution there. :)
But if its a 32/64-bit issue, why does it work with the given example, it also runs locally.
I found solutions like that, when I tried to fix the problem by using Google, but none of them worked. :-/
这篇关于将Excel读入数据表失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
