James O'Neill's Blog

November 25, 2017

Getting parts of Excel files as images.

Filed under: Office,Powershell — jamesone111 @ 7:54 pm

I feel old when I realise its more than two decades since I learnt about the object models in Word, Excel and even Microsoft project and how to control them from other applications. Although my preferred tool is now PowerShell rather than Access’s version of Visual basic, the idea that “it’s all in there somewhere” means I’ll go and do stuff inside Excel from time to time…

One of the things I needed to do recently was to get performance data into a spreadsheet with charts – which the export part of Doug Finke’s ImportExcel module handles very nicely. But we had a request to display the charts on a web page without the need to open an Excel file, so it was time to have a look around in Excel’s [very hierarchical] object model.

An Excel.Application contains
…. Workbooks which contain
…. …. Worksheets which contain
…. …. …. Chartobjects each of which contains
…. …. …. …. A Chart which has
…. …. …. …. …. An Export Method

It seems I can get what I need if I get an Excel application object, load the workbook, work through the sheets, find each chart, decide a name to save it as and call its export method. The PowerShell to do that looks like this

$OutputType    = "JPG"
$excelApp      = New-Object -ComObject "Excel.Application"
$excelWorkBook = $excelApp.Workbooks.Open($path)
foreach ($excelWorkSheet in $excelWorkBook.Worksheets) {
  foreach ($excelchart in $excelWorkSheet.ChartObjects([System.Type]::Missing)) {
    $excelApp.Goto($excelchart.TopLeftCell,$true)
    $imagePath = Join-Path -Path $Destination -ChildPath ($excelWorkSheet.Name +
                        "_" + ($excelchart.Chart.ChartTitle.Text + ".$OutputType"))
    $excelchart.Chart.Export($imagePath, $OutputType, $false)    
  }
}
$excelApp.Quit()

A couple of things to note – the export method can output a PNG, JPG or GIF file and in the final version of this code, $OutputType is passed as a parameter (like $Path and $Destination  I’ve got into the habit of capitalizing parameter names, and starting normal variables with lowercase letters). There’s a slightly odd way of selecting ‘all charts’ and if the chart isn’t selected before exporting it doesn’t export properly.

I sent Doug a this which he added to his module (along with some other additions I’d been meaning to send him for over a year!). Shortly afterwards he sent me a message 
Hello again. Someone asked me about png files from Excel. They generate a sheet, do conditional formatting and then they want to save is as a png and send that instead of the xlsx…

Back at Excel’s object model… there isn’t an Export method which applies to a range of cells or a whole worksheet – the SaveAs method doesn’t have the option to save a sheet (or part of one) as an image. Which left me asking “how would I do this manually?” I’d copy what I needed and paste it into something which can save it. From version 5 PowerShell has a Get-Clipboard cmdlet which can handle image data. (Earlier versions let you access the clipboard via the .net objects but images were painful). The Excel object model will allow a selection to be copied, so a single script can load the workbook, make a selection, copy it, receive it from the clipboard as an image and save the image.

$Format = [system.Drawing.Imaging.ImageFormat]::Jpeg
$xlApp  = New-Object -ComObject "Excel.Application"
$xlWbk  = $xlApp.Workbooks.Open($Path)
$xlWbk.Worksheets($WorkSheetname).Select()
$xlWbk.ActiveSheet.Range($Range).Select() | Out-Null
$xlApp.Selection.Copy() | Out-Null
$image = Get-Clipboard -Format Image
$image.Save($Destination, $Format)

In practice $Path, $Worksheetname, $Range, $Format and $Destination are all parameters. And the whole thing is wrapped in a function Convert-XlRangeToImage
Excel puts up a warning that there is a lot of data in the clipboard on exit and to stop that I copy a single cell before exiting.

$xlWbk.ActiveSheet.Range("a1").Select() | Out-Null
$xlApp.Selection.Copy() | Out-Null
$xlApp.Quit()

The Select and Copy methods return TRUE if they succeed so I send those to Null. The whole thing combines with Doug’s module like this

$excelPackage = $myData | Export-Excel -Path $Path -WorkSheetname $workSheetname
$workSheet    = $excelPackage.Workbook.Worksheets[$workSheetname]
$range        = $workSheet.Dimension.Address
#      << apply formatting >>
Export-Excel -ExcelPackage $excelPackage -WorkSheetname $workSheetname
Convert-XlRangeToImage -Path $Path -WorkSheetname $workSheetname -Range $range –Destination "$pwd\temp.png" –Show

I sent the new function over to Doug and starting with version 4.0.8 it’s part of the downloadable module

Advertisements

Create a free website or blog at WordPress.com.

%d bloggers like this: