Hé, le scripteur ! Apprendre à Excel

The Microsoft Scripting Guys

ayant passé la le mois dernier vivant et l'utilisation en Australie, ce scripteur constamment amazed par la créativité et la robustesse globale de mes collègues Aussie. Il y a quelques nuits un ami ici nécessaire la femme script et me hors au dîner. Il a dit que restaurant était un parcours court de notre hôtel.

QUARANTE minutes plus tard, nous arrivés à un restaurant magnifique overlooking accord Harbor Sydney, à la maison Opera Sydney glistening comme un seashell washed haut sur une plage de plusieurs. Le pont Harbour — le pont seule durée plus long dans le monde, impose ordre en maintenant les peninsulas deux ensemble comme un clamp massif restraining deux cartes warped.

Nouveau domicile 40 minutes de parcours n'est pas un court stroll à dîner. C'est un entraînement quotidien précédé de 15 minutes de warm-ups et Étirement.

Une raison que scripteur est actuellement ce déplacement vers le bas sous est pour la perspective différente qu'ont ici, Mes collègues. Elles semblent disposer d'un moyen unique d'examiner les choses, souvent défis mon notions preconceived de la façon dont les éléments doivent être effectuées.

Dans script, il est tout trop facile insérer dans un rut que vous restez le cours avec méthodologies essayé-et-la valeur true. Souvent, en utilisant la même approche pour obtenir les mêmes résultats représente exactement ce que vous voulez.

Du point de vue analyse, par exemple, à l'aide de Windows PowerShell vous pouvez facilement obtenir un instantané intéressant de l'utilisation des processus sur votre ordinateur local. Vous utilisez la cmdlet Get-Process et accueilli avec sortie agréable, ordonnée, comme illustré figure 1 .

Figure 1 recherchez au processus locales à l'aide Get-Process

Les résultats de la cmdlet Get-Process sont utiles dans la plupart des cas. Ils indiquent le nombre de handles ouverts, quelques différents affichages de la consommation de mémoire et un instantané de l'utilisation du processeur. Et Windows PowerShell 2.0 agissant ainsi que, vous même pourront utiliser Get-Process avec un paramètre –computername pour récupérer cette présentation à partir d'un ordinateur distant. Avec cette goodness, pourquoi dans le monde est tout le monde jamais besoin Examen chose ?

Le problème est il est beaucoup de détails masqués dans les colonnes de données longs. Toutes les données tend à masquer les détails plus importants. Et s'il est très que Windows Power­Shell 2.0 prendra en charge dans le futur de –computername paramètre, maintenant absolument rien fait pour un administrateur réseau. Par conséquent, nous avons simplement devrez utiliser WMI (Windows Management Instrumentation) et la classe Win32_Process WMI pour faire de notre contrôle des systèmes distants et présenter les informations d'une façon qui est réellement utile. Si vous pensez que le résultat de Get-Process a bountiful, obtenez une charge de la sortie de Win32_Process, illustré figure 2 .

La figure 2 Utilisation de WMI pour afficher les processus

Quel est donc un administrateur réseau OLE médiocre de faire lorsque tous qu'il veut est un rapport facile à lire de la quantité de mémoire consommée ? Cela est où vous devez penser à l'extérieur de la zone de tirer parti de votre environnement et commencent à Excel à automation. Plus probablement, vous Microsoft Office Excel être installé sur votre ordinateur. Si vous êtes comme nous, vous n'êtes pas un expert, mais elle fait partie du système Microsoft Office et vous pourriez en tant que vérifiez bien utiliser de celui-ci.

Comment dur est-il pour automatiser Excel ? Il est en fait assez facile car Microsoft a créé un modèle d'automatisation spécifiquement pour travailler avec Excel. L'ID programme est Excel.Application et il s'agit d'un objet COM. Lorsque vous créez une instance de l'objet Excel.Application, par défaut Excel va démarrer et s'exécutent, mais il ne sera pas visible. Toutefois, vous pouvez utiliser la propriété visible pour rendre Excel visible.

Voici comment procéder pour créer l'objet Excel.Application, demander l'état de la propriété visible et définir la propriété visible sur true :

PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true

Lorsque vous avez terminé que, vous accueilli avec une vue plutôt impaire d'Excel, quelle apparence simplement un shell de l'application Excel (voir figure 3 ). Il existe sans classeurs, aucun des feuilles de calcul, rien mais Excel photos.

La figure 3 naked Excel — pas classeurs ou feuilles de calcul

Nous devons ajouter un classeur à l'application. Pour ce faire, nous utilisons la méthode Ajout à partir de l'objet de classeur. L'objet de classeur est accessible de l'objet Excel.Application principal, comme vous le voyez ici dans lequel nous stocker l'objet de classeur dans un classeur variable nommée $ :

$workbook = $excel.Workbooks.add()

Nous devons maintenant pour vous connecter à une feuille de calcul spécifique. Par défaut, lorsqu'un classeur est ajouté à Excel, trois feuilles de calcul sont ajoutés au classeur. Ces feuilles de calcul peuvent être traités par numéro. Dans cette ligne de code, nous vous connecter à la première feuille de calcul et stocker l'objet spreadsheet renvoyé dans une feuille variable nommée $ :

$sheet = $workbook.worksheets.Item(1)

Nous pouvons désormais écrire des données dans cette feuille de calcul. Les informations dans des feuilles de calcul Excel sont stockées dans les cellules. Parce que les cellules réside dans des feuilles de calcul, vous utilisez l'objet feuille de calcul qui est stockée dans la variable de tableau $ pour accéder à une cellule spécifique. Vous utiliser à l'aide des numéros qui font référence à des lignes et colonnes dans la feuille de calcul. Une chose qui est un peu confuse est que dans des feuilles de calcul Excel lignes sont des nombres et les colonnes sont des lettres. Lorsque vous utilisez le modèle d'automatisation, cependant, les lignes et les colonnes sont numéros. Le numéro de la première est la ligne et le deuxième nombre est la colonne. Vous pouvez écrire à la cellule en modifiant simplement une affectation de valeurs à cette cellule spécifique :

$sheet.cells.item(1,1) = "Test"

Après l'ajout d'un classeur à notre objet Excel.Application et les données pour une cellule dans la feuille de calcul, notre classeur Excel ressemble à celle de La figure 4 .

La figure 4 Ajout d'une valeur à une cellule

Tous de ce ESPRIT, nous allons regroupé un outil utile. Supposons d'obtenir un ensemble d'informations de processus à partir de WMI, écriture de la consommation de nom et la mémoire de chaque traitement à une feuille de calcul Excel et puis créer un graphique pour mettre en évidence la mémoire utilisée. C'est seulement ce que fait WriteProcessInformation­ToExcel.ps1. Vous trouverez le script entier sur le site Web TechNet Magazine.

Nous commencer le script en utilisant la cmdlet Get-WmiObject afin de récupérer une collection d'informations sur les processus. Nous utilisez la classe Win32_Process WMI pour obtenir ces informations et nous stocker dans la variable de processus $ :

$processes = Get-WmiObject -class Win32_Process

Nous créons maintenant une instance de l'objet Excel.Application et de la banque dans la variable $ excel, puis que l'application visibles et pour ajouter un classeur. Vous devez normalement effectuer ces étapes pour n'importe quel automation d'Excel que vous avez décidé de faire. Voici les lignes de code :

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()

Un des notre annoyances avec Excel est la façon dont un classeur crée toujours trois feuilles de calcul. Nous considérons cette option pour être superflues car nous pouvons utiliser peine un tableur unique, beaucoup moins de trois. Heureusement, avec automation, nous pouvez simplement supprimer les feuilles de calcul supplémentaires utilisez la collection de feuilles de calcul pour vous connecter à la troisième feuille de calcul et d'appeler la méthode de suppression. Vous pouvez effectuer la même opération pour supprimer la deuxième feuille de calcul :

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

Ensuite, nous renommer la feuille de calcul restante. Ceci est important car si vous décidez jamais utiliser les objets de données ActiveX (ADO) pour interroger la feuille de calcul Excel, vous utilisez le nom de feuille de calcul dans la chaîne de connexion. Par conséquent, pour que le code lisible et intuitif, la feuille de calcul doit avoir un nom logique. Pour renommer la feuille de calcul, affecter tout simplement une nouvelle valeur pour la propriété Nom de la feuille de calcul spécifique. Ici, nous renommer la première feuille de calcul à « processus » :

$workbook.WorkSheets.item(1).Name = "Processes"

Nous devons maintenant pour vous connecter à la feuille de calcul renommé. Utilisez la méthode Item de l'objet de feuilles de calcul et donnez-lui le nom de la feuille de calcul :

$sheet = $workbook.WorkSheets.Item("Processes")

La première ligne de la feuille de calcul contiendra des informations d'en-tête. Nous allons dessiner une bordure et mettre les noms des propriétés en gras. Par conséquent, les données commencera dans la ligne 2, pour nous affecter la valeur 2 au x: $ variable de compteur

$x = 2

Ensuite, quatre lignes de code créer quatre types d'énumération. Types d'énumération sont utilisés pour indiquer à Excel, les valeurs sont autorisées pour des types spécifiques d'options. Par exemple, les xlLineStyle énumération est utilisée pour déterminer le type de ligne à dessiner : double en pointillés et ainsi de suite. Ces les valeurs d'énumération décrites sur le site Web MSDN.

Pour faciliter le code lire, nous créez alias de raccourci pour chacun des types d'énumération quatre que nous utiliserons. En fait, nous vous convertir une chaîne qui représente le nom de l'énumération pour [type]. Cette technique est en fait une astuce très utile :

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Nous devons maintenant mettre en forme la première ligne. Nous offrons la police en gras, définir la ligne en tant que xlDashDot, autoriser la couleur à être automatiquement spécifié et définir l'épaisseur de bordure avec Épaisseur moyenne :

For($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

Lorsque Qu'est terminé, nous assigner valeurs à la première ligne en sélectionnant la cellule avec la méthode article et en donnant les coordonnées de la ligne et colonne. Ensuite, nous procédons une affectation de valeurs directement à écrire les en-têtes de colonne :

$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

Nous devons maintenant mettre les informations de processus stockées dans la variable $ processus qui a été créée de notre requête WMI dans les cellules appropriées. Nous utiliser l'instruction foreach pour remonter dans la collection d'informations sur les processus. Nous définir le processus $ variable à notre énumérateur (espace réservé pour) de la collection, puis nous choisissez le nom et les propriétés workingSetSize pour écrire dans la première et deuxième colonne respectivement.

Voici où le $ x variable entre en jeu. Il commence par la deuxième ligne, et que nous en revue la collection de processus, nous incrémentons la valeur de la $ x variable afin qu'il pointe toujours vers la ligne active de la collection. Cela est comment nous peuvent trier toutes les données stockées dans la collection de processus $ d'informations sur les processus :

Foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} #end foreach

Une fois que nous ont rempli la feuille de calcul Excel, nous souhaitez ajuster la taille des colonnes afin que les cellules soient la même taille que les données qui y sont stockées. Pour ce faire, nous peut créer une plage en spécifiant les coordonnées de colonne à utiliser ; nous pouvez, toutefois, utilisez simplement la propriété D'usedRange de la feuille de calcul. Lorsque nous avons créé notre objet range, nous sélectionnez la propriété EntireColumn et utiliser la méthode d'ajustement automatique pour redimensionner les colonnes. Dans la mesure où cette méthode renvoie toujours données, nous canaliser les résultats de la Out-Null cmdlet. Cela empêche encombrer la console avec des informations inutiles. Voici le code que nous utilisons :

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

À ce stade, nous avons pu interrompre et nous faudrait une feuille de calcul intéressante avec le nom et le jeu de travail de mémoire de tous nos processus. Mais revenons à l'avance et créer un graphique. Il est facile. Nous utilisons la méthode d'ajout de l'objet graphiques du classeur. Comme cette méthode renvoie également des informations que nous ne souhaitez pas voir, nous pipeline les résultats de la Out-Null cmdlet, comme illustré ici :

$workbook.charts.add() | out-null  

La commande ci-dessus ajoute un graphique en courbes. Si vous souhaitez définir un autre type de graphique, vous devez utiliser une des valeurs d'énumération type de graphique. Pour ce faire, vous pouvez apporter utiliser une des valeurs énumération microsoft.office.interop.excel.xlChartType, telles que le type xl3DPieExploded. Le type xl3DPie­Exploded crée, pas étonnamment, un graphique en secteurs 3D est éclaté. Nous affecter ce type d'énumération à chartType propriété l'objet ActiveChart. Ensuite, nous affecter la source de données pour le graphique à la plage que nous avons défini dans la variable de plage $. Le résultat est que vous voir le graphique en courbes brièvement et ensuite le graphique en secteurs 3D éclate à l'écran. Voici le code :

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

Tant que nous écoutez autour, nous allons faire pivoter le graphique en secteurs. Cela faire en utilisant la propriété rotation de l'objet ActiveChart. Nous utilisons une instruction à incrémenter le nombre à 360 à 15. 360 est le nombre de degrés d'un cercle ; le graphique pivote un cercle complet 15 degrés à la fois. Il semble en fait assez sympa. Voici le code pour effectuer cette feat :

For($i = 1 ; $i -le 360 ; $i +=15)
{
 $workbook.ActiveChart.rotation = $i
}

La dernière chose que nous devons faire consiste à enregistrer la feuille de calcul. Pour ce faire, nous utilisons la cmdlet Test-chemin d'accès pour si la feuille de calcul existe déjà. Si c'est le cas, nous supprimer l'ancien en utilisant la cmdlet Remove-Item, et ensuite nous enregistrez le classeur actif à l'emplacement stocké dans la variable strPath $. Nous utilisons l'objet ActiveWorkbook de l'objet Excel.Application et de la méthode SaveAs pour enregistrer le classeur. Si il n'est pas déjà une copie de la feuille de calcul enregistrée, nous utilisez la méthode SaveAs de l'objet ActiveWorkbook et l'enregistrer directement :

IF(Test-Path $strPath)
  { 
   Remove-Item $strPath
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }
ELSE
  {
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }

Lorsque le script s'exécute, s'affiche avec un graphique uniquement comme celui illustré La figure 5 .

La figure 5 le processus éclatée graphique en secteurs

La feuille de calcul se trouve sous l'onglet Processus. la figure 6 illustre les en-têtes de colonne, le style de trait point/tiret nous sélectionné pour la bordure et les têtes colonnes en gras. Les noms de processus et les propriétés de taille de jeu travail sont les deux colonnes de données que nous avez affichée.

La figure 6, la feuille de calcul terminé

Comme vous pouvez le voir, avec le modèle d'automatisation Excel.Application, vous disposez les outils nécessaires pour vous permettre de traiter les données à partir de vos serveurs d'une manière qui peuvent tirer parti de l'analyse et graphiques outils à partir de cette application riche et puissante.

Wilson Édition est consultant senior chez Microsoft et un expert de script bien connu. Il est un Trainer de Certified pour le Microsoft qui fournit un atelier de Windows PowerShell courante à Microsoft premier clients dans le monde entier. Il a écrit huit livres, notamment plusieurs sur Windows Script et a contribué à presque une douzaine autres livres. Édition contient plus de 20 certifications de secteur d'activité.

Craig Liebendorfer est wordsmith et longtime éditeur Web de Microsoft. Craig ne peut pas pensez toujours il y a un travail paie lui pour travailler avec des mots chaque jour. Une des ses choses favoris est irreverent d'humour, afin qu'il doit ajuster droite ici. Il considère que ses accomplishment plus élevé dans la vie à sa fille magnificent.