vb web app 中的电子表格连接

Question

我的客户希望在他们的网站上列出他们的价目表。我尝试使用电子表格 (.xlsx) 作为数据源，以便在数据出现在网站上之前限制数据的处理。

该站点在 IIS 7 上运行。服务器是 Windows Server 2008。我也将其上的 .NET Framework 更新到了 4.0。

它没有安装任何 Office 软件包，我有预感这就是我的问题所在，但我真的很想确定一下。

这是我收到的错误文本：

System.InvalidOperationException：“Microsoft.Jet.OLEDB.4.0”提供程序未在本地计算机上注册。在System.Data.OleDb.OleDbServicesWrapper.GetDataSource（OleDbConnectionString constr，DataSourceWrapper＆amp; datasrcWrapper）在System.Data.OleDb.OleDbConnectionInternal..ctor（OleDbConnectionString constr，OleDbConnection连接）在System.Data.OleDb.OleDbConnectionFactory.CreateConnection（DbConnectionOptions选项，对象 poolGroupProviderInfo、DbConnectionPool 池、DbConnection owningObject) 在 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection、DbConnectionPoolGroup poolGroup) 在 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) 在 System.Data.ProviderBase.DbConnectionClosed.OpenConnection (DbConnectionouterConnection、DbConnectionFactoryconnectionFactory) 在 System.Data.OleDb.OleDbConnection.Open() 在 C:\HostingSpaces\webbuddies\waterinc.webbuddies.co.za\wwwroot\Products 中的 Products.Page_Load(Object sender, EventArgs e) 处。 aspx.vb:line 14

这是我的代码：

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Here's the connection string as defined in web.config:
' <connectionStrings>
'     <add name="xlsx" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=App_Data/Pricelist.xlsx;Extended Properties=Excel 8.0;" />
' </connectionStrings>
    Dim con As New OleDbConnection(ConfigurationManager.ConnectionStrings("xlsx").ConnectionString)

    ' The spreadsheet has 4 sheets in it, 1 for each category on the client's pricelist.
    Try
        con.Open()

        Dim dsWater As DataSet
        Dim daWater As New OleDbDataAdapter
        daWater.SelectCommand = New OleDbCommand("SELECT * FROM Water", con)

        Dim dsJuice As DataSet
        Dim daJuice As New OleDbDataAdapter
        daJuice.SelectCommand = New OleDbCommand("SELECT * FROM FruitJiuce", con)

        Dim dsMix As DataSet
        Dim daMix As New OleDbDataAdapter
        daMix.SelectCommand = New OleDbCommand("SELECT * FROM MuffinMix", con)

        Dim dsMisc As DataSet
        Dim daMisc As New OleDbDataAdapter
        daMisc.SelectCommand = New OleDbCommand("SELECT * FROM Miscellaneous", con)

        daWater.Fill(dsWater, "Water")
        daJuice.Fill(dsJuice, "FruitJuice")
        daMix.Fill(dsMix, "MuffinMix")
        daMisc.Fill(dsMisc, "Miscellaneous")

        rptWater.DataSource = dsWater : rptWater.DataBind()
        rptJuices.DataSource = dsJuice : rptJuices.DataBind()
        rptMixes.DataSource = dsMix : rptMixes.DataBind()
        rptMisc.DataSource = dsMisc : rptJuices.DataBind()

        con.Close()
    Catch ex As Exception
        errorMessage = ex.ToString
        lblResponse.Text = "Could not connect to one or more data sources required to display the " & _
            "pricelist. Please contact the webmaster." & vbCrLf & errorMessage
        lblResponse.ForeColor = Drawing.Color.Red
    End Try
End Sub

编辑 1
自发布以来，我已通过第一个答案中的链接在服务器上安装了 2007 Office 驱动程序，并且我已根据第二个答案修改了我的连接字符串：

<connectionStrings>
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=App_Data/Pricelist.xlsx; Extended Properties=Excel 12.0 Xml; HDR=YES;"/>
</connectionStrings>

Answer 1

您需要安装 2007 Office 驱动程序才能正常工作。

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Answer 2

对于 XLSX 文件，您需要像这样修改连接字符串。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

更多信息 - http://connectionstrings.com/excel-2007