HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET

???? ?????? ???? ??????
???? ID: 318373 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

This step-by-step article describes how to retrieve table and column metadata from Microsoft Excel data sources by using theGetOleDbSchemaTablemethod with the Microsoft OLE DB Managed Provider and the Microsoft OLE DB Provider for Jet.

TheGetOleDbSchemaTablemethod that is exposed by theSystem.Data.OleDbclass of Microsoft .NET Framework is the .NET successor to theOpenSchemamethod in earlier versions of Microsoft ActiveX Data Objects (ADO).

????? ?? ?????

?? ?? ?????? ???? ?? ??? ???? Excel ???? ????? ADO.NET ?? ????? ???? ???, ?? ????? ?? ?????? ???????? ?? ?? ???? ???????GetOleDbSchemaTable, ?? ??? ??? ???? ?? ????? ???? ???? ?? ??? ????? ?????? ?? ??? ????? ???????? ??????? ???? ?? ???? ?? ????? ?? ???? ??? ???DataGridTableStyle???? ????? ??? ????? ???? ?? ???? ?????? ?? ???????? ???? ?? ??? ???????? ?????? ????

??????????

????? ???? ??? ???????? ?????????, ??????????, ??????? ??????, ?? ?????? ??? ?? ???????? ???? ?? outlines ??:
  • ????? Studio .NET ???? Microsoft Windows ???????? ?????? ?? ??????? ????
  • ?? ??? ????????? ?? ??????? ?? ???? ?? ?? ?? ?? ?? Microsoft Excel ????????????? (.xls) ?????
?? ???? ????? ?? ?? ???? ??? ????? ???? ?? ??? ??? ?? ?? ?? familiarity ???:
  • Visual Basic .NET
  • ADO.NET ???? ?????
  • Excel ??? ??????????????? ?? ??????????

?????

  1. Microsoft Visual Studio .NET ?? ??????? ????, ?? ??? ??? .NET Windows ????????? Visual Basic ????????? ??????
  2. ??? ????????????????? ?? ??DataGrid???????? ??????? ??????? (Form1) ?? ??? ??? ????????? ????????? ?????????? ?? ??? ???????????????? ??????? ????,?????? ???? ?? ???????? ????, ???????? ????? ????respectively.
  3. ??????? ?? ??? ??????? ??? ????, ?? ????? ??????Imports????? ?? ???:
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.ComponentModel
  4. ????? ????-??????? declarations ??? ???????? ???????????"Inherits System.Windows.Forms.Form" ?????? ?? ??? ????? ??????? ???????? ?? ?????? ??? ????????? ?? ??????? ?? ???? ?? ??? Excel ????????????? ????? ?? ????? ???? ?? ??? ?????? ?? ??? ??? ???????? ?????
        Dim cn As OleDbConnection
        Dim strCn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\test.xls;Extended Properties=Excel 8.0"
        Dim dtTables As DataTable
        Dim WithEvents cm As CurrencyManager
        Dim dtColumns As DataTable
        Dim dvColumns As DataView
  5. ??? ????? ??? ???????? ???????????"Windows ??????? ??????? ??? ????? ???? ???" ??????? ?? ??? ????? ?? ??? ?? ??? ???? ??GetOleDbSchemaTableto load the table and columns lists, populates theDataGridcontrols, and updates the columns list when the selected table changes.
        Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
            Call GetTablesList()
        End Sub
    
        Private Sub GetTablesList()
            cn = New OleDbConnection(strCn)
            cn.Open()
            dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            DataGrid1.DataSource = dtTables
            DataGrid1.ReadOnly = True
            cn.Close()
            Call GetColumnsList()
        End Sub
    
        Private Sub GetColumnsList()
            If cm Is Nothing Then
                cm = CType(Me.BindingContext(dtTables), CurrencyManager)
            End If
            Dim r As Integer = cm.Position
            Dim strTable As String = dtTables.Rows(r)("TABLE_NAME")
            cn = New OleDbConnection(strCn)
            cn.Open()
            dtColumns = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                New Object() {Nothing, Nothing, strTable, Nothing})
            dvColumns = New DataView(dtColumns)
            dvColumns.Sort = "ORDINAL_POSITION"
            DataGrid2.DataSource = dvColumns
            DataGrid2.ReadOnly = True
            cn.Close()
        End Sub
    
        Private Sub cm_PositionChanged(ByVal sender As Object, _
                ByVal e As System.EventArgs) Handles cm.PositionChanged
            Call GetColumnsList()
        End Sub
  6. Insert the following code to lay out and format the TablesDataGridby usingTableStyles. Note the use ofPropertyDescriptorto facilitate non-default formatting of the date columns.
        Private Sub Button2_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button2.Click
            Call FormatTablesGrid(dtTables)
        End Sub
    
        Private Sub FormatTablesGrid(ByVal dt2format As DataTable)
            Dim gs As DataGridTableStyle = New DataGridTableStyle()
            gs.MappingName = dt2format.TableName
    
            Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()
            With cs
                .MappingName = "TABLE_NAME"
                .HeaderText = "Table Name"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            cs = New DataGridTextBoxColumn()
            With cs
                .MappingName = "TABLE_TYPE"
                .HeaderText = "Table Type"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            Dim cm As CurrencyManager = CType(Me.BindingContext(dt2format), CurrencyManager)
            Dim pd As PropertyDescriptor = cm.GetItemProperties()("DATE_CREATED")
    
            cs = New DataGridTextBoxColumn(pd, "d")
            With cs
                .MappingName = "DATE_CREATED"
                .HeaderText = "Date Created"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            cm = CType(Me.BindingContext(dt2format), CurrencyManager)
            pd = cm.GetItemProperties()("DATE_MODIFIED")
    
            cs = New DataGridTextBoxColumn(pd, "d")
            With cs
                .MappingName = "DATE_MODIFIED"
                .HeaderText = "Date Modified"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            DataGrid1.TableStyles.Add(gs)
            Me.Button2.Enabled = False
    
        End Sub
  7. Insert the following code to lay out and format the ColumnsDataGridby usingTableStyle:
        Private Sub Button3_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button3.Click
            Call FormatColumnsGrid(dtColumns)
        End Sub
    
        Private Sub FormatColumnsGrid(ByVal dt2format As DataTable)
            Dim gs As DataGridTableStyle = New DataGridTableStyle()
            gs.MappingName = dtColumns.TableName
    
            Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()
            With cs
                .MappingName = "COLUMN_NAME"
                .HeaderText = "Column Name"
                .Width = 100
            End With
            gs.GridColumnStyles.Add(cs)
    
            cs = New DataGridTextBoxColumn()
            With cs
                .MappingName = "ORDINAL_POSITION"
                .HeaderText = "Ordinal Position"
                .Width = 100
            End With
            gs.GridColumnStyles.Add(cs)
    
            cs = New DataGridTextBoxColumn()
            With cs
                .MappingName = "DATA_TYPE"
                .HeaderText = "Data Type"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            cs = New DataGridTextBoxColumn()
            With cs
                .MappingName = "CHARACTER_MAXIMUM_LENGTH"
                .HeaderText = "Text Length"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            cs = New DataGridTextBoxColumn()
            With cs
                .MappingName = "NUMERIC_PRECISION"
                .HeaderText = "Numeric Precision"
                .Width = 75
            End With
            gs.GridColumnStyles.Add(cs)
    
            DataGrid2.TableStyles.Add(gs)
            Me.Button3.Enabled = False
    
        End Sub
  8. ????????? ?? ??????
  9. ????? ????,Retrieve Metadatato fill the????????list (DataGrid1) with all of the columns of information that are returned for each table in the Excel workbook byGetOleDbSchemaTable. The?????list (DataGrid2) is filled at the same time with all of the columns of information that are returned for the columns in the first table in the???????????? ????
  10. Select a different table in the???????????? ???? The?????list changes to display the columns from the selected table because of thecm_PositionChangedevent handler that you implemented.
  11. ????? ????,?????? ???? ?? ???????? ????to define and apply a TableMappingStyle to DataGrid1. This makes the column headers more "friendly" and displays only the four columns of useful data from the nine columns that are returned byGetOleDbSchemaTable. These columns are displayed:
    TABLE_NAME
    TABLE_TYPE
    DATE_CREATED
    DATE_MODIFIED
    These blanks columns are not displayed:
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_GUID
    ?????:
    TABLE_PROPID
  12. ????? ????,?????? ????? ????to define and to apply a TableMappingStyle to DataGrid2. This makes the column headers more "friendly" and displays only the 5 most useful columns of data from the 28 columns that are returned byGetOleDbSchemaTable.
    COLUMN_NAME
    ORDINAL_POSITION
    DATA_TYPE
    MAXIMUM_CHARACTER_LENGTH
    NUMERIC_PRECISION
    These mostly blank columns are not displayed:
    TABLE_CATALOG
    TABLE_SCHEMA
    TABLE_NAME (already known)
    COLUMN_GUID
    COLUMN_PROPID
    COLUMN_HASDEFAULT (always false)
    COLUMN_DEFAULT
    COLUMN_FLAGS
    IS_NULLABLE (always true)
    TYPE_GUID
    CHARACTER_OCTET_LENGTH
    NUMERIC_SCALE
    DATETIME_PRECISION
    CHARACTER_SET_CATALOG
    CHARACTER_SET_SCHEMA
    CHARACTER_SET_NAME
    COLLATION_CATALOG
    COLLATION_SCHEMA
    COLLATION_NAME
    DOMAIN_CATALOG
    DOMAIN_SCHEMA
    DOMAIN_NAME
    ?????:
  13. Click a different row in DataGrid1 to select a different table. The?????list is automatically updated. The TableStyles that you previously applied to eachDataGridremain in effect.

?????? ??????

  • The column data types that are returned byGetOleDbSchemaTable???? Excel ?? ??? ???? ????? ???? ??? ????? ??? ?????? ??? ?????? ??? ?? ??? ?? ???? ?????? ?? ???OpenSchema???????? ADO ??? ????:
    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    ????? ?????????????? ADOADO.NET (OLE DB)
    ????????5 adDouble5 OleDbType.Double
    ??????6 adCurrency6 OleDbType.Currency
    ??????/???7 adDate7 OleDbType.Date
    ??????11 adBoolean11 OleDbType.Boolean
    ???<>202 adVarWChar130 OleDbType.WChar
    ????203 adLongVarWChar130 OleDbType.WChar
  • GetOleDbSchemaTable, ????OpenSchema, ?? ??? ????? "F1" ?? ???? ??, ???? ???? Excel ?????????? ?????? ?????? ??? ??? ???? ?? ????? ????? ????? ????

??????

???????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
257819HOWTO: ????? ADO Visual Basic ?? VBA ?? ???? ?? Excel ?? ???

???

???? ID: 318373 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft ADO.NET 1.1
??????: 
kbhowtomaster kbprovider kbmt KB318373 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:318373

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com