Como utilizar o ADO.NET para obter e modificar registos de um livro do Excel com o Visual Basic .NET

Voltar ao topo

Como utilizar o fornecedor OLE DB do Jet com livros do Microsoft Excel

Cadeia de ligação

Tipos de dados

Convenções de nomenclatura de tabela

• Utilizar o nome de folha seguido de um cifrão (por exemplo, [Folha1 $] ou [minha folha de cálculo $]). Uma tabela de livro que é referenciada desta forma inclui o intervalo da folha de cálculo completamente utilizado.
  Seleccionar * da [Folha1 $]
• Utilize um intervalo com um nome definido (por exemplo, [MyNamedRange]):
  Seleccionar * da [MyNamedRange]
• Utilizar um intervalo com um endereço específico (por exemplo, [Folha1 $ A1: B10]):
  Seleccionar * da [Folha1 $ A1: B10]

Voltar ao topo

Como livros do Excel utilizar como origem de dados de ADO.NET

Obter registos

Adicionar e actualizar registos

• Execute directamente um comando para inserir ou actualizar registos um cada vez. Para efectuar este procedimento, pode criar um objecto OLEDbCommand na ligação e defina a propriedade CommandText para um comando válido para inserir registos
  
  

  INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')

  ou um comando para actualizar registos
  
  

  UPDATE [Sheet1$] SET F2 = 'XYZ' WHERE F1 = '111'

  e, em seguida, chamar o método ExecuteNonQuery .
• Efectue alterações a um DataSet que tenha preenchido com uma tabela/consulta a partir de um livro do Excel e, em seguida, chamar o método Update de ' DataAdapter ' para resolver alterações do DataSet a voltar para o livro. No entanto, a utilizar o método de actualização para resolução de alteração tem de definir com parâmetros comandos para o ' DataAdapter ' do InsertCommand

  INSERT INTO [Sheet1$] (F1, F2) values (?, ?)

  e UpdateCommand :

  UPDATE [Sheet1$] SET F2 = ? WHERE F1 = ?

  comandos parametrizadas INSERT e UPDATE são necessários porque OleDbDataAdapter não fornece informações de chave/índice para livros do Excel; sem campos de chave/índice, o CommandBuilder não é possível gerar automaticamente os comandos para o utilizador.
• Exportar dados provenientes de dados de outro origem para um livro do Excel desde que a origem de dados possa ser utilizada com o Jet fornecedor de OLE DB. Dados de origens que pode utilizar com o fornecedor de Jet OLE DB desta forma incluem ficheiros de texto, bases de dados do Microsoft Access e, claro, outros livros do Excel. Com um único INSERT INTO comando, é possível exportar dados de outra tabela/consulta no livro:

  INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"

  INSERT INTO requer que a tabela de destino (ou folha de cálculo) já existe; dados são acrescentados à tabela de destino.
  
  Pode também utilizar SELECTINTO para exportar a tabela/consulta para um livro:

  SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]

  quando utiliza SELECTINTO, se a tabela de destino ou o livro não existir, será criado por si. Se a tabela já existir antes do SELECTINTO comando é emitido, receberá um erro.

Eliminar registos

Criar livros e tabelas

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

Voltar ao topo

Passo a passo

Código de exemplo

1. Inicie um novo projecto de Visual Basic .NET Aplicação do Windows .
   
   é criado o Form1 por predefinição.
2. Adicione controlos de tipo ' botão de opção ' seis e um controlo de botão ao Form1 .
3. Seleccione todos os controlos do tipo ' botão de opção ' e defina a propriedade de tamanho para 200,24 .
4. No menu Ver , clique em código .
5. Adicione a seguinte linha ao início muito do módulo de código:

   Imports System.Data.OleDb

6. Insira o seguinte código de classe de formulário :

   Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=C:\ExcelData1.xls;" & _
                  "Extended Properties=""Excel 8.0;HDR=YES"""
   
   Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=C:\ExcelData2.xls;" & _
                  "Extended Properties=""Excel 8.0;HDR=YES"""
   
   Private m_sNorthwind = _
         "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
   
   Private m_sAction As String
   
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         RadioButton1.Text = "Create_Workbook"
         RadioButton2.Text = "Retrieve_Records"
         RadioButton3.Text = "Add_Records"
         RadioButton4.Text = "Update_Records"
         RadioButton5.Text = "Update_Individual_Cells"
         RadioButton6.Text = "Use_External_Source"
         Button1.Text = "Go!"
   End Sub
   
   Private Sub RadioButtons_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
         Handles RadioButton1.Click, RadioButton2.Click, RadioButton3.Click, _
         RadioButton4.Click, RadioButton5.Click, RadioButton6.Click
         m_sAction = sender.Text'Store the text for the selected radio button
   End Sub
   
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
         Try
            ' Call the associated routine to add/update/modify the workbook.
            Select Case m_sAction
               Case "Create_Workbook" : Create_Workbook()
               Case "Retrieve_Records" : Retrieve_Records()
               Case "Add_Records" : Add_Records()
               Case "Update_Records" : Update_Records()
               Case "Update_Individual_Cells" : Update_Individual_Cells()
               Case "Use_External_Source" : Use_External_Source()
            End Select
   
         Catch ex As OleDbException
            Dim er As OleDbError
            For Each er In ex.Errors
               MsgBox(er.Message)
            Next
         Catch ex2 As System.InvalidOperationException
            MsgBox(ex2.Message)
         End Try
   
   
   End Sub
   
   Public Sub Create_Workbook()
   
         ' If the workbooks already exist, prompt to delete.
         Dim answer As MsgBoxResult
         If Dir("C:\ExcelData1.xls") <> "" Or Dir("C:\ExcelData2.xls") <> "" Then
             answer = MsgBox("Delete existing workbooks (C:\ExcelData1.xls and " & _
                      "C:\ExcelData2.xls)?", MsgBoxStyle.YesNo)
             If answer = MsgBoxResult.Yes Then
                 If Dir("C:\ExcelData1.xls") <> "" Then Kill("C:\ExcelData1.xls")
                 If Dir("C:\ExcelData2.xls") <> "" Then Kill("C:\ExcelData2.xls")
             Else
                 Exit Sub
             End If
         End If
   
   '==========================================================================
         ' Create a workbook with a table named EmployeeData. The table has 3 
         ' fields: ID (char 255), Name (char 255) and Birthdate (date).  
   '==========================================================================
         Dim conn As New OleDbConnection()
         conn.ConnectionString = m_sConn1
         conn.Open()
         Dim cmd1 As New OleDbCommand()
         cmd1.Connection = conn
         cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
         cmd1.ExecuteNonQuery()
         cmd1.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')"
         cmd1.ExecuteNonQuery()
         conn.Close()
   
   '==========================================================================
         ' Create a workbook with a table named InventoryData. The table has 3 
         ' fields: Product (char 255), Qty (float) and Price (currency). 
   '==========================================================================
   
         conn.ConnectionString = m_sConn2
         conn.Open()
         Dim cmd2 As New OleDbCommand()
         cmd2.Connection = conn
         cmd2.CommandText = "CREATE TABLE InventoryData (Product char(255), Qty float, Price currency)"
         cmd2.ExecuteNonQuery()
         cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Cola', 200, 1.35)"
         cmd2.ExecuteNonQuery()
         cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Chips', 550, 0.89)"
         cmd2.ExecuteNonQuery()
         conn.Close()
   
         ' NOTE: You can ALTER and DROP tables in a similar fashion.
   
   End Sub
   
   Public Sub Retrieve_Records()
   
         '==========================================================
         'Use a DataReader to read data from the EmployeeData table.
         '==========================================================
   
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [EmployeeData$]", conn1)
         Dim rdr As OleDbDataReader = cmd1.ExecuteReader
   
         Debug.WriteLine(vbCrLf & "EmployeeData:" & vbCrLf & "=============")
         Do While rdr.Read()
            Debug.WriteLine(System.String.Format("{0,-10}{1, -15}{2}", _
               rdr.GetString(0), rdr.GetString(1), _
               rdr.GetDateTime(2).ToString("d")))
         Loop
         rdr.Close()
         conn1.Close()
   
         '========================================================
         'Use a DataSet to read data from the InventoryData table.
         '========================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds)
         Debug.WriteLine(vbCrLf & "InventoryData:" & vbCrLf & "==============")
         Dim dr As DataRow
         For Each dr In ds.Tables(0).Rows'Show results in output window
            Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
               dr("Product"), dr("Qty"), dr("Price")))
         Next
         conn2.Close()
   
   End Sub
   
   Public Sub Add_Records()
   
   '==========================================================================
         ' Run an INSERT INTO command to add new records to the workbook. 
   '==========================================================================
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn1
         cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('CCC', 'Charlie', '10/14/48')"
         cmd.ExecuteNonQuery()
         cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('DDD', 'Deloris', '7/19/98')"
         cmd.ExecuteNonQuery()
         conn1.Close()
   
         '====================================================================
         'Use the InsertCommand object to add new records to the InventoryData
         'table.
         '====================================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds, "MyExcelTable")
   
         ' Generate the InsertCommand and add the parameters for the command.
         da.InsertCommand = New OleDbCommand( _
            "INSERT INTO [InventoryData$] (Product, Qty, Price) VALUES (?, ?, ?)", conn2)
         da.InsertCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
         da.InsertCommand.Parameters.Add("@Qty", OleDbType.Double).SourceColumn = "Qty"
         da.InsertCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
   
         ' Add two new records to the dataset.
         Dim dr As DataRow
         dr = ds.Tables(0).NewRow
         dr("Product") = "Bread" : dr("Qty") = 390 : dr("Price") = 1.89 : ds.Tables(0).Rows.Add(dr)
         dr = ds.Tables(0).NewRow
         dr("Product") = "Milk" : dr("Qty") = 99 : dr("Price") = 2.59 : ds.Tables(0).Rows.Add(dr)
   
         ' Apply the dataset changes to the actual data source (the workbook).
         da.Update(ds, "MyExcelTable")
         conn2.Close()
   
   End Sub
   
   Public Sub Update_Records()
   
   '==========================================================================
         ' Run an UPDATE command to change a record in the EmployeeData
         ' table.
   '==========================================================================
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn1
         cmd.CommandText = "UPDATE [EmployeeData$] " & _
                       "SET NAME = 'Aaron', BirthDate = '5/4/1975' WHERE ID = 'AAA'"
         cmd.ExecuteNonQuery()
         conn1.Close()
   
         '====================================================================
         ' Use the UpdateCommand object to modify records in the InventoryData
         ' table.
         '====================================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds, "MyInventoryTable")
   
         ' Generate the UpdateCommand and add the parameters for the command.
         da.UpdateCommand = New OleDbCommand( _
            "UPDATE [InventoryData$] SET Qty = ?, Price=? WHERE Product = ?", conn2)
         da.UpdateCommand.Parameters.Add("@Qty", OleDbType.Numeric).SourceColumn = "Qty"
         da.UpdateCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
         da.UpdateCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
   
         ' Update the first two records.
         ds.Tables(0).Rows(0)("Qty") = 1000
         ds.Tables(0).Rows(0)("Price") = 10.1
         ds.Tables(0).Rows(1)("Qty") = 2000
         ds.Tables(0).Rows(1)("Price") = 20.2
   
         ' Apply the dataset changes to the actual data source (the workbook).
         da.Update(ds, "MyInventoryTable")
         conn2.Close()
   
   End Sub
   
   Public Sub Update_Individual_Cells()
   
   '==========================================================================
         ' Update individual cells on the EmployeeData worksheet; 
         ' specifically, cells F3, G3, and I4 are modified.
   '==========================================================================
   
         ' NOTE: The connection string indicates that the table does *NOT* 
         ' have a header row.
         Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn1.Replace("HDR=YES", "HDR=NO"))
         conn.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn
         cmd.CommandText = "UPDATE [EmployeeData$F3:G3] SET F1 = 'Cell F3', F2 = 'Cell G3'"
         cmd.ExecuteNonQuery()
         cmd.CommandText = "UPDATE [EmployeeData$I4:I4] SET F1 = 'Cell I4'"
         cmd.ExecuteNonQuery()
         conn.Close()
   
   End Sub
   
   Public Sub Use_External_Source()
   
         ' Open a connection to the sample Northwind Access database.
         Dim conn As New System.Data.OleDb.OleDbConnection( _
               "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_sNorthwind & ";")
         conn.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn
   
   '=======================================================================
         ' Run an INSERT..INTO command on the Northwind database to append 
         ' the records from a table/query to an existing table in the Excel 
         ' workbook.
   '=======================================================================
         cmd.CommandText = "INSERT INTO [EmployeeData$] IN 'C:\ExcelData1.xls' 'Excel 8.0;'" & _
            "SELECT EmployeeID AS ID, FirstName AS Name, BirthDate FROM Employees"
         cmd.ExecuteNonQuery()
   
   '==========================================================================
         ' Run a SELECT..INTO command on the Northwind database to insert 
         ' all the records from a table/query into a new sheet in the Excel 
         ' workbook.
   '==========================================================================
         cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\ExcelData2.xls].[ProductSales]" & _
                         "FROM [Product Sales for 1997]"
         cmd.ExecuteNonQuery()
   
         conn.Close()
   
   End Sub

7. Modifique o caminho de dados do Access de exemplo, Adamastor, para o membro m_sNorthwind no código, se necessário.

Tente-saída

1. No menu Ver , aponte para Windows outro e, em seguida, clique em saída para apresentar a janela de saída .
2. Prima F5 para compilar e executar o programa.
3. Clique em Create_Workbook e clique em Ir . O procedimento Create_Workbook executa comandos CREATE TABLE para criar dois novos livros: C:\ExcelData1.xls e C:\ExcelData2.xls. ExcelData1.xls contém uma folha de cálculo (tabela) denominado EmployeeData e ExcelData2.xls contém uma folha (tabela) com o nome InventoryData. As tabelas são preenchidas com registos.
   
   Nota : em cada passo restante neste teste, abra os livros no Excel para examinar os resultados. Ou, clique em Retrieve_Records para visualizar o conteúdo das tabelas na saída janela do Visual Studio .NET.
4. Clique em Retrieve_Records e clique em Ir . O procedimento Retrieve_Records extrai os registos de tabelas e apresenta-os na janela de saída , semelhante ao seguinte:

   EmployeeData:
   =============
   AAA       Andrew         12/4/1955
   
   InventoryData:
   ==============
   Cola           200   1.35
   Chips          550   0.89

5. Clique em Add_Records e clique em Ir . A rotina Add_Records adiciona dois registos a cada tabela:

   EmployeeData:
   =============
   AAA       Andrew         12/4/1955
   CCC       Charlie        10/14/1948
   DDD       Deloris        7/19/1998
   
   InventoryData:
   ==============
   Cola           200   1.35
   Chips          550   0.89
   Bread          390   1.89
   Milk           99    2.59

