Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Powershell , Excel charts and data presentation

13 September,2011 by Jack Vamvas

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)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


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