帮我找到此excel导入代码中的泄漏 [英] Help me find the leak in this excel import code

问题描述

以下方法当然可以，但是在上传一定数量(不是恒定的)后，我的客户端收到了可怕的错误:"System.Data.OleDb.OleDbException:未指定的错误"

The following method of course works, but after a certain number of uploads (this is not constant) my client gets the dreaded error: "System.Data.OleDb.OleDbException: Unspecified error"

步骤:

1. 客户端通过文件上传控件上传excel文件
2. 文件已保存到文件系统
3. 通过oledb提供程序打开文件并读入数据集

我唯一的猜测是提供者不以某种方式释放资源.

My only guess is that the provider is somehow not releasing resources.

清除此问题的唯一方法(临时)是重置IIS.因此，我倾向于认为提供商可以被该服务器上的其他网站锁定.我们确实为使用该提供程序的客户端(我们未构建)托管一个站点，因此在它们的端部可能存在问题.有人可以对此发表评论吗?

The only way to clear this up (temporarily) is to reset IIS. For that reason, I'm inclined to think that the provider can get locked up by other websites on this server. We do host one site for a client (we did not build) that makes use of this provider, so it's possible that there is an issue on their end. Can anyone comment on this?

请查看下面的方法，帮助我摆脱这个问题！

Please take a look at the method below and help me get rid of this problem!

Public Shared Function GetExcelData(ByVal excelFile As String, ByVal sheetNumber As Integer) As DataSet

    Dim connString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFile)
    Dim excelDataSet As New DataSet()

    Using conn As New OleDbConnection(connString)
        conn.Open()
        Using dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            Dim excelSheets(dt.Rows.Count) As String
            Dim i As Integer = 0
            For Each row As DataRow In dt.Rows
                excelSheets(i) = row("TABLE_NAME").ToString
                i += 1
                If i = sheetNumber Then
                    Exit For
                End If
            Next

            Using excelCommand As New OleDbCommand("Select * from [" & excelSheets(sheetNumber - 1) & "]", conn)
                Using excelAdapter As OleDbDataAdapter = New OleDbDataAdapter(excelCommand)
                    excelAdapter.Fill(excelDataSet)
                End Using
            End Using

        End Using
        conn.Close()
    End Using

    Return excelDataSet

End Function

推荐答案

似乎没有使用oledb提供程序的真正解决方案.

It seems that there is no real solution using the oledb provider.

因此，我将让客户将其电子表格转换为CSV(在这种情况下，因为它是单张表格导入方式，所以有可能).

So, I'm going to have the client convert their spreadsheet into a CSV (it's possible in this case as it's a single sheet tabular import).

然后，我在这里找到了一个很棒的CSV解析器:

Then, I found a great CSV parser here:

http://www.codeproject.com/KB/database/CsvReader.aspx

这篇关于帮我找到此excel导入代码中的泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！