Manipuler Excel avec PowerShell : Part 4 – Ajouter un graphique

Envoyer Imprimer PDF

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 ».

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

 

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é ».

$Chart.ChartType = 54

 

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ù…

try
{
$Chart.ChartStyle = 288
}
catch
{
$chart.ChartStyle = 1
}

 

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 ».

$Chart.HasLegend = $false
$Chart.HasTitle = $true
$Chart.ChartTitle.Text = "Production de bananes (en tonnes)"

 

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.

$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))

 

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.

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

 

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.

$ChartObj = $Chart.Parent
$ChartObj.Height = $RangePositionChart.Height
$ChartObj.Width = $RangePositionChart.Width
$ChartObj.Top = $RangePositionChart.Top
$ChartObj.Left = $RangePositionChart.Left

 

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,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

 

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:

Mise à jour le Vendredi, 25 Octobre 2013 09:58