读取Excel文件（.xls）：未指定的错误 [英] Reading Excel File(.xls): Unspecified Error
问题描述
Hello All，
我有一个应用程序读取excel文件（.xls）但偶尔会抛出错误：未指定错误。
我使用 OleDbConnection 打开连接。
OleDbCommand 执行查询。并且 DbDataReader 来读取行。
我的程序逻辑是这样的：
1.打开excel文件
2.读取每一行
3.验证每一列 - 某些验证可能需要再次打开连接
4.将其保存到没有错误发生时的数据库
当我上传一个小文件（少于70行）时，错误永远不会被抛出。
但是当我上传一个大文件（71行及以上）错误显示。
这是错误发生的地方：
Hello All,
I have an application that reads an excel file(.xls) but occasionally an error is thrown: Unspecified Error.
I use an OleDbConnection to open a connection.
OleDbCommand to execute a query. And DbDataReader to read the rows.
My program logic is like this:
1. Open the excel file
2. Read each row
3. Validate each column - some validation may require opening the connection again
4. Save it to the database when no errors occur
When I upload a small file(less than 70 rows) the error is never thrown.
But when I upload a large file(71 rows and above)the error shows.
This is where the error occur:
string myconnection1 = @"provider=Microsoft.Jet.OLEDB.4.0; data source=" + _excelFile + ";Extended Properties='Excel 8.0;IMEX=1';";
OleDbConnection Connection1 = new OleDbConnection(myconnection1);
OleDbCommand mycommand1 = new OleDbCommand("Select * FROM [MASTERLIST FORMAT$] where F6 = '" + _insured.StrDepLname + "' and F7 = '" + _insured.StrDepFname + "' and F8 = '" + _insured.StrDepMname + "' ", Connection1);
Connection1.Open();
DbDataReader dr1 = mycommand1.ExecuteReader();
while (dr1.Read())
{
string civilStatus = dr1[9].ToString();
if (civilStatus == "Single")
{
error = "" + _insured.StrDepLname + " " + _insured.StrDepFname + " is set to SINGLE Civil Status but has Spouse";
}
}
我的一些代码中也会出现打开连接并读取excel文件的情况。
我的验证是在一个循环语句中，像这样的代码可能会出现，我想每行8次。
请帮助我。
先谢谢。
It also occur in some of my codes that opens the connection and read the excel file.
My validation is inside a loop statement and codes like this may occur a, I think 8 times each row.
Please help me.
Thanks in Advance.
推荐答案
其中F6 ='+ _insured.StrDepLname +'和F7 ='+ _insured。 StrDepFname +'和F8 ='+ _insured.StrDepMname +'，Connection1）;
Connection1.Open（）;
DbDataReader dr1 = mycommand1.ExecuteReader（）;
while（ dr1.Read（））
{
string civilStatus = dr1 [9] .ToString（）;
if（civilStatus ==Single）
{
error =+ _insured.StrDepLname ++ _insured.StrDepFname +设置为SINGLE Civil Status但有配偶;
}
}
where F6 = '" + _insured.StrDepLname + "' and F7 = '" + _insured.StrDepFname + "' and F8 = '" + _insured.StrDepMname + "' ", Connection1); Connection1.Open(); DbDataReader dr1 = mycommand1.ExecuteReader(); while (dr1.Read()) { string civilStatus = dr1[9].ToString(); if (civilStatus == "Single") { error = "" + _insured.StrDepLname + " " + _insured.StrDepFname + " is set to SINGLE Civil Status but has Spouse"; } }
它也出现在我的一些代码中，它打开连接并读取excel文件。
我的验证在里面循环语句和这样的代码可能会出现，我想每行8次。
请帮助我。
先谢谢。
It also occur in some of my codes that opens the connection and read the excel file.
My validation is inside a loop statement and codes like this may occur a, I think 8 times each row.
Please help me.
Thanks in Advance.
好的，你发布的代码看起来并不太糟糕（除了我提到的SQL注入攻击警告）。
所以，首先要做的事情。
您是否已经隔离了该代码，并且在没有其他程序的情况下尝试了它，只是为了消除任何其他因素？
多少行你期望它回归吗？多少列？这些不应该是相关的，但在这个阶段很难说。
是否存在内部异常？
究竟是哪条线除外？
输入（_insured.StrDepFname ...）也来自excel文件。是的，它受到sql注入，但即使值正确，错误仍然会发生。我还没有真正检查错误发生的行是什么，因为它会在抛出异常之前循环太多时间。即使输入都是相同的，仍然会在循环的第一次执行中发生错误，但是在n？次循环中的语句已经执行了。是吗因为在OleDb重新连接太多次了？
啊！你在循环中执行上面的代码吗？
试试这个：使用 c>块：
OK, the code you have posted doesn't look too bad (apart from the SQL Injection Attack caveat I mentioned).
So, first things first.
Have you isolated that code, and tried it without the rest of your program, just to eliminate any other factors?
How many rows do you expect it to return? How many columns? These shouldn't be relevant, but at this stage it is difficult to tell.
Is there an inner exception?
Exactly which line is it excepting on?
"The inputs(_insured.StrDepFname ...) is from the excel file too. Yes, it is subject to sql injections, but even if the values is correct the error still occur. I haven't really checked what line the error occur because it loops too many time before an exception is thrown. Even if the inputs are all the same, still the error occur not in the first execution of the loop but on the n? times the statements inside the loop had already been executed. Is it because of reconnecting in OleDb too many times?"
Ah! You do the above code in a loop?
Try this: put each of your OleDbConnection and OleDbCommand objects into ausingblock:
using (OleDbConnection Connection1 = new OleDbConnection(myconnection1))
{
using (OleDbCommand mycommand1 = new OleDbCommand("Select * FROM [MASTERLIST FORMAT
其中F6 ='+ _insured.StrDepLname +'和F7 ='+ _insured.StrDepFname +'和F8 ='+ _insured.StrDepMname +' ，Connection1））
{
Connection1.Open（）;
...
}
}
where F6 = '" + _insured.StrDepLname + "' and F7 = '" + _insured.StrDepFname + "' and F8 = '" + _insured.StrDepMname + "' ", Connection1)) { Connection1.Open(); ... } }
并确保其他所有这些也是如此。我认为你的问题会神奇地消失......
如果是这样，那是因为连接是稀缺资源，在垃圾收集器可以处理之前就已经用完了他们。 使用块会在你完成后强制立即进行Dispose，这样就可以立即重新使用它。
And make sure that all your other such are as well. I think your problem will magically disappear...
If it does, it is because the connections are scarce resources, which are running out well before the Garbage collector can Dispose them. The using block forces an immediate Dispose when you are finished with them so they cfan be re-used immediately.
这篇关于读取Excel文件（.xls）：未指定的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
