comment créer des dynamiques des feuilles de calcul excel basé sur les données de la table

J'ai plusieurs packages SSIS qui remplissent une fonction similaire. Un seul fichier Excel est composé de plusieurs feuilles de calcul avec chaque feuille de calcul peuplée par des résultats à partir d'une autre requête SQL. Voici les étapes générales j'ai appliqué. Avant de commencer, assurez-vous de créer un gestionnaire de connexion pour la base de données doit être appliquée et la sortie fichier Excel.

1) Créer une tâche de Script dans le Contrôle de flux et de le remplir comme suit. Ici, je suis entrain de créer le fichier Excel avec les feuilles de calcul qu'il contiendra. (Les feuilles de calcul ne doit jamais contenir d'espaces ou de caractères spéciaux). Mon code ci-dessous est en C#.

using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Dts.Runtime;

namespace ST_87e8d62a054b4e16b60297154afc19d8.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    #region VSTA generated code
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

    public void Main()
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        //Create First worksheet
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet.Name = "Names";

        //Define column headers for "RawData" WorkSheet
        xlWorkSheet.Cells[1, 1] = "First Name";
        xlWorkSheet.Cells[1, 2] = "Last Name";
        xlWorkSheet.Cells[1, 3] = "Title";

        //Create Second Worksheet
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
        xlWorkSheet.Name = "Addresses";

        //Define column headers for "CCDN" WorkSheet
        xlWorkSheet.Cells[1, 1] = "Street";
        xlWorkSheet.Cells[1, 2] = "City";
        xlWorkSheet.Cells[1, 3] = "State";
        xlWorkSheet.Cells[1, 4] = "Zip";
        xlWorkSheet.Cells[1, 5] = "Country";

        string Filename = "C:\\MyFile.xls";


        if (File.Exists(Filename))
        {
            File.Delete(Filename);
        }


        xlWorkBook.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);


        Dts.TaskResult = (int)ScriptResults.Success;
    }

2) Créer dans votre base de données de deux tables qui vont être remplis de façon temporaire. C'est, une table sera remplie pour les résultats de la première feuille de calcul et la seconde table sera remplie pour les résultats de la deuxième feuille. C'est une bonne technique de nommage de faire précéder le nom de chaque table avec "Working_", de sorte que vous savez le but de chacun. J'ai pris l'approche de l'utilisation des tables au lieu de vues est parce que j'ai envie de tri (ORDER BY) mes résultats, qui ne peut être fait avec un point de vue.

3) Ajouter le package SSIS deux Tâches d'Exécution SQL sous le Contrôle de Flux. La première tâche sera exécutée une INSERTION instruction SQL qui remplira le premier tableau que vous venez de créer et la deuxième tâche sera exécutée une autre INSÉREZ une instruction SQL qui va peupler le deuxième tableau venez de créer.

4) Ajouter le package SSIS deux tâches de Flux de Données sous le Contrôle de Flux. La première sera pour le remplissage de la première feuille de calcul et le second pour le remplissage de la deuxième feuille de calcul.

5) Sélectionnez la première tâche de Flux de Données et d'y ajouter des sous Flux de Données OLE DB Source où vous pourrez définir le fournisseur OLE DB conenction (gestionnaire de votre base de données), puis de la table ou de la vue. Sélectionnez la première nouvelle table créée. Assurez-vous que toutes les colonnes d'intérêt sont sélectionnés et que vous pouvez effectuer un aperçu.

6) Ajouter une Conversion de Données de la tâche de flux, puis une Destination Excel tâche de flux.

7) Répétez les étapes 5 et 6 pour la deuxième feuille de calcul et tableau.

8) Enfin, en vertu de Contrôle de Flux d'ajouter un fichier Excel Tâche SQL qui permet de supprimer le contenu des deux tables de Travail. Vous ne voulez pas que l'ancien contenu à inclure la prochaine fois que le paquet est exécuté.

Maintenant, si vous voulez jouer avec le formatage du fichier Excel après qu'il est terminé et impressionner votre manager, vous pouvez aussi le faire dans le code avec une finale de l'équipe de Script (également à l'aide de C#). La belle partie de cette approche est que vous n'êtes pas avoir à appliquer une mise en forme particulière des fonctions SQL, Excel est en train de faire tout le travail. On pourrait inclure la mise en forme à l'Étape 1 et dès que vous copiez les données dans les étapes suivantes, il est automatiquement mis en forme. Comme avec n'importe quel rapport de sortie, il n'y a pas de point dans la prise de SQL effectuer la mise en forme suit (en ajoutant des supplémentaires de travail pour le serveur de base de données) lorsqu'il est plus efficace de laisser Excel ou SSRS faire ce qu'ils font de mieux.

        public void Main()
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        Excel.Range xlRange;

        xlApp = new Excel.ApplicationClass();
        string Filename = "C:\\MyFile.xls";
        xlWorkBook = xlApp.Workbooks.Open(FileName, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        //Format cells in Names worksheet
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //Set the header range in bold font
        xlRange = xlWorkSheet.get_Range("a1", "p1");
        xlRange.Font.Bold = true;
        xlRange.WrapText = true;

        //Freeze first row listing headers
        xlWorkSheet.Application.ActiveWindow.SplitRow = 1;
        xlWorkSheet.Application.ActiveWindow.FreezePanes = true;

        //Auto adjust the width of each column
        xlWorkSheet.Columns.AutoFit();

        xlRange = xlWorkSheet.get_Range("c1", "j6467");
        xlRange.Cells.Locked = false;
        xlRange.Interior.Color = 65535;

        xlRange = xlWorkSheet.get_Range("o1", "p6467");
        xlRange.Cells.Locked = false;
        xlRange.Interior.Color = 65535;

        //Do not alert when saving changes to Excel file.
        xlWorkBook.Application.DisplayAlerts = false;

        //Save Excel file modifications
        xlWorkBook.Save();

        //Close workbook and application
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        //Release from cache.
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        //Set formatting of percent cells
        xlRange = xlWorkSheet.get_Range("d3", "d7");
        xlRange.NumberFormat = "###,###%";

      //Define the top left cell and bottom right cell of the table in the Excel worksheet
        xlRange = xlWorkSheet.get_Range("c1", "c7");

        //Draw grid of thin line around each cell in table
        xlRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, 1);

        //Draw thick border around entire table
        xlRange = xlWorkSheet.get_Range("a1", "d7");
        xlRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic, 1);


        //Right justify columns B and C
        xlRange = xlWorkSheet.get_Range("b3", "c7");
        xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;



        //Do not alert when saving changes to Excel file.
        xlWorkBook.Application.DisplayAlerts = false;

        //Save Excel file modifications
        xlWorkBook.Save();

        //Close workbook and application
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        //Release from cache.
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);


        Dts.TaskResult = (int)ScriptResults.Success;
    }

Et c'est à ce sujet. Notez que juste pour les besoins de cet exemple, je suis coder en dur le nom de fichier. Mais dans mon code je fais une demande à un Utilisateur de la variable qui est ensuite complétée par une autre instruction SQL tirant sur le nom d'une autre table de base de données. Pour les meilleures pratiques, c'est une bonne idée de garder vos packages SSIS entièrement de la table. De cette façon, les modifications apportées aux noms et les emplacements sont réalisés dans une table de base de données dans un dossier spécifique de votre package SSIS... en évitant la nécessité de mettre à jour votre package SSIS et de passer par le dev d'assurance de la qualité pour la production cycle de vie de nouveau.

Espère que cette aide et s'il vous plaît laissez-moi savoir si vous avez des questions.

OriginalL'auteur