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ÇÔ¸Õ¡ÒÃã¹àÇÍÃìªÑ¹¡è͹˹éҢͧÍÍ»à¨ç¡µì¢éÍÁÙÅ ActiveX ¢Í§ Microsoft (ADO)

¤Ó͸ԺÒÂà¡ÕèÂǡѺ෤¹Ô¤

ËÅѧ¨Ò¡·Õè¤Ø³àª×èÍÁµè͡ѺáËÅ觢éÍÁÙÅ Excel â´Âãªé ADO.NET ¤Ø³á¡ÃÒ¡Òâͧ¢éÍÁÙÅàÁµÒ¢Í§µÒÃÒ§ â´ÂãªéGetOleDbSchemaTable¨Ò¡¹Ñé¹ ãªéÇÔ¸Õ¡ÒÃà´ÕÂǡѹ¡ÑºÍÒÃì¡ÔÇàÁ¹µì·ÕèᵡµèÒ§¡Ñ¹à¾×èÍ¢ÍÃѺ¢éÍÁÙÅàÁµÒ¢Í§¤ÍÅÑÁ¹ìÊÓËÃѺµÒÃÒ§·ÕèàÅ×Í¡ ¤Ø³ÂѧÊÒÁÒöãªéà»ç¹DataGridTableStyleÇѵ¶Ø¡ÒèѴà¤éÒâ¤Ã§ áÅСÒèѴÃٻẺẺÊͺ¶ÒÁ¢Í§¤Ø³Ê觼Åã¹àÊé¹µÒÃÒ§¢éÍÁÙÅ

¤ÇÒÁµéͧ¡ÒÃ

ÃÒ¡ÒõèÍ仹ÕéáÊ´§ÎÒÃì´áÇÃì·Õèá¹Ð¹Ó «Í¿µìáÇÃì â¤Ã§ÊÃéÒ§¾×é¹°Ò¹¢Í§à¤Ã×Í¢èÒ áÅÐ service pack ·Õè¤Ø³µéͧ¡ÒÃ:

• Studio .NET ·ÕèáÊ´§¼Å·ÕèµÔ´µÑé§ÍÂÙ躹Ãкº»¯ÔºÑµÔ¡Òà Microsoft Windows à¢éҡѹä´é
• á¿éÁÊÁØ´§Ò¹ (.xls) Microsoft Excel ÍÂèÒ§¹éÍÂ˹Öè§á¶ÇáÅФÍÅÑÁ¹ì¢Í§¢éÍÁÙÅ

º·¤ÇÒÁ¹Õé͹ØÁÒ¹ÇèÒ ¤Ø³ÁÕ familiarity ¹é;×é¹°Ò¹¡ÑºËÑÇ¢é͵èÍ仹Õé:

• visual Basic .NET
• ¡ÒÃà¢éÒ¶Ö§¢éÍÁÙÅ ADO.NET
• ÊÁØ´§Ò¹ Excel áÅÐá¼è¹§Ò¹

µÑÇÍÂèÒ§

1. àÃÔèÁ¡Ò÷ӧҹ¢Í§ Microsoft Visual Studio .NET áÅÐÊÃéÒ§â¤Ã§¡ÒÃãËÁè¢Í§á;ÅÔपѹ Windows .NET Visual Basic
2. à¾ÔèÁ 3»ØèÁµÑǤǺ¤ØÁáÅÐÊͧDataGridµÑǤǺ¤ØÁ¿ÍÃìÁàÃÔèÁµé¹ (Form1) ¡ÒÃà»ÅÕè¹á»Å§¹Ñé¹¢éͤÇÒÁ¤Ø³ÊÁºÑµÔ¹Õé»ØèÁ¤Çº¤ØÁ¡Òô֧¢éÍÁÙÅàÁµÒ,ÃÒ¡ÒõÒÃÒ§ÃٻẺáÅÐÃٻẺ¤ÍÅÑÁ¹ìÃÒ¡ÒõÒÁÅӴѺ
3. ÊÅѺä»ÂѧâÁ´ÙÃËÑʢͧ¿ÍÃìÁ áÅÐà¾ÔèÁµèÍ仹Õé¹Óà¢éÒ¤ÓªÕéᨧ´éÒ¹º¹:

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

4. á·Ã¡ declarations ÃдѺâÁ´ÙŵèÍ仹Õé㹡ÒÃẺ¿ÍÃìÁ¤ÅÒËÅѧ¨Ò¡ºÃ÷Ѵ "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âËÅ´ÃÒ¡ÒõÒÃÒ§áÅФÍÅÑÁ¹ì populatesDataGrid¤Çº¤ØÁ áÅСÒûÃѺ»ÃاÃÒ¡ÒäÍÅÑÁ¹ìàÁ×èÍà»ÅÕè¹á»Å§µÒÃÒ§·ÕèàÅ×Í¡

       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 «Ö觷ÓãËéà»ç¹ÁÒ¡ "ÁÔµÃ" ÊèǹËÑǢͧ¤ÍÅÑÁ¹ì áÅÐáÊ´§à©¾ÒФÍÅÑÁ¹ì·ÕèÊÕè¢Í§¢éÍÁÙÅ·Õèà»ç¹»ÃÐ⪹ì¨Ò¡¤ÍÅÑÁ¹ì nine ·ÕèÊ觡ÅѺâ´Â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
    ¤ÍÅÑÁ¹ì·ÕèÇèÒ§à»ÅèÒ mostly àËÅèÒ¹ÕéäÁèä´éáÊ´§:
    table_catalog
    table_schema
    TABLE_NAME (·ÃÒºÍÂÙèáÅéÇ)
    column_guid
    column_propid
    COLUMN_HASDEFAULT (à·ç¨àÊÁÍ)
    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 db)
  µÑÇàÅ¢	5 adDouble	5 OleDbType.Double
  Ê¡ØÅà§Ô¹	6 adCurrency	6 OleDbType.Currency
  Çѹ·Õè/àÇÅÒ	7 adDate	7 OleDbType.Date
  Boolean	11 adBoolean	11 OleDbType.Boolean
  ¢éͤÇÒÁ<>	202 ´éÇ adVarWChar	130 OleDbType.WChar
  ºÑ¹·Ö¡	203 adLongVarWChar	130 OleDbType.WChar

• GetOleDbSchemaTableàËÁ×͹¡ÑºOpenSchemaÊ觡ÅѺ¤èÒ¤ÍÅÑÁ¹ìà´ÕÂÇ "F1" ¨Ò¡á¼è¹ÇèÒ§§Ò¹ Excel ¶Ö§áÁéÇèÒã¹ fact ÁÕ·ÕèÍÂÙè¢Í§ÊèǹËÑÇäÁèÁÕ¢éÍÁÙÅËÃ×ͤÍÅÑÁ¹ì

ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé¤ÅÔ¡ËÁÒÂàÅ¢º·¤ÇÒÁµèÍ仹Õé à¾×èÍ´Ùº·¤ÇÒÁã¹°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft::