Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Powershell , Excel charts and data presentation

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)


$x=1

foreach ($svr in get-content "$basepath\sales.txt")
{
	$string = $svr.split("*")
      $worksheet1.cells.item($x,1)=$string[0]
      $worksheet1.cells.item($x,2)=$string[1]

      $x++
}
    $range = $worksheet1.UsedRange
    $range.EntireColumn.AutoFit()
    $workbook.charts.add()
    $workbook.ActiveChart.chartType= $chartType
    $workbook.ActiveChart.SetSourceData($range)


 

The data set used in this example :

 

Jan*500

Feb*600

Mar*700

Apr*800

May*500

Jun*400

Jul*900

Aug*700

Sep*750

Oct*1000

Nov*800

Dec*600

 

 

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: Jack Vamvas (http://www.sqlserver-dba.com)

Comments

Posted by Jason Brimhall on 15 September 2011

I hadn't considered PoSH as a means to populate Excel charts.  Neat solution.

Leave a Comment

Please register or log in to leave a comment.