FreshRSS

🔒
❌ À propos de FreshRSS
Il y a de nouveaux articles disponibles, cliquez pour rafraîchir la page.
À partir d’avant-hierFlux principal

Comment manipuler des fichiers Excel avec PowerShell ?

9 mai 2022 à 16:15

I. Présentation

Microsoft Excel est un logiciel très populaire en entreprise, et on l'utilise très souvent comme une petite base de données afin de stocker des données diverses et variées. Au sein même d'un service informatique, c'est un outil très utile, au même titre que peut l'être PowerShell, alors pourquoi ne pas combiner l'usage de ces deux outils ?

Lorsque l'on utilise PowerShell, il n'est pas rare de manipuler des fichiers de données au format CSV, notamment pour exporter ou importer des données afin de les exploiter dans un script. Dans le cas d'Excel, on peut également réaliser plusieurs actions : importer un fichier Excel, exporter des données dans un fichier Excel, écrire dans une cellule spécifique d'un fichier Excel, etc... Le tout avec PowerShell.

Ainsi, on peut imaginer un script qui va alimenter un fichier Excel, et ce dernier sera facilement consultable depuis un poste de travail, grâce à l'affichage sous forme de tableur. Vous l'aurez compris, dans ce tutoriel, nous allons apprendre à manipuler des fichiers Excel avec PowerShell. L'usage d'Excel à partir de PowerShell est un sujet vaste tant les possibilités sont nombreuses, donc ce tutoriel sera une première introduction pour vous permettre de démarrer sur de bonnes bases !

II. PowerShell et Excel : les prérequis

Depuis 2015, Douglas Finke développe un module nommé "ImportExcel" qui permet de manipuler des fichiers Excel avec PowerShell. Ce module populaire est disponible sur la PowerShell Gallery et compte plus de 1,4 million de téléchargements ! Grâce à ce module, il est possible d'effectuer de nombreuses opérations : importer les données d'un fichier Excel, exporter au format Excel, ajouter des onglets, des tableaux, des graphiques, mais aussi déclarer une formule, voire même changer la couleur de fond d'une cellule ou créer des règles de mise en forme conditionnelle.

Voici le lien vers la page du module :

Pour interagir avec Excel à partir de PowerShell sur Windows, c'est le seul prérequis ! En effet, il n'est pas nécessaire d'installer Microsoft Excel sur la machine où vous souhaitez manipuler des fichiers Excel avec PowerShell, ce qui est un avantage intéressant sur les serveurs.

III. Installer le module ImportExcel de PowerShell

L'installation de ce module s'effectue très simplement, à l'aide de la commande Install-Module que l'on utilise habituellement pour installer un module. Ce qui donne :

Install-Module -Name ImportExcel

Pour rappel, il est également possible d'installer le module uniquement pour l'utilisateur actuellement connecté sur la machine :

Install-Module -Name ImportExcel -Scope CurrentUser

Quelques secondes plus tard, le tour est joué : vous êtes prêt à passer à la suite !

PowerShell - Installer le module ExportExcel

Sachez que vous pouvez lister l'ensemble des commandes disponibles dans ce module via la commande suivante :

Get-Command -Module ImportExcel

IV. Exporter des données dans Excel avec PowerShell

L'export de données dans un fichier Excel, reprend un peu le même principe que l'export CSV avec PowerShell. Très facilement, nous pouvons exporter le résultat d'une commande dans un fichier Excel. Prenons un exemple où nous allons exporter la liste des comptes d'un annuaire Active Directory dans un fichier Excel.

La commande ci-dessous récupère la liste de tous les utilisateurs de l'Active Directory et exporte cette liste dans un fichier nommé "AD-utilisateurs.xlsx" qui sera généré dans le répertoire courant.

Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx'

Quelques secondes plus tard, on obtient le résultat suivant :

Exporter des données dans Excel via Powershell

Dans cet exemple, les colonnes ne sont pas dimensionnées automatiquement, ce qui n'est très pratique pour lire. La bonne nouvelle, c'est que le cmdlet Export-Excel dispose de nombreux paramètres, notamment :

  • -AutoSize pour redimensionner les colonnes automatiquement selon la largeur du contenu
  • -WorksheetName pour nommer l'onglet dans le tableau Excel
  • -StartColumn pour écrire les données à partir de la Xème colonnes
  • -StartRow pour écrire les données à partir de la Xème lignes
  • -Append pour ajouter les données à la suite des données existantes dans le fichier
  • -ClearSheet pour supprimer les données avant d'ajouter les nouvelles données (uniquement dans l'onglet/la feuille, si un onglet est précisé)
  • Etc.
Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local"

Cette fois-ci, les colonnes sont correctement dimensionnées, et l'onglet est bien nommé comme je l'ai demandé :

Voici un autre exemple qui va permettre de laisser les 3 premières lignes et les 3 premières colonnes vierges :

Get-ADUser -Filter * | Export-Excel -Path './AD-utilisateurs.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local" -StartColumn 4 -StartRow 4

La preuve en image :

Dans le but d'exporter uniquement certaines propriétés, on peut ajouter la commande Select-Object afin de sélectionner les propriétés que l'on souhaite exporter. Voici un exemple pour exporter dans le fichier Excel uniquement deux champs : SamAccountName et Enabled.

Get-ADUser -Filter * | Select-Object SamAccountName,Enabled | Export-Excel -Path './AD-utilisateursBis.xlsx' -AutoSize -WorksheetName "Domaine it-connect.local"

À tout moment, vous pouvez obtenir de l'aide sur l'utilisation de cette commande, ou d'une autre commande, via :

Get-Help Export-Excel

Si vous n'avez pas d'Active Directory sous la main, vous pouvez exporter d'autres données, comme la liste des processus actifs sur votre machine ou l'état des services.

Get-Service | Export-Excel -Path './Services.xlsx' -AutoSize -WorksheetName "Etat des services"

V. Importer un fichier Excel avec PowerShell

Dans cette partie, nous allons voir que l'on peut aussi importer un fichier Excel dans PowerShell afin de lire son contenu. Cette fois-ci, c'est le cmdlet "Import-Excel" qui va être notre alliée pour importer le contenu d'un classeur Excel. Voici un exemple pour importer le fichier que l'on a importé précédemment :

Import-Excel -Path '.\AD-utilisateursBis.xlsx'

Ce qui donne :

PowerShell et Import-Excel

Si l'on reprend l'exemple du premier fichier Excel généré, avec de nombreuses colonnes, mais que l'on souhaite en importer uniquement certaines, c'est également possible via le paramètre -HeaderName. On peut également aller encore plus loin en précisant le nom de l'onglet du fichier Excel dans lequel il faut récupérer les données, via le paramètre -WorksheetName. Voici un exemple pour récupérer uniquement les colonnes DistinguishedName, Enabled et SamAccountName de l'onglet "Domaine it-connect.local" de notre fichier. On commence à la ligne 2 (-StartRow 2) pour ne pas récupérer la ligne d'en-tête. Ce qui donne :

Import-Excel -Path '.\AD-utilisateurs.xlsx' -HeaderName DistinguishedName,Enabled,SamAccountName -WorksheetName "Domaine it-connect.local" -StartRow 2

Le résultat obtenu dans la console PowerShell, et que l'on pourrait tout à fait stocker dans une variable, est plutôt satisfaisant :

Il faut bien respecter l'ordre des colonnes que l'on sélectionne avec -HeaderName, telles qu'elles sont positionnées dans le fichier Excel, sinon les noms de colonnes ne vont pas correspondre aux valeurs. Pour organiser les colonnes dans l'ordre que l'on souhaite dès l'import des données, il faut le faire en deux temps, de cette façon :

Import-Excel -Path '.\AD-utilisateurs.xlsx' -WorksheetName "Domaine it-connect.local" | Select-Object DistinguishedName,SamAccountName,Enabled

On voit bien, dans cet exemple, que j'ai pu inverser l'ordre des colonnes Enabled et SamAccountName par rapport au premier exemple.

Parfois, nous ne connaissons pas forcément la contenance du fichier Excel, ou en tout cas le nom exact des onglets. La commande Get-ExcelFileSummary permet d'obtenir des informations sur un fichier Excel, notamment le nom de chaque onglet, avec le nombre de colonnes utilisées, ainsi que le nombre de lignes, et la plage que cela représente. Voici un exemple :

Get-ExcelFileSummary -Path '.\AD-utilisateurs.xlsx'

Le résultat est parlant :

D'ailleurs, on peut aller plus loin et obtenir des informations sur tous les fichiers Excel situés dans un dossier ou une arborescence. Sur le site officiel, il y a un exemple donné (voir ci-dessous) qui permet d'obtenir un résumé de tous les fichiers Excel en se positionnant sur le dossier courant (avec récursivité).

dir . -r *.xlsx | Get-ExcelFileSummary | ft

Ainsi, on obtient des informations de différents fichiers Excel :

Note : le paramètre -Password est disponible avec Import-Excel et Export-Excel afin de gérer le mot de passe qui peut éventuellement protéger le fichier Excel.

VI. Écrire dans un fichier Excel avec PowerShell

Vous attendiez probablement cette partie : l'ajout de données dans un fichier Excel à l'aide de PowerShell. Sachez que c'est possible, et que l'on peut même modifier des données existantes. Ainsi, on va aller plus loin que simplement exporter et importer des données au format Excel, même si c'est déjà bien !

A. Ouvrir le fichier Excel avec Open-ExcelPackage

La première étape consiste à ouvrir le fichier Excel avec la commande Open-ExcelPackage afin de le convertir (en quelque sorte) en un objet PowerShell. Une fois qu'il sera dans ce nouveau format, il sera possible de lire et de le modifier.

Commençons par ouvrir le fichier Excel "AD-utilisateurs.xlsx" que nous avons créé précédemment. L'objet sera stocké dans la variable $Excel dans mon exemple, mais vous pouvez utiliser un autre nom.

$Excel = Open-ExcelPackage -Path '.\AD-utilisateurs.xlsx'

Actuellement, la variable $Excel ne contient pas le contenu de notre fichier Excel directement. Si on affiche son contenu, ce sont plutôt les propriétés du fichier Excel que l'on obtient.

B. Lire le contenu d'une cellule

Pour lire le contenu d'une cellule, il faut parcourir le fichier Excel, via les différentes propriétés de l'objet. On commencera par préciser "Workbook.Worksheets['nom de la feuille']" pour se positionner dans le bon onglet de notre classeur Excel. Ensuite, via "Cells", on peut préciser la cellule que l'on veut cibler et obtenir sa valeur facilement. Voici un exemple pour la valeur de la cellule A1 :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].Cells['A1'].Value

La valeur est bien retournée, et elle pourrait être stockée dans une variable :

Pour obtenir les valeurs d'une plage de cellules, par exemple la plage A1:A8, on utilisera plutôt SelectedRange :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].SelectedRange['A1:A8'].Value

On peut aussi stocker le "chemin" vers l'onglet du fichier Excel dans une variable, comme ceci :

$Onglet = $Excel.Workbook.Worksheets['Domaine it-connect.local']
$Onglet.Cells['A1'].Value

C. Éditer le contenu d'une cellule

Si l'on souhaite modifier la valeur d'une cellule, on peut s'appuyer sur ce que nous avons vu précédemment. Par exemple, on sait que la cellule A1 (qui correspond à l'en-tête de la colonne A) a pour valeur "DistinguishedName" donc on peut changer cette valeur par "DN" pour avoir un nom plus court. Il suffit d'utiliser cette commande :

$Excel.Workbook.Worksheets['Domaine it-connect.local'].Cells['A1'].Value = "DN"

Avec cette commande, le tour est joué ! Bien sûr, on aurait pu réutiliser la variable $Onglet déclarée précédemment.

D. Fermer le classeur Excel

Il est indispensable de fermer le classeur Excel au moment où l'on a terminé les modifications. En fait, cette option s'effectue via le cmdlet Close-ExcelPackage et elle permettra d'enregistrer les modifications apportées au fichier Excel, s'il y en a eu. Tout le temps que cette commande n'est pas exécutée, les modifications ne sont pas visibles dans Excel.

Il suffit d'appeler notre variable $Excel :

Close-ExcelPackage $Excel

VII. Créer un fichier Excel vierge en PowerShell

Lorsque l'on utilise Open-ExcelPackage en appelant un fichier qui n'existe pas et en ajoutant le paramètre -Create, on peut créer un fichier Excel vierge ! Voici une commande qui permet de créer le fichier "C:\partage\test.xlsx".

$ExcelNew = Open-ExcelPackage -Path "C:\partage\test.xlsx" -Create

Pour le moment, ce fichier n'est pas visible à cet emplacement, car il n'est pas enregistré. Disons qu'il est en mémoire. Nous devons lui ajouter à minima un onglet, par exemple nommé "IT-Connect" grâce au cmdlet Add-Worksheet. D'ailleurs, ce cmdlet s'applique aussi sur les fichiers existants et que l'on manipule via Open-ExcelPackage.

Add-Worksheet -ExcelPackage $ExcelNew -WorksheetName "IT-Connect"

À partir du moment où il y a au moins un onglet de déclaré, on peut fermer le fichier ce qui aura pour effet de le créer.

Close-ExcelPackage $ExcelNew

Sans quitter notre console PowerShell, on peut récupérer des informations sur la structure de notre fichier Excel via cette commande :

Get-ExcelFileSummary -Path '.\test.xlsx'

Voici un récapitulatif en image :

Créer un fichier Excel avec PowerShell

VIII. Créer un tableau croisé dynamique Excel

Pour terminer en beauté cet article, nous allons voir qu'il est possible d'exporter la liste des ordinateurs intégrés au domaine Active Directory pour faire un tableau croisé dynamique dans un document Excel, associé à un graphique qui montrera la répartition entre chaque version de Windows !

Tout d'abord, commençons par exporter la liste des ordinateurs du domaine, en sélectionnant le nom et le système d'exploitation, dans un fichier Excel nommé "AD-Inventaire.xlsx" au sein d'un onglet nommé "Inventaire". Ce qui donne :

Get-ADComputer -Filter * -Properties OperatingSystem | Select-Object Name,OperatingSystem | Export-Excel -Path './AD-Inventaire.xlsx' -AutoSize -WorksheetName "Inventaire" -clearsheet

Ensuite, on va ouvrir ce fichier Excel via Open-ExcelPackage comme nous l'avons vu précédemment :

$Filename = "C:\Partage\AD-Inventaire.xlsx"
$Excel = Open-ExcelPackage -Path $Filename

Puis, on va définir notre tableau croisé dynamique ainsi que le graphique grâce au cmdlet New-PivotTableDefinition. Comme ceci :

$Graph1 = New-PivotTableDefinition -PivotTableName "Graph-Inventaire" -SourceWorkSheet "Inventaire" -PivotRows "OperatingSystem" -PivotData @{OperatingSystem='Count'} -IncludePivotChart -ChartType Pie3D -ShowPercent

