尝试从VB.Net中的网页读取时无法在excel文件中找到引用的工作表 [英] Can't find referenced sheet in excel file while trying to read from a webpage in VB.Net

问题描述

我正在尝试构建一个允许用户选择excel文件的网页，然后该页面将读取该页面的内容，并在验证后将数据上传到DB.

I'm trying to build a webpage that allows a user to choose an excel file and then the page will read the contents of the page and upload the data to the DB after validation.

我有一个fileUpload asp控件，带有一个用于执行的按钮和一个显示数据的gridview.这不是最终目标，但我只是想测试脚本是否成功读取了文件(不是).

I have a fileUpload asp control with a button for execution and a gridview to display the data. This isn't the end-goal, but I have it simply to test if the script is reading the file successfully (which it isn't).

我一直收到的错误是:

"The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly."

我上传的Excel文件中肯定有一个Sheet1，所以我不确定发生了什么.

The excel file I upload definitely has a Sheet1, so I'm not sure what's going on.

我不会假装对OleDB的工作有很多经验或了解，所以我敢肯定这很简单.

I won't pretend to have a lot of experience or understanding as how OleDB works, so I'm sure it's something simple.

我的代码如下:

Protected Sub Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upload.Click
    If (testFile.HasFile) Then
        Dim conn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim query As String
        Dim connString As String = ""
        Dim strFileType As String = System.IO.Path.GetExtension(testFile.FileName).ToString().ToLower()

        'Check file type
        If strFileType.Trim = ".xls" Or strFileType.Trim = ".xlsx" Then
        Else
            MsgBox("Only excel files allowed")
            Exit Sub
        End If

        Try
            'Connection String to Excel Workbook
            If strFileType.Trim = ".xls" Then
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & testFile.FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            ElseIf strFileType.Trim = ".xlsx" Then
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & testFile.FileName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            End If

            query = "SELECT * FROM [Sheet1$]"

            'Create the connection object
            conn = New OleDbConnection(connString)
            'Open connection
            If conn.State = ConnectionState.Closed Then conn.Open()
            'Create the command object
            cmd = New OleDbCommand(query, conn)
            da = New OleDbDataAdapter(cmd)
            ds = New DataSet()
            da.Fill(ds)

            grvExcelData.DataSource = ds.Tables(0)
            grvExcelData.DataBind()

            da.Dispose()
            conn.Close()
            conn.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    Else
        MsgBox("Must have file")
        Exit Sub
    End If
End Sub

我也非常感谢您能获得很好的资源，以了解有关OleDB的更多信息以及代码的具体错误！

I'd also appreciate a good resource on how to learn more about OleDB along with the specifics errors of my code!

谢谢！

推荐答案

您需要创建一个输出以查看页面中的工作表，也许是在DropDownList对象中(此代码有点难看，但应该将您指向正确的方向).基本上先返回您的工作表以确认它是否存在...

You need to create an output to view the sheets from the page, perhaps in a DropDownList object (this code is a little ugly, but should point you in the right direction). Basically return your sheets first to verify it exists...

 private void ProcessExcelFile(string fileName, bool isOpenXMLFormat)
    {
        string fn = System.IO.Path.GetFileName(fileName);
        String RelativePath = "YourPath/" + fn;
        string connectionString = String.Empty;
        OleDbConnection con;

        if (isOpenXMLFormat)
            //read a 2007 file  
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
        else
            //read a 97-2003 file  
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                fileName + ";Extended Properties=Excel 8.0;";

        con = new OleDbConnection(connectionString);
        con.Open();

        //get all the available sheets  
        System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        //get the number of sheets in the file  
        string[] workSheetNames = new String[dataSet.Rows.Count];
        int i = 0;
        foreach (DataRow row in dataSet.Rows)
        {
            //insert the sheet's name in the current element of the array  
            //and remove the $ sign at the end  
            //workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });
            workSheetNames[i] = row["TABLE_NAME"].ToString();
            i++;
        }
        SheetNames.DataSource = workSheetNames;
        SheetNames.DataBind();

这篇关于尝试从VB.Net中的网页读取时无法在excel文件中找到引用的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！