Export-charts.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
40
41
42
43
44
45
46
47
48
49
50
51
52
<#
    .Synopsis
        Exports the charts in an Excel spreadSheet
    .Example
        Export-Charts .\test.xlsx
        Exports the charts in test.xlsx to JPEG files in the current directory.
 
    .Example
        Export-Charts -path .\test,xlsx -destination [System.Environment+SpecialFolder]::MyDocuments -outputType PNG -passthrough
        Exports the charts to PNG files in MyDocuments , and returns file objects representing the newly created files
 
#>

Param (
    #Path to the Excel file whose chars we will export.
    $Path          = "C:\Users\public\Documents\stats.xlsx", 
    #If specified, output file objects representing the image files
    [switch]$Passthru, 
    #Format to write - JPG by default
    [ValidateSet("JPG","PNG","GIF")]
    $OutputType = "JPG", 
    #Folder to write image files to (defaults to same one as the Excel file is in)
    $Destination
)

#if no output folder was specified, set destination to the folder where the Excel file came from
if (-not $Destination) {$Destination = Split-Path -Path $Path -Parent } 

#Call up Excel and tell it to open the file.
try   { $excelApp      = New-Object -ComObject "Excel.Application" } 
catch { Write-Warning "Could not start Excel application - which usually means it is not installed."  ; return } 

try   { $excelWorkBook = $excelApp.Workbooks.Open($Path) } 
catch { Write-Warning -Message "Could not Open $Path."  ; return } 

#For each worksheet, for each chart, jump to the chart, create a filename of "WorksheetName_ChartTitle.jpg", and export the file.
foreach ($excelWorkSheet in $excelWorkBook.Worksheets) {
    #note somewhat unusual way of telling excel we want all the charts.
    foreach ($excelchart in $excelWorkSheet.ChartObjects([System.Type]::Missing))  {
        #if you don't go to the chart the image will be zero size !
        $excelApp.Goto($excelchart.TopLeftCell,$true)
        $imagePath  = Join-Path -Path $Destination -ChildPath ($excelWorkSheet.Name + "_" + ($excelchart.Chart.ChartTitle.Text -split "\s\d\d:\d\d,")[0] + ".$OutputType")
        if ( $excelchart.Chart.Export($imagePath, $OutputType, $false) ) {  # Export returs true/false for success/failure
            if ($Passthru) {Get-Item -Path $imagePath }                     # when succesful return a file object (-Passthru) or print a verbose message, write warning for any failures
            else {Write-Verbose -Message "Exported $imagePath"}
        } 
        else     {Write-Warning -Message "Failure exporting $imagePath" } 
    }
}
$excelApp.DisplayAlerts = $false
$excelWorkBook.Close($false,$null,$null)
$excelApp.Quit()