mirror of
https://github.com/dfinke/ImportExcel.git
synced 2025-12-06 00:23:20 +00:00
52 lines
2.5 KiB
PowerShell
52 lines
2.5 KiB
PowerShell
<#
|
|
.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()
|