Graph and PowerShell BlogThe ImportExcel PowerShell module by Doug Finke allows users to import and export data in PowerShell to an Excel spreadsheet (.xlsx). This is my most-used module and my go-to for creating reports.
Most of the reports can be created with just the default export of PowerShell objects to a single worksheet. I normally create the spreadsheet as part of a table to make it easier to navigate:
$dataArray | export-excel -path c:\scripts\report.xlsx -tablename "MyTable"
While this is fine for most use cases, there is sometimes a need to highlight certain cells based on a value. In this example, we have a column for Disk size and another column for Free Disk space. I was asked to highlight cells with less than 5% disk space:
$xlpkg = $dataArray | export-excel -path c:\scripts\report.xlsx -tablename "MyTable" -worksheetname "ws1" -passtru
$ws = $xlpkg.workbook.worksheets["ws1"]
$rows = $ws.dimension.rows
$ws = $xlpkg.workbook.worksheets["ws1"]
$rows = $ws.dimension.rows
for ($=2;$i-lt $rows;$i++) { # cycle through all the rows starting at row 2, below the header
$diskSize = $ws.cells["b$i"].value
$diskFree = $ws.cells["c$i"].value
if ($diskSize) {
[int]$percentFree = ($diskSize / $diskFree) * 100
if ($percentFree -lt $diskWarning) { set-format -worksheet sw1 -address "d$i" -backgroundColor red }
}
}
close-excelPackage $xlpkg