Quelques explications sur les différents paramètres utilisés :

  • -PivotTableName : nom de l'onglet dans le classeur Excel
  • -SourceWorkSheet : onglet source pour récupérer les informations, en l'occurrence "Inventaire" que l'on a créé au moment de la création du fichier Excel
  • -PivotRows : champs à définir comme lignes dans le tableau croisé dynamique
  • -PivotData : les données du tableau, ici on veut compter les occurrences pour la propriété "OperatingSystem" de notre tableau dont on prend la fonction "count" mais d'autres valeurs sont possibles (Average, Count, CountNums, Max, Min, Product, None, StdDev, StdDevP, Sum, Var, VarP)
  • -IncludePivotChart : inclure un graphique correspondant au tableau
  • -ChartType : le type de graphique, ici "Pie3D" pour avoir un camembert 3D (désolé j'aime trop le fromage pour ne pas utiliser celui-ci... non en vrai il est adapté)
  • -ShowPercent : afficher les différents pourcentages sur le graphique

Il nous reste à mettre à jour le fichier Excel en intégrant le tableau croisé dynamique via le paramètre -PivotTableDefinition :

Export-Excel -ExcelPackage $Excel -PivotTableDefinition $Graph1 -Activate

Et voilà le résultat final dans un onglet nommé "Graph-Inventaire" de notre fichier Excel ! C'est top, non ? 🙂

PowerShell et les tableaux croisés dynamiques Excel

Afin d'explorer plus en détail les possibilités de ce module et des différentes commandes, il sera indispensable de consulter l'aide du module via la console PowerShell. Pour obtenir les détails sur une commande, vous pouvez utiliser (en remplaçant le cmdlet pour lequel vous souhaitez obtenir de l'aide) les commandes suivantes :

  • Aide détaillée :
Get-Help New-PivotTableDefinition -Detailed
  • Exemples d'utilisation :
Get-Help New-PivotTableDefinition -Examples

Maintenant, c'est à vous de jouer et n'oubliez pas de me dire ce que vous allez faire avec Excel via PowerShell !

The post Comment manipuler des fichiers Excel avec PowerShell ? first appeared on IT-Connect.

Écrire des scripts shell en Node.js avec Google ZX

30 avril 2022 à 09:00
Par : Korben

Si vous faites des scripts en bash, déjà ça veut dire que vous êtes une belle personne. Mais ça veut également dire que Google pense à vous.

En effet, cette petite entreprise qui un jour sera connue dans le monde entier, j’en suis sûr, a mis en ligne sur son Github un outil qui s’appelle ZX et qui permet d’écrire des scripts beaucoup plus simplement et beaucoup plus rapidement.

Reposant sur NodeJS, ZX ne perdra pas tous ceux qui sont déjà familiers avec JavaScript et Node.js. ZX a la particularité de faciliter la création de child_process et la gestion des messages sortants (stdout et stderr).

Prenons un exemple type hello world qui va utiliser la commande « ls » pour lister les fichiers d’un répertoire et récupérer la sortie de cette commande.

On va d’abord créer un répertoire :

mkdir hello
cd hello

Puis on va initialiser un nouveau projet dans le dossier :

npm init --yes

Ensuite, pour installer zx, il faudra passer par npm comme ceci :

npm install --save-dev zx

On va ensuite créer un script avec l’extension .mjs

nano hello.mjs

Dont voici le code… En gros, on initialise le shebang et y’a plus qu’à utiliser les fonctions de récupération des commandes comme elles sont décrites dans la documentation.

Ainsi, on importe la fonction $ (shell) et on lui passe la commande ls. On récupère la sortie (stdout) de ls dans la variable output et on affiche cette variable.

#! /usr/bin/env node

import { $ } from "zx";

const output = (await $`ls`).stdout;

console.log(output);

ZX est une façon agréable de faire des scripts bash avec NodeJS sans trop se prendre la tête. Vous pouvez même mixer ça avec de la doc en markdown pour exécuter le contenu « code » contenu dans un fichier .md.

Évidemment, je vous recommande lire la doc pour aller au-delà de mon petit exemple.

Ah et en bonus, pour les Patreons, je vous ai même fait un tuto en vidéo.

Merci de votre soutien !

AutoVPN – Pour créer des points d’accès OpenVPN à la demande sur AWS

25 mars 2022 à 09:00
Par : Korben

Si vous pratiquez AWS, saviez-vous qu’avec ce script python nommé AutoVPN, vous pouviez facilement créer des points d’accès OpenVPN dans n’importe quelle région AWS ?

La création d’un point d’accès OpenVPN avec ce script ne vous prendra que quelques minutes et créera automatiquement les groupes qui vont bien pour vos accès sécurisés. Pour ce faire, AutoVPN démarre une instance EC2 et configure OpenVPN. Dès l’installation et la configuration terminée, un fichier de config OpenVPN est téléchargeable et prêt à être utilisé.

Une autre fonction permet de voir également quelles sont les instances en cours d’exécution et dans quelles régions AWS.

AutoVPN permet de spécifier le type d’instance dont vous avez besoin, de générer des paires de clés SSH, de spécifier une IAM personnalisée…etc.

C’est super pratique pour créer des points d’accès en fonction de la demande et pouvoir ensuite les supprimer comme si de rien n’était.

Si vous voulez en savoir plus, toute la documentation et le script se trouvent sur la page Github.

Comment créer un petit bouton javascript qui permet aux gens d’ajouter votre événement à leur calendrier

22 février 2022 à 09:00
Par : Korben

Si vous organisez des réunions, des conférences téléphones ou des grosses teufs avec champagne et cluster de covid, il est pratique de pouvoir proposer à vos invités d’ajouter cet événement dans leur calendrier. Pour cela, aujourd’hui je vous propose un script qui s’appelle Add To Calendar Button. Il s’agit tout simplement d’un petit bout de JavaScript qui une fois paramétré et inséré dans vos pages web ressemblera à ceci :

Le code de ce script fonctionnera sur desktop et mobile et peut être entièrement paramétré et traduit comme bon vous semble. Il permet d’ajouter une invitation dans les calendriers les plus connus (Google Calendar, Yahoo Calendar, Microsoft 365 et Outlook calendar sans oublier la génération de fichiers iCal / ICS pour les autres calendriers comme celui d’Apple.

Pour mettre en place ce bout de code, téléchargez-le depuis le dépôt Github. Ensuite, copiez les fichiers présents dans le dossier assets sur votre site, puis intégrez ces fichiers à votre page comme ceci :

<link rel="stylesheet" href="./assets/css/atcb.min.css">

<script src="./assets/js/atcb.min.js" defer></script>

Puis créez un bouton comme ceci :

<div class="atcb" style="display:none;">
  {
    "name":Le titre de l'événement",
    "startDate":"02-21-2022",
    "endDate":"03-24-2022",
    "options":[
      "Google"
    ]
  }
</div>

On a la une configuration minimale, mais sachez que vous pouvez ajouter beaucoup plus d’options comme ceci :

<div class="atcb" style="display:none;">
  {
    "name":"Le titre de votre événement",
    "description":"Une petite description",
    "startDate":"02-21-2022",
    "endDate":"03-24-2022",
    "startTime":"10:13",
    "endTime":"17:57",
    "location":"Roubaix",
    "label":"Ajouter au calendrier",
    "options":[
      "Apple",
      "Google",
      "iCal",
      "Microsoft365",
      "Outlook.com",
      "Yahoo"
    ],
    "timeZone":"Europe/Berlin",
    "timeZoneOffset":"+01:00",
    "trigger":"click",
    "iCalFileName":"Reminder-Event"
  }
</div>

Je vous invite vraiment à lire la documentation pour intégrer facilement ce genre de petit bouton.

Comment récupérer le résultat d’un CHKDSK ?

Hey ! Bonjour à toutes et à tous ! Ceci est mon premier article pour 2022 et c’est pourquoi avant de rentrer dans le vif du sujet du jour, je vous souhaite une bonne et heureuse année ! Que celle-ci vous amène pleine de bonnes choses positives (sauf les tests COVID) ! Aujourd’hui, nous allons …

L’article Comment récupérer le résultat d’un CHKDSK ? est apparu en premier sur Tech2Tech | News, Astuces, Tutos, Vidéos autour de l'informatique.

❌