Excel Automation

Excel Automation Using PowerShell and Data from the AdventureWorks2012 Database

In this blog post I will demonstrate how to create the Excel Report below using PowerShell and data from the AdventureWorks2012 database.




1. Creating a New Excel Workbook

In this step we will be creating a new Excel Workbook

#Open a new Excel Workbook
$EXCL = New-Object -ComObject Excel.Application
$EXCL.Visible = $true
$WB = $EXCL.Workbooks.Add()


2. Rename a Worksheet

In this step we will be renaming “Sheet1” to “Worksheet1”.

#Rename Worksheets
$SHEET = $EXCL.WorkSheets.item("Sheet1")
$SHEET.Name = "Worksheet1"


3. Add a new Worksheet

In this step we will be adding a new Worksheet and the name will be “Sheet4”

#Add WorkSheets
$SHEET = $EXCL.WorkSheets.add()
$SHEET = $workbook.ActiveSheet

4. Move Worksheets Order

In this step we will moving worksheet “Sheet3” to be in front of Worksheet “Sheet4”

#Move Worksheets
$SHEET1 = $EXCL.sheets.item("Sheet3");
$SHEET2 = $EXCL.sheets.item("Sheet4"); 

5. Delete a Worksheet

In this step we will be deleting Worksheet “WorkSheet3”

#Delete Sheet3
$DeleteSheet = $EXCL.sheets.item("Sheet3");

6. Add Report Title

In this step we will add a report title  of “AdventureWorks Sales Year to Date By Country”, change the text to bold and change the text size to 16 pt. We are also going to Merge A1,B1,C1 cell together, change the font color to white, change the fill color to Green and change the Alignment to the middle.

#Add Report Title
$SHEET = $EXCL.WorkSheets.item("Sheet2")
$SHEET.Cells.Item(1,1) = 'AdventureWorks Sales Year to Date By Country'
$SHEET.Range("A1:C1").MergeCells =$True
$SHEET.Range("A1:C1").Interior.ColorIndex = 10  #Fill Color Green
$SHEET.Range("A1:C1").Font.ColorIndex = 2
$SHEET.Range("A1:C1").HorizontalAlignment = -4108  #Alignment Middle
#Right Alignment -4152
#Left Alignment -4131


7. Add Column Titles

In this step we will add three column titles in row 2, change the text to bold and change the column width

#Add Column Titles
$SHEET = $EXCL.sheets.item("Sheet2")
$SHEET.Cells.Item(2,1) = 'CountryRegionCode'
$SHEET.Cells.Item(2,2) = 'SalesYTD'
$SHEET.Cells.Item(2,3) = 'SalesLastYear'



8. Create DataTable

In this step I am selecting data from the AdventureWorks2012 SQL Server Database. Note in the $Datatable variable you will need to enter your SQL Server Name in the “-ServerInstane” parameter.

function Invoke-Sqlcmd3

    $conn=new-object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

#Create DataTable with our report data
[String]$TSQL= "
use AdventureWorks2012

,sum(SalesYTD) as SalesYTD
,SUM(SalesLastYear) as SalesLastYear
FROM AdventureWorks2012.Sales.SalesTerritory
GROUP BY CountryRegionCode
ORDER BY CountryRegionCode
$Datatable = Invoke-Sqlcmd3 -ServerInstance [Enter SQL Server Name] -Database AdventureWorks2012 -Query $TSQL -as 'DataTable'


9. Populate the Worksheet

In this step starting in row 3 we will be populating the Worksheet with data from the DataTable that was created in step 8.

#Populate the Worksheet
$SHEET = $EXCL.sheets.item("Sheet2")
$Datatable | FOREACH-OBJECT{
$SHEET.cells.item($x, 1) =  $_.CountryRegionCode
$SHEET.cells.item($x, 2) =  $_.SalesYTD
$SHEET.cells.item($x, 3) =  $_.SalesLastYear


10. Clean Up

In this Step we will rename “WorkSheet2” to “SalesYTD” and delete worksheets “WorkSheet1” and “sheet4”.

#Clean Up
#Rename Sheet2 to SalesYTD
$SHEET = $EXCL.sheets.item("Sheet2")
$SHEET.Name = "SalesYTD"
#Delete Worksheet and sheet4
$DeleteSheet = $EXCL.sheets.item("WorkSheet1");
$DeleteSheet = $EXCL.sheets.item("sheet4");


11. Save the Excel File

In this step we will save the Excel file to C:\Temp\SalesYTD.xlsx

#Save Excel File to C:\Temp\SalesYTD.xlsx
$SHEET = $EXCL.sheets.item("SalesYTD")