利用ADO.NET连接Excel数据库，并执行相应的操作

近几天在研究利用ADO.NET连接Excel数据库，将其要点汇总到一个类中，总结如下：

Imports System.Data.OleDbImports System.Windows.Forms''' <summary>''' 利用ADO.NET连接Excel数据库，并执行相应的操作：''' 创建表格，读取数据，写入数据，获取工作簿中的所有工作表名称。''' </summary>''' <remarks></remarks>Public Class ExcelConnection    Public Shared Sub main()        Dim strPath As String = "C:\Users\Administrator\Desktop\测试Visio与Excel交互\数据.xlsx"        Call DoSomeThingWithOleDbConnection(strPath)    End Sub    Public Shared Sub DoSomeThingWithOleDbConnection(ByVal strFilePath As String)        '创建对于Excel数据库的连接        Dim conn As OleDbConnection = GetExcelConnection(strFilePath)        '打开连接        conn.Open()        ' -------------------------------------        Call CreateNewTable(conn)        ' -------------------------------------        Call WriteToExcel(conn)        ' -------------------------------------        Call GetDataFromExcel(conn)        ' -------------------------------------        Dim sheetNames As String() = GetSheetsName(conn)        ' -------------------------------------        conn.Close()    End Sub    ''' <summary>    ''' 创建对Excel工作簿的连接    ''' </summary>    ''' <param name="WorkbookPath">要进行连接的Excel工作簿的路径</param>    ''' <returns>一个OleDataBase的Connection连接，此连接还没有Open。</returns>    ''' <remarks></remarks>    Private Shared Function GetExcelConnection(ByVal WorkbookPath As String) As OleDbConnection        Dim strConn As String = String.Empty        If WorkbookPath.EndsWith("xls") Then            strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " +                       "Data Source=" + WorkbookPath + "; " +                       "Extended Properties='Excel 8.0;IMEX=1'"        ElseIf WorkbookPath.EndsWith("xlsx") OrElse WorkbookPath.EndsWith("xlsb") Then            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +                      "Data Source=" + WorkbookPath + ";" +                      "Extended Properties=""Excel 12.0;HDR=YES"""        End If        Dim conn As OleDbConnection = New OleDbConnection(strConn)        Return conn    End Function    ''' <summary>    ''' 从对于Excel的数据连接中获取Excel工作簿中的所有工作表    ''' </summary>    ''' <param name="conn"></param>    ''' <returns></returns>    ''' <remarks></remarks>    Private Shared Function GetSheetsName(ByVal conn As OleDbConnection) As String()        '如果连接已经关闭，则先打开连接        If conn.State = ConnectionState.Closed Then conn.Open()        '获取工作簿连接中的每一个工作表，        '注意下面的Rows属性返回的并不是Excel工作表中的每一行，而是Excel工作簿中的所有工作表。        Dim Tables As DataRowCollection = conn.GetSchema("Tables").Rows        '        Dim sheetNames(0 To Tables.Count - 1) As String        For i As Integer = 0 To Tables.Count - 1            '注意这里的表格Table是以DataRow的形式出现的。            Dim Tb As DataRow = Tables.Item(i)            Dim Tb_Name As Object = Tb.Item("TABLE_NAME")            sheetNames(i) = Tb_Name.ToString        Next        Return sheetNames    End Function    ''' <summary>    ''' 读取Excel工作簿中的数据    ''' </summary>    ''' <param name="conn"></param>    ''' <remarks></remarks>    Private Shared Sub GetDataFromExcel(ByVal conn As OleDbConnection)        '如果连接已经关闭，则先打开连接        If conn.State = ConnectionState.Closed Then conn.Open()        '创建向数据库发出的指令        Dim olecmd As OleDbCommand = conn.CreateCommand()        '类似SQL的查询语句这个[Sheet1$对应Excel文件中的一个工作表]          '如果要提取Excel中的工作表中的某一个指定区域的数据，可以用："select * from [Sheet3$A1:C5]"        olecmd.CommandText = "select * from [Sheet3$]"        '创建数据适配器——根据指定的数据库指令        Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(olecmd)        '创建一个数据集以保存数据        Dim dtSet As DataSet = New DataSet()        '将数据适配器按指令操作的数据填充到数据集中的某一工作表中（默认为“Table”工作表）        Adapter.Fill(dtSet)        '将数据适配器按指令操作的数据填充到数据集的另一工作表“工作表2”中；        '如果这个工作表与前面的工作表同名，则是继续附加填充到同一工作表中。        '也可以将数据填充到其他数据集的某一工作表中。        Adapter.Fill(dtSet, "工作表2")        '按上面的操作方式，此时dtSet中有两个工作表，分别为“Table”与“工作表2”，        '其中的数据都是由 "select * from [Sheet3$]"得到的Excel中工作表Sheet3中的数据。        Dim intTablesCount As Integer = dtSet.Tables.Count        '索引数据集中的第一个工作表对象        Dim DataTable As System.Data.DataTable = dtSet.Tables(0) ' conn.GetSchema("Tables")        '工作表中的数据有8列9行(它的范围与用Worksheet.UsedRange所得到的范围相同。        '不一定是写有数据的单元格才算进行，对单元格的格式，如底纹，字号等进行修改的单元格也在其中。)        Dim intRowsInTable As Integer = DataTable.Rows.Count        Dim intColsInTable As Integer = DataTable.Columns.Count        '提取每一行数据中的“成绩”数据        For i As Integer = 0 To intRowsInTable - 1            Debug.Print(DataTable.Rows(i)("成绩").ToString())        Next    End Sub    ''' <summary>    ''' 向Excel工作表中插入一条数据    ''' </summary>    ''' <param name="conn"></param>    ''' <remarks></remarks>    Private Shared Sub WriteToExcel(ByVal conn As OleDbConnection)        Using ole_cmd As OleDbCommand = conn.CreateCommand()            '在插入数据时，字段名必须是数据表中已经有的字段名，插入的数据类型也要与字段下的数据类型相符。            ole_cmd.CommandText = "insert into [Sheet1$](学号,性别) values('B154','1')"            Try                '这种插入方式在Excel中的实时刷新的，也就是说插入时工作簿可以处于打开的状态，                '而且这里插入后在Excel中会立即显示出插入的值。                ole_cmd.ExecuteNonQuery()                MessageBox.Show("数据插入成功。")            Catch ex As Exception                MessageBox.Show("数据插入失败，错误信息： " + ex.Message)                Exit Sub            End Try        End Using    End Sub    ''' <summary>    ''' 创建一个新的Excel工作表，并向其中插入一条数据    ''' </summary>    ''' <param name="conn"></param>    ''' <remarks></remarks>    Private Shared Sub CreateNewTable(ByVal conn As OleDbConnection)        Using ole_cmd As OleDbCommand = conn.CreateCommand()            '----- 生成Excel表格 --------------------            '要新创建的表格不能是在Excel工作簿中已经存在的工作表。            ole_cmd.CommandText = "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)"            Try                '在工作簿中创建新表格时，Excel工作簿不能处于打开状态                ole_cmd.ExecuteNonQuery()                MessageBox.Show("创建Excel文件成功。")            Catch ex As Exception                MessageBox.Show("创建Excel文件失败，错误信息： " + ex.Message)                Exit Sub            End Try            '----- 在新生成的表格中插入一条数据 --------------------            ole_cmd.CommandText = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','点击科技')"            Try                '这种插入方式在Excel中的实时刷新的，也就是说插入时工作簿可以处于打开的状态，                '而且这里插入后在Excel中会立即显示出插入的值。                ole_cmd.ExecuteNonQuery()                MessageBox.Show("数据插入成功。")            Catch ex As Exception                MessageBox.Show("数据插入失败，错误信息： " + ex.Message)                Exit Sub            End Try        End Using    End SubEnd Class