HOW TO: ??????? ?????? ??????? ?? Excel ?? ???? ??????? ????? GetOleDbSchemaTable ?? Visual Basic .NET

???? ??? ??????? ???? ????? ??? ????? ??????? ?????? ????? ?????? ??????? ?? ????? ?????? Microsoft Excel ?????? ??????? ??????? GetOleDbSchemaTable ?? ???? Microsoft OLE DB ????? ? ???? Microsoft OLE DB ?? Jet.

????? GetOleDbSchemaTable ???? ??? ????? ?????? ????? System.Data.OleDb Microsoft .NET Framework ?? ??????? .NET ??? ??????? OpenSchema ?? ????????? ??????? ?? Microsoft ?????? ?????? ActiveX (ADO).

??? ?????

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

???????

???? ??????? ??????? ??????? ?????? ??? ????? ?????? ???????? ?????? ? ??? ??????? ???:

• Visual Studio .NET ?????? ??? ???? ????? Microsoft Windows ??????
• ??? ?????? (.xls) Microsoft Excel ???? ??? ????? ?? ??? ?????? ???????? ?? ????????

????? ??? ??????? ???? ?????? ???????? ??? ????? ?? ???????? ???????:

• Visual Basic .NET
• ?????? ??? ???????? ADO.NET
• ?????? Excel ?????? ?????

?????

1. ???? ????? Microsoft Visual Studio .NET ?? ????? ????? Visual Basic ????? Windows .NET ????.
2. ????? ????? ???? ?? ????? ????? ???? DataGrid ??????? ????????? (Form1). ?? ?????? ??????? Text ????? ???? ?? ??? ??????? ?????? ??????? "?" ????? ??????? ????? "?" ????? ????? ????? ??? ???????.
3. ??????? ??? ?????? ??????? ????????? ???????? ?????? ???????? ?????? ?????? ?????? ????????? ??????? ?? ????? ??????:

   Imports System.Data
   Imports System.Data.OleDb
   Imports System.ComponentModel

4. ????? ????????? ????? ?????? ??????? ?????? ?? ??? ??????? ??? ??? "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 ????? ????????? ????????". ??? ???????? ???????? ?????? GetOleDbSchemaTable ????? ????? ?????? ???????? ???? ????? ?????? DataGrid ? ?????? ????? ??????? ??? ????? ?????? ??????.

       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. ????? ????????? ???????? ??????? ?????? ????? DataGrid ??????? ???????? TableStyles. ???? ??????? PropertyDescriptor ?????? ??? ??????? ????? ??????? ?????.

       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. ????? ????????? ???????? ??????? ????? ?????? ??????? DataGrid ???????? TableStyle:

       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. ???? ??? ??????? ?????? ??????? ?????? ??????? ????? (DataGrid1) ?? ???? ????? ????????? ???? ?? ??????? ??? ???? ?? ???? Excel ?????? GetOleDbSchemaTable. ??? ????? ??????? ??????? (DataGrid2) ?? ??? ????? ?? ???? ????? ????????? ???? ??? ??????? ??????? ?? ?????? ????? ?? ??????? ?????.
10. ??? ???? ????? ?? ??????? ?????. ??????? ????? ????? ???? ??????? ?? ?????? ?????? ????? ????? ??????? cm_PositionChanged ???? ????? ???????.
11. ???? ??? ????? ????? ??????? ?????? ?? ????? TableMappingStyle DataGrid1. ??? ???? ???? ??????? "???????" ???? ????? ????? ????? ?????? ????? ?? ????? ???? ???? ?? ??????? ?????? GetOleDbSchemaTable. ??? ??? ??? ???????:
    table_name
    table_type
    date_created
    date_modified
    ?? ??? ??? ??? ??????? ????????:
    table_catalog
    table_schema
    table_guid
    ???
    table_propid
12. ???? ??? ????? ????? ????? ?????? ? ?????? TableMappingStyle DataGrid2. ??? ???? ???? ??????? "???????" ???? ????? 5 ????? ??????? ??? ?? ???????? ?? ????? 28 ???? ?? ??????? ?????? GetOleDbSchemaTable.
    column_name
    ordinal_position
    data_type
    maximum_character_length
    numeric_precision
    ?? ??? ??? ??? ??????? ??????? ??????:
    table_catalog
    table_schema
    TABLE_NAME (??????? ??????)
    column_guid
    column_propid
    COLUMN_HASDEFAULT (false ?????)
    column_default
    column_flags
    IS_NULLABLE (????? ?????)
    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
    ???_??????
    ???
13. ???? ??? ?? ????? ?? DataGrid1 ?????? ???? ?????. ??? ????? ??????? ??????? ????????. ??? TableStyles ???? ??? ??????? ?????? ??? ?? DataGrid ????? ???????.

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

• ????? ?????? ??????? ???? ??? ??????? ?????? GetOleDbSchemaTable ????? ?????? Excel ??? ??????? ?? ???? ??????? ??? ????? ???????? ???? ?? ??????? ?????? ??????? OpenSchema ?? ADO ?????????:
  ?? ??? ??????????? ??? ??????

  ??? ??????	ADO ??????????	ADO.NET (OLE ????? ??????)
  ???????	5 adDouble	5 OleDbType.Double
  ????	6 adCurrency	6 OleDbType.Currency
  ?????/???	7 adDate	7 OleDbType.Date
  ?????	11 adBoolean	11 OleDbType.Boolean
  ???? < 255	202 adVarWChar	130 OleDbType.WChar
  ???????	203 adLongVarWChar	130 OleDbType.WChar

• ????? GetOleDbSchemaTable, ??? OpenSchema ???? ???? "F1" ?? ???? ???? ??? Excel ? ??? ????? ?? ???? ?? ?????? ?? ???? ?????? ???????? ?? ???????.

?????? ??? ??????? ??????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft: