Procédure pas à pas : modification des données mises en cache dans un classeur sur un serveur

Article
03/04/2013

Cette procédure pas à pas montre comment modifier un groupe de données mis en cache dans un classeur Microsoft Office Excel sans démarrer Excel à l'aide de la classe ServerDocument.

S'applique à : Les informations contenues dans cette rubrique s'appliquent aux projets de niveau document pour Excel 2013 et Excel 2010. Pour en savoir plus, consultez Fonctionnalités disponibles par type d'application et de projet Office.

Cette procédure pas à pas décrit les tâches suivantes :

Définition d'un dataset qui contient des données de la base de données AdventureWorksLT.
Création d'instances du groupe de données dans un projet de classeur Excel et un projet d'application console.
Création d'un ListObject lié au groupe de données dans le classeur et remplissage du ListObject avec les données lorsque le classeur est ouvert.
Ajout du groupe de données du classeur au cache de données.
Modification d'une colonne de données dans le groupe de données mis en cache en exécutant le code dans l'application console, sans démarrer Excel.

Bien que cette procédure pas à pas suppose que vous exécutiez le code sur votre ordinateur de développement, le code présenté par ce biais peut être mis en œuvre sur un serveur sur lequel Excel n'est pas installé.

[!REMARQUE]

Il est possible que pour certains des éléments de l'interface utilisateur de Visual Studio, votre ordinateur affiche des noms ou des emplacements différents de ceux indiqués dans les instructions suivantes.Ces éléments dépendent de l'édition de Visual Studio dont vous disposez et des paramètres que vous utilisez.Pour plus d'informations, consultez Paramètres Visual Studio.

Composants requis

Pour exécuter cette procédure pas à pas, vous devez disposer des composants suivants :

Une édition de Visual Studio 2012 qui inclut les outils de développement Microsoft Office. Pour plus d'informations, consultez [Configuration d'un ordinateur pour développer des solutions Office](bb398242\(v=vs.110\).md).

Excel 2010.
Accès à une instance en cours d'exécution de Microsoft SQL Server ou Microsoft SQL Server Express à laquelle l'exemple de base de données AdventureWorksLT est attaché.Vous pouvez télécharger la base de données AdventureWorksLT à partir du site Web CodePlex.Pour plus d'informations sur la façon d'attacher une base de données, consultez les rubriques suivantes :
- Pour attacher une base de données en utilisant SQL Server Management Studio ou SQL Server Management Studio Express, consultez Procédure : attacher une base de données (SQL Server Management Studio).
- Pour attacher une base de données en utilisant la ligne de commande, consultez Procédure : attacher un fichier de base de données à SQL Server Express.

Création d'un projet de bibliothèque de classes définissant un groupe de données

Pour utiliser le même groupe de données dans un projet de classeur Excel et une application console, vous devez définir le groupe de données dans un assembly séparé référencé par ces deux projets.Pour cette procédure pas à pas, définissez le groupe de données dans un projet de bibliothèque de classes.

Pour créer le projet Bibliothèque de classes

Démarrez Visual Studio.
Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Projet.
Dans le volet des modèles, développez Visual C# ou Visual Basic, puis cliquez sur Fenêtres.
Dans la liste des modèles de projet, sélectionnez Bibliothèque de classes.
Dans la zone Nom, tapez AdventureWorksDataSet.
Cliquez sur Parcourir pour accéder au dossier %UserProfile%\Mes documents (Windows XP ou version antérieure) ou %UserProfile%\Documents (Windows Vista), puis cliquez sur Sélectionner un dossier.
Dans la boîte de dialogue Nouveau projet, vérifiez que la case à cocher Créer le répertoire pour la solution n'est pas activée.
Cliquez sur OK.

Visual Studio ajoute le projet AdventureWorksDataSet à l'Explorateur de solutions et ouvre le fichier de code Class1.cs ou Class1.vb.
Dans l'Explorateur de solutions, cliquez avec le bouton droit sur Class1.cs ou Class1.vb, puis sélectionnez Supprimer.Vous n'avez pas besoin de ce fichier pour cette procédure pas à pas.

Définition d'un groupe de données dans le projet de bibliothèque de classes

Définissez un dataset typé qui contient des données de la base de données AdventureWorksLT pour SQL Server 2005.Plus tard au cours de cette procédure, vous ferez référence à ce dataset à partir d'un projet de classeur Excel et d'un projet d'application console.

Le groupe de données est un groupe de données typé qui représente les données de la table Product de la base de données AdventureWorksLT.Pour plus d'informations sur les groupes de données typés, consultez Utilisation de groupes de données dans Visual Studio.

Pour définir un groupe de données typé dans le projet de bibliothèque de classes

Dans l'Explorateur de solutions, cliquez sur le projet AdventureWorksDataSet.
Si la fenêtre Sources de données n'est pas visible, affichez- la par, dans la barre de menus, choisissant Afficher, Autres fenêtres, Sources de données.
Choisissez Ajouter une nouvelle source de données pour démarrer Assistant Configuration de source de données.
Cliquez sur Base de données, puis sur Suivant.
Si vous disposez d'une connexion active à la base de données AdventureWorksLT, choisissez cette connexion, puis cliquez sur Suivant.

Dans le cas contraire, cliquez sur Nouvelle connexion, puis utilisez la boîte de dialogue Ajouter une connexion pour créer la nouvelle connexion.Pour plus d'informations, consultez Comment : établir une connexion à des données d'une base de données.
Sur la page Enregistrer la chaîne de connexion dans le fichier de configuration de l'application, cliquez sur Suivant.
Sur la page Choisir vos objets de base de données, développez le nœud Tables, puis sélectionnez Product (SalesLT).
Cliquez sur Terminer.

Le fichier AdventureWorksLTDataSet.xsd est ajouté au projet AdventureWorksDataSet.Ce fichier définit les éléments suivants :
- un groupe de données typé nommé AdventureWorksLTDataSet.Ce groupe de données représente le contenu de la table Product dans la base de données AdventureWorksLT.
- Un TableAdapter nommé ProductTableAdapter.Ce TableAdapter peut être utilisé pour lire et écrire des données dans AdventureWorksLTDataSet.Pour plus d'informations, consultez Vue d'ensemble de TableAdapter.
Vous utiliserez ces deux objets plus loin dans cette procédure pas à pas.
Dans l'Explorateur de solutions, cliquez avec le bouton droit sur AdventureWorksDataSet, puis sélectionnez Générer.

Vérifiez que la génération du projet s'exécute correctement.

Création d'un projet de classeur Excel

Créez un projet de classeur Excel pour assurer l'interface vers les données.Plus loin dans cette procédure pas à pas, vous allez créer un objet ListObject qui affiche les données et vous allez ajouter une instance du groupe de données au cache de données dans le classeur.

Pour créer le projet de classeur Excel

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur la solution AdventureWorksDataSet, pointez sur Ajouter, puis cliquez sur Nouveau projet.
Dans le volet de modèles, développez Visual C# ou Visual Basic, puis développez Office.
Sous le nœud développé Office, sélectionnez le nœud 2010 .
Dans la liste des modèles de projet, sélectionnez le projet de classeur Excel.
Dans la zone Nom, tapez AdventureWorksReport.Ne modifiez pas l'emplacement.
Cliquez sur OK.

L'Assistant Projet Visual Studio Tools pour Office s'ouvre.
Assurez-vous d'avoir sélectionné Créer un nouveau document, puis cliquez sur OK.

Visual Studio ouvre le classeur AdventureWorksReport dans le concepteur et ajoute le projet AdventureWorksReport à l'Explorateur de solutions.

Ajout du groupe de données aux sources de données du projet de classeur Excel

Avant de pouvoir afficher le groupe de données dans le classeur Excel, vous devez l'ajouter aux sources de données du projet de classeur Excel.

Pour ajouter le groupe de données aux sources de données dans le projet de classeur Excel

Dans l'Explorateur de solutions, double-cliquez sur Sheet1.cs ou sur Sheet1.vb sous le projet AdventureWorksReport.

Le classeur s'ouvre dans le concepteur.
Dans le menu Données, cliquez sur Ajouter une nouvelle source de données.

L'Assistant Configuration de source de données s'ouvre.
Cliquez sur Objet, puis sur Suivant.
Sur la page Sélectionner l'objet que vous souhaitez lier, cliquez sur Ajouter une référence.
Sous l'onglet Projets, cliquez sur AdventureWorksDataSet puis cliquez sur OK.
Sous l'espace de noms AdventureWorksDataSet de l'assembly AdventureWorksDataSet, cliquez sur AdventureWorksLTDataSet, puis sur Terminer.

