One
PowerShell module I use a lot is ImportExcel, although I mainly use it to export data 😉 I have not even started using half of the CMDlets, but I also have yet to run into something it
cannot do. Also the documentation is awesome, there’s videos or articles on any
conceivable scenario on GitHub
and powershellgallery.
Specifically,
I use it mostly for two common scenarios:
a) Ad-hoc data exports. The “can you
quickly get me XYZ numbers?”. It’s just much nicer to
share a formatted excel sheet than a csv file.
b) Scheduled reports, that are usually
shared on Teams or by Email.
Often
scenario a turns into scenario b quickly, so it might
be worth saving the initial scripts.
In my
little example I export a simple query from AdventureWorks,
add some conditional formatting and then protect the sheet. So, these few lines
of code
$Server = "SQL"
$DB = "AdventureWorks"
$Path = "D:\Reports\SalesRepYearly.xlsx"
$Password = "Super$3cr37"
$Query = @"
SELECT pvt.[FullName]
,pvt.[SalesTerritory]
,pvt.[2011]
,pvt.[2012]
,pvt.[2013]
,pvt.[2014]
FROM (SELECT
p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') +
' ' + p.[LastName] AS [FullName]
,e.[JobTitle]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate]))
AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader]
soh
ON sp.[BusinessEntityID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee]
e
ON soh.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = sp.[BusinessEntityID]
) x
PIVOT (SUM([SubTotal]) FOR [FiscalYear] IN
([2011], [2012], [2013], [2014])) AS pvt;
"@
Send-SQLDataToExcel -Connection $Server -MsSqlServer -SQL $Query -DataBase $DB -Path $Path -WorkSheetname SalesRepYearly -AutoSize -FreezeTopRow -BoldTopRow -ClearSheet -TableName SalesRepYearly
-Numberformat Currency -QueryTimeout 36000
$excel = Open-ExcelPackage -Path $Path -KillExcel
$Rows = $excel.SalesRepYearly.Dimension.Rows
Add-ConditionalFormatting -Worksheet $excel.SalesRepYearly -Address "C2:F$($Rows)" -RuleType LessThanOrEqual
-ConditionValue 10000 -ForegroundColor RED -StopIfTrue
Add-ConditionalFormatting -Worksheet $excel.SalesRepYearly -Address "C2:F$($Rows)" -RuleType GreaterThanOrEqual
-ConditionValue 2500000 -ForegroundColor GREEN -StopIfTrue
$excel.SalesRepYearly.Protection.IsProtected
= $True
$excel.SalesRepYearly.Protection.AllowSelectLockedCells = $True
$excel.SalesRepYearly.Protection.AllowSelectUnlockedCells = $True
$excel.SalesRepYearly.Protection.AllowEditObject
= $False
$excel.SalesRepYearly.Protection.AllowFormatCells =
$True
$excel.SalesRepYearly.Protection.AllowFormatColumns = $True
$excel.SalesRepYearly.Protection.AllowFormatRows
= $True
$excel.SalesRepYearly.Protection.AllowInsertColumns = $True
$excel.SalesRepYearly.Protection.AllowInsertRows
= $False
$excel.SalesRepYearly.Protection.AllowDeleteColumns = $False
$excel.SalesRepYearly.Protection.AllowDeleteRows
= $False
$excel.SalesRepYearly.Protection.AllowSort
= $True
$excel.SalesRepYearly.Protection.AllowAutoFilter
= $True
$excel.SalesRepYearly.Protection.AllowPivotTables =
$True
$excel.SalesRepYearly.Protection.SetPassword($Password)
Close-ExcelPackage $excel
produce a
shareable, formatted, and protected Excel Sheet:
I
can now use Send-MailMessage to send it or Add-PnPFile to
share it on Teams or SharePoint.
