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.

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.

Author: Jack Vamvas (http://www.sqlserver-dba.com)