La fenêtre Sources de données s'ouvre et AdventureWorksLTDataSet est ajouté à la liste des sources de données.

Création d'un ListObject lié à une instance du groupe de données

Pour afficher le groupe de données dans le classeur, créez un ListObject lié à une instance du groupe de données.Pour plus d'informations sur la liaison des contrôles aux données, consultez Liaison de données aux contrôles dans les solutions Office.

Pour créer un ListObject lié à une instance du groupe de données

Dans la fenêtre Sources de données, développez le nœud AdventureWorksLTDataSet, sous AdventureWorksDataSet.
Sélectionnez le nœud Product, cliquez sur la flèche de déroulement qui s'affiche et sélectionnez ListObject dans la liste déroulante.

Si la flèche de déroulement ne s'affiche pas, assurez-vous que le classeur est ouvert dans le concepteur.
Faites glisser la table Product vers la cellule A1.

Un contrôle ListObject nommé productListObject est créé dans la feuille de calcul, à partir de la cellule A1.Parallèlement, un objet DataSet nommé adventureWorksLTDataSet et un BindingSource nommé productBindingSource sont ajoutés au projet.ListObject est lié au BindingSource, qui est lui-même lié à l'objet de groupe de données.

Ajout du groupe de données au cache de données

Pour activer du code en dehors du projet de classeur Excel afin d'accéder au groupe de données du classeur, vous devez ajouter le groupe de données au cache de données.Pour plus d'informations sur le cache de données, consultez Données mises en cache dans des personnalisations au niveau du document et Mise en cache des données.

Pour ajouter le groupe de données au cache de données

Dans le concepteur, cliquez sur adventureWorksLTDataSet.
Dans la fenêtre Propriétés, affectez à la propriété Modifiers la valeur Public.
Affectez à la propriété CacheInDocument la valeur True.

Initialisation du groupe de données dans le classeur

Avant de pouvoir extraire les données du groupe de données mis en cache à l'aide de l'application console, vous devez commencer par le remplir de données.

Pour initialiser le groupe de données dans le classeur

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur Sheet1.cs ou Sheet1.vb et sélectionnez Afficher le code.

Remplacez le gestionnaire d'événements Sheet1_Startup par le code suivant.Ce code utilise une instance de la classe ProductTableAdapter définie dans le projet AdventureWorksDataSet pour remplir de données le groupe de données mis en cache, s'il est actuellement vide.

Private ProductTableAdapter As New  _
    AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter()

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    If Me.NeedsFill("AdventureWorksLTDataSet") Then
        Me.ProductTableAdapter.Fill(Me.AdventureWorksLTDataSet.Product)
    End If
End Sub

private AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter productTableAdapter =
    new AdventureWorksDataSet.AdventureWorksLTDataSetTableAdapters.ProductTableAdapter();

private void Sheet1_Startup(object sender, System.EventArgs e)
{
    if (this.NeedsFill("adventureWorksLTDataSet"))
    {
        this.productTableAdapter.Fill(this.adventureWorksLTDataSet.Product);
    }
}

Point de contrôle

Générez et exécutez le projet de classeur Excel afin de garantir sa compilation et son exécution sans erreur.Cette opération remplit également le groupe de données mis en cache et enregistre les données dans le classeur.

Pour générer et exécuter le projet

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le projet AdventureWorksReport, sélectionnez Déboguer, puis Démarrer une nouvelle instance.

Le projet est généré et le classeur s'ouvre dans Excel.Vérifiez les points suivants :
- Le ListObject se remplit de données.
- La valeur de la première ligne du ListObject dans la colonne ListPriceest 1431.5.À une étape ultérieure de cette procédure, vous utiliserez une application console pour modifier les valeurs de la colonne ListPrice.
Enregistrez le classeur.Ne modifiez pas le nom du fichier ni l'emplacement du classeur.
Fermez Excel.

Création d'un projet d'application console

Créez un projet d'application console à utiliser pour modifier des données dans le groupe de données mis en cache dans le classeur.

Pour créer le projet d'application console

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur la solution AdventureWorksDataSet, pointez sur Ajouter, puis cliquez sur Nouveau projet.
Dans le volet Types de projets, développez Visual C# ou Visual Basic, puis cliquez sur Windows.
Dans le volet Modèles, sélectionnez Application console.
Dans la zone Nom, tapez DataWriter.Ne modifiez pas l'emplacement.
Cliquez sur OK.

Visual Studio ajoute le projet DataWriter à l'Explorateur de solutions et ouvre le fichier de code Program.cs ou Module1.vb.

Modification de données dans le groupe de données mis en cache à l'aide de l'application console

Utilisez la classe ServerDocument de l'application console pour lire les données d'un objet AdventureWorksLTDataSet local, modifier ces données, puis les réenregistrer dans le groupe de données mis en cache.

Pour modifier des données dans le groupe de données mis en cache

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le projet DataWriter, puis sélectionnez Ajouter une référence.
Sous l'onglet de .NET, sélectionnez Microsoft.VisualStudio.Tools.Applications.
Cliquez sur OK.
Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le projet DataWriter, puis sélectionnez Ajouter une référence.
Sous l'onglet Projets, sélectionnez AdventureWorksDataSet, puis cliquez sur OK.
Ouvrez le fichier Program.cs ou Module1.vb dans l'éditeur de code.
Ajoutez l'instruction suivante using (pour C#) ou Imports (pour Visual Basic) au début du fichier de code.
```
Imports Microsoft.VisualStudio.Tools.Applications
```
```
using Microsoft.VisualStudio.Tools.Applications;
```

Ajoutez le code suivant à la méthode Main.Ce code déclare les objets suivants :

une instance du type AdventureWorksLTDataSet définie dans le projet AdventureWorksDataSet ;
Chemin du classeur AdventureWorksReport dans le dossier build du projet AdventureWorksReport
un objet ServerDocument à utiliser pour accéder au cache de données du classeur.

[!REMARQUE]

Le code suivant suppose que vous utilisez un classeur avec l'extension de fichier .xlsx.Si le classeur de votre projet possède une autre extension de fichier, modifiez le chemin d'accès de la manière appropriée.

Dim productDataSet As New AdventureWorksDataSet.AdventureWorksLTDataSet()
Dim workbookPath As String = System.Environment.GetFolderPath( _
    Environment.SpecialFolder.MyDocuments) & _
    "\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx"
Dim serverDocument1 As ServerDocument = Nothing

AdventureWorksDataSet.AdventureWorksLTDataSet productDataSet =
    new AdventureWorksDataSet.AdventureWorksLTDataSet();
string workbookPath = System.Environment.GetFolderPath(
    Environment.SpecialFolder.MyDocuments) +
    @"\AdventureWorksReport\bin\Debug\AdventureWorksReport.xlsx";
ServerDocument serverDocument1 = null;

Ajoutez le code suivant à la méthode Main après le code inséré à l'étape précédente.Ce code exécute les tâches suivantes :

Il utilise la propriété CachedData de la classe ServerDocument pour accéder au groupe de données mis en cache dans le classeur.
Il lit les données du groupe de données mis en cache dans le groupe de données local.
Il modifie la valeur ListPrice de chaque produit dans la table Product du groupe de données.
Il enregistre les modifications apportées au groupe de données mis en cache dans le classeur.

Try
    serverDocument1 = New ServerDocument(workbookPath)
    Dim dataHostItem1 As CachedDataHostItem = _
        serverDocument1.CachedData.HostItems("AdventureWorksReport.Sheet1")
    Dim dataItem1 As CachedDataItem = dataHostItem1.CachedData("AdventureWorksLTDataSet")

    If dataItem1 IsNot Nothing Then
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())

        ' Read the cached data from the worksheet dataset into the local dataset.
        Dim schemaReader As New System.IO.StringReader(dataItem1.Schema)
        Dim xmlReader As New System.IO.StringReader(dataItem1.Xml)
        productDataSet.ReadXmlSchema(schemaReader)
        productDataSet.ReadXml(xmlReader)

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " & _
            "{0} rows.", productDataSet.Product.Rows.Count.ToString())

        ' Modify the prices of each product in the local dataset.
        Dim row As AdventureWorksDataSet.AdventureWorksLTDataSet.ProductRow
        For Each row In productDataSet.Product.Rows
            If row.ProductCategoryID < 20 Then
                row.ListPrice = row.ListPrice + row.ListPrice * 0.1
            Else
                row.ListPrice = row.ListPrice - row.ListPrice * 0.1
            End If
        Next row

        ' Write the modified local dataset to the worksheet dataset using the DiffGram format.
        Dim stringIn As New System.Text.StringBuilder()
        Dim stringOut As New System.IO.StringWriter(stringIn)
        productDataSet.WriteXml(stringOut, System.Data.XmlWriteMode.DiffGram)
        dataItem1.Xml = stringIn.ToString()

        serverDocument1.Save()
        Console.WriteLine("The product prices have been modified.")
    Else
        Console.WriteLine("The data object is not found in the data cache.")
    End If
