尝试从一个网页在VB.Net阅读找不到在Excel文件中引用表 [英] Can't find referenced sheet in excel file while trying to read from a webpage in VB.Net

问题描述

我想建立一个网页，它允许用户选择一个Excel文件，然后页面将读取页面的内容和验证后，将数据上传到数据库。

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.

我有执行和GridView控件来显示数据的一个按钮一个FileUpload ASP的控制。这不是最终的目标，但我有它只是测试脚本是否被成功读取该文件（它不是）。

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.

我不会pretend有很多的经验和理解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.

我的code是如下：

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

我也想AP preciate如何了解更多有关OLEDB和我的code的具体错误一起一个很好的资源！

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

谢谢！

推荐答案

您需要创建一个输出，从页面中查看表，也许在一个DropDownList对象（this code是有点难看，但应该指向你在正确的方向）。基本上回到你的床单首先要确认它的存在...

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屋！