Multi-Tabbed Excel Through Powershell

A while back, I read a zany Phil Factor article on automating Microsoft Office through Powershell.  Not too long after that, I actually needed to put an important part of it into use:  automating Excel scripts.  In my case, I needed to do some complicated projections through SQL Server and export them to Excel.  There were too many output sets for me to want to do the job myself, so I enlisted Powershell to do the work for me.  Here’s a cleaned-up version of that script, built off of AdventureWorks2008.  In this script, we’ll create an Excel spreadsheet with each schema listed in a different tab, and each of the sheets will contain the tables for that schema.

#This script was inspired by Phil Factor's article at http://www.simple-talk.com/dotnet/.net-tools/com-automation-of-office-applications-via-powershell/.
Import-Module SqlServer
Import-Module Pscx

#This script will, for each relevant service, create an Excel workbook and populate it with the values from that service.
$sql = Get-SQLServer "."
$db = "AdventureWorks2008"

#Create a directory if it does not already exist.
$DirectoryToSaveTo='c:\temp\'
if (!(Test-Path -path "$DirectoryToSaveTo"))
{
	New-Item "$DirectoryToSaveTo" -type directory | out-null
}
$filename = $DirectoryToSaveTo + "Test File.xlsx"
if (test-path $filename) { rm $filename }	#delete existing files

$Excel = New-Object -Com Excel.Application
$Excel.Visible = $true

#Sleep!  This is because I don't know better and need to wait to add the workbook.
Start-Sleep -s 1
$Workbook = $Excel.Workbooks.Add()

$schemas_query = "select distinct SCHEMA_NAME(schema_id) as Name, schema_id from sys.tables order by Name DESC;"
foreach ($record in Get-SQLData $sql $db $schemas_query)
{
	$query = "select SCHEMA_NAME(schema_id) as SchemaName, name as TableName, object_id as ObjectId, max_column_id_used as MaxColumnId from sys.tables where schema_id =" + $record.schema_id
	$csvResults = Get-SQLData $sql $db $query | Select-Object SchemaName, TableName, ObjectId, MaxColumnId | ConvertTo-CSV -Delimiter "`t" -NoTypeInformation

	#This requires Pscx.
	$csvResults | Out-Clipboard

	#Create the workbook and paste in the board data.  We throw in a sleep here because I don't know how to wait until the workbook is ready to be added...
	Start-Sleep -s 1
	$Worksheet = $Workbook.Sheets.Add()
	$Worksheet.Name = $record.Name
	$Range = $Worksheet.Range("a1","d$($csvResults.count + 1)")
	$Worksheet.Paste($Range, $false)

	#Make this look pretty and copy the data to the clipboard.
	$Worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
	$Worksheet.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
	$Range.EntireColumn.Autofit()
}

#Clean up by deleting the Sheet1, Sheet2, and Sheet3 sheets.
$Excel.DisplayAlerts = $false
$Workbook.Worksheets.Item("Sheet1").Delete()
$Workbook.Worksheets.Item("Sheet2").Delete()
$Workbook.Worksheets.Item("Sheet3").Delete()

$Workbook.SaveAs($filename)
#$Excel.quit()

This is kind of long for a Powershell script, so I’ll break things out into sections.

<span class="Apple-style-span" style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px; white-space: pre;">Import-Module SqlServer</span>
Import-Module Pscx

You need SQLPSX and Pscx for this.

$sql = Get-SQLServer "."
$db = "AdventureWorks2008"

#Create a directory if it does not already exist.
$DirectoryToSaveTo='c:\temp\'
if (!(Test-Path -path "$DirectoryToSaveTo"))
{
	New-Item "$DirectoryToSaveTo" -type directory | out-null
}
$filename = $DirectoryToSaveTo + "Test File.xlsx"
if (test-path $filename) { rm $filename }	#delete existing files

This is some pretty simple stuff: check to make sure the directory exists, and if the file already exists, delete it before starting work.

$Excel = New-Object -Com Excel.Application
$Excel.Visible = $true

#Sleep!  This is because I don't know better and need to wait to add the workbook.
Start-Sleep -s 1
$Workbook = $Excel.Workbooks.Add()

Once you set $Excel.Visible to $true, you’ll see a new Excel window pop up. Then, we want to create a workbook, which automatically creates three sheets. Don’t worry—we’ll take care of those sheets later.

By the way, I throw in sleep statements here because I am not sure of a better way to do it. I just know that there has to be one…

$schemas_query = "select distinct SCHEMA_NAME(schema_id) as Name, schema_id from sys.tables order by Name DESC;"
foreach ($record in Get-SQLData $sql $db $schemas_query)

This will set up our list of schemas.

$query = "select SCHEMA_NAME(schema_id) as SchemaName, name as TableName, object_id as ObjectId, max_column_id_used as MaxColumnId from sys.tables where schema_id =" + $record.schema_id
$csvResults = Get-SQLData $sql $db $query | Select-Object SchemaName, TableName, ObjectId, MaxColumnId | ConvertTo-CSV -Delimiter "`t" -NoTypeInformation

#This requires Pscx.
$csvResults | Out-Clipboard

Now you can see the inner query: we’ll get the tables associated with this schema. In this particular case, it’s a better idea just to get the entire list once and then filter it out using Powershell. But when I did my more complicated version, I needed to do some complex calculations and getting the entire set was out of the question. So even though it’s overkill here and lower performance, I’ll still show it this way.

Then, we get the results of this query, select the appropriate columns (in our case, all of them) and turn it into a tab-delimited result set. We’ll use Pscx to copy this to the clipboard.

Start-Sleep -s 1
$Worksheet = $Workbook.Sheets.Add()
$Worksheet.Name = $record.Name
$Range = $Worksheet.Range("a1","d$($csvResults.count + 1)")
$Worksheet.Paste($Range, $false)

Again with the sleep statement…oy…

So once we have the results, we create a new worksheet for this schema and populate it with our columns. Note that a superior result here might be to turn the “d” into a variable, as right now, it will only copy four columns. That’s fine for this particular data set, but if you wanted to generalize the code above, you’d need to get the right number of columns.

Finally, we paste our clipboard contents into the worksheet.

$Worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
$Worksheet.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
$Range.EntireColumn.Autofit()

This isn’t necessary, but it’s easy to make your results look pretty, so why not?

$Excel.DisplayAlerts = $false
$Workbook.Worksheets.Item("Sheet1").Delete()
$Workbook.Worksheets.Item("Sheet2").Delete()
$Workbook.Worksheets.Item("Sheet3").Delete()

$Workbook.SaveAs($filename)
#$Excel.quit()

We turn DisplayAlerts off to hide a message asking if you really want to delete the worksheet. Then, we can get rid of those three sheets we didn’t use. Finally, save the worksheet using the name we selected above, and you can uncomment the last line to close Excel.

Not too bad for a few lines of Powershell.