Catch ex As System.IO.FileNotFoundException
    Console.WriteLine("The specified workbook does not exist.")
Catch ex As System.Xml.XmlException
    Console.WriteLine("The data object has invalid XML information.")
Finally
    If Not (serverDocument1 Is Nothing) Then
        serverDocument1.Close()
    End If
    Console.WriteLine(vbLf & vbLf & "Press Enter to close the application.")
    Console.ReadLine()
End Try

try
{
    serverDocument1 = new ServerDocument(workbookPath);
    CachedDataHostItem dataHostItem1 =
        serverDocument1.CachedData.HostItems["AdventureWorksReport.Sheet1"];
    CachedDataItem dataItem1 = dataHostItem1.CachedData["adventureWorksLTDataSet"];

    if (dataItem1 != null)
    {
        Console.WriteLine("Before reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());

        // Read the cached data from the worksheet dataset into the local dataset.
        System.IO.StringReader schemaReader = new System.IO.StringReader(dataItem1.Schema);
        System.IO.StringReader xmlReader = new System.IO.StringReader(dataItem1.Xml);
        productDataSet.ReadXmlSchema(schemaReader);
        productDataSet.ReadXml(xmlReader);

        Console.WriteLine("After reading data from the cache dataset, the local dataset has " +
            "{0} rows.", productDataSet.Product.Rows.Count.ToString());

        // Modify the prices of each product in the local dataset.
        foreach (AdventureWorksDataSet.AdventureWorksLTDataSet.ProductRow row in 
                 productDataSet.Product.Rows)
        {
            if (row.ProductCategoryID < 20)
            {
                row.ListPrice = row.ListPrice + (row.ListPrice * (Decimal).10);
            }
            else
            {
                row.ListPrice = row.ListPrice - (row.ListPrice * (Decimal).10);
            }
        }

        // Write the modified local dataset to the worksheet dataset using the DiffGram format.
        System.Text.StringBuilder stringIn = new System.Text.StringBuilder();
        System.IO.StringWriter stringOut = new System.IO.StringWriter(stringIn);
        productDataSet.WriteXml(stringOut, System.Data.XmlWriteMode.DiffGram);
        dataItem1.Xml = stringIn.ToString();

        serverDocument1.Save();
        Console.WriteLine("The product prices have been modified.");
    }
    else
    {
        Console.WriteLine("The data object is not found in the data cache.");
    }
}
catch (System.IO.FileNotFoundException)
{
    Console.WriteLine("The specified workbook does not exist.");
}
catch (System.Xml.XmlException)
{
    Console.WriteLine("The data object has invalid XML information.");
}
finally
{
    if (serverDocument1 != null)
    {
        serverDocument1.Close();
    }

    Console.WriteLine("\n\nPress Enter to close the application.");
    Console.ReadLine();
}

Dans l'Explorateur de solutions, cliquez avec le bouton droit sur le projet DataWriter, pointez sur Déboguer, puis sélectionnez Démarrer une nouvelle instance.

L'application console affiche des messages pendant la lecture du groupe de données mis en cache dans le groupe de données local, modifie les prix des produits dans le groupe de données local et enregistre les nouvelles valeurs dans le groupe de données mis en cache.Appuyez sur ENTRÉE pour fermer l'application.

Test du classeur

Lorsque vous ouvrez le classeur, ListObject affiche maintenant les modifications que vous avez apportées à la colonne ListPrice dans le groupe de données mis en cache.

Pour tester le classeur

Fermez le classeur AdventureWorksReport dans le concepteur Visual Studio, s'il est toujours ouvert.
Ouvrez le classeur AdventureWorksReport présent dans le dossier build du projet AdventureWorksReport.Par défaut, ce dossier se trouve à l'un des emplacements suivants :
- %UserProfile%\Mes Documents\AdventureWorksReport\bin\Debug (pour Windows XP et antérieur)
- %UserProfile%\Documents\AdventureWorksReport\bin\Debug (pour Windows Vista)
Vérifiez que la valeur de la colonne ListPrice pour la première ligne du ListObject est désormais 1574,65.
Fermez le classeur.

Partager via