Manipuler Excel avec PowerShell : Part 3 – Mettre en forme un tableau

Envoyer Imprimer PDF

Dans l’article précédent, je vous ai montré comment appliquer un style à une cellule pour obtenir le fichier Excel suivant:

 

Maintenant, on va aller un peu plus loin et on va manipuler le tableau et lui appliquer un style.  On va définir l’étendue de notre tableau (dans notre exemple de A3 à B7) :

$RangeTable = $WorkSheet.Range($WorkSheet.Cells.Item(3,1),$WorkSheet.Cells.Item(7,2))

 

On va définir notre tableau en un type « ListObjects » (http://msdn.microsoft.com/en-us/library/ff196851(v=office.14).aspx)  et en utilisant la méthode « add() » (http://msdn.microsoft.com/en-us/library/office/bb211863(v=office.12).aspx). Ceci va nous permettre de profiter de l’ensemble des fonctions.

$ListObject = $WorkSheet.ListObjects.Add(1,$RangeTable,$null,1,$null)


Nous n’avons plus qu’à appliquer un style de tableau Excel existant. Pour obtenir la liste complète, vous pouvez utiliser la commande « $WorkBook.TableStyles | ft name ». Il s’agit de la liste disponible depuis « Mettre sous forme de tableau » sous Excel.

 

Je vais appliquer le style « TableStyleLight6 »:

$ListObject.TableStyle = "TableStyleLight6"


Comme c’était le cas pour les styles de cellules, on peut appeler un style de tableau par son nom ou par son index. Il semblerait toutefois que les noms des styles de tableau ne soient pas traduits dans la langue locale… Par contre, certains styles peuvent avoir le même nom et être différents entre les versions d’Excel. C’est le cas du style « TableStyleLight6 » qui n’est pas le même entre Excel 2007 et Excel 2013… Je n’ai pas de solution miracle sachant que la création d’un style de tableau est beaucoup complexe que la création d’un style de cellules.

 

On pourrait affiner la mise en forme en permettant, par exemple, l’affichage de la ligne des totaux :

$ListObject.ShowTotals = $true


…ou en désactivant le filtrage automatique :

$ListObject.ShowAutoFilterDropDown = $false


C’est un exemple parmi tant d’autres… mais voici ce que l’on peut déjà obtenir visuellement.

 

Pour conclure cet article, on va corser un peu tout cela et on va essayer de trier les données. L’objectif est de classer par ordre croissant la production de manière automatique. Je ne vous cache que j’ai eu pas mal de difficultés pour y arriver et qu'il y a peut-être plus simple...

Premièrement, je défini l’étendue sur laquelle portera le tri. Je désigne simplement la première cellule concernée soit la cellule B4.

$RangeSort = $WorkSheet.Range("B4")


J’aurai pu très bien utiliser la valeur numérique pour identifier la position de la cellule. Ça peut être très pratique lorsque l’on veut améliorer l’automatisation. Par contre, comme la fonction « Range » ne peut pas interpréter la position d’une cellule par sa valeur numérique seule, il faut la convertir.

$RangeSort = $WorkSheet.Range($WorkSheet.Cells.Item(4,2).Address($False,$False))


On utilise ensuite la méthode SortFields.Add() pour la création de notre tri (http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sortfields.add.aspx). La syntaxe est .Add(MonEtendue, SortOn, Order, CustomOrder, DataOption) (consulter les liens pour obtenir les différentes valeurs possibles).

$WorkSheet.Sort.SortFields.Add($RangeSort,0,1) | Out-Null


On définit l’étendue affectée par le tri. En l’occurrence il s’agit de notre tableau.

$WorkSheet.Sort.SetRange($RangeTable)


Il nous reste plus qu’à exclure l’entête du triage à l’aide la propriété « Header » de l’objet « Sort ». Pour la liste des valeurs possibles consulter le lien http://msdn.microsoft.com/fr-fr/library/office/ff838812.aspx.

$WorkSheet.Sort.Header = 1


Nous achevons le tri via la méthode Apply().

$WorkSheet.Sort.Apply()


Et voila le résultat!

 

Et le script au complet:

[System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo] "en-US"
 
$Excel = New-Object -ComObject "Excel.Application"
 
$WorkBook = $Excel.Workbooks.Add()
 
$WorkSheet = $WorkBook.WorkSheets.Add()
$WorkSheet.Name = "Production de bananes"
$WorkSheet.Select()
 
$WorkSheet.Cells.Item(1,1) = "Production annuelle de bananes"
 
$ArrProduction = @((‘2010’,’4.5’),(‘2011’,’12’),(‘2012’,’11.5’),(‘2013’,’15’))
 
$WorkSheet.Cells.Item(3,1) = "Année"
$WorkSheet.Cells.Item(3,2) = "Production (en tonnes)"
 
$InitialRow = 4
 
for ( $i=0; $i -lt $ArrProduction.Count; $i++ )
{
$WorkSheet.Cells.Item($InitialRow,1) = $ArrProduction[$i][0]
$WorkSheet.Cells.Item($InitialRow,2) = [decimal] $ArrProduction[$i][1]
$InitialRow++
}
 
$RangeTitle = $WorkSheet.Range($WorkSheet.Cells.Item(1,1),$WorkSheet.Cells.Item(1,2))
$RangeTitle.MergeCells = $true
$RangeTitle.Style = ($WorkBook.Styles.Item(43)).Name
$RangeTitle.HorizontalAlignment = [Microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter
$RangeTitle.ColumnWidth = 20
 
$RangeTable = $WorkSheet.Range($WorkSheet.Cells.Item(3,1),$WorkSheet.Cells.Item(7,2))
$ListObject = $WorkSheet.ListObjects.Add(1,$RangeTable,$null,1,$null)
$ListObject.TableStyle = "TableStyleLight6"
 
$ListObject.ShowTotals = $true
$ListObject.ShowHeaders = $true
$ListObject.ShowAutoFilterDropDown = $false
 
$RangeSort = $WorkSheet.Range($WorkSheet.Cells.Item(4,2).Address($False,$False))
$WorkSheet.Sort.SortFields.Add($RangeSort,0,1) | Out-Null
$WorkSheet.Sort.SetRange($RangeTable)
$WorkSheet.Sort.Header = 1
$WorkSheet.Sort.Apply()
 
$WorkBook.SaveAs("c:\temp\MaProductionDeBananes.xlsx")
$Excel.Visible = $true


Nous avons donc vu dans cette troisième partie comme manipuler et mettre en forme notre tableau Excel. Dans la quatrième partie, on va s’attaquer à la création du graphique.

Pour les autres articles sur le sujet:

Mise à jour le Dimanche, 19 Janvier 2014 22:13