Como usar o ADO.NET para recuperar e modificar registros em uma pasta de trabalho Excel com Visual Basic .NET

Voltar para o início

Como usar o Jet OLE DB Provider com pastas de trabalho Microsoft Excel

Seqüência de caracteres de conexão

Tipos de dados

Convenções de nome de tabela

• Usar o nome planilha seguido de um cifrão (por exemplo, [Sheet1 $] ou [My Worksheet $]). Uma tabela de pasta de trabalho que é referenciada dessa maneira inclui o intervalo totalmente usado da planilha.
  Selecione * from [Sheet1 $]
• Use um intervalo com um nome definido (por exemplo, [MyNamedRange]):
  Selecione * de [MyNamedRange]
• Usar um intervalo com um endereço específico (por exemplo, [Sheet1 $ A1: B10]):
  Selecione * de [Sheet1 $ A1: B10]

Voltar para o início

Como pastas de trabalho Excel usar como fontes de dados ADO.NET

Recuperar registros

Adicionar e atualizar registros

• Executar um comando para inserir ou atualizar registros um por vez diretamente. Para fazer isso, você pode criar um objeto OLEDbCommand em sua conexão e defina sua propriedade CommandText para um comando válido para inserir registros
  
  

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

  ou um comando para atualizar registros
  
  

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

  e, em seguida, chame o método ExecuteNonQuery .
• Fazer alterações em um DataSet que você tenha preenchido com uma tabela/consulta de uma pasta de trabalho do Excel e chamar o método Update do DataAdapter para resolver alterações do DataSet de volta para a pasta de trabalho. No entanto, para usar o método Update para alterar resolução você deve definir parametrizadas comandos para o DataAdapter do InsertCommand

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

  e UpdateCommand :

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

  comandos parametrizados INSERT e UPDATE são necessários porque o OleDbDataAdapter não fornece informações de chave/índice para pastas de trabalho do Excel; sem campos de chave/índice, o CommandBuilder não é possível gerar automaticamente os comandos para você.
• Exportar dados de dados de outro fonte em uma pasta de trabalho do Excel desde que fonte de dados pode ser usado com o Jet OLE DB Provider. Dados de fontes que você pode usar com o Jet OLE DB Provider dessa maneira incluem arquivos de texto, bancos de dados do Microsoft Access e, claro, outras pastas de trabalho Excel. Com um único INSERT INTO comando, você pode exportar dados de outra tabela/consulta em sua pasta de trabalho:

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

  INSERT INTO requer que a tabela de destino (ou planilha) já existe; os dados são acrescentados à tabela de destino.
  
  Você também pode usar SELECTINTO para exportar sua consulta (tabela para uma pasta de trabalho:

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

  quando você usar SELECTINTO, se a tabela de destino ou pasta de trabalho não existir, ela será criada para você. Se a tabela já existir antes do SELECTINTO comando é emitido, você receberá um erro.

Excluir registros

Criar pastas de trabalho e tabelas

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

Voltar para o início

Passo a passo

Código de exemplo

1. Inicie um novo projeto Visual Basic.NET Windows Application .
   
   o Form1 é criado por padrão.
2. Adicione controles RadioButton seis e um controle de botão ao Form1 .
3. Selecione todos os controles RadioButton e defina a propriedade Size como 200,24 .
4. No menu Exibir , clique em código .
5. Adicione a seguinte linha ao bem no início do módulo de código:

   Imports System.Data.OleDb

6. Insira o código a seguir na classe do 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 para o banco de dados de Access de exemplo, Northwind, para o membro m_sNorthwind no código, se necessário.

Experimente IT

1. No menu Exibir , aponte para Other Windows e, em seguida, clique em saída para exibir a janela de saída .
2. Pressione 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 duas novas pastas de trabalho: C:\ExcelData1.xls e C:\ExcelData2.xls. ExcelData1.xls contém uma planilha (tabela) chamado EmployeeData e ExcelData2.xls contém uma planilha (tabela) chamada InventoryData. As tabelas são preenchidas com registros.
   
   Observação : em cada etapa restante nesse teste, abra as pastas de trabalho no Excel para examinar os resultados. Ou, clique em Retrieve_Records para exibir 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 registros das tabelas e os exibe na janela Output , semelhante a este:

   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 registros 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 atualiza dois registros em cada pasta de trabalho:

   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 específicas células na planilha EmployeeData em ExcelData1.xls; especificamente, as células F3, G3 e I4 são atualizadas.
8. Clique em Use_External_Source e clique em Ir . Quando você usa um INSERTINTO comando, a rotina Use_External_Source acrescenta registros da tabela Northwind 'Employees' para a planilha EmployeeData na ExcelData1.xls. E Use_External_Source usa um SELECTINTO comando para criar uma nova tabela (ou planilha) em ExcelData2.xls que contém todos os registros da tabela Northwind 'Produtos'.
   
   Observação : se você clicar em Use_External_Source mais de uma vez, a lista de funcionários será acrescentada várias vezes porque a chave primária não é reconhecida ou aplicada.

Voltar para o início

Formatação de célula

1. Pressione F5 para criar e executar a amostra.
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 de fonte em negrito para a célula A2.
5. Aplicar um itálico, estilo, a célula B2 de sublinhado e Alinhar Centro.
6. Aplicar um formato de data por extenso a célula C2.
7. Salve e feche C:\ExcelData1.xls.
8. No Form1, clique em Add_Records e clique em Ir .
9. Abrir C:\ExcelData1.xls no Excel e observe que as duas novas linhas tem herdado a formatação da primeira linha.

Voltar para o início

Limitações

• Você não pode inserir fórmulas nas células usando o ADO.NET.
• O Jet OLE DB Provider não consegue fornecer informações de chave/índice para tabelas em uma pasta de trabalho do Excel. Por esse motivo, você não pode usar o CommandBuilder para gerar automaticamente as atualizações e inserções para registros em uma pasta de trabalho do Excel.

Voltar para o início