Examples/Data-XLSx-Drive-dlChart.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<#
    .Description
        Creates a very simple Excel file with a chart, and uses the Graph API to upload it
        Then uses the Excel part of the Graph API to extract the chart as a picture, back to the local machine.
        A roundabout way of getting a chart as a picture from data in Powershell :-)
#>


Param (
    $ExcelFile   = 'C:\temp\tempchart.xlsx',
    $Destination = 'root:/Documents'

)
#Remove old files
Remove-Item   C:\temp\graph.png,  $ExcelFile -ea SilentlyContinue

#Get some trivial data - the names and lengths of PowerShell files in this module.
#Export it to Excel and chart it
$excel = Get-ChildItem -path (Get-Module 'Microsoft.Graph.PlusPlus').ModuleBase -Recurse  -Include *.ps1 |
    Select-Object name,length |
        Export-Excel -PassThru -ChartType BarClustered -AutoNameRange -Path $ExcelFile
Add-ExcelChart -Worksheet $excel.Sheet1 -ChartType ColumnClustered -XRange "Name" -YRange "Length" -Column 4 -SeriesHeader "file size"
Close-ExcelPackage $excel

#Upload the file to current users' one drive. Open the file in Excel web app to see it
$graphfile  = Copy-ToGraphFolder -Path $ExcelFile -Destination $Destination
Start-Process $graphfile.webUrl

#We can add /workbook to the end of the URI which refers to a one drive object, so use this to find the first chart ...
$chartsURI  = "https://graph.microsoft.com/v1.0/me/drive/items/$($graphfile.id)/workbook/worksheets/sheet1/charts"
$chartName  = (Invoke-RestMethod -Uri $chartsURI  -method Get -Headers @{Authorization = "Bearer $AccessToken"} ).value |
    Select-Object -First 1 -ExpandProperty Name

#We can download the chart image - it will come down as base-64 encoded PNG, so convert it back and save it , then open the PNG
$imagechars = (Invoke-RestMethod -Uri "$chartsURI/$chartName/image" -Method Get -Headers @{Authorization = "Bearer $AccessToken"} ).value -as [char[]]
$imagebytes = [convert]::FromBase64CharArray($imagechars , 0 , $imagechars.Count)
[System.IO.File]::WriteAllBytes("C:\temp\graph.png",$imagebytes)
Start-Process C:\temp\graph.png