Manipuler Excel avec PowerShell : Part 4 – Ajouter un graphique

Dans l’article précédent, je vous ai montré comment mettre en forme votre tableau sous Excel à l’aide de PowerShell.

Nous avions obtenu le résultat ci-dessous.

 

Dans cet article, je vais vous montrer comment ajouter un graphique.

 

On commence par instancier notre objet de type « Chart ».

{codecitation style= »brush: PowerShell »}

$Chart = $WorkSheet.Shapes.AddChart().Chart

{/codecitation}

 

On va maintenant définir le type de notre graphique. Pour cela, il faut choisir parmi la longue liste que propose Excel. Ils sont énumérés depuis le lien http://msdn.microsoft.com/en-us/library/office/bb241008(v=office.12).aspx avec la valeur correspondante. Je vais partir sur un « histogramme 3D groupé ».

{codecitation style= »brush: PowerShell »}

$Chart.ChartType = 54

{/codecitation}

 

On peut spécifier également un style à notre graphique via la propriété « ChartStyle ». Il est assez dur de les identifier et d’ailleurs la documentation Microsoft parle d’une valeur possible comprise entre 1 et 48 mais j’ai pu définir mon style à une valeur bien plus élevée… J’ai donc fait un petit try-catch au cas où…

{codecitation style= »brush: PowerShell »}

try

{

$Chart.ChartStyle = 288

}

catch

{

$chart.ChartStyle = 1

}

{/codecitation}

 

On va continuer sur la mise en forme. On va désactiver les légendes à l’aide de la propriété « HasLegend ». On va activer le titre avec les propriétés « HasTitle » et « Text ».

{codecitation style= »brush: PowerShell »}

$Chart.HasLegend = $false

$Chart.HasTitle = $true

$Chart.ChartTitle.Text = « Production de bananes (en tonnes) »

{/codecitation}

 

Nous en avons terminé avec la mise en forme… On va s’attaquer aux données. On va ajouter une nouvelle série avec les années en abscisse (propriété « XValues ») et les tonnes en ordonnée (propriété « Values »). Pour cela on va utiliser des étendues.

{codecitation style= »brush: PowerShell »}

$Chart.SeriesCollection(1).XValues = $WorkSheet.Range($WorkSheet.Cells.Item(4,1),$WorkSheet.Cells.Item(7,1))

$Chart.SeriesCollection(1).Values = $WorkSheet.Range($WorkSheet.Cells.Item(4,2),$WorkSheet.Cells.Item(7,2))

{/codecitation}

 

Il y a différentes solutions en fonction des données à traiter, le nombre de séries ou le type de graphique mais vous avez déjà un bon exemple à votre disposition.

La toute dernière étape est le positionnement du graphique… A ma connaissance, la meilleure solution est de s’appuyer sur une étendue pour définir le positionnement et la taille. Ça pourrait paraitre plus compliqué mais au final vous verrez que s’est nettement plus pratique, surtout si vous êtes maniaque…

On créée donc une nouvelle étendue.

{codecitation style= »brush: PowerShell »}

$RangePositionChart = $WorkSheet.Range($WorkSheet.Cells.Item(10,1),$WorkSheet.Cells.Item(25,4))

{/codecitation}

 

On va ensuite utiliser l’objet parent contenant le graphique et définir la taille et le positionnement par rapport à notre étendue définie préalablement.

{codecitation style= »brush: PowerShell »}

$ChartObj = $Chart.Parent

$ChartObj.Height = $RangePositionChart.Height

$ChartObj.Width = $RangePositionChart.Width

$ChartObj.Top = $RangePositionChart.Top

$ChartObj.Left = $RangePositionChart.Left

{/codecitation}

 

Et voila le résultat !

 

Et le script au complet:

{codecitation style= »brush: PowerShell »}

[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,1).Address($False,$False))

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

$WorkSheet.Sort.SetRange($RangeTable)

$WorkSheet.Sort.Header = 1

$WorkSheet.Sort.Apply()

 

$Chart = $WorkSheet.Shapes.AddChart().Chart

$Chart.ChartType = 54

 

try

{

$Chart.ChartStyle = 288

}

catch

{

$chart.ChartStyle = 1

}

 

$Chart.HasLegend = $false

$Chart.HasTitle = $true

$Chart.ChartTitle.Text = « Production de bananes (en tonnes) »

 

$Chart.SeriesCollection(1).XValues = $WorkSheet.Range($WorkSheet.Cells.Item(4,1),$WorkSheet.Cells.Item(7,1))

$Chart.SeriesCollection(1).Values = $WorkSheet.Range($WorkSheet.Cells.Item(4,2),$WorkSheet.Cells.Item(7,2))

 

$RangePositionChart = $WorkSheet.Range($WorkSheet.Cells.Item(10,1),$WorkSheet.Cells.Item(25,4))

$ChartObj = $Chart.Parent

$ChartObj.Height = $RangePositionChart.Height

$ChartObj.Width = $RangePositionChart.Width

$ChartObj.Top = $RangePositionChart.Top

$ChartObj.Left = $RangePositionChart.Left

 

$WorkBook.SaveAs(« c:\temp\MaProductionDeBananes.xlsx »)

$Excel.Visible = $true

{/codecitation}

 

Grâce à cette séries d’articles, v devriez avoir bonnes bases pour commencer à générer vos tableaux et graphiques sur Excel à l’aide de Powershell.

Si vous voulez un exemple plus complexe, vous pouvez également consulter le code d’un de mes scripts depuis le lien technet suivant : http://gallery.technet.microsoft.com/scriptcenter/Generate-Excel-report-48c3f3b4

Il y a également de très nombreux exemples en VBA sur Internet qui vous permettra de vous guider sur les différentes méthodes et propriétés à utiliser.

Enfin, vous avez également le site MSDN dont j’ai fait assez souvent référence et qui est plutôt bien documenté !

Pour les autres articles sur le sujet:

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *