将Excel工作表数据读取到DataTable或Dataset中 [英] Read Excel Sheet Data into DataTable or Dataset

问题描述

我试图从Excel导入到数据集，并出现错误消息.

I tried to import from Excel to a dataset and an error message occurred.

我正在研究此网站 http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            Dim Loc As String = Application.StartupPath() + "\Param\exceldata.xlsx"
            Dim strCOleCon As String = "provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Loc.Trim + "';Extended Properties=Excel 8.0;"

            MyConnection = New OleDb.OleDbConnection(strCOleCon)
            MyCommand = New OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New DataSet
            MyCommand.Fill(DtSet)
            DataGridView1.DataSource = DtSet.Tables(0)
            MyConnection.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

推荐答案

首先，我认为您的连接不正确，您有一个xlsx文件，但已为xls设置了连接.

First off I believe your connection is incorrect, you have an xlsx file but the connection is set for xls.

顺便说一句，不确定这是表单项目还是asp项目，但肯定看起来像是Windows的表单项目，正在考虑您的第一个标记asp.net.

BTW Not sure if this is a forms or asp project but sure looks like a forms project for windows thinking about your first tag indicating asp.net.

在设置连接字符串的方式，要阅读的内容，表的第一行是否包含列名或数据，列中是否有混合数据等方面，Excel可能会很棘手.

Excel can be tricky in regards to how the connection string is setup, do you want to just read, does the first row in the sheet have column names or data, is there mixed data in a column etc.

这是我用来简化连接操作的摘要，但请注意，它并非万无一失，因为您需要在连接中正确设置HDR和IMEX，

Here is a snippet I have used to make things easier with connection but be forewarned it's not fool-proof in that you need to set HDR and IMEX correctly in the connection,

Imports System.Data.OleDb

Module ExcelOleDbConnections
    ''' <summary>
    ''' Creates a connection string on read data from an excel file
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="Header">Yes if first row is column-names, No if first row is data</param>
    ''' <param name="IMEX"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' See following page for clarification on extended properties
    ''' including IMEX. Ignore C# code.
    ''' http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled
    ''' </remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    Public Function ExcelConnectionString(
        ByVal FileName As String,
        Optional ByVal Header As String = "No",
        Optional ByVal IMEX As Integer = 1) As String

        Dim Builder As New OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR={1};", IMEX, Header))
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR={1};", IMEX, Header))
        End If

        Builder.DataSource = FileName

        Return Builder.ToString

    End Function
End Module

示例读取工作表，其中第一行是数据.注意我使用的是可选参数，因此后两个使用默认值

Sample reading a sheet where the first row is data. Note I am using optional parameters so the last two use default values

Dim dt As New DataTable

' sheet has data in first row
Using cn As New OleDb.OleDbConnection With {.ConnectionString = ExcelConnectionString(Loc)}
    Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "select * from [Sheet1$]"}
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
End Using

在此示例中，我们假设第一行是字段/列名

This example we assume the first row are field/column names

Dim dt As New DataTable

' sheet has column names for first row
Using cn As New OleDb.OleDbConnection With {.ConnectionString = ExcelConnectionString(Loc, "Yes", 0)}
    Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "select * from [Sheet1$]"}
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
End Using

填充DataGridView

Populate the DataGridView

If dt.Rows.Count > 0 Then
    DataGridView1.DataSource = dt
Else
    '
    ' recover e.g. tell user there are no records in this sheet
    '
End If

一起举例

Try
    Dim Loc As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Param\exceldata.xlsx")
    If IO.File.Exists(Loc) Then
        Dim dt As New DataTable

        ' sheet has column names for first row
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = ExcelConnectionString(Loc, "Yes", 0)}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "select * from [Sheet1$]"}
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using


        If dt.Rows.Count > 0 Then
            DataGridView1.DataSource = dt
        Else
            '
            ' recover e.g. tell user there are no records in this sheet
            '
        End If
    End If

Catch ex As Exception
    MessageBox.Show("TODO")
End Try

请参阅我的Windows窗体示例(是的，我在您的标签中看到了asp.net) Excel和OleDb基础知识可促进操作

See my windows forms examples (yes I see asp.net in your tags) Excel and OleDb basics to advance operations

这篇关于将Excel工作表数据读取到DataTable或Dataset中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！