6. Clique em Update_Records e clique em Ir . A rotina Update_Records actualiza dois registos em cada livro:

   EmployeeData:
   =============
   AAA       Aaron          5/4/1975
   CCC       Charlie        10/14/1948
   DDD       Deloris        7/19/1998
   
   InventoryData:
   ==============
   Cola           1000  10.1
   Chips          2000  20.2
   Bread          390   1.89
   Milk           99    2.59

7. Clique em Update_Individual_Cells e clique em Ir . A rotina Update_Individual_Cells modifica células específicas numa folha de cálculo EmployeeData em ExcelData1.xls; especificamente, as células F3, compatível com G3 e I4 estão actualizadas.
8. Clique em Use_External_Source e clique em Ir . Quando utiliza um INSERTINTO comando, a rotina Use_External_Source acrescenta registos da tabela 'Empregados' base de dados Adamastor para a folha de cálculo EmployeeData no ExcelData1.xls. E Use_External_Source utiliza um SELECTINTO para criar uma nova tabela (ou folha) de ExcelData2.xls que contém todos os registos da tabela de base de dados Adamastor 'Produtos'.
   
   Nota : Se clicar em Use_External_Source mais do que uma vez, a lista de empregados será acrescentada várias vezes porque a chave primária não é reconhecida ou aplicada.

Voltar ao topo

Formatação da célula

1. Prima F5 para compilar e executar o exemplo.
2. No Form1, clique em Create_Workbook e clique em Ir .
3. Inicie o Microsoft Excel e abra C:\ExcelData1.xls.
4. Aplica um estilo negrito para a célula A2.
5. Aplicar um itálico, sublinhado estilo de célula B2 e Alinhar pelo centro.
6. Aplica um formato de data por extenso a célula C2.
7. Guarde e feche C:\ExcelData1.xls.
8. No Form1, clique em Add_Records e clique em Ir .
9. Abrir C:\ExcelData1.xls no Excel e repare que duas novas linhas tem herdado a formatação da primeira linha.

Voltar ao topo

Limitações

• Não pode inserir fórmulas nas células a utilizar o ADO.NET.
• O fornecedor de OLE DB do Jet não consegue fornecer informações de chave/índice para tabelas de um livro do Excel. Por este motivo, pode utilizar o CommandBuilder para gerar automaticamente actualizações e inserções para registos de um livro do Excel.

Voltar ao topo