Comment interroger et mettre à jour des données Excel à l’aide d’ADO à partir d’ASP

Résumé

Cet article explique comment interroger et mettre à jour des informations dans une feuille de calcul Excel à l’aide d’ActiveX Data Objects (ADO) à partir d’une page ASP (Active Server Pages). L’article décrit également les limitations associées à ce type d’application.

Informations supplémentaires

Pour accéder aux données de votre feuille de calcul Excel pour cet exemple, utilisez microsoft ODBC Driver pour Excel. Créez un tableau pour accéder aux données en créant une plage nommée dans votre feuille de calcul Excel.

Étapes de création d’un exemple d’application

• Créez le fichier Excel ADOtest.xls avec les données suivantes dans la feuille 1 :

  column1	column2	column3
  Rr	Ce	15
  Bb	test	20
  Ee	Fonctionne	25

  Remarque

  Si une colonne de votre feuille de calcul Excel contient du texte et des nombres, le pilote ODBC Excel ne peut pas interpréter correctement le type de données que doit être la colonne. Vérifiez que toutes les cellules d’une colonne sont du même type de données. Les trois erreurs suivantes peuvent se produire si chaque cellule d’une colonne n’est pas du même type ou si les types sont mélangés entre « text » et « general » :

  1. Erreur « 80040e21 » du fournisseur Microsoft OLE DB pour les pilotes ODBC. Les propriétés de requête ne peuvent pas être prises en charge par ce pilote ODBC.
  2. Erreur du fournisseur Microsoft OLE DB pour les pilotes ODBC « 80004005 » La requête n’est pas modifiable, car elle ne contient aucune colonne pouvant faire l’objet d’une recherche à utiliser comme clé d’espoir.
  3. Échec de l’erreur « 80004005 » de mise à jour basée sur la requête du fournisseur Microsoft OLE DB pour les pilotes ODBC. La ligne à mettre à jour est introuvable.

• Créez une plage nommée, myRange1, dans votre feuille de calcul :

  1. Mettez en surbrillance la ou les lignes et la ou les colonnes où résident vos données.
  2. Dans le menu Insertion, pointez sur Nom, puis cliquez sur Définir.
  3. Entrez le nom myRange1 pour le nom de la plage nommée.
  4. Cliquez sur OK.

  La plage nommée myRange1 contient les données suivantes :

  column1	column2	column3
  Rr	Ce	15
  Bb	test	20
  Ee	Fonctionne	25

  Remarque

  • ADO suppose que la première ligne d’une requête Excel contient les en-têtes de colonne. Par conséquent, la plage nommée doit inclure les en-têtes de colonne. Il s’agit d’un comportement différent de DAO.
  • Les en-têtes de colonne ne peuvent pas être un nombre. Le pilote Excel ne peut pas les interpréter et retourne à la place une référence de cellule. Par exemple, un en-tête de colonne de « F1 » serait mal interprété.

• Créez un nom de source de données système (DSN) ODBC pointant vers le fichier ADOTest.xls.

  1. À partir de la Panneau de configuration, ouvrez l’administrateur ODBC.
  2. Sous l’onglet DSN système, cliquez sur Ajouter.
  3. Sélectionnez Pilote Microsoft Excel (*.xls), puis cliquez sur Terminer. Si cette option n’existe pas, vous devez installer le pilote Microsoft ODBC pour Excel à partir de l’installation d’Excel.
  4. Choisissez ADOExcel comme Nom de la source de données.
  5. Vérifiez que version est définie sur la version correcte d’Excel.
  6. Cliquez sur « Sélectionner un classeur... », accédez au fichier ADOTest.xls, puis cliquez sur OK.
  7. Cliquez sur le bouton « Options>> » et désactivez la zone de case activée « Lecture seule ».
  8. Cliquez sur OK, puis de nouveau sur OK.

• Définissez les autorisations sur le fichier ADOTest.xls.

Si votre page Active Server est accessible de manière anonyme, vous devez vous assurer que le compte anonyme (IUSR_<MachineName>) dispose au moins d’un accès en lecture/écriture (RW) à la feuille de calcul. Si vous souhaitez supprimer des informations de la feuille de calcul, vous devez accorder les autorisations en conséquence.

Si vous authentifiez l’accès à votre page Active Server, vous devez vous assurer que tous les utilisateurs qui accèdent à votre application disposent des autorisations appropriées.

Si vous ne définissez pas les autorisations appropriées sur la feuille de calcul, vous obtenez un message d’erreur semblable au suivant :

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. Créez une page ASP et collez le code suivant :

      <!-- 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. Enregistrez et nommez votre page Active Server et affichez-la dans le navigateur. Les informations suivantes s’affichent :

   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|