How To Query and Update Excel Data Using ADO From ASP

Resumo

Este artigo demonstra como consultar e atualizar informações numa folha de cálculo do Excel com o ActiveX Data Objects (ADO) a partir de uma página do Active Server Pages (ASP). O artigo também descreve as limitações associadas a este tipo de aplicação.

Mais Informações

Para aceder aos dados na sua folha de cálculo do Excel para este exemplo, utilize o Controlador Microsoft ODBC para Excel. Crie uma tabela para aceder aos dados ao criar um Intervalo com Nome na sua folha de cálculo do Excel.

Passos para Criar Aplicação de Exemplo

• Crie o ficheiro do Excel ADOtest.xls com os seguintes dados na folha1:

  coluna1	coluna2	coluna3
  rr	este	15
  bb	testar	20
  ee	funciona	25

  Nota

  Se uma coluna na sua folha de cálculo do Excel contiver texto e números, o controlador ODBC do Excel não conseguirá interpretar corretamente que tipo de dados deve ser a coluna. Certifique-se de que todas as células numa coluna têm o mesmo tipo de dados. Os três erros seguintes podem ocorrer se cada célula numa coluna não for do mesmo tipo ou se tiver os tipos misturados entre "texto" e "geral":

  1. Erro "80040e21" do Fornecedor OLE DB para Controladores ODBC: as propriedades do pedido não podem ser suportadas por este Controlador ODBC.
  2. Erro "80004005" do Fornecedor OLE DB para Controladores ODBC A consulta não é atualizável porque não contém colunas pesquisáveis para utilizar como chave esperançosa.
  3. Falha na atualização baseada em consultas do Fornecedor OLE DB para Controladores ODBC "80004005". Não foi possível localizar a linha a atualizar.

• Crie um Intervalo com Nome, myRange1, na sua folha de cálculo:

  1. Realce as linhas e colunas onde residem os dados.
  2. No menu Inserir, aponte para Nome e clique em Definir.
  3. Introduza o nome myRange1 para o nome do Intervalo Com Nome.
  4. Clique em OK.

  O Intervalo com Nome myRange1 contém os seguintes dados:

  coluna1	coluna2	coluna3
  rr	este	15
  bb	testar	20
  ee	funciona	25

  Nota

  • O ADO pressupõe que a primeira linha numa consulta do Excel contém os cabeçalhos de coluna. Por conseguinte, o Intervalo com Nome tem de incluir os cabeçalhos de coluna. Este é um comportamento diferente do DAO.
  • Os cabeçalhos de coluna não podem ser um número. O controlador do Excel não consegue interpretá-los e, em vez disso, devolve uma referência de célula. Por exemplo, um cabeçalho de coluna de "F1" seria mal interpretado.

• Crie um Nome de Origem de Dados do Sistema ODBC (DSN) que aponte para o ficheiro de ADOTest.xls.

  1. No Painel de Controlo, abra o Administrador ODBC.
  2. No separador DSN do Sistema, clique em Adicionar.
  3. Selecione Controlador do Microsoft Excel (*.xls) e clique em Concluir. Se esta opção não existir, terá de instalar o controlador Microsoft ODBC para Excel a partir da configuração do Excel.
  4. Selecione ADOExcel para o Nome da Origem de Dados.
  5. Certifique-se de que a Versão está definida para a versão correta do Excel.
  6. Clique em "Selecionar Livro...", navegue para o ficheiro ADOTest.xls e clique em OK.
  7. Clique no botão "Opções>>" e desmarque a caixa de verificação "Só de Leitura".
  8. Clique em OK e, em seguida, clique novamente em OK.

• Defina permissões no ficheiro ADOTest.xls.

Se a sua Página do Active Server for acedida anonimamente, tem de se certificar de que a Conta Anónima (IUSR_<MachineName>) tem, pelo menos, acesso de Leitura/Escrita (RW) à folha de cálculo. Se quiser eliminar informações da folha de cálculo, tem de conceder as permissões em conformidade.

Se estiver a autenticar o acesso à sua Página do Active Server, tem de garantir que todos os utilizadores que acedem à sua aplicação têm as permissões adequadas.

Se não definir as permissões adequadas na folha de cálculo, 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 o 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. Guarde e atribua um nome à sua Página do Servidor Ativo e veja-a no browser. Verá o seguinte:

   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|