C#/ASP.NET Oledb - MS Excel 读取“未指定错误”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/472079/
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
C#/ASP.NET Oledb - MS Excel read "Unspecified error"
提问by
We have a C#/ASP.NET (2.0) application running on IIS 6 on Windows Server 2003 Enterprise Edition. This application reads Excel files using OleDb, but there are instances when we get an "Unspecified Error" exception thrown from within the application.
我们有一个 C#/ASP.NET (2.0) 应用程序在 Windows Server 2003 企业版上的 IIS 6 上运行。此应用程序使用 OleDb 读取 Excel 文件，但有时我们会收到从应用程序内部抛出的“未指定错误”异常。
The file is stored in the temporary directory by our file upload code before opening. Since we have anonymous access enabled in IIS and since we also use impersonation in web.config, the folder C:\Windows\Temp\ has the proper permissions for the Internet Guest User Account (IUSR_[MachineName]) to be able to create, modify and delete files there.
文件在打开之前由我们的文件上传代码存储在临时目录中。由于我们在 IIS 中启用了匿名访问并且我们还在 web.config 中使用了模拟，文件夹 C:\Windows\Temp\ 具有 Internet 来宾用户帐户 (IUSR_[MachineName]) 的适当权限，可以创建，在那里修改和删除文件。
OleDb connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Windows\Temp\tmp123.tmp.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"
OleDb 连接字符串：
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Windows\Temp\tmp123.tmp.xls;
扩展属性="Excel 8.0;HDR=Yes;IMEX=1;"
[The "Data Source" attribute above would change for every file.]
[上面的“数据源”属性会因每个文件而改变。]
The stack trace of the exception is:
System.Exception: FileParsingFailed ---> System.Data.OleDb.OleDbException:
Unspecified error at
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr,
OleDbConnection connection) at
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options,
Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup) at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection) at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory) at
System.Data.OleDb.OleDbConnection.Open()
Workaround:
So far, the only workaround we could come up with was to do an iisreset (we also have application pool recycling configured to happen once everyday in IIS, but it doesn't seem to help since the issue sometimes persists over several consecutive days). While this is not a good thing to do, what makes it worse is that we have other applications on the same web site that would get impacted whenever we reset IIS.
解决方法：
到目前为止，我们能想出的唯一解决方法是执行 iisreset（我们还将应用程序池回收配置为每天在 IIS 中发生一次，但这似乎无济于事，因为该问题有时会持续数天）。虽然这不是一件好事，但更糟糕的是，我们在同一网站上还有其他应用程序，只要我们重置 IIS，这些应用程序就会受到影响。
The questions:
1. How do we resolve this error since it happens occasionally and we don't see a pattern?
2. Are there any better (and free) ways of processing Excel files from C#/ASP.NET apart from OleDb? (We prefer not to install MS Office on the servers since it's not recommended by Microsoft)
问题：
1. 我们如何解决这个错误，因为它偶尔会发生并且我们没有看到任何模式？
2. 除了 OleDb 之外，是否有更好（和免费）的方法来处理来自 C#/ASP.NET 的 Excel 文件？（我们不希望在服务器上安装 MS Office，因为 Microsoft 不建议这样做）
Our limitations:
1. We're stuck with the MS Office 2003 (.xls) format and cannot move to the MS Office 2007 (OOXML) format.
2. The reasons we don't use CSV are because we may have commas within our data (this is a pain to deal with even if we use quoting) and we also use multiple worksheets in our spreadsheet (this cannot be done with CSV).
我们的局限性：
1. 我们一直使用 MS Office 2003 (.xls) 格式，无法移动到 MS Office 2007 (OOXML) 格式。
2. 我们不使用 CSV 的原因是因为我们的数据中可能有逗号（即使我们使用引用也很难处理）并且我们还在电子表格中使用多个工作表（这不能用 CSV 完成） .
Thanks! :)
谢谢！:)
Update:
Thanks, Keith. It does seem like an issue with the Jet engine, but we use it because of the lack of (freeand easy to use) alternatives.
Thanks, Joe. But we're on a limited budget - so we're mainly looking for free tools/libraries.
更新：
谢谢，基思。这似乎是 Jet 引擎的一个问题，但我们使用它是因为缺乏（免费且易于使用的）替代品。
谢谢，乔。但是我们的预算有限——所以我们主要寻找免费的工具/库。
回答by Keith
I suspect the error is something to do with the venerable Jet OLEDB engine. It's fairly creaky - fine for most desktop things but not much use for enterprise data interchange.
我怀疑该错误与古老的 Jet OLEDB 引擎有关。它相当糟糕——适用于大多数桌面设备，但对于企业数据交换用处不大。
If you can upgrade to a recent C# 3/.Net 3.5 you can use the System.IO.Packaginglibrary to open Office 2007 files (.xlsx or .xlsm files).
如果您可以升级到最新的 C# 3/.Net 3.5，则可以使用该System.IO.Packaging库打开 Office 2007 文件（.xlsx 或 .xlsm 文件）。
These files are actually zips - rename them to .zip and you can just view the XML files inside.
这些文件实际上是 zips - 将它们重命名为 .zip，您就可以查看其中的 XML 文件。
The XML files' formats are fairly horrible (for instance cell comments are VML, ugh!) but readable.
XML 文件的格式相当糟糕（例如单元格注释是 VML，呃！）但可读。
Alternatively get your users to save the Excel tables as CSVs. I'd avoid the Microsoft text driver DB provider though - it's rubbish and can't handle unicode. CSVs are easy to read anyway.
或者让您的用户将 Excel 表格保存为 CSV。不过，我会避免使用 Microsoft 文本驱动程序 DB 提供程序 - 它是垃圾，无法处理 unicode。无论如何，CSV 很容易阅读。
回答by CRice
Make sure you are closing your connections.
确保您正在关闭连接。
For example when developing MS Access applications (Jet) this error occurs if too many connections are left open. It works fine (there is your occasionally) for a while until it reaches max open connections.
例如，在开发 MS Access 应用程序 (Jet) 时，如果有太多连接处于打开状态，则会出现此错误。它可以正常工作（偶尔会出现）一段时间，直到达到最大打开连接数。
回答by David
I've been using SpreadSheetGear.NET for a while, mostly to create Excel files, and it works well.
我一直在使用 SpreadSheetGear.NET 一段时间，主要是为了创建 Excel 文件，它运行良好。
http://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx
http://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx
It provides binary Excel file reading/writing in native .NET, solving all the previous problems I've encountered trying to use OLE and JET to read and create Excel files.
它提供了本地 .NET 中二进制 Excel 文件的读取/写入，解决了我之前在尝试使用 OLE 和 JET 读取和创建 Excel 文件时遇到的所有问题。
The basic version used to come free as a perk for registering Visual C++ Express 2005. This was unadvertised, so it may or may not still exist with the 2008 edition.
基本版本曾经作为注册 Visual C++ Express 2005 的特权免费提供。这是未公开的，因此它可能存在也可能不存在于 2008 版本中。
回答by Joe Erickson
SpreadsheetGear for .NETgives you an API for working with xls and xlsx workbooks from .NET. It is easier to use and faster than OleDB or the Excel COM object model (keep reading for some evidence of this).
SpreadsheetGear for .NET为您提供了一个 API，用于处理来自 .NET 的 xls 和 xlsx 工作簿。它比 OleDB 或 Excel COM 对象模型更易于使用且速度更快（请继续阅读这方面的一些证据）。
Disclaimer: I own SpreadsheetGear LLC
免责声明：我拥有 SpreadsheetGear LLC
Below is the code to create a 50,000 row by 10 column workbook with SpreadsheetGear, save it to disk, and then sum the numbers using OleDb and SpreadsheetGear. SpreadsheetGear reads the 500K cells in 0.31 seconds compared to 0.63 seconds with OleDB - just over twice as fast. SpreadsheetGear actually creates and reads the workbook in less time than it takes to read the workbook with OleDB.
下面是使用 SpreadsheetGear 创建 50,000 行 x 10 列工作簿的代码，将其保存到磁盘，然后使用 OleDb 和 SpreadsheetGear 对数字求和。与 OleDB 的 0.63 秒相比，SpreadsheetGear 在 0.31 秒内读取 500K 单元格 - 快两倍多。与使用 OleDB 读取工作簿相比，SpreadsheetGear 实际上创建和读取工作簿所需的时间更少。
The code is below. You can see the live samplesor try it for yourself with the free trial.
using System;
using System.Data;
using System.Data.OleDb;
using SpreadsheetGear;
using SpreadsheetGear.Advanced.Cells;
using System.Diagnostics;
namespace SpreadsheetGearAndOleDBBenchmark
{
class Program
{
static void Main(string[] args)
{
// Warm up (get the code JITed).
BM(10, 10);
// Do it for real.
BM(50000, 10);
}
static void BM(int rows, int cols)
{
// Compare the performance of OleDB to SpreadsheetGear for reading
// workbooks. We sum numbers just to have something to do.
//
// Run on Windows Vista 32 bit, Visual Studio 2008, Release Build,
// Run Without Debugger:
// Create time: 0.25 seconds
// OleDb Time: 0.63 seconds
// SpreadsheetGear Time: 0.31 seconds
//
// SpreadsheetGear is more than twice as fast at reading. Furthermore,
// SpreadsheetGear can create the file and read it faster than OleDB
// can just read it.
string filename = @"C:\tmp\SpreadsheetGearOleDbBenchmark.xls";
Console.WriteLine("\nCreating {0} rows x {1} columns", rows, cols);
Stopwatch timer = Stopwatch.StartNew();
double createSum = CreateWorkbook(filename, rows, cols);
double createTime = timer.Elapsed.TotalSeconds;
Console.WriteLine("Create sum of {0} took {1} seconds.", createSum, createTime);
timer = Stopwatch.StartNew();
double oleDbSum = ReadWithOleDB(filename);
double oleDbTime = timer.Elapsed.TotalSeconds;
Console.WriteLine("OleDb sum of {0} took {1} seconds.", oleDbSum, oleDbTime);
timer = Stopwatch.StartNew();
double spreadsheetGearSum = ReadWithSpreadsheetGear(filename);
double spreadsheetGearTime = timer.Elapsed.TotalSeconds;
Console.WriteLine("SpreadsheetGear sum of {0} took {1} seconds.", spreadsheetGearSum, spreadsheetGearTime);
}
static double CreateWorkbook(string filename, int rows, int cols)
{
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets[0];
IValues values = (IValues)worksheet;
double sum = 0.0;
Random rand = new Random();
// Put labels in the first row.
foreach (IRange cell in worksheet.Cells[0, 0, 0, cols - 1])
cell.Value = "Cell-" + cell.Address;
// Using IRange and foreach would be less code,
// but we'll do it the fast way.
for (int row = 1; row <= rows; row++)
{
for (int col = 0; col < cols; col++)
{
double number = rand.NextDouble();
sum += number;
values.SetNumber(row, col, number);
}
}
workbook.SaveAs(filename, FileFormat.Excel8);
return sum;
}
static double ReadWithSpreadsheetGear(string filename)
{
IWorkbook workbook = Factory.GetWorkbook(filename);
IWorksheet worksheet = workbook.Worksheets[0];
IValues values = (IValues)worksheet;
IRange usedRahge = worksheet.UsedRange;
int rowCount = usedRahge.RowCount;
int colCount = usedRahge.ColumnCount;
double sum = 0.0;
// We could use foreach (IRange cell in usedRange) for cleaner
// code, but this is faster.
for (int row = 1; row <= rowCount; row++)
{
for (int col = 0; col < colCount; col++)
{
IValue value = values[row, col];
if (value != null && value.Type == SpreadsheetGear.Advanced.Cells.ValueType.Number)
sum += value.Number;
}
}
return sum;
}
static double ReadWithOleDB(string filename)
{
String connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand selectCommand =new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
connection.Close();
double sum = 0.0;
// We'll make some assumptions for brevity of the code.
DataTable dataTable = dataSet.Tables[0];
int cols = dataTable.Columns.Count;
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < cols; i++)
{
object val = row[i];
if (val is double)
sum += (double)val;
}
}
return sum;
}
}
}
回答by mt99
I had the same issue and it seems that it is fixed by closing the connection to the file (xls or csv) on every iteration of the loop. I assume you are also looping through a list of files and .Open() a new connection to each file. If you .Close() the connection at the end of the loop, the problem seems to go away.
我遇到了同样的问题，似乎通过在循环的每次迭代中关闭与文件（xls 或 csv）的连接来修复它。我假设您还遍历文件列表和 .Open() 到每个文件的新连接。如果您在循环结束时 .Close() 连接，问题似乎消失了。
回答by LearnEarn
Connection TimeOut might be one of the reason. Check the query how long it is taking to excecute in the application by debugging.
连接超时可能是原因之一。通过调试检查查询在应用程序中执行需要多长时间。
回答by Residuum
Seems like I got it wrong, see Problem with OleDbConnection, Excel and connection pooling
好像我弄错了，请参阅OleDbConnection、Excel 和连接池问题
Basically what CRice said, but there seems to be a problem in the implementation of Dispose()when OleDbDataAdapter (String, String)constructor is called with the Excel-ConnectionString, as the implicitely created connection apparently is not closed.
基本上是CRice所说的，但是在使用Excel-ConnectionString调用构造函数Dispose()时的实现似乎存在问题OleDbDataAdapter (String, String)，因为隐式创建的连接显然没有关闭。
The workaround is to wrap all calls OleDbDataApaterusages (you were doing the using ... stuff as it implements IDisposable) with a seperate
解决方法是将所有调用OleDbDataApater用法（您正在执行 using ... 实现的东西IDisposable）与一个单独的
using (var conn = new OleDbConnection(connectionString))
and then call the OleDbDataAdapter (String, OleDbConnection)constructor.
然后调用OleDbDataAdapter (String, OleDbConnection)构造函数。
EDIT:I was wrong about connection closing on disposing. conn.Dispose()does not close the connection, so inside the using (var conn = new OleDbConnection(connectionString))you still need to do a conn.Close().
编辑：我在处理时关闭连接是错误的。conn.Dispose()不会关闭连接，所以在里面using (var conn = new OleDbConnection(connectionString))你仍然需要做一个conn.Close().
