Como consultar e atualizar dados do Excel usando o ADO do ASP

Resumo

Este artigo demonstra como consultar e atualizar informações em uma planilha do Excel usando a ADO (ActiveX Data Objects) de uma página do ASP (Active Server Pages). O artigo também descreve as limitações associadas a esse tipo de aplicativo.

Informações adicionais

Para acessar os dados em sua planilha do Excel para este exemplo, use o Driver ODBC do Microsoft para Excel. Crie uma tabela para acessar os dados criando um Intervalo Nomeado na planilha do Excel.

Etapas para criar aplicativo de exemplo

• Crie o arquivo do Excel ADOtest.xls com os seguintes dados na planilha1:

  column1	column2	column3
  Rr	Este	15
  Bb	test	20
  Ee	Funciona	25

  Observação

  Se uma coluna na planilha do Excel contiver texto e números, o driver ODBC do Excel não poderá interpretar corretamente qual tipo de dados a coluna deve ser. Verifique se todas as células de uma coluna são do mesmo tipo de dados. Os três erros a seguir podem ocorrer se cada célula em uma coluna não for do mesmo tipo ou você tiver os tipos misturados entre "texto" e "geral":

  1. Microsoft OLE DB Provider for ODBC Drivers error '80040e21' As propriedades de solicitação não podem ser suportadas por este Driver ODBC.
  2. Erro do Provedor OLE DB da Microsoft para Drivers ODBC '80004005' A consulta não é atualizável porque não contém colunas pesquisáveis a serem usadas como uma chave esperançosa.
  3. Falha na atualização baseada em consulta do Microsoft OLE DB para drivers ODBC '80004005'. A linha a ser atualizada não pôde ser encontrada.

• Crie um intervalo nomeado, myRange1, em sua planilha:

  1. Realce a área de linhas e colunas onde seus dados residem.
  2. No menu Inserir, aponte para Nome e clique em Definir.
  3. Insira o nome myRange1 para o nome de intervalo nomeado.
  4. Clique em OK.

  O myRange1 do Intervalo Nomeado contém os seguintes dados:

  column1	column2	column3
  Rr	Este	15
  Bb	test	20
  Ee	Funciona	25

  Observação

  • O ADO pressupõe que a primeira linha em uma consulta do Excel contenha os títulos de coluna. Portanto, o Intervalo Nomeado deve incluir os títulos de coluna. Esse é um comportamento diferente do DAO.
  • Os títulos de coluna não podem ser um número. O driver do Excel não pode interpretá-los e, em vez disso, retorna uma referência de célula. Por exemplo, um título de coluna de "F1" seria mal interpretado.

• Crie um DSN (Nome da Fonte de Dados do Sistema ODBC) apontando para o arquivo ADOTest.xls.

  1. No Painel de Controle, abra o Administrador ODBC.
  2. Na guia DSN do Sistema, clique em Adicionar.
  3. Selecione Microsoft Excel Driver (*.xls) e clique em Concluir. Se essa opção não existir, você precisará instalar o driver ODBC do Microsoft para Excel na instalação do Excel.
  4. Escolha ADOExcel para o Nome da Fonte de Dados.
  5. Verifique se a Versão está definida como a versão correta do Excel.
  6. Clique em "Selecionar Pasta de Trabalho...", navegue até o arquivo ADOTest.xls e clique em OK.
  7. Clique no botão "Opções>>" e desmarque a caixa "Somente leitura" marcar.
  8. Clique em OK e clique em OK novamente.

• Defina permissões no arquivo ADOTest.xls.

Se sua Página do Servidor Ativo for acessada anonimamente, você precisará garantir que a Conta Anônima (IUSR_<MachineName>) tenha pelo menos acesso de Leitura/Gravação (RW) à planilha. Se você quiser excluir informações da planilha, precisará conceder as permissões de acordo.

Se você estiver autenticando o acesso à página do Active Server, precisará garantir que todos os usuários que acessam seu aplicativo tenham as permissões apropriadas.

Se você não definir as permissões apropriadas na planilha, receberá uma mensagem de erro semelhante à seguinte:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

1. Crie uma nova página asp e cole no seguinte código:

      <!-- Begin ASP Source Code -->
      <%@ LANGUAGE="VBSCRIPT" %>
      <%
        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open "ADOExcel"
   
   Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.ActiveConnection = objConn
        objRS.CursorType = 3                    'Static cursor.
        objRS.LockType = 2                      'Pessimistic Lock.
        objRS.Source = "Select * from myRange1"
        objRS.Open
   %>
   <br>
   <%
      Response.Write("Original Data")
   
   'Printing out original spreadsheet headings and values.
   
   'Note that the first recordset does not have a "value" property
      'just a "name" property.  This will spit out the column headings.
   
   Response.Write("<TABLE><TR>")
      For X = 0 To objRS.Fields.Count - 1
         Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
      Next
      Response.Write("</TR>")
      objRS.MoveFirst
   
   While Not objRS.EOF
         Response.Write("<TR>")
         For X = 0 To objRS.Fields.Count - 1
            Response.write("<TD>" & objRS.Fields.Item(X).Value)
         Next
         objRS.MoveNext
         Response.Write("</TR>")
      Wend
      Response.Write("</TABLE>")
   
   'The update is made here
   
   objRS.MoveFirst
      objRS.Fields(0).Value = "change"
      objRS.Fields(1).Value = "look"
      objRS.Fields(2).Value = "30"
      objRS.Update
   
   'Printing out spreadsheet headings and values after update.
   
   Response.Write("<br>Data after the update")
      Response.Write("<TABLE><TR>")
      For X = 0 To objRS.Fields.Count - 1
         Response.Write("<TD>" & objRS.Fields.Item(X).Name & "</TD>")
      Next
      Response.Write("</TR>")
      objRS.MoveFirst
   
   While Not objRS.EOF
         Response.Write("<TR>")
         For X = 0 To objRS.Fields.Count - 1
            Response.write("<TD>" & objRS.Fields.Item(X).Value)
         Next
         objRS.MoveNext
         Response.Write("</TR>")
      Wend
      Response.Write("</TABLE>")
   
   'ADO Object clean up.
   
   objRS.Close
      Set objRS = Nothing
   
   objConn.Close
      Set objConn = Nothing
   %>
   <!-- End ASP Source Code -->
   

2. Salve e nomeie sua Página do Servidor Ativo e exiba-a no navegador. Você verá:

   Original Data:
   
   |column1|column2|column3|
   |------------|------------|------------|
   |rr|this|30|
   |bb|test|20|
   |tt|works|25|
   
   Data after the update:
   
   |column1|column2|column3|
   |------------|------------|------------|
   |change|look|30|
   |bb|test|20|
   |tt|works|25|