Powershell , Excel charts and data presentation

13 September,2011 by Tom Collins

The topic for T-SQL Tuesday is Data Presentation. Powershell to Excel is a a good way to presenting data, and works with SQL Server. Check my post Powershell sql server security audit  for other applications of Powershell and Excel

Data Presentation doesn’t just mean presenting data to the client via the tabular format. A recordset is produced , passed back to the client, the client uses the recordset to iterate and present the data.TSQL2sDay150x150

In this  example , using Powershell,  I’m passing a data set to an Excel object . The data is placed in a worksheet . The script uses the data placed in the worksheet to create a bar chart.

The data source is a text file , but could just as easily be a recordset direct from a SQL query


$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$chartType = [microsoft.office.interop.excel.xlChartType]::xlBar
$workbook =  $excel.Workbooks.Add()
$worksheet = $workbook.Worksheets.Add()
$worksheet1 = $workbook.worksheets.Item(1)


foreach ($svr in get-content "$basepath\sales.txt")
	$string = $svr.split("*")

    $range = $worksheet1.UsedRange
    $workbook.ActiveChart.chartType= $chartType


The data set used in this example :
















The chart generated is a bar chart . But as the chart is generated using the Excel Object – the whole range of charts are available. Use the ChartType function to define the chart type used.

 Powershell charts


Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Powershell , Excel charts and data presentation

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer