-
Notifications
You must be signed in to change notification settings - Fork 401
/
Export-charts.ps1
51 lines (45 loc) · 2.54 KB
/
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
<#
.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()