Utiliser Microsoft Excel via PowerShell

Si vous avez besoin de réaliser des opérations récurrentes sous Excel, il se pourrait que cet article vous intéresse. Nous allons en effet voire comment utiliser et contrôler MS Excel via PowerShell.

Avant d’aller plus loin, sachez qu’il ne s’agit pas de faire du Excel sans passer par Excel mais bien de contrôler certains comportements Excel via des lignes de commande PowerShell. Ce type d’actions n’est donc pas transparent pour l’utilisateur puisque Excel se lancera et fonctionnera de la même façon que si vous l’exécutiez manuellement. Simplement tout se déroulera sans que vous pressiez les touches de votre clavier !

Etape 1 – Lancer Excel en ligne de commandes

Pour démarrer Microsoft Excel, vous pouvez procéder comme suit :

$excel = new-object -comobject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $False

Si vous n’utilisez pas le paramètre visible à true, Excel s’exécutera bien mais vous ne le verrez pas à l’écran… 😉 ! Mais il ne faut pas me croire sur parole, vous pouvez effectuer le test et vous en assurer en ouvrant le gestionnaire de tâches… pour voir qu’il exécute correctement.

Si vous souhaitez pouvoir utiliser Excel sans intervention humaine mais uniquement en scripting (comme c’est le cas ici) vous devez également mettre le paramètre « DisplayAlerts » à false. Cela évitera toutes les demandes Excel du genre :

• Êtes-vous sûr de vouloir supprimer cette feuille…
• Voulez-vous vraiment quitter sans avoir sauvegarder votre document…
• etc.

Notre Excel s’exécute mais… il ne fait pas grand-chose… et en plus il est vide.

Continuons.😀

Etape 2 – Création d’une nouvelle feuille

Les 2 lignes suivantes vont vous permettre de créer un fichier Excel, et immédiatement après, de créer un nouvel espace de travail au sein d’une nouvelle feuille.

$workbook = $excel.Workbooks.Add()
$workbook = $workbook.Worksheets.Item(1)

Le numéro en paramètre dans Item() correspond à l’emplacement où vous souhaitez créer une nouvelle feuille. Mon fichier étant actuellement vide, je choisi de l’insérer en position 1.

Vous pouvez bien entendu ajouter plusieurs feuilles à votre document Excel ! A présent, nous disposons maintenant d’un fichier Excel avec une feuille Excel prête à servir.

Etape 3 – Sauvegarder votre fichier et quitter

Avec les divers traitements ou données que vous avez pu insérer dans vos document Excel, vous aimeriez certainement pouvoir le sauvegarder puis quitter Excel. 

Pour effectuer une nouvelle sauvegarde de votre fichier, vous pouvez définir le nom et l’emplacement du fichier comme suit :

$workbook.SaveAs("C:\Users\your-name\Desktop\exemple.xlsx")

Pour sauvegarder à nouveau votre fichier au même endroit et quitter Excel, vous pouvez utiliser les 2 lignes de commandes ci-dessous :

$workbook.Save()
$excel.Quit()

Etape 4 – Ouverture d’un document existant

Avant d’aller plus loin, noter que lorsque je parle de workbook il s’agit d’un fichier Excel. Un fichier Excel, un workbook, peut contenir plusieurs feuilles de calculs qui correspondent aux différents onglets que vous pouvez voir en bas de votre fichier Excel.

Supposons que vous disposez d’un fichier Excel qui porte le nom : exemple.xlsx la commande ressemblerait à celle-ci :

Pour procéder à l’ouverture de votre document, vous allez utiliser la commande suivante :

$excel_file_path = 'C:\Users\your-name-here\Desktop\exemple.xlsx'
$workbook = $excel.Workbooks.open($excel_file_path)

 Cette fois-ci Excel démarre mais ouvre également votre fichier Excel avec les différenes données qu’il peut contenir.

Etape 5 – Insertion/saisie de données

L’intérêt consiste maintenant à rajouter du contenu pour remplir notre fichier Excel avec des données.

Pour ce faire, nous pouvons utiliser les commandes suivantes :

$workbook.Cells.Item(1,1) = 'hello'
$workbook.Cells.Item(1,2) = 'world'

Comme vous allez le voir si vous tenter d’exécuter ces lignes de commande, vous venez de remplir les cellules A1 et B1. Vous l’aurez donc compris, la première valeur dans Item(x,y) correspond au numéro de lignes tandis que la seconde valeur correspond au numéro de la colonne cible.

• (1,1) correspond donc à la cellule A1
• (1,2) correspond donc à la cellule B1
• (3,2) correspond donc à la cellule B3
• Etc.

Je ne vais pas plus loin, je pense que vous aurez compris le fonctionnement. Il est assez facile par la suite de faire du remplissage de fichiers Excel via les boucles for/while disponibles en PowerShell.

Etape 6 – Exemples

Dans cette partie, nous allons considérer les 2 exemples suivants :

• Imaginons que nous souhaitions récupérer et lister dans un fichier Excel la liste des machines virtuelles présentes sur Hyper-V. Pour chaque VM, nous allons donc récupérer son nom, son état, son uptime et son statut.  Pour ce faire, nous utiliserons la Cmdlet Get-VM.

• Autre exemple tout aussi classique, nous allons maintenant lister les disques durs locaux de la machine. Pour chacun des disques détectés, nous allons récupérer son DeviceID, le nom du Volume et le montant d’espace disque encore libre (en GB) [voir image un peu plus bas]

Rappel de la commande pour saisir des données dans Excel :

$sheet.Cells.Item($row,$column)= 'Status'

Le script est identique en ce qui concerne le début. Les deux points auxquels vous devez faire attention c’est :

1. la création d’une boucle foreach qui vous permettra d’afficher les valeurs à saisir sur Excel ;
2. la gestion des variables $Column et $row pour se déplacer et parcourir intelligemment dans votre ficheir Excel !

Conclusion et téléchargements

Vous devriez maintenant pouvoir contrôler assez facilement Microsoft Excel via PowerShell… Vous vous en doutez les possibilités sont vastes aux vues des possibilités offertes par les 2 produits (PowerShell, Excel). A noter que dans mon cas, j’ai effectué les tests avec MS Excel 2013 mais il n’y a pas de raison que ça pose des difficultés sur les versions les plus récentes du pack Office.

Comme d’habitude, vous trouverez ci-dessous quelques scripts exemples sans prétention pour vous permettre de tester ce que nous avons évoqué ci-dessus :

• Script 1 – Ouverture excel / enregistrement / quitter (téléchargeable ici : LaunchExcel)
• Script 2 – Insertion des données des machines virtuelles à partir de la CmdLet Get-VM (téléchargeable ici : DisplayVM)
• Script 3 – Insertion des données des disques durs locaux (téléchargeable ici : DisplayDrives)

Les scripts sont également disponibles sur GitHub.

Pour aller plus loin

• Conditional formatting en PowerShell : http://fatbeards.blogspot.fr/2009/02/powershell-vm-excel-conditional.html
• Blog officiel TechNet : http://blogs.technet.com/b/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx

Si vous avez des questions, n’hésitez pas 😀 !