Examples/AddImage/Add-ExcelImage.ps1
function Add-ExcelImage {
<#
.SYNOPSIS
Adds an image to a worksheet in an Excel package.
.DESCRIPTION
Adds an image to a worksheet in an Excel package using the
`WorkSheet.Drawings.AddPicture(name, image)` method, and places the
image at the location specified by the Row and Column parameters.
Additional position adjustment can be made by providing RowOffset and
ColumnOffset values in pixels.
.EXAMPLE
$image = [System.Drawing.Image]::FromFile($octocat)
$xlpkg = $data | Export-Excel -Path $path -PassThru
$xlpkg.Sheet1 | Add-ExcelImage -Image $image -Row 4 -Column 6 -ResizeCell
Where $octocat is a path to an image file, and $data is a collection of
data to be exported, and $path is the output path for the Excel document,
Add-Excel places the image at row 4 and column 6, resizing the column
and row as needed to fit the image.
.INPUTS
[OfficeOpenXml.ExcelWorksheet]
.OUTPUTS
None
#>
[CmdletBinding()]
param(
# Specifies the worksheet to add the image to.
[Parameter(Mandatory, ValueFromPipeline)]
[OfficeOpenXml.ExcelWorksheet]
$WorkSheet,
# Specifies the Image to be added to the worksheet.
[Parameter(Mandatory)]
[System.Drawing.Image]
$Image,
# Specifies the row where the image will be placed. Rows are counted from 1.
[Parameter(Mandatory)]
[ValidateRange(1, [int]::MaxValue)]
[int]
$Row,
# Specifies the column where the image will be placed. Columns are counted from 1.
[Parameter(Mandatory)]
[ValidateRange(1, [int]::MaxValue)]
[int]
$Column,
# Specifies the name to associate with the image. Names must be unique per sheet.
# Omit the name and a GUID will be used instead.
[Parameter()]
[string]
$Name,
# Specifies the number of pixels to offset the image on the Y-axis. A
# positive number moves the image down by the specified number of pixels
# from the top border of the cell.
[Parameter()]
[int]
$RowOffset = 1,
# Specifies the number of pixels to offset the image on the X-axis. A
# positive number moves the image to the right by the specified number
# of pixels from the left border of the cell.
[Parameter()]
[int]
$ColumnOffset = 1,
# Increase the column width and row height to fit the image if the current
# dimensions are smaller than the image provided.
[Parameter()]
[switch]
$ResizeCell
)
begin {
if ($IsWindows -eq $false) {
throw "This only works on Windows and won't run on $([environment]::OSVersion)"
}
<#
These ratios work on my machine but it feels fragile. Need to better
understand how row and column sizing works in Excel and what the
width and height units represent.
#>
$widthFactor = 1 / 7
$heightFactor = 3 / 4
}
process {
if ([string]::IsNullOrWhiteSpace($Name)) {
$Name = (New-Guid).ToString()
}
if ($null -ne $WorkSheet.Drawings[$Name]) {
Write-Error "A picture with the name `"$Name`" already exists in worksheet $($WorkSheet.Name)."
return
}
<#
The row and column offsets of 1 ensures that the image lands just
inside the gray cell borders at the top left.
#>
$picture = $WorkSheet.Drawings.AddPicture($Name, $Image)
$picture.SetPosition($Row - 1, $RowOffset, $Column - 1, $ColumnOffset)
if ($ResizeCell) {
<#
Adding 1 to the image height and width ensures that when the
row and column are resized, the bottom right of the image lands
just inside the gray cell borders at the bottom right.
#>
$width = $widthFactor * ($Image.Width + 1)
$height = $heightFactor * ($Image.Height + 1)
$WorkSheet.Column($Column).Width = [Math]::Max($width, $WorkSheet.Column($Column).Width)
$WorkSheet.Row($Row).Height = [Math]::Max($height, $WorkSheet.Row($Row).Height)
}
}
}
Examples/AddImage/AddImage.ps1
if ($IsWindows -eq $false) {
throw "This only works on Windows and won't run on $([environment]::OSVersion)"
}
Add-Type -AssemblyName System.Drawing
. $PSScriptRoot\Add-ExcelImage.ps1
$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$path = "$PSScriptRoot/Add-Picture-test.xlsx"
Remove-Item $path -ErrorAction SilentlyContinue
try {
$octocat = "$PSScriptRoot/Octocat.jpg"
$image = [System.Drawing.Image]::FromFile($octocat)
$xlpkg = $data | Export-Excel -Path $path -PassThru
$xlpkg.Sheet1 | Add-ExcelImage -Image $image -Row 4 -Column 6 -ResizeCell
}
finally {
if ($image) {
$image.Dispose()
}
if ($xlpkg) {
Close-ExcelPackage -ExcelPackage $xlpkg -Show
}
}
Examples/AddImage/README.md
# Add-ExcelImage Example
Adding pictures to an Excel worksheet is possible by calling the `AddPicture(name, image)`
method on the `Drawings` property of an `ExcelWorksheet` object.
The `Add-ExcelImage` example here demonstrates how to add a picture at a given
cell location, and optionally resize the row and column to fit the image.
## Running the example
To try this example, run the script `AddImage.ps1`. The `Add-ExcelImage`
function will be dot-sourced, and an Excel document will be created in the same
folder with a sample data set. The Octocat image will then be embedded into
Sheet1.
The creation of the Excel document and the `System.Drawing.Image` object
representing Octocat are properly disposed within a `finally` block to ensure
that the resources are released, even if an error occurs in the `try` block.
## Note about column and row sizing
Care has been taken in this example to get the image placement to be just inside
the cell border, and if the `-ResizeCell` switch is present, the height and width
of the row and column will be increased, if needed, so that the bottom right of
the image also lands just inside the cell border.
The Excel row and column sizes are measured in "point" units rather than pixels,
and a fixed multiplication factor is used to convert the size of the image in
pixels, to the corresponding height and width values in Excel.
It's possible that different DPI or text scaling options could result in
imperfect column and row sizing and if a better strategy is found for converting
the image dimensions to column and row sizes, this example will be updated.
Examples/AddWorkSheet/AddMultiWorkSheet.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Put some simple data in a worksheet and Get an excel package object to represent the file
1..5 | Export-Excel $xlSourcefile -WorksheetName 'Tab1' -AutoSize -AutoFilter
#Add another tab. Replace the $TabData2 with your data
1..10 | Export-Excel $xlSourcefile -WorksheetName 'Tab 2' -AutoSize -AutoFilter
#Add another tab. Replace the $TabData3 with your data
1..15 | Export-Excel $xlSourcefile -WorksheetName 'Tab 3' -AutoSize -AutoFilter -Show
Examples/AddWorkSheet/AddWorkSheet.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Put some simple data in a worksheet and Get an excel package object to represent the file
$excel = 1..10 | Export-Excel $xlSourcefile -PassThru
#Add a new worksheet named 'NewSheet' and copying the sheet that was just made (Sheet1) to the new sheet
Add-Worksheet -ExcelPackage $excel -WorkSheetname "NewSheet" -CopySource $excel.Workbook.Worksheets["Sheet1"]
#Save and open in Excel
Close-ExcelPackage -ExcelPackage $excel -Show
Examples/Charts/ChartAndTrendlines.ps1
# Creates a worksheet, addes a chart and then a Linear trendline
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv @"
Region,Item,TotalSold
West,screws,60
South,lemon,48
South,apple,71
East,screwdriver,70
East,kiwi,32
West,screwdriver,1
South,melon,21
East,apple,79
South,apple,68
South,avocado,73
"@
$cd = New-ExcelChartDefinition -XRange Region -YRange TotalSold -ChartType ColumnClustered -ChartTrendLine Linear
$data | Export-Excel $xlSourcefile -ExcelChartDefinition $cd -AutoNameRange -Show
Examples/Charts/ChartDataSeparatePage.ps1
$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$xlfile = "$PSScriptRoot\spike.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$xlpkg = $data | Export-Excel $xlfile -WorksheetName Data -AutoNameRange -PassThru
$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Summary -Activate
$params = @{
Worksheet = $xlpkg.Summary
Title = "Sales by Region"
ChartType = 'ColumnClustered'
# XRange = "Data!A2:A10"
# YRange = "Data!C2:C10"
XRange = 'Data!Region'
YRange = 'Data!Units'
}
Add-ExcelChart @params
Close-ExcelPackage $xlpkg -Show
Examples/Charts/MultiSeries.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = Invoke-Sum -data (Get-Process) -dimension Company -measure Handles, PM, VirtualMemorySize
$c = New-ExcelChartDefinition -Title "ProcessStats" `
-ChartType LineMarkersStacked `
-XRange "Processes[Name]" `
-YRange "Processes[PM]","Processes[VirtualMemorySize]" `
-SeriesHeader "PM","VM"
$data |
Export-Excel -Path $xlSourcefile -AutoSize -TableName Processes -ExcelChartDefinition $c -Show
Examples/Charts/MultiSeries1.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = @"
A,B,C,Date
2,1,1,2016-03-29
5,10,1,2016-03-29
"@
$c = New-ExcelChartDefinition -Title Impressions `
-ChartType Line `
-XRange "Impressions[Date]" `
-YRange @("Impressions[B]","Impressions[A]") `
-SeriesHeader 'B data','A data' `
-Row 0 -Column 0
$data | ConvertFrom-Csv | Export-Excel -path $xlSourcefile -AutoSize -TableName Impressions
Export-Excel -path $xlSourcefile -worksheetName chartPage -ExcelChartDefinition $c -show
Examples/Charts/MultipleCharts.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = @"
ID,Product,Quantity,Price,Total
12001,Nails,37,3.99,147.63
12002,Hammer,5,12.10,60.5
12003,Saw,12,15.37,184.44
12010,Drill,20,8,160
12011,Crowbar,7,23.48,164.36
"@
$c1 = New-ExcelChartDefinition -YRange "Price" -XRange "Product" -Title "Item price" -NoLegend -Height 225
$c2 = New-ExcelChartDefinition -YRange "Total "-XRange "Product" -Title "Total sales" -NoLegend -Height 225 -Row 9 -Column 15
$c3 = New-ExcelChartDefinition -YRange "Quantity"-XRange "Product" -Title "Sales volume" -NoLegend -Height 225 -Row 15
$data | ConvertFrom-Csv |
Export-Excel -Path $xlSourcefile -AutoFilter -AutoNameRange -AutoSize -ExcelChartDefinition $c1,$c2,$c3 -Show
Examples/Charts/NumberOfVisitors.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv @"
Week, TotalVisitors
1,11916
2,11665
3,13901
4,15444
5,21592
6,15057
7,26187
8,20662
9,28935
10,32443
"@
$cd = New-ExcelChartDefinition `
-XRange Week `
-YRange TotalVisitors `
-Title "No. Of Visitors" `
-ChartType ColumnClustered `
-NoLegend `
-ChartTrendLine Linear
$data | Export-Excel $xlSourcefile -Show -AutoNameRange -AutoSize -TableName Visitors -ExcelChartDefinition $cd
Examples/Charts/plot.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
function plot {
param(
$f,
$minx,
$maxx
)
$minx=[math]::Round($minx,1)
$maxx=[math]::Round($maxx,1)
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
# $c = New-ExcelChart -XRange X -YRange Y -ChartType Line -NoLegend -Title Plot -Column 2 -ColumnOffSetPixels 35
$(for ($i = $minx; $i -lt $maxx-.1; $i+=.1) {
[pscustomobject]@{
X=$i.ToString("N1")
Y=(&$f $i)
}
}) | Export-Excel $xlSourcefile -Show -AutoNameRange -LineChart -NoLegend #-ExcelChartDefinition $c
}
function pi {[math]::pi}
plot -f {[math]::Tan($args[0])} -minx (pi) -maxx (3*(pi)/2-.01)
Examples/CommunityContributions/MultipleWorksheets.ps1
<#
To see this written up with example screenshots, head over to the IT Splat blog
URL: http://bit.ly/2SxieeM
#>
## Create an Excel file with multiple worksheets
# Get a list of processes on the system
$processes = Get-Process | Sort-Object -Property ProcessName | Group-Object -Property ProcessName | Where-Object {$_.Count -gt 2}
# Export the processes to Excel, each process on its own sheet
$processes | ForEach-Object { $_.Group | Export-Excel -Path MultiSheetExample.xlsx -WorksheetName $_.Name -AutoSize -AutoFilter }
# Show the completed file
Invoke-Item .\MultiSheetExample.xlsx
## Add an additional sheet to the new workbook
# Use Open-ExcelPackage to open the workbook
$excelPackage = Open-ExcelPackage -Path .\MultiSheetExample.xlsx
# Create a new worksheet and give it a name, set MoveToStart to make it the first sheet
$ws = Add-Worksheet -ExcelPackage $excelPackage -WorksheetName 'All Services' -MoveToStart
# Get all the running services on the system
Get-Service | Export-Excel -ExcelPackage $excelPackage -WorksheetName $ws -AutoSize -AutoFilter
# Close the package and show the final result
Close-ExcelPackage -ExcelPackage $excelPackage -Show
Examples/ConditionalFormatting/CodeGenExamples.ps1
"Last7Days", "LastMonth", "LastWeek", "NextMonth", "NextWeek", "ThisMonth", "ThisWeek", "Today", "Tomorrow", "Yesterday" |
Foreach-Object {
$text = @"
`$f = ".\testExport.xlsx"
remove-item `$f -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel `$f -Show -AutoSize -ConditionalText `$(
New-ConditionalText -ConditionalType $_
)
"@
$text | Set-Content -Encoding Ascii "Highlight-$($_).ps1"
}
Examples/ConditionalFormatting/ConditionalFormattingIcontSetOnlyIcon.ps1
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return }
$data = ConvertFrom-Csv @"
Region,State,Other,Units,Price,InStock
West,Texas,1,927,923.71,1
North,Tennessee,3,466,770.67,0
East,Florida,0,1520,458.68,1
East,Maine,1,1828,661.24,0
West,Virginia,1,465,053.58,1
North,Missouri,1,436,235.67,1
South,Kansas,0,214,992.47,1
North,North Dakota,1,789,640.72,0
South,Delaware,-1,712,508.55,1
"@
$xlfile = "$PSScriptRoot\test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$cfi1 = New-ConditionalFormattingIconSet -Range C:C -ConditionalFormat ThreeIconSet -IconType Symbols -ShowIconOnly
$cfi2 = New-ConditionalFormattingIconSet -Range F:F -ConditionalFormat ThreeIconSet -IconType Symbols2 -ShowIconOnly
$data | Export-Excel $xlfile -AutoSize -ConditionalFormat $cfi1, $cfi2 -Show
Examples/ConditionalFormatting/ConditionalText.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
Get-Service |
Select-Object Status, Name, DisplayName, ServiceName |
Export-Excel $xlSourcefile -Show -AutoSize -AutoFilter -ConditionalText $(
New-ConditionalText stop #Stop is the condition value, the rule is defaults to 'Contains text' and the default Colors are used
New-ConditionalText runn darkblue cyan #runn is the condition value, the rule is defaults to 'Contains text'; the foregroundColur is darkblue and the background is cyan
New-ConditionalText -ConditionalType EndsWith svc wheat green #the rule here is 'Ends with' and the value is 'svc' the forground is wheat and the background dark green
New-ConditionalText -ConditionalType BeginsWith windows darkgreen wheat #this is 'Begins with "Windows"' the forground is dark green and the background wheat
)
Examples/ConditionalFormatting/ContainsBlanks.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Define a "Contains blanks" rule. No format is specified so it default to dark-red text on light-pink background.
$ContainsBlanks = New-ConditionalText -ConditionalType ContainsBlanks
$data = $(
New-PSItem a b c @('p1', 'p2', 'p3')
New-PSItem
New-PSItem d e f
New-PSItem
New-PSItem
New-PSItem g h i
)
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#use the conditional format definition created above
$data | Export-Excel $xlSourcefile -show -ConditionalText $ContainsBlanks
Examples/ConditionalFormatting/Databar.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Export processes, and get an ExcelPackage object representing the file.
$excel = Get-Process |
Select-Object -Property Name,Company,Handles,CPU,PM,NPM,WS |
Export-Excel -Path $xlSourcefile -ClearSheet -WorkSheetname "Processes" -PassThru
$sheet = $excel.Workbook.Worksheets["Processes"]
#Apply fixed formatting to columns. -NFormat is an alias for numberformat
$sheet.Column(1) | Set-ExcelRange -Bold -AutoFit
$sheet.Column(2) | Set-ExcelRange -Width 29 -WrapText
$sheet.Column(3) | Set-ExcelRange -HorizontalAlignment Right -NFormat "#,###"
Set-ExcelRange -Range $sheet.Cells["E1:H1048576"] -HorizontalAlignment Right -NFormat "#,###"
Set-ExcelRange -Range $sheet.Column(4) -HorizontalAlignment Right -NFormat "#,##0.0" -Bold
#In Set-ExcelRange "-Address" is an alias for "-Range"
Set-ExcelRange -Address $sheet.Row(1) -Bold -HorizontalAlignment Center
#Create a Red Data-bar for the values in Column D
Add-ConditionalFormatting -Worksheet $sheet -Address "D2:D1048576" -DataBarColor Red
# Conditional formatting applies to "Addreses" aliases allow either "Range" or "Address" to be used in Set-ExcelRange or Add-Conditional formatting.
Add-ConditionalFormatting -Worksheet $sheet -Range "G2:G1048576" -RuleType GreaterThan -ConditionValue "104857600" -ForeGroundColor Red
foreach ($c in 5..9) {Set-ExcelRange -Address $sheet.Column($c) -AutoFit }
#Create a pivot and save the file.
Export-Excel -ExcelPackage $excel -WorkSheetname "Processes" -IncludePivotChart -ChartType ColumnClustered -NoLegend -PivotRows company -PivotData @{'Name'='Count'} -Show
Examples/ConditionalFormatting/FormatCalculations.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = $(
New-PSItem North 111 @( 'Region', 'Amount' )
New-PSItem East 111
New-PSItem West 122
New-PSItem South 200
New-PSItem NorthEast 103
New-PSItem SouthEast 145
New-PSItem SouthWest 136
New-PSItem South 127
New-PSItem NorthByNory 100
New-PSItem NothEast 110
New-PSItem Westerly 120
New-PSItem SouthWest 118
)
# in this example instead of doing $variable = New-Conditional text .... ; Export-excel -ConditionalText $variable
# the syntax is used is Export-excel -ConditionalText (New-Conditional text )
#$data | Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText (New-ConditionalText -ConditionalType AboveAverage)
$data | Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText (New-ConditionalText -ConditionalType BelowAverage)
#$data | Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText (New-ConditionalText -ConditionalType TopPercent)
Examples/ConditionalFormatting/GenDates.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
function Get-DateOffset {
param($days=0)
(Get-Date).AddDays($days).ToShortDateString()
}
function Get-Number {
Get-Random -Minimum 10 -Maximum 100
}
New-PSItem (Get-DateOffset -7) (Get-Number) 'LastWeek,Last7Days,ThisMonth' @('Date', 'Amount', 'Label')
New-PSItem (Get-DateOffset) (Get-Number) 'Today,ThisMonth,ThisWeek'
New-PSItem (Get-DateOffset -30) (Get-Number) LastMonth
New-PSItem (Get-DateOffset -1) (Get-Number) 'Yesterday,ThisMonth,ThisWeek'
New-PSItem (Get-DateOffset) (Get-Number) 'Today,ThisMonth,ThisWeek'
New-PSItem (Get-DateOffset -5) (Get-Number) 'LastWeek,Last7Days,ThisMonth'
New-PSItem (Get-DateOffset 7) (Get-Number) 'NextWeek,ThisMonth'
New-PSItem (Get-DateOffset 28) (Get-Number) NextMonth
New-PSItem (Get-DateOffset) (Get-Number) 'Today,ThisMonth,ThisWeek'
New-PSItem (Get-DateOffset -6) (Get-Number) 'LastWeek,Last7Days,ThisMonth'
New-PSItem (Get-DateOffset -2) (Get-Number) 'Last7Days,ThisMonth,ThisWeek'
New-PSItem (Get-DateOffset 1) (Get-Number) 'Tomorrow,ThisMonth,ThisWeek'
Examples/ConditionalFormatting/GetConditionalFormatting.ps1
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return}
# This example is using Excel generated by Highlight-DiffCells.ps1
# The displayed rule should be the same as in the PS script
function Get-ConditionalFormatting {
param (
[string] $xlSourcefile
)
$excel = Open-ExcelPackage -Path $xlSourcefile
$excel.Workbook.Worksheets | ForEach-Object {
$wsNme = $_.Name
$_.ConditionalFormatting | ForEach-Object {
"Add-ConditionalFormatting -Worksheet `$excel[""$wsNme""] -Range '$($_.Address)' -ConditionValue '=$($_.Formula)' -RuleType $($_.Type) "
}
}
}
$xlSourcefile = "$PSScriptRoot\GetConditionalFormatting.xlsx"
Get-ConditionalFormatting -xlSourcefile $xlSourcefile
Examples/ConditionalFormatting/GetProcess.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
Get-Process | Where-Object Company | Select-Object Company, Name, PM, Handles, *mem* |
#This example creates a 3 Icon set for the values in the "PM column, and Highlights company names (anywhere in the data) with different colors
Export-Excel -Path $xlSourcefile -Show -AutoSize -AutoNameRange `
-ConditionalFormat $(
New-ConditionalFormattingIconSet -Range "C:C" `
-ConditionalFormat ThreeIconSet -IconType Arrows
) -ConditionalText $(
New-ConditionalText Microsoft -ConditionalTextColor Black
New-ConditionalText Google -BackgroundColor Cyan -ConditionalTextColor Black
New-ConditionalText authors -BackgroundColor LightBlue -ConditionalTextColor Black
New-ConditionalText nvidia -BackgroundColor LightGreen -ConditionalTextColor Black
)
Examples/ConditionalFormatting/Highlight-DiffCells.ps1
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return }
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv @"
Region,State,Units2021,Units2022
West,Texas,927,925
North,Tennessee,466,466
East,Florida,520,458
East,Maine,828,661
West,Virginia,465,465
North,Missouri,436,235
South,Kansas,214,214
North,North Dakota,789,640
South,Delaware,712,508
"@
$excel = $data | Export-Excel $xlSourcefile -AutoSize -PassThru
Add-ConditionalFormatting -Worksheet $excel.sheet1 -Range "C2:D10" -ConditionValue '=$C2=$D2' -RuleType Expression -BackgroundColor ([System.Drawing.Color]::Thistle) -Bold
Add-ConditionalFormatting -Worksheet $excel.sheet1 -Range "A2:D10" -ConditionValue '=$C2=$D2' -RuleType Expression -BackgroundColor ([System.Drawing.Color]::LavenderBlush)
Close-ExcelPackage $excel -Show
Examples/ConditionalFormatting/Highlight-Last7Days.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType Last7Days
)
Examples/ConditionalFormatting/Highlight-LastMonth.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType LastMonth
)
Examples/ConditionalFormatting/Highlight-LastWeek.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType LastWeek
)
Examples/ConditionalFormatting/Highlight-NextMonth.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType NextMonth
)
Examples/ConditionalFormatting/Highlight-NextWeek.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType NextWeek
)
Examples/ConditionalFormatting/Highlight-ThisMonth.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType ThisMonth
)
Examples/ConditionalFormatting/Highlight-ThisWeek.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType ThisWeek
)
Examples/ConditionalFormatting/Highlight-Today.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType Today
)
Examples/ConditionalFormatting/Highlight-Tomorrow.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType Tomorrow
)
Examples/ConditionalFormatting/Highlight-Yesterday.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
.\GenDates.ps1 |
Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText $(
New-ConditionalText -ConditionalType Yesterday
)
Examples/ConditionalFormatting/HighlightDuplicates.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = $(
New-PSItem North 111 @('Region', 'Amount' )
New-PSItem East 11
New-PSItem West 12
New-PSItem South 1000
New-PSItem NorthEast 10
New-PSItem SouthEast 14
New-PSItem SouthWest 13
New-PSItem South 12
New-PSItem NorthByNory 100
New-PSItem NothEast 110
New-PSItem Westerly 120
New-PSItem SouthWest 11
)
$data | Export-Excel $xlSourcefile -Show -AutoSize -ConditionalText (New-ConditionalText -ConditionalType DuplicateValues)
Examples/ConditionalFormatting/MonthlyTemperatuesDatabar.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$excel = @"
Month,New York City,Austin Texas,Portland Oregon
Jan,39,61,46
Feb,42,65,51
Mar,50,73,56
Apr,62,80,61
May,72,86,67
Jun,80,92,73
Jul,85,95,80
Aug,84,96,80
Sep,76,90,75
Oct,65,82,63
Nov,54,71,52
Dec,44,63,46
"@ | ConvertFrom-csv |
Export-Excel -Path $xlSourcefile -WorkSheetname Sheet1 -AutoNameRange -AutoSize -Title "Monthly Temperatures" -PassThru
$sheet = $excel.Workbook.Worksheets["Sheet1"]
Add-ConditionalFormatting -Worksheet $sheet -Range "B1:D14" -DataBarColor CornflowerBlue
Close-ExcelPackage $excel -Show
Examples/ConditionalFormatting/RangeFormatting.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
function Get-DateOffset ($days=0) {
(Get-Date).AddDays($days).ToShortDateString()
}
$(
New-PSItem (Get-DateOffset -1) (Get-DateOffset 1) @("Start", "End")
New-PSItem (Get-DateOffset) (Get-DateOffset 7)
New-PSItem (Get-DateOffset -10) (Get-DateOffset -1)
) |
Export-Excel $xlSourcefile -Show -AutoSize -AutoNameRange -ConditionalText $(
New-ConditionalText -Range Start -ConditionalType Yesterday -ConditionalTextColor Red
New-ConditionalText -Range End -ConditionalType Yesterday -BackgroundColor Blue -ConditionalTextColor Red
)
Examples/ConditionalFormatting/SalesReportWithDatabar.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$excel = @"
Month,Sales
Jan,1277
Feb,1003
Mar,1105
Apr,952
May,770
Jun,621
"@ | ConvertFrom-csv |
Export-Excel -Path $xlSourcefile -WorkSheetname Sheet1 -AutoNameRange -PassThru
$sheet = $excel.Workbook.Worksheets["Sheet1"]
Add-ConditionalFormatting -Worksheet $sheet -Range "B1:B7" -DataBarColor LawnGreen
Set-ExcelRange -Address $sheet.Cells["A8"] -Value "Total"
Set-ExcelRange -Address $sheet.Cells["B8"] -Formula "=Sum(Sales)"
Close-ExcelPackage $excel -Show
Examples/ConditionalFormatting/TextComparisons.ps1
try {Import-Module ..\..\ImportExcel.psd1 -Force} catch {throw ; return}
$data = $(
New-PSItem 100 @('test', 'testx')
New-PSItem 200
New-PSItem 300
New-PSItem 400
New-PSItem 500
)
#Get rid of pre-exisiting sheet
$xlSourcefile1 = "$env:TEMP\ImportExcelExample1.xlsx"
$xlSourcefile2 = "$env:TEMP\ImportExcelExample2.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile1"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile2"
Remove-Item $xlSourcefile1 -ErrorAction Ignore
Remove-Item $xlSourcefile2 -ErrorAction Ignore
$data | Export-Excel $xlSourcefile1 -Show -ConditionalText $(
New-ConditionalText -ConditionalType GreaterThan 300
New-ConditionalText -ConditionalType LessThan 300 -BackgroundColor cyan
)
$data | Export-Excel $xlSourcefile2 -Show -ConditionalText $(
New-ConditionalText -ConditionalType GreaterThanOrEqual 275
New-ConditionalText -ConditionalType LessThanOrEqual 250 -BackgroundColor cyan
)
Examples/ConditionalFormatting/Top10-DataBar-TwoColorScale.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-csv @"
Store,January,February,March,April,May,June
store27,99511,64582,45216,48690,64921,54066
store82,22275,23708,28223,26699,41388,31648
store41,24683,22583,97947,31999,39092,41201
store16,16568,48040,68589,20394,63202,26197
store21,99353,23470,28398,21788,94101,88608
store86,66662,83321,27489,92627,54084,24278
store07,92692,53300,29284,39643,33556,53885
store58,68875,83705,66635,81025,30207,75570
store01,21292,82341,81339,12505,29516,41634
store82,74047,93325,25002,40113,76278,45707
"@
Export-Excel -InputObject $data -Path $xlSourcefile -TableName RawData -WorksheetName RawData
Export-Excel -InputObject $data -Path $xlSourcefile -TableName TopData -WorksheetName StoresTop10Sales
Export-Excel -InputObject $data -Path $xlSourcefile -TableName Databar -WorksheetName StoresSalesDataBar
Export-Excel -InputObject $data -Path $xlSourcefile -TableName TwoColorScale -WorksheetName StoresSalesTwoColorScale
$xl = Open-ExcelPackage -Path $xlSourcefile
Set-ExcelRange -Worksheet $xl.StoresTop10Sales -Range $xl.StoresTop10Sales.dimension.address -NumberFormat 'Currency' -AutoSize
Set-ExcelRange -Worksheet $xl.StoresSalesDataBar -Range $xl.StoresSalesDataBar.dimension.address -NumberFormat 'Currency' -AutoSize
Set-ExcelRange -Worksheet $xl.StoresSalesTwoColorScale -Range $xl.StoresSalesDataBar.dimension.address -NumberFormat 'Currency' -AutoSize
Add-ConditionalFormatting -Worksheet $xl.StoresTop10Sales -Address $xl.StoresTop10Sales.dimension.address -RuleType Top -ForegroundColor white -BackgroundColor green -ConditionValue 10
Add-ConditionalFormatting -Worksheet $xl.StoresSalesDataBar -Address $xl.StoresSalesDataBar.dimension.address -DataBarColor Red
Add-ConditionalFormatting -Worksheet $xl.StoresSalesTwoColorScale -Address $xl.StoresSalesDataBar.dimension.address -RuleType TwoColorScale
Close-ExcelPackage $xl -Show
Examples/ConvertFrom/ConvertFrom.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
ConvertFrom-ExcelToSQLInsert People .\testSQLGen.xlsx
ConvertFrom-ExcelData .\testSQLGen.xlsx {
param($propertyNames, $record)
$reportRecord = @()
foreach ($pn in $propertyNames) {
$reportRecord += "{0}: {1}" -f $pn, $record.$pn
}
$reportRecord +=""
$reportRecord -join "`r`n"
}
Examples/CustomNumbers/ShortenNumbers.ps1
# How to convert abbreviate or shorten long numbers in Excel
Remove-Item .\custom.xlsx -ErrorAction SilentlyContinue
$data = $(
12000
1000
2000
3000
2400
3600
6000
13000
40000
400000
1000000
)
$excel = $data | Export-Excel .\custom.xlsx -PassThru
Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A:A" -NumberFormat '[>999999]#,,"M";#,"K"'
Close-ExcelPackage $excel -Show
Examples/CustomReporting/CustomReport.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = @"
From,To,RDollars,RPercent,MDollars,MPercent,Revenue,Margin
Atlanta,New York,3602000,.0809,955000,.09,245,65
New York,Washington,4674000,.105,336000,.03,222,16
Chicago,New York,4674000,.0804,1536000,.14,550,43
New York,Philadelphia,12180000,.1427,-716000,-.07,321,-25
New York,San Francisco,3221000,.0629,1088000,.04,436,21
New York,Phoneix,2782000,.0723,467000,.10,674,33
"@ | ConvertFrom-Csv
$data | Export-Excel $xlSourcefile -AutoSize
$excel = Open-ExcelPackage $xlSourcefile
$sheet1 = $excel.Workbook.Worksheets["sheet1"]
$sheet1.View.ShowGridLines = $false
$sheet1.View.ShowHeaders = $false
Set-ExcelRange -Address $sheet1.Cells["C:C"] -NumberFormat "$#,##0" -WrapText -HorizontalAlignment Center
Set-ExcelRange -Address $sheet1.Cells["D:D"] -NumberFormat "#.#0%" -WrapText -HorizontalAlignment Center
Set-ExcelRange -Address $sheet1.Cells["E:E"] -NumberFormat "$#,##0" -WrapText -HorizontalAlignment Center
Set-ExcelRange -Address $sheet1.Cells["F:F"] -NumberFormat "#.#0%" -WrapText -HorizontalAlignment Center
Set-ExcelRange -Address $sheet1.Cells["G:H"] -WrapText -HorizontalAlignment Center
## Insert Rows/Columns
$sheet1.InsertRow(1, 1)
foreach ($col in @(2, 4, 6, 8, 10, 12, 14)) {
$sheet1.InsertColumn($col, 1)
$sheet1.Column($col).width = .75
}
Set-ExcelRange -Address $sheet1.Cells["E:E"] -Width 12
Set-ExcelRange -Address $sheet1.Cells["I:I"] -Width 12
$BorderBottom = "Thick"
$BorderColor = "Black"
Set-ExcelRange -Address $sheet1.Cells["A2"] -BorderBottom $BorderBottom -BorderColor $BorderColor
Set-ExcelRange -Address $sheet1.Cells["C2"] -BorderBottom $BorderBottom -BorderColor $BorderColor
Set-ExcelRange -Address $sheet1.Cells["E2:G2"] -BorderBottom $BorderBottom -BorderColor $BorderColor
Set-ExcelRange -Address $sheet1.Cells["I2:K2"] -BorderBottom $BorderBottom -BorderColor $BorderColor
Set-ExcelRange -Address $sheet1.Cells["M2:O2"] -BorderBottom $BorderBottom -BorderColor $BorderColor
Set-ExcelRange -Address $sheet1.Cells["A2:C8"] -FontColor Gray
$HorizontalAlignment = "Center"
Set-ExcelRange -Address $sheet1.Cells["F1"] -HorizontalAlignment $HorizontalAlignment -Bold -Value Revenue
Set-ExcelRange -Address $sheet1.Cells["J1"] -HorizontalAlignment $HorizontalAlignment -Bold -Value Margin
Set-ExcelRange -Address $sheet1.Cells["N1"] -HorizontalAlignment $HorizontalAlignment -Bold -Value Passenger
Set-ExcelRange -Address $sheet1.Cells["E2"] -Value '($)'
Set-ExcelRange -Address $sheet1.Cells["G2"] -Value '%'
Set-ExcelRange -Address $sheet1.Cells["I2"] -Value '($)'
Set-ExcelRange -Address $sheet1.Cells["K2"] -Value '%'
Set-ExcelRange -Address $sheet1.Cells["C10"] -HorizontalAlignment Right -Bold -Value "Grand Total Calculation"
Set-ExcelRange -Address $sheet1.Cells["E10"] -Formula "=Sum(E3:E8)" -Bold
Set-ExcelRange -Address $sheet1.Cells["I10"] -Formula "=Sum(I3:I8)" -Bold
Set-ExcelRange -Address $sheet1.Cells["M10"] -Formula "=Sum(M3:M8)" -Bold
Set-ExcelRange -Address $sheet1.Cells["O10"] -Formula "=Sum(O3:O8)" -Bold
Close-ExcelPackage $excel -Show
Examples/CustomizeExportExcel/Out-Excel.ps1
<#
This is an example on how to customize Export-Excel to your liking.
First select a name for your function, in ths example its "Out-Excel" you can even set the name to "Export-Excel".
You can customize the following things:
1. To add parameters to the function define them in "param()", here I added "Preset1" and "Preset2".
The parameters need to be removed after use (see comments and code below).
2. To remove parameters from the function add them to the list under "$_.Name -notmatch", I removed "Now".
3. Add your custom code, here I defined what the Presets do:
Preset1 configure the TableStyle, name the table depending on WorksheetName and FreezeTopRow.
Preset2 will set AutoFilter and add the Title "Daily Report".
(see comments and code below).
#>
function Out-Excel {
[CmdletBinding(DefaultParameterSetName = 'Default')]
param(
[switch]
${Preset1},
[switch]
${Preset2}
)
DynamicParam {
$paramDictionary = [System.Management.Automation.RuntimeDefinedParameterDictionary]::new()
foreach ($P in (Get-Command -Name Export-Excel).Parameters.values.where( { $_.Name -notmatch 'Verbose|Debug|Action$|Variable$|Buffer$|Now' })) {
$paramDictionary.Add($P.Name, [System.Management.Automation.RuntimeDefinedParameter]::new( $P.Name, $P.ParameterType, $P.Attributes ) )
}
return $paramDictionary
}
begin {
try {
# Run you custom code here if it need to run before calling Export-Excel.
$PSBoundParameters['Now'] = $true
if ($Preset1) {
$PSBoundParameters['TableStyle'] = 'Medium7'
$PSBoundParameters['FreezeTopRow'] = $true
if ($PSBoundParameters['WorksheetName'] -and -not $PSBoundParameters['TableName']) {
$PSBoundParameters['TableName'] = $PSBoundParameters['WorksheetName'] + '_Table'
}
}
elseif ($Preset2) {
$PSBoundParameters['Title'] = 'Daily Report'
$PSBoundParameters['AutoFilter'] = $true
}
# Remove the extra params we added as Export-Excel will not know what to do with them:
$null = $PSBoundParameters.Remove('Preset1')
$null = $PSBoundParameters.Remove('Preset2')
# The rest of the code was auto generated.
$outBuffer = $null
if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer)) {
$PSBoundParameters['OutBuffer'] = 1
}
$wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Export-Excel', [System.Management.Automation.CommandTypes]::Function)
# You can add a pipe after @PSBoundParameters to manipulate the output.
$scriptCmd = { & $wrappedCmd @PSBoundParameters }
$steppablePipeline = $scriptCmd.GetSteppablePipeline()
$steppablePipeline.Begin($PSCmdlet)
}
catch {
throw
}
}
process {
try {
$steppablePipeline.Process($_)
}
catch {
throw
}
}
end {
try {
$steppablePipeline.End()
}
catch {
throw
}
}
<#
.ForwardHelpTargetName Export-Excel
.ForwardHelpCategory Function
#>
}
Examples/ExcelBuiltIns/DSUM.ps1
# DSUM
# Adds the numbers in a field (column) of records in a list or database that match conditions that you specify.
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv @"
Color,Date,Sales
Red,1/15/2018,250
Blue,1/15/2018,200
Red,1/16/2018,175
Blue,1/16/2018,325
Red,1/17/2018,150
Blue,1/17/2018,300
"@
$xl = Export-Excel -InputObject $data -Path $xlSourcefile -AutoSize -AutoFilter -TableName SalesInfo -AutoNameRange -PassThru
$databaseAddress = $xl.Sheet1.Dimension.Address
Set-Format -Worksheet $xl.Sheet1 -Range C:C -NumberFormat '$##0'
Set-Format -Worksheet $xl.Sheet1 -Range E1 -Value Color
Set-Format -Worksheet $xl.Sheet1 -Range F1 -Value Date
Set-Format -Worksheet $xl.Sheet1 -Range G1 -Value Sales
Set-Format -Worksheet $xl.Sheet1 -Range E2 -Value Red
Set-Format -Worksheet $xl.Sheet1 -Range E4 -Value Sales
Set-Format -Worksheet $xl.Sheet1 -Range F4 -Formula ('=DSUM({0},"Sales",E1:G2)' -f $databaseAddress) -NumberFormat '$##0'
Close-ExcelPackage $xl -Show
Examples/ExcelBuiltIns/VLOOKUP.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv @"
Fruit,Amount
Apples,50
Oranges,20
Bananas,60
Lemons,40
"@
$xl = Export-Excel -InputObject $data -Path $xlSourcefile -PassThru -AutoSize
Set-ExcelRange -Worksheet $xl.Sheet1 -Range D2 -BackgroundColor LightBlue -Value Apples
$rows = $xl.Sheet1.Dimension.Rows
Set-ExcelRange -Worksheet $xl.Sheet1 -Range E2 -Formula "=VLookup(D2,A2:B$($rows),2,FALSE)"
Close-ExcelPackage $xl -Show
Examples/ExcelDataValidation/MutipleValidations.ps1
#region Setup
<#
This examples demos three types of validation:
* Creating a list using a PowerShell array
* Creating a list data from another Excel Worksheet
* Creating a rule for numbers to be between 0 an 10000
Run the script then try"
* Add random data in Column B
* Then choose from the drop down list
* Add random data in Column C
* Then choose from the drop down list
* Add .01 in column F
#>
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$data = ConvertFrom-Csv -InputObject @"
ID,Region,Product,Quantity,Price
12001,North,Nails,37,3.99
12002,South,Hammer,5,12.10
12003,East,Saw,12,15.37
12010,West,Drill,20,8
12011,North,Crowbar,7,23.48
"@
# Export the raw data
$excelPackage = $Data |
Export-Excel -WorksheetName "Sales" -Path $xlSourcefile -PassThru
# Creates a sheet with data that will be used in a validation rule
$excelPackage = @('Chisel', 'Crowbar', 'Drill', 'Hammer', 'Nails', 'Saw', 'Screwdriver', 'Wrench') |
Export-excel -ExcelPackage $excelPackage -WorksheetName Values -PassThru
#endregion
#region Creating a list using a PowerShell array
$ValidationParams = @{
Worksheet = $excelPackage.sales
ShowErrorMessage = $true
ErrorStyle = 'stop'
ErrorTitle = 'Invalid Data'
}
$MoreValidationParams = @{
Range = 'B2:B1001'
ValidationType = 'List'
ValueSet = @('North', 'South', 'East', 'West')
ErrorBody = "You must select an item from the list."
}
Add-ExcelDataValidationRule @ValidationParams @MoreValidationParams
#endregion
#region Creating a list data from another Excel Worksheet
$MoreValidationParams = @{
Range = 'C2:C1001'
ValidationType = 'List'
Formula = 'values!$a$1:$a$10'
ErrorBody = "You must select an item from the list.`r`nYou can add to the list on the values page" #Bucket
}
Add-ExcelDataValidationRule @ValidationParams @MoreValidationParams
#endregion
#region Creating a rule for numbers to be between 0 an 10000
$MoreValidationParams = @{
Range = 'F2:F1001'
ValidationType = 'Integer'
Operator = 'between'
Value = 0
Value2 = 10000
ErrorBody = 'Quantity must be a whole number between 0 and 10000'
}
Add-ExcelDataValidationRule @ValidationParams @MoreValidationParams
#endregion
#region Close Package
Close-ExcelPackage -ExcelPackage $excelPackage -Show
#endregion
Examples/ExcelToSQLInsert/DemoSQLInsert.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
ConvertFrom-ExcelToSQLInsert -TableName "Movies" -Path ".\Movies.xlsx" -ConvertEmptyStringsToNull
''
'# UseMSSQLSyntax'
ConvertFrom-ExcelToSQLInsert -UseMSSQLSyntax -TableName "Movies" -Path ".\Movies.xlsx" -ConvertEmptyStringsToNull
Examples/Experimental/Export-MultipleExcelSheets.ps1
function Export-MultipleExcelSheets {
<#
.Synopsis
Takes a hash table of scriptblocks and exports each as a sheet in an Excel file
.Example
$p = Get-Process
$InfoMap = @{
PM = { $p | Select-Object company, pm }
Handles = { $p | Select-Object company, handles }
Services = { Get-Service }
}
Export-MultipleExcelSheets -Path $xlfile -InfoMap $InfoMap -Show -AutoSize
#>
param(
[Parameter(Mandatory = $true)]
$Path,
[Parameter(Mandatory = $true)]
[hashtable]$InfoMap,
[string]$Password,
[Switch]$Show,
[Switch]$AutoSize
)
$parameters = @{ } + $PSBoundParameters
$parameters.Remove("InfoMap")
$parameters.Remove("Show")
$parameters.Path = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path)
foreach ($entry in $InfoMap.GetEnumerator()) {
if ($entry.Value -is [scriptblock]) {
Write-Progress -Activity "Exporting" -Status "$($entry.Key)"
$parameters.WorkSheetname = $entry.Key
& $entry.Value | Export-Excel @parameters
}
else {
Write-Warning "$($entry.Key) not exported, needs to be a scriptblock"
}
}
if ($Show) { Invoke-Item $Path }
}
Examples/Experimental/tryExportMultipleExcelSheets.ps1
. "$PSScriptRoot\Export-MultipleExcelSheets.ps1"
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$p = Get-Process
$InfoMap = @{
PM = { $p | Select-Object company, pm }
Handles = { $p | Select-Object company, handles }
Services = { Get-Service }
Files = { Get-ChildItem -File }
Albums = { ConvertFrom-Csv (Invoke-RestMethod https://raw.githubusercontent.com/dfinke/powershell-for-developers/master/chapter05/BeaverMusic/BeaverMusic.UI.Shell/albums.csv) }
WillNotGetExported = "Hello World"
}
Export-MultipleExcelSheets -Path $xlSourcefile -InfoMap $InfoMap -Show -AutoSize
Examples/Extra/Get-ModuleStats.ps1
<#
.Synopsis
Chart download stats for modules/scripts published on the PowerShell Gallery
.Example
.\Get-ModuleStats.ps1 ImportExcel
#>
param(
$moduleName = "ImportExcel",
[ValidateSet('Column','Bar','Line','Pie')]
$chartType="Line"
)
$download = Get-HtmlTable "https://www.powershellgallery.com/packages/$moduleName" -FirstDataRow 1 |
Select-Object @{n="Version";e={$v = $Null ; if ($_.version -is [valuetype]) {[string][version]($_.version.tostring("0.0")) }
elseif ($_.version -is [string] -and [version]::TryParse($_.version.trim(),[ref]$v)) {$v}
else {$_.Version.trim() -replace "\s+"," " } }},
Downloads, @{n="LastUpdated";e={[datetime]$_.last_updated}} |
Sort-Object lastupdated -Descending
& "$($chartType)Chart" $download "Download stats for $moduleName" -nolegend:($chartype -ne 'pie')
Examples/Fibonacci/ShowFibonacci.ps1
param ($fibonacciDigits=10)
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$(
New-PSItem 0
New-PSItem 1
(
2..$fibonacciDigits |
ForEach-Object {
New-PSItem ('=a{0}+a{1}' -f ($_+1),$_)
}
)
) | Export-Excel $xlSourcefile -Show
Examples/FormatCellStyles/ApplyFormatInScriptBlock.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
Get-Process |
Select-Object Company,Handles,PM, NPM|
Export-Excel $xlSourcefile -Show -AutoSize -CellStyleSB {
param(
$workSheet,
$totalRows,
$lastColumn
)
Set-CellStyle $workSheet 1 $LastColumn Solid Cyan
foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 0})) {
Set-CellStyle $workSheet $row $LastColumn Solid Gray
}
foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 1})) {
Set-CellStyle $workSheet $row $LastColumn Solid LightGray
}
}
Examples/FormatCellStyles/ApplyStyle.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$data = ConvertFrom-Csv @'
Item,Quantity,Price,Total Cost
Footballs,9,21.95,197.55
Cones,36,7.99,287.64
Shin Guards,14,10.95,153.3
Turf Shoes,22,79.95,1758.9
Baseballs,68,7.99,543.32
Baseball Gloves,31,65.00,2015.00
Baseball Bats,38,159.00,6042.00
'@
$f = "$env:TEMP\styles.xlsx"
Remove-Item $f -ErrorAction SilentlyContinue
$pkg = $data | Export-Excel -Path $f -AutoSize -PassThru
$ws = $pkg.Workbook.Worksheets["Sheet1"]
Set-ExcelRange -Worksheet $ws -Range "A2:C6" -BackgroundColor PeachPuff -FontColor Purple -FontSize 12 -Width 12
Set-ExcelRange -Worksheet $ws -Range "D2:D6" -BackgroundColor WhiteSmoke -FontColor Orange -Bold -FontSize 12 -Width 12
Set-ExcelRange -Worksheet $ws -Range "A1:D1" -BackgroundColor BlueViolet -FontColor Wheat -FontSize 12 -Width 12
Set-ExcelRange -Worksheet $ws -Range "A:A" -Width 15
Close-ExcelPackage -ExcelPackage $pkg -Show
Examples/FormatCellStyles/PassInScriptBlock.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$RandomStyle = {
param(
$workSheet,
$totalRows,
$lastColumn
)
2..$totalRows | ForEach-Object{
Set-CellStyle $workSheet $_ $LastColumn Solid (Get-Random @("LightGreen", "Gray", "Red"))
}
}
Get-Process |
Select-Object Company,Handles,PM, NPM|
Export-Excel $xlSourcefile -Show -AutoSize -CellStyleSB $RandomStyle
Examples/FormatResults/GetAsMarkdownTable.ps1
param(
[Alias('FullName')]
[String[]]$Path
)
if ($PSVersionTable.PSVersion.Major -gt 5 -and -not (Get-Command Format-Markdown -ErrorAction SilentlyContinue)) {
throw "This requires EZOut. Install-Module EZOut -AllowClobber -Scope CurrentUser"
}
Import-Excel $Path | Format-Markdown
Examples/FormatResults/GetAsYaml.ps1
param(
[Alias('FullName')]
[String[]]$Path
)
if ($PSVersionTable.PSVersion.Major -gt 5 -and -not (Get-Command Format-YAML -ErrorAction SilentlyContinue)) {
throw "This requires EZOut. Install-Module EZOut -AllowClobber -Scope CurrentUser"
}
Import-Excel $Path | Format-YAML
Examples/FormatResults/Sample.csv
"OrderId","Category","Sales","Quantity","Discount"
"1","Cosmetics","744.01","7","0.7"
"2","Grocery","349.13","25","0.3"
"3","Apparels","535.11","88","0.2"
"4","Electronics","524.69","60","0.1"
"5","Electronics","439.1","41","0"
"6","Apparels","56.84","54","0.8"
"7","Electronics","326.66","97","0.7"
"8","Cosmetics","17.25","74","0.6"
"9","Grocery","199.96","39","0.4"
"10","Grocery","731.77","20","0.3"
Examples/Freeze/FreezePane.ps1
# Freeze the columns/rows to left and above the cell
$data = ConvertFrom-Csv @"
Region,State,Units,Price,Name,NA,EU,JP,Other
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
West,Texas,927,923.71,Wii Sports,41.49,29.02,3.77,8.46
"@
$xlfilename = "test.xlsx"
Remove-Item $xlfilename -ErrorAction SilentlyContinue
<#
Freezes the top two rows and the two leftmost column
#>
$data | Export-Excel $xlfilename -Show -Title 'Sales Data' -FreezePane 3, 3
Examples/GenerateData/GenDataForCustomReport.ps1
if(!(Get-Command ig -ErrorAction SilentlyContinue)) {
"Use ``Install-Module NameIT`` to get the needed module from the gallery to support running this script"
return
}
$sign=@{sign=@( "+", "-" )}
$location=@{location=@("Atlanta", "Newark", "Washington", "Chicago", "Philadelphia", "Houston", "Phoneix")}
$(1..6 | Foreach-Object {
$from=$to=""
while($from -eq $to) {
$from=ig "[location]" -CustomData $location
$to=ig "[location]" -CustomData $location
}
[double]$a=ig "########"
[double]$b=ig ".####"
[double]$c=ig "#######"
[double]$d=ig "[sign].##" -CustomData $sign
[double]$e=ig "###"
[double]$f=ig "[sign]##" -CustomData $sign
#"{0},{1},{2},{3},{4},{5},{6},{7}" -f $from, $to, $a, $b, $c, $d, $e, $f
[PSCustomObject][Ordered]@{
From=$from
To=$to
RDollars=$a
RPercent=$b
MDollars=$c
MPercent=$d
Revenue=$e
Margin=$f
}
} | ConvertTo-Csv -NoTypeInformation) -replace '"','' # | Export-Excel
Examples/Grouping/First10Races.csv
Race,Date,FinishPosition,Driver,GridPosition,Team,Points
Australian,25/03/2018,1,Sebastian Vettel,3,Ferrari,25
Australian,25/03/2018,2,Lewis Hamilton,1,Mercedes,18
Australian,25/03/2018,3,Kimi Räikkönen,2,Ferrari,15
Australian,25/03/2018,4,Daniel Ricciardo,8,Red Bull Racing-TAG Heuer,12
Australian,25/03/2018,5,Fernando Alonso,10,McLaren-Renault,10
Australian,25/03/2018,6,Max Verstappen,4,Red Bull Racing-TAG Heuer,8
Australian,25/03/2018,7,Nico Hülkenberg,7,Renault,6
Australian,25/03/2018,8,Valtteri Bottas,15,Mercedes,4
Australian,25/03/2018,9,Stoffel Vandoorne,11,McLaren-Renault,2
Australian,25/03/2018,10,Carlos Sainz,9,Renault,1
Bahrain,08/04/2018,1,Sebastian Vettel,1,Ferrari,25
Bahrain,08/04/2018,2,Valtteri Bottas,3,Mercedes,18
Bahrain,08/04/2018,3,Lewis Hamilton,9,Mercedes,15
Bahrain,08/04/2018,4,Pierre Gasly,5,STR-Honda,12
Bahrain,08/04/2018,5,Kevin Magnussen,6,Haas-Ferrari,10
Bahrain,08/04/2018,6,Nico Hülkenberg,7,Renault,8
Bahrain,08/04/2018,7,Fernando Alonso,13,McLaren-Renault,6
Bahrain,08/04/2018,8,Stoffel Vandoorne,14,McLaren-Renault,4
Bahrain,08/04/2018,9,Marcus Ericsson,17,Sauber-Ferrari,2
Bahrain,08/04/2018,10,Esteban Ocon,8,Force India-Mercedes,1
Chinese,15/04/2018,1,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,25
Chinese,15/04/2018,2,Valtteri Bottas,3,Mercedes,18
Chinese,15/04/2018,3,Kimi Räikkönen,2,Ferrari,15
Chinese,15/04/2018,4,Lewis Hamilton,4,Mercedes,12
Chinese,15/04/2018,5,Max Verstappen,5,Red Bull Racing-TAG Heuer,10
Chinese,15/04/2018,6,Nico Hülkenberg,7,Renault,8
Chinese,15/04/2018,7,Fernando Alonso,13,McLaren-Renault,6
Chinese,15/04/2018,8,Sebastian Vettel,1,Ferrari,4
Chinese,15/04/2018,9,Carlos Sainz,9,Renault,2
Chinese,15/04/2018,10,Kevin Magnussen,11,Haas-Ferrari,1
Azerbaijan,29/04/2018,1,Lewis Hamilton,2,Mercedes,25
Azerbaijan,29/04/2018,2,Kimi Räikkönen,6,Ferrari,18
Azerbaijan,29/04/2018,3,Sergio Pérez,8,Force India-Mercedes,15
Azerbaijan,29/04/2018,4,Sebastian Vettel,1,Ferrari,12
Azerbaijan,29/04/2018,5,Carlos Sainz,9,Renault,10
Azerbaijan,29/04/2018,6,Charles Leclerc,13,Sauber-Ferrari,8
Azerbaijan,29/04/2018,7,Fernando Alonso,12,McLaren-Renault,6
Azerbaijan,29/04/2018,8,Lance Stroll,10,Williams-Mercedes,4
Azerbaijan,29/04/2018,9,Stoffel Vandoorne,16,McLaren-Renault,2
Azerbaijan,29/04/2018,10,Brendon Hartley,19,STR-Honda,1
Spanish,13/05/2018,1,Lewis Hamilton,1,Mercedes,25
Spanish,13/05/2018,2,Valtteri Bottas,2,Mercedes,18
Spanish,13/05/2018,3,Max Verstappen,5,Red Bull Racing-TAG Heuer,15
Spanish,13/05/2018,4,Sebastian Vettel,3,Ferrari,12
Spanish,13/05/2018,5,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,10
Spanish,13/05/2018,6,Kevin Magnussen,7,Haas-Ferrari,8
Spanish,13/05/2018,7,Carlos Sainz,9,Renault,6
Spanish,13/05/2018,8,Fernando Alonso,8,McLaren-Renault,4
Spanish,13/05/2018,9,Sergio Pérez,15,Force India-Mercedes,2
Spanish,13/05/2018,10,Charles Leclerc,14,Sauber-Ferrari,1
Monaco,27/05/2018,1,Daniel Ricciardo,1,Red Bull Racing-TAG Heuer,25
Monaco,27/05/2018,2,Sebastian Vettel,2,Ferrari,18
Monaco,27/05/2018,3,Lewis Hamilton,3,Mercedes,15
Monaco,27/05/2018,4,Kimi Räikkönen,4,Ferrari,12
Monaco,27/05/2018,5,Valtteri Bottas,5,Mercedes,10
Monaco,27/05/2018,6,Esteban Ocon,6,Force India-Mercedes,8
Monaco,27/05/2018,7,Pierre Gasly,10,STR-Honda,6
Monaco,27/05/2018,8,Nico Hülkenberg,11,Renault,4
Monaco,27/05/2018,9,Max Verstappen,20,Red Bull Racing-TAG Heuer,2
Monaco,27/05/2018,10,Carlos Sainz,8,Renault,1
Canadian,10/06/2018,1,Sebastian Vettel,1,Ferrari,25
Canadian,10/06/2018,2,Valtteri Bottas,2,Mercedes,18
Canadian,10/06/2018,3,Max Verstappen,3,Red Bull Racing-TAG Heuer,15
Canadian,10/06/2018,4,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,12
Canadian,10/06/2018,5,Lewis Hamilton,4,Mercedes,10
Canadian,10/06/2018,6,Kimi Räikkönen,5,Ferrari,8
Canadian,10/06/2018,7,Nico Hülkenberg,7,Renault,6
Canadian,10/06/2018,8,Carlos Sainz,9,Renault,4
Canadian,10/06/2018,9,Esteban Ocon,8,Force India-Mercedes,2
Canadian,10/06/2018,10,Charles Leclerc,13,Sauber-Ferrari,1
French,24/06/2018,1,Lewis Hamilton,1,Mercedes,25
French,24/06/2018,2,Max Verstappen,4,Red Bull Racing-TAG Heuer,18
French,24/06/2018,3,Kimi Räikkönen,6,Ferrari,15
French,24/06/2018,4,Daniel Ricciardo,5,Red Bull Racing-TAG Heuer,12
French,24/06/2018,5,Sebastian Vettel,3,Ferrari,10
French,24/06/2018,6,Kevin Magnussen,9,Haas-Ferrari,8
French,24/06/2018,7,Valtteri Bottas,2,Mercedes,6
French,24/06/2018,8,Carlos Sainz,7,Renault,4
French,24/06/2018,9,Nico Hülkenberg,12,Renault,2
French,24/06/2018,10,Charles Leclerc,8,Sauber-Ferrari,1
Austrian,01/07/2018,1,Max Verstappen,4,Red Bull Racing-TAG Heuer,25
Austrian,01/07/2018,2,Kimi Räikkönen,3,Ferrari,18
Austrian,01/07/2018,3,Sebastian Vettel,6,Ferrari,15
Austrian,01/07/2018,4,Romain Grosjean,5,Haas-Ferrari,12
Austrian,01/07/2018,5,Kevin Magnussen,8,Haas-Ferrari,10
Austrian,01/07/2018,6,Esteban Ocon,11,Force India-Mercedes,8
Austrian,01/07/2018,7,Sergio Pérez,15,Force India-Mercedes,6
Austrian,01/07/2018,8,Fernando Alonso,20,McLaren-Renault,4
Austrian,01/07/2018,9,Charles Leclerc,17,Sauber-Ferrari,2
Austrian,01/07/2018,10,Marcus Ericsson,18,Sauber-Ferrari,1
British,08/07/2018,1,Sebastian Vettel,2,Ferrari,25
British,08/07/2018,2,Lewis Hamilton,1,Mercedes,18
British,08/07/2018,3,Kimi Räikkönen,3,Ferrari,15
British,08/07/2018,4,Valtteri Bottas,4,Mercedes,12
British,08/07/2018,5,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,10
British,08/07/2018,6,Nico Hülkenberg,11,Renault,8
British,08/07/2018,7,Esteban Ocon,10,Force India-Mercedes,6
British,08/07/2018,8,Fernando Alonso,13,McLaren-Renault,4
British,08/07/2018,9,Kevin Magnussen,7,Haas-Ferrari,2
British,08/07/2018,10,Sergio Pérez,12,Force India-Mercedes,1
Examples/Grouping/GroupDateColumn.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$PivotTableDefinition = New-PivotTableDefinition -Activate -PivotTableName Points `
-PivotRows Driver -PivotColumns Date -PivotData @{Points = "SUM"} -GroupDateColumn Date -GroupDatePart Years, Months
Import-Csv "$PSScriptRoot\First10Races.csv" |
Select-Object Race, @{n = "Date"; e = {[datetime]::ParseExact($_.date, "dd/MM/yyyy", (Get-Culture))}}, FinishPosition, Driver, GridPosition, Team, Points |
Export-Excel $xlSourcefile -Show -AutoSize -PivotTableDefinition $PivotTableDefinition
Examples/Grouping/GroupDateRow.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$PivotTableDefinition = New-PivotTableDefinition -Activate -PivotTableName Points `
-PivotRows Driver, Date -PivotData @{Points = "SUM"} -GroupDateRow Date -GroupDatePart Years, Months
Import-Csv "$PSScriptRoot\First10Races.csv" |
Select-Object Race, @{n = "Date"; e = {[datetime]::ParseExact($_.date, "dd/MM/yyyy", (Get-Culture))}}, FinishPosition, Driver, GridPosition, Team, Points |
Export-Excel $xlSourcefile -Show -AutoSize -PivotTableDefinition $PivotTableDefinition
Examples/Grouping/GroupNumericColumn.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$PivotTableDefinition = New-PivotTableDefinition -Activate -PivotTableName Places `
-PivotRows Driver -PivotColumns FinishPosition -PivotData @{Date = "Count"} -GroupNumericColumn FinishPosition -GroupNumericMin 1 -GroupNumericMax 25 -GroupNumericInterval 3
Import-Csv "$PSScriptRoot\First10Races.csv" |
Select-Object Race, @{n = "Date"; e = {[datetime]::ParseExact($_.date, "dd/MM/yyyy", (Get-Culture))}}, FinishPosition, Driver, GridPosition, Team, Points |
Export-Excel $xlSourcefile -Show -AutoSize -PivotTableDefinition $PivotTableDefinition
Examples/Grouping/GroupNumericRow.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$PivotTableDefinition = New-PivotTableDefinition -Activate -PivotTableName Places `
-PivotRows Driver, FinishPosition -PivotData @{Date = "Count"} -GroupNumericRow FinishPosition -GroupNumericMin 1 -GroupNumericMax 25 -GroupNumericInterval 3
Import-Csv "$PSScriptRoot\First10Races.csv" |
Select-Object Race, @{n = "Date"; e = {[datetime]::ParseExact($_.date, "dd/MM/yyyy", (Get-Culture))}}, FinishPosition, Driver, GridPosition, Team, Points |
Export-Excel $xlSourcefile -Show -AutoSize -PivotTableDefinition $PivotTableDefinition
Examples/Grouping/TimestampBucket.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$data = ConvertFrom-Csv @"
Timestamp,Tenant
10/29/2018 3:00:00.123,1
10/29/2018 3:00:10.456,1
10/29/2018 3:01:20.389,1
10/29/2018 3:00:30.222,1
10/29/2018 3:00:40.143,1
10/29/2018 3:00:50.809,1
10/29/2018 3:01:00.193,1
10/29/2018 3:01:10.555,1
10/29/2018 3:01:20.739,1
10/29/2018 3:01:30.912,1
10/29/2018 3:01:40.989,1
10/29/2018 3:01:50.545,1
10/29/2018 3:02:00.999,1
"@ | Select-Object @{n = 'Timestamp'; e = {Get-date $_.timestamp}}, tenant, @{n = 'Bucket'; e = { - (Get-date $_.timestamp).Second % 30}}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$pivotDefParams = @{
PivotTableName = 'Timestamp Buckets'
PivotRows = @('Timestamp', 'Tenant')
PivotData = @{'Bucket' = 'count'}
GroupDateRow = 'TimeStamp'
GroupDatePart = @('Hours', 'Minutes')
Activate = $true
}
$excelParams = @{
PivotTableDefinition = New-PivotTableDefinition @pivotDefParams
Path = $xlSourcefile
WorkSheetname = "Log Data"
AutoSize = $true
AutoFilter = $true
Show = $true
}
$data | Export-Excel @excelParams
Examples/HyperLinks/First10Races.csv
Race,Date,FinishPosition,Driver,GridPosition,Team,Points
Australian,25/03/2018,1,Sebastian Vettel,3,Ferrari,25
Australian,25/03/2018,2,Lewis Hamilton,1,Mercedes,18
Australian,25/03/2018,3,Kimi Räikkönen,2,Ferrari,15
Australian,25/03/2018,4,Daniel Ricciardo,8,Red Bull Racing-TAG Heuer,12
Australian,25/03/2018,5,Fernando Alonso,10,McLaren-Renault,10
Australian,25/03/2018,6,Max Verstappen,4,Red Bull Racing-TAG Heuer,8
Australian,25/03/2018,7,Nico Hülkenberg,7,Renault,6
Australian,25/03/2018,8,Valtteri Bottas,15,Mercedes,4
Australian,25/03/2018,9,Stoffel Vandoorne,11,McLaren-Renault,2
Australian,25/03/2018,10,Carlos Sainz,9,Renault,1
Bahrain,08/04/2018,1,Sebastian Vettel,1,Ferrari,25
Bahrain,08/04/2018,2,Valtteri Bottas,3,Mercedes,18
Bahrain,08/04/2018,3,Lewis Hamilton,9,Mercedes,15
Bahrain,08/04/2018,4,Pierre Gasly,5,STR-Honda,12
Bahrain,08/04/2018,5,Kevin Magnussen,6,Haas-Ferrari,10
Bahrain,08/04/2018,6,Nico Hülkenberg,7,Renault,8
Bahrain,08/04/2018,7,Fernando Alonso,13,McLaren-Renault,6
Bahrain,08/04/2018,8,Stoffel Vandoorne,14,McLaren-Renault,4
Bahrain,08/04/2018,9,Marcus Ericsson,17,Sauber-Ferrari,2
Bahrain,08/04/2018,10,Esteban Ocon,8,Force India-Mercedes,1
Chinese,15/04/2018,1,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,25
Chinese,15/04/2018,2,Valtteri Bottas,3,Mercedes,18
Chinese,15/04/2018,3,Kimi Räikkönen,2,Ferrari,15
Chinese,15/04/2018,4,Lewis Hamilton,4,Mercedes,12
Chinese,15/04/2018,5,Max Verstappen,5,Red Bull Racing-TAG Heuer,10
Chinese,15/04/2018,6,Nico Hülkenberg,7,Renault,8
Chinese,15/04/2018,7,Fernando Alonso,13,McLaren-Renault,6
Chinese,15/04/2018,8,Sebastian Vettel,1,Ferrari,4
Chinese,15/04/2018,9,Carlos Sainz,9,Renault,2
Chinese,15/04/2018,10,Kevin Magnussen,11,Haas-Ferrari,1
Azerbaijan,29/04/2018,1,Lewis Hamilton,2,Mercedes,25
Azerbaijan,29/04/2018,2,Kimi Räikkönen,6,Ferrari,18
Azerbaijan,29/04/2018,3,Sergio Pérez,8,Force India-Mercedes,15
Azerbaijan,29/04/2018,4,Sebastian Vettel,1,Ferrari,12
Azerbaijan,29/04/2018,5,Carlos Sainz,9,Renault,10
Azerbaijan,29/04/2018,6,Charles Leclerc,13,Sauber-Ferrari,8
Azerbaijan,29/04/2018,7,Fernando Alonso,12,McLaren-Renault,6
Azerbaijan,29/04/2018,8,Lance Stroll,10,Williams-Mercedes,4
Azerbaijan,29/04/2018,9,Stoffel Vandoorne,16,McLaren-Renault,2
Azerbaijan,29/04/2018,10,Brendon Hartley,19,STR-Honda,1
Spanish,13/05/2018,1,Lewis Hamilton,1,Mercedes,25
Spanish,13/05/2018,2,Valtteri Bottas,2,Mercedes,18
Spanish,13/05/2018,3,Max Verstappen,5,Red Bull Racing-TAG Heuer,15
Spanish,13/05/2018,4,Sebastian Vettel,3,Ferrari,12
Spanish,13/05/2018,5,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,10
Spanish,13/05/2018,6,Kevin Magnussen,7,Haas-Ferrari,8
Spanish,13/05/2018,7,Carlos Sainz,9,Renault,6
Spanish,13/05/2018,8,Fernando Alonso,8,McLaren-Renault,4
Spanish,13/05/2018,9,Sergio Pérez,15,Force India-Mercedes,2
Spanish,13/05/2018,10,Charles Leclerc,14,Sauber-Ferrari,1
Monaco,27/05/2018,1,Daniel Ricciardo,1,Red Bull Racing-TAG Heuer,25
Monaco,27/05/2018,2,Sebastian Vettel,2,Ferrari,18
Monaco,27/05/2018,3,Lewis Hamilton,3,Mercedes,15
Monaco,27/05/2018,4,Kimi Räikkönen,4,Ferrari,12
Monaco,27/05/2018,5,Valtteri Bottas,5,Mercedes,10
Monaco,27/05/2018,6,Esteban Ocon,6,Force India-Mercedes,8
Monaco,27/05/2018,7,Pierre Gasly,10,STR-Honda,6
Monaco,27/05/2018,8,Nico Hülkenberg,11,Renault,4
Monaco,27/05/2018,9,Max Verstappen,20,Red Bull Racing-TAG Heuer,2
Monaco,27/05/2018,10,Carlos Sainz,8,Renault,1
Canadian,10/06/2018,1,Sebastian Vettel,1,Ferrari,25
Canadian,10/06/2018,2,Valtteri Bottas,2,Mercedes,18
Canadian,10/06/2018,3,Max Verstappen,3,Red Bull Racing-TAG Heuer,15
Canadian,10/06/2018,4,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,12
Canadian,10/06/2018,5,Lewis Hamilton,4,Mercedes,10
Canadian,10/06/2018,6,Kimi Räikkönen,5,Ferrari,8
Canadian,10/06/2018,7,Nico Hülkenberg,7,Renault,6
Canadian,10/06/2018,8,Carlos Sainz,9,Renault,4
Canadian,10/06/2018,9,Esteban Ocon,8,Force India-Mercedes,2
Canadian,10/06/2018,10,Charles Leclerc,13,Sauber-Ferrari,1
French,24/06/2018,1,Lewis Hamilton,1,Mercedes,25
French,24/06/2018,2,Max Verstappen,4,Red Bull Racing-TAG Heuer,18
French,24/06/2018,3,Kimi Räikkönen,6,Ferrari,15
French,24/06/2018,4,Daniel Ricciardo,5,Red Bull Racing-TAG Heuer,12
French,24/06/2018,5,Sebastian Vettel,3,Ferrari,10
French,24/06/2018,6,Kevin Magnussen,9,Haas-Ferrari,8
French,24/06/2018,7,Valtteri Bottas,2,Mercedes,6
French,24/06/2018,8,Carlos Sainz,7,Renault,4
French,24/06/2018,9,Nico Hülkenberg,12,Renault,2
French,24/06/2018,10,Charles Leclerc,8,Sauber-Ferrari,1
Austrian,01/07/2018,1,Max Verstappen,4,Red Bull Racing-TAG Heuer,25
Austrian,01/07/2018,2,Kimi Räikkönen,3,Ferrari,18
Austrian,01/07/2018,3,Sebastian Vettel,6,Ferrari,15
Austrian,01/07/2018,4,Romain Grosjean,5,Haas-Ferrari,12
Austrian,01/07/2018,5,Kevin Magnussen,8,Haas-Ferrari,10
Austrian,01/07/2018,6,Esteban Ocon,11,Force India-Mercedes,8
Austrian,01/07/2018,7,Sergio Pérez,15,Force India-Mercedes,6
Austrian,01/07/2018,8,Fernando Alonso,20,McLaren-Renault,4
Austrian,01/07/2018,9,Charles Leclerc,17,Sauber-Ferrari,2
Austrian,01/07/2018,10,Marcus Ericsson,18,Sauber-Ferrari,1
British,08/07/2018,1,Sebastian Vettel,2,Ferrari,25
British,08/07/2018,2,Lewis Hamilton,1,Mercedes,18
British,08/07/2018,3,Kimi Räikkönen,3,Ferrari,15
British,08/07/2018,4,Valtteri Bottas,4,Mercedes,12
British,08/07/2018,5,Daniel Ricciardo,6,Red Bull Racing-TAG Heuer,10
British,08/07/2018,6,Nico Hülkenberg,11,Renault,8
British,08/07/2018,7,Esteban Ocon,10,Force India-Mercedes,6
British,08/07/2018,8,Fernando Alonso,13,McLaren-Renault,4
British,08/07/2018,9,Kevin Magnussen,7,Haas-Ferrari,2
British,08/07/2018,10,Sergio Pérez,12,Force India-Mercedes,1
Examples/HyperLinks/Hyperlinks.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
@"
site,link
google,http://www.google.com
stackoverflow,http://stackoverflow.com
microsoft,http://microsoft.com
"@ | ConvertFrom-Csv | Export-Excel
Examples/HyperLinks/Races.ps1
#First 10 races is a CSV file containing the top 10 finishers for the first 10 Formula one races of 2018. Read this file and group the results by race
#We will create links to each race in the first 10 rows of the spreadSheet
#The next row will be column labels
#After that will come a block for each race.
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Read the data, and decide how much space to leave for the hyperlinks
$scriptPath = Split-Path -Path $MyInvocation.MyCommand.path -Parent
$dataPath = Join-Path -Path $scriptPath -ChildPath "First10Races.csv"
$results = Import-Csv -Path $dataPath | Group-Object -Property RACE
$topRow = $lastDataRow = 1 + $results.Count
#Export the first row of the first group (race) with headers.
$path = "$env:TEMP\Results.xlsx"
Remove-Item -Path $path -ErrorAction SilentlyContinue
$excel = $results[0].Group[0] | Export-Excel -Path $path -StartRow $TopRow -BoldTopRow -PassThru
#export each group (race) below the last one, without headers, and create a range for each using the group (Race) name
foreach ($r in $results) {
$excel = $R.Group | Export-Excel -ExcelPackage $excel -NoHeader -StartRow ($lastDataRow +1) -RangeName $R.Name -PassThru -AutoSize
$lastDataRow += $R.Group.Count
}
#Create a hyperlink for each property with display text of "RaceNameGP" which links to the range created when the rows were exported a
$results | ForEach-Object {(New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList "Sheet1!$($_.Name)" , "$($_.name) GP")} |
Export-Excel -ExcelPackage $excel -AutoSize -Show
Examples/Import-Excel/ImportMultipleSheetsAsArray.ps1
Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 -Force
$xlfile = "$PSScriptRoot\yearlySales.xlsx"
$result = Import-Excel -Path $xlfile -WorksheetName * -Raw
$result | Measure-Object
Examples/Import-Excel/ImportMultipleSheetsAsHashtable.ps1
Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 -Force
$xlfile = "$PSScriptRoot\yearlySales.xlsx"
$result = Import-Excel -Path $xlfile -WorksheetName *
foreach ($sheet in $result.Values) {
$sheet
}
Examples/ImportByColumns/import-by-columns.ps1
function Import-ByColumns {
<#
.synopsis
Works like Import-Excel but with data in columns instead of the conventional rows.
.Description.
Import-excel will read the sample file in this folder like this
> Import-excel FruitCity.xlsx | ft *
GroupAs Apple Orange Banana
------- ----- ------ ------
London 1 4 9
Paris 2 4 10
NewYork 6 5 11
Munich 7 8 12
Import-ByColumns transposes it
> Import-Bycolumns FruitCity.xlsx | ft *
GroupAs London Paris NewYork Munich
------- ------ ----- ------- ------
Apple 1 2 6 7
Orange 4 4 5 8
Banana 9 10 11 12
.Example
C:\> Import-Bycolumns -path .\VM_Build_Example.xlsx -StartRow 7 -EndRow 21 -EndColumn 7 -HeaderName Desc,size,type,
cpu,ram,NetAcc,OS,OSDiskSize,DataDiskSize,LogDiskSize,TempDbDiskSize,BackupDiskSize,ImageDiskDize,AzureBackup,AzureReplication | ft -a *
This reads a spreadsheet which has a block from row 7 to 21 containing 14 properties of virtual machines.
The properties names are in column A and the 6 VMS are in columns B-G
Because the property names are written for easy reading by the person completing the spreadsheet, they are replaced with new names.
All the parameters work as they would for Import-Excel
#>
[Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")]
param(
[Alias('FullName')]
[Parameter(ParameterSetName = "PathA", Mandatory, ValueFromPipelineByPropertyName, ValueFromPipeline, Position = 0 )]
[Parameter(ParameterSetName = "PathB", Mandatory, ValueFromPipelineByPropertyName, ValueFromPipeline, Position = 0 )]
[Parameter(ParameterSetName = "PathC", Mandatory, ValueFromPipelineByPropertyName, ValueFromPipeline, Position = 0 )]
[String]$Path,
[Parameter(ParameterSetName = "PackageA", Mandatory)]
[Parameter(ParameterSetName = "PackageB", Mandatory)]
[Parameter(ParameterSetName = "PackageC", Mandatory)]
[OfficeOpenXml.ExcelPackage]$ExcelPackage,
[Alias('Sheet')]
[Parameter(Position = 1)]
[ValidateNotNullOrEmpty()]
[String]$WorksheetName,
[Parameter(ParameterSetName = 'PathB' , Mandatory)]
[Parameter(ParameterSetName = 'PackageB', Mandatory)]
[String[]]$HeaderName ,
[Parameter(ParameterSetName = 'PathC' , Mandatory)]
[Parameter(ParameterSetName = 'PackageC', Mandatory)]
[Switch]$NoHeader,
[Alias('TopRow')]
[ValidateRange(1, 9999)]
[Int]$StartRow = 1,
[Alias('StopRow', 'BottomRow')]
[Int]$EndRow ,
[Alias('LeftColumn','LabelColumn')]
[Int]$StartColumn = 1,
[Int]$EndColumn,
[switch]$DataOnly,
[switch]$AsHash,
[ValidateNotNullOrEmpty()]
[String]$Password
)
function Get-PropertyNames {
<#
.SYNOPSIS
Create objects containing the row number and the row name for each of the different header types.
#>
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseSingularNouns', '', Justification = "Name would be incorrect, and command is not exported")]
param(
[Parameter(Mandatory)]
[Int[]]$Rows,
[Parameter(Mandatory)]
[Int]$StartColumn
)
if ($HeaderName) {
$i = 0
foreach ($h in $HeaderName) {
$h | Select-Object @{n='Row'; e={$rows[$i]}}, @{n='Value'; e={$h} }
$i++
}
}
elseif ($NoHeader) {
$i = 0
foreach ($r in $rows) {
$i++
$r | Select-Object @{n='Row'; e={$_}}, @{n='Value'; e={"P$i"} }
}
}
else {
foreach ($r in $Rows) {
#allow "False" or "0" to be headings
$Worksheet.Cells[$r, $StartColumn] | Where-Object {-not [string]::IsNullOrEmpty($_.Value) } | Select-Object @{n='Row'; e={$r} }, Value
}
}
}
#region open file if necessary, find worksheet and ensure we have start/end row/columns
if ($Path -and -not $ExcelPackage -and $Password) {
$ExcelPackage = Open-ExcelPackage -Path $Path -Password $Password
}
elseif ($Path -and -not $ExcelPackage ) {
$ExcelPackage = Open-ExcelPackage -Path $Path
}
if (-not $ExcelPackage) {
throw 'Could not get an Excel workbook to work on' ; return
}
if (-not $WorksheetName) { $Worksheet = $ExcelPackage.Workbook.Worksheets[1] }
elseif (-not ($Worksheet = $ExcelPackage.Workbook.Worksheets[$WorkSheetName])) {
throw "Worksheet '$WorksheetName' not found, the workbook only contains the worksheets '$($ExcelPackage.Workbook.Worksheets)'. If you only wish to select the first worksheet, please remove the '-WorksheetName' parameter." ; return
}
if (-not $EndRow ) { $EndRow = $Worksheet.Dimension.End.Row }
if (-not $EndColumn) { $EndColumn = $Worksheet.Dimension.End.Column }
#endregion
$Rows = $Startrow .. $EndRow ;
$Columns = (1 + $StartColumn)..$EndColumn
if ((-not $rows) -or (-not ($PropertyNames = Get-PropertyNames -Rows $Rows -StartColumn $StartColumn))) {
throw "No headers found in left coulmn '$Startcolumn'. "; return
}
if (-not $Columns) {
Write-Warning "Worksheet '$WorksheetName' in workbook contains no data in the rows after left column '$StartColumn'"
}
else {
foreach ($c in $Columns) {
$NewColumn = [Ordered]@{ }
foreach ($p in $PropertyNames) {
$NewColumn[$p.Value] = $Worksheet.Cells[$p.row,$c].text
}
if ($AsHash) {$NewColumn}
elseif (($NewColumn.Values -ne "") -or -not $dataonly) {[PSCustomObject]$NewColumn}
}
}
}
Examples/ImportColumns/ImportColumns.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
# Create example file
$xlFile = "$PSScriptRoot\ImportColumns.xlsx"
Get-Process | Export-Excel -Path $xlFile
# -ImportColumns will also arrange columns
Import-Excel -Path $xlFile -ImportColumns @(1,3,2) -NoHeader -StartRow 1
# Get only pm, npm, cpu, id, processname
Import-Excel -Path $xlFile -ImportColumns @(6,7,12,25,46) | Format-Table -AutoSize
Examples/ImportHtml/DemoGraphics.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Import-Html "http://en.wikipedia.org/wiki/Demographics_of_India" 4
Examples/ImportHtml/PeriodicElements.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Import-Html "http://www.science.co.il/PTelements.asp" 1
Examples/ImportHtml/StarTrek.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Import-Html "https://en.wikipedia.org/wiki/List_of_Star_Trek:_The_Original_Series_episodes" 2
Examples/Index - Music.ps1
#requires -modules "Get-IndexedItem"
[CmdletBinding()]
Param()
Remove-Item ~\documents\music.xlsx -ErrorAction SilentlyContinue
[System.Diagnostics.Stopwatch]$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
#Query system index for .MP3 files in C:\Users, where album artist is non-blank. Leave sorted table with columns of interest in $Music.
Get-IndexedItem "itemtype='.mp3'","AlbumArtist like '%'" -Recurse C:\Users -OutputVariable Music `
-OrderBy AlbumArtist, AlbumTitle, TrackNumber, Title -NoFiles `
-Property AlbumArtist, AlbumTitle, TrackNumber, Title, Duration, Size, SampleRate
Write-Verbose -Message ("Fetched " + $music.Rows.Count + " rows from index: " + $stopwatch.Elapsed.TotalSeconds)
#Send Table in $Music to Excel, format as a table, point $ws to the Worksheet
$excel = Send-SQLDataToExcel -Path ~\documents\music.xlsx -DataTable $music -WorkSheetname Music -TableName Music -Passthru
Write-Verbose -Message ("Inserted into Excel: " + $stopwatch.Elapsed.TotalSeconds)
$ws = $excel.Music
#Strip "SYSTEM.", "SYSTEM.AUDIO", "SYSTEM.MEDIA", "SYSTEM.MUSIC" from the column headings
#Convert Duration (column 5) from 100ns ticks to days and format as minutes, seconds, decimal
#Format filesize and sample rate nicely
#Autofit the columns.
Set-ExcelRow -Worksheet $ws -Row 1 -Value {($worksheet.cells[$row,$column].value -replace '^SYSTEM\.','') -replace '^MEDIA\.|^AUDIO\.|^MUSIC\.','' }
Set-ExcelColumn -Worksheet $ws -Column 5 -NumberFormat 'mm:ss.0' -StartRow 2 -Value {$worksheet.cells[$row,$column].value / 864000000000 }
Write-Verbose -Message ("Cells Reset: " + $stopwatch.Elapsed.TotalSeconds)
Set-ExcelColumn -Worksheet $ws -Column 6 -NumberFormat '#.#,,"MB"'
Set-ExcelColumn -Worksheet $ws -Column 7 -NumberFormat '0.0,"KHz"'
$ws.Cells[$ws.Dimension].AutoFitColumns()
#Make a Pivot table for sum of space and count of tracks by artist. Sort by artist, apply formatting to space, give it nice titles.
$pt = Add-PivotTable -PassThru -PivotTableName SpaceUsedByMusic -ExcelPackage $excel -SourceWorkSheet $ws `
-PivotRows ALBUMARTIST -PivotData ([ordered]@{"Size"="Sum"; "Duration"="Count"}) -PivotDataToColumn
$pt.RowFields[0].Sort = [OfficeOpenXml.Table.PivotTable.eSortType]::Ascending
$pt.DataFields[0].Format = '#.0,,"MB"'
$pt.DataFields[0].Name = 'Space Used'
$pt.DataFields[1].Name = 'Tracks'
#Save the file, and load it into Excel
$stopwatch.Stop()
Write-Verbose -Message ("Pivot Done: " + $stopwatch.Elapsed.TotalSeconds)
Close-ExcelPackage -show $excel
Examples/InteractWithOtherModules/Pester/Analyze_that.ps1
param(
$PesterTestsPath = "$PSScriptRoot\..\..\..\__tests__\"
)
$xlfile = "$env:Temp\testResults.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$xlparams = @{
Path = $xlfile
InputObject = (Invoke-Pester -Script $PesterTestsPath -PassThru).TestResult | Sort-Object describe
WorksheetName = 'FullResults'
IncludePivotTable = $true
PivotRows = 'Describe'
PivotColumns = 'Passed'
PivotData = @{'Passed' = 'Count' }
IncludePivotChart = $true
ChartType = 'BarClustered'
AutoSize = $true
AutoFilter = $true
Activate = $true
}
Export-Excel -Show @xlparams
Examples/InteractWithOtherModules/ScriptAnalyzer/Analyze_this.ps1
<#
.Synopsis
Runs PsScriptAnalyzer against one or more folders and pivots the results to form a report.
.Example
Analyze_this.ps1
Invokes script analyzer on the current directory; creates a file in $env:temp and opens it in Excel
.Example
Analyze_this.ps1 -xlfile ..\mymodule.xlsx -quiet
Invokes script analyzer on the current directory; creates a file in the parent directory but does not open it
.Example
"." , (dir 'C:\Program Files\WindowsPowerShell\Modules\ImportExcel\') | .\examples\ScriptAnalyzer\Analyze_this.ps1
run from a developemnt directory for importExcel it will produce a report for that directory compared against installed versions
this creates the file in the default location and opens it
#>
[CmdletBinding()]
param (
[parameter(ValueFromPipeline = $true)]
$Path = $PWD,
$xlfile = "$env:TEMP\ScriptAnalyzer.xlsx",
$ChartType = 'BarClustered' ,
$PivotColumns = 'Location',
[switch]$Quiet
)
begin {
Remove-Item -Path $xlfile -ErrorAction SilentlyContinue
$xlparams = @{
Path = $xlfile
WorksheetName = 'FullResults'
AutoSize = $true
AutoFilter = $true
Activate = $true
Show = (-not $Quiet)
}
$pivotParams = @{
PivotTableName = 'BreakDown'
PivotData = @{RuleName = 'Count' }
PivotRows = 'Severity', 'RuleName'
PivotColumns = 'Location'
PivotTotals = 'Rows'
}
$dirsToProcess = @()
}
process {
if ($path.fullName) {$dirsToProcess += $path.fullName}
elseif ($path.path) {$dirsToProcess += $path.Path}
else {$dirsToProcess += $path}
}
end {
$pivotParams['-PivotChartDefinition'] = New-ExcelChartDefinition -ChartType $chartType -Column (1 + $dirsToProcess.Count) -Title "Script analysis" -LegendBold
$xlparams['PivotTableDefinition'] = New-PivotTableDefinition @pivotParams
$dirsToProcess | ForEach-Object {
$dirName = (Resolve-Path -Path $_) -replace "^.*\\(.*?)\\(.*?)$", '$1-$2'
Write-Progress -Activity "Running Script Analyzer" -CurrentOperation $dirName
Invoke-ScriptAnalyzer -Path $_ -ErrorAction SilentlyContinue |
Add-Member -MemberType NoteProperty -Name Location -Value $dirName -PassThru
} | Export-Excel @xlparams
Write-Progress -Activity "Running Script Analyzer" -Completed
}
Examples/InvokeExcelQuery/Examples.ps1
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return }
$queries =
'select * from [sheet1$A:A]',
'select * from [sheet1$]',
'select * from [sheet1$A2:E11]',
'select F2,F5 from [sheet1$A2:E11]',
'select * from [sheet1$A2:E11] where F2 = "Grocery"',
'select F2 as [Category], F5 as [Discount], F5*2 as [DiscountPlus] from [sheet1$A2:E11]'
foreach ($query in $queries) {
"query: $($query)"
Invoke-ExcelQuery .\testOleDb.xlsx $query | Format-Table
}
Examples/JoinWorksheet/EastSales.csv
"Region","Item","UnitSold","UnitCost"
"East","Banana","38","0.26"
"East","Kale","71","0.69"
"East","Apple","35","0.55"
"East","Potato","48","0.48"
"East","Kale","41","0.74"
Examples/JoinWorksheet/Join-Worksheet.sample.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Create simple pages for 3 stores with product ID, Product Name, quanity price and total
@"
ID,Product,Quantity,Price,Total
12001,Nails,37,3.99,147.63
12002,Hammer,5,12.10,60.5
12003,Saw,12,15.37,184.44
12010,Drill,20,8,160
12011,Crowbar,7,23.48,164.36
"@ | ConvertFrom-Csv| Export-Excel -Path $xlSourcefile -WorkSheetname Oxford
@"
ID,Product,Quantity,Price,Total
12001,Nails,53,3.99,211.47
12002,Hammer,6,12.10,72.60
12003,Saw,10,15.37,153.70
12010,Drill,10,8,80
12012,Pliers,2,14.99,29.98
"@ | ConvertFrom-Csv| Export-Excel -Path $xlSourcefile -WorkSheetname Abingdon
@"
ID,Product,Quantity,Price,Total
12001,Nails,20,3.99,79.80
12002,Hammer,2,12.10,24.20
12010,Drill,11,8,88
12012,Pliers,3,14.99,44.97
"@ | ConvertFrom-Csv| Export-Excel -Path $xlSourcefile -WorkSheetname Banbury
#define a pivot table with a chart to show a sales by store, broken down by product
$ptdef = New-PivotTableDefinition -PivotTableName "Summary" -PivotRows "Store" -PivotColumns "Product" -PivotData @{"Total"="SUM"} -IncludePivotChart -ChartTitle "Sales Breakdown" -ChartType ColumnStacked -ChartColumn 10
#Join the 3 worksheets.
#Name the combined page "Total" and Name the column with the sheet names "store" (as the sheets 'Oxford','Abingdon' and 'Banbury' are the names of the stores
#Format the data as a table named "Summary", using the style "Light1", put the column headers in bold
#Put in a title and freeze to top of the sheet including title and colmun headings
#Add the Pivot table.
#Show the result
Join-Worksheet -Path $xlSourcefile -WorkSheetName "Total" -Clearsheet -FromLabel "Store" -TableName "Combined" -TableStyle Light1 -AutoSize -BoldTopRow -FreezePane 2,1 -Title "Store Sales Summary" -TitleBold -TitleSize 14 -PivotTableDefinition $ptdef -show
Examples/JoinWorksheet/Join-worksheet-blocks.sample.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Export disk volume, and Network adapter to their own sheets.
Get-CimInstance -ClassName Win32_LogicalDisk |
Select-Object -Property DeviceId,VolumeName, Size,Freespace |
Export-Excel -Path $xlSourcefile -WorkSheetname Volumes -NumberFormat "0,000"
Get-NetAdapter |
Select-Object -Property Name,InterfaceDescription,MacAddress,LinkSpeed |
Export-Excel -Path $xlSourcefile -WorkSheetname NetAdapters
#Create a summary page with a title of Summary, label the blocks with the name of the sheet they came from and hide the source sheets
Join-Worksheet -Path $xlSourcefile -HideSource -WorkSheetName Summary -NoHeader -LabelBlocks -AutoSize -Title "Summary" -TitleBold -TitleSize 22 -show
Examples/JoinWorksheet/JoinSalesData.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$params = @{
AutoSize = $true
AutoFilter = $true
AutoNameRange = $true
ExcelChartDefinition = New-ExcelChartDefinition -XRange Item -YRange UnitSold -Title 'Units Sold'
Path = $xlSourcefile
}
#Import 4 sets of sales data from 4 CSV files, using the parameters above.
Import-Csv $PSScriptRoot\NorthSales.csv | Export-Excel -WorkSheetname North @params
Import-Csv $PSScriptRoot\EastSales.csv | Export-Excel -WorkSheetname East @params
Import-Csv $PSScriptRoot\SouthSales.csv | Export-Excel -WorkSheetname South @params
Import-Csv $PSScriptRoot\WestSales.csv | Export-Excel -WorkSheetname West @params
#Join the 4 worksheets together on a sheet named Allsales, use the same parameters, except for AutoNameRange and ExcelChartDefinition.
$params.Remove("AutoNameRange")
$params.Remove("ExcelChartDefinition")
Join-Worksheet -WorkSheetName AllSales -Show @params
Examples/JoinWorksheet/NorthSales.csv
"Region","Item","UnitSold","UnitCost"
"North","Apple","40","0.68"
"North","Kale","55","0.35"
"North","Banana","33","0.31"
"North","Pear","29","0.74"
Examples/JoinWorksheet/SouthSales.csv
"Region","Item","UnitSold","UnitCost"
"South","Banana","54","0.46"
"South","Pear","39","0.44"
"South","Potato","33","0.46"
"South","Banana","49","0.31"
"South","Apple","38","0.59"
Examples/JoinWorksheet/WestSales.csv
"Region","Item","UnitSold","UnitCost"
"West","Banana","74","0.56"
"West","Apple","26","0.7"
"West","Banana","59","0.49"
"West","Potato","56","0.62"
"West","Banana","60","0.64"
"West","Pear","32","0.29"
"West","Apple","73","0.26"
"West","Banana","49","0.59"
"West","Pear","65","0.35"
"West","Apple","60","0.34"
"West","Kale","67","0.38"
Examples/JustCharts/CentralLimitTheorem.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
ColumnChart -Title "Central Limit Theorem" -NoLegend ($(
for ($i = 1; $i -le 500; $i++) {
$s = 0
for ($j = 1; $j -le 100; $j++) {
$s += Get-Random -Minimum 0 -Maximum 2
}
$s
}
) | Sort-Object | Group-Object | Select-Object Count, Name)
Examples/JustCharts/PieChartHandles.ps1
# Get only processes hat have a company name
# Sum up handles by company
# Show the Pie Chart
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
PieChart -Title "Total Handles by Company" `
(Invoke-Sum (Get-Process | Where-Object company) company handles)
Examples/JustCharts/PieChartPM.ps1
# Get only processes hat have a company name
# Sum up PM by company
# Show the Pie Chart
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
PieChart -Title "Total PM by Company" `
(Invoke-Sum (Get-Process|Where-Object company) company pm)
Examples/JustCharts/TargetData.csv
Cost,Date,Name
1.1,1/1/2015,John
2.1,1/2/2015,Tom
5.1,1/2/2015,Dick
11.1,1/2/2015,Harry
7.1,1/2/2015,Jane
22.1,1/2/2015,Mary
32.1,1/2/2015,Liz
Examples/JustCharts/TargetData.ps1
$PropertyNames = @("Cost", "Date", "Name")
New-PSItem 1.1 1/1/2015 John $PropertyNames
New-PSItem 2.1 1/2/2015 Tom
New-PSItem 5.1 1/2/2015 Dick
New-PSItem 11.1 1/2/2015 Harry
New-PSItem 7.1 1/2/2015 Jane
New-PSItem 22.1 1/2/2015 Mary
New-PSItem 32.1 1/2/2015 Liz
Examples/JustCharts/TryBarChart.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
BarChart (.\TargetData.ps1) "A BarChart"
Examples/JustCharts/TryColumnChart.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
ColumnChart (.\TargetData.ps1) "A ColumnChart"
Examples/JustCharts/TryPieChart.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
PieChart (.\TargetData.ps1) "A PieChart"
Examples/MergeWorkSheet/MergeCSV.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
$leftCsv = @"
MyProp1,MyProp2,Length
a,b,10
c,d,20
"@ | ConvertFrom-Csv
$rightCsv = @"
MyProp1,MyProp2,Length
a,b,10
c,d,21
"@ | ConvertFrom-Csv
Merge-Worksheet -OutputFile $xlSourcefile -ReferenceObject $leftCsv -DifferenceObject $rightCsv -Key Length -Show
Examples/MergeWorkSheet/Merge_2_Servers_Services.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Remove-Item -Path "$env:temp\server*.xlsx" , "$env:temp\Combined*.xlsx" -ErrorAction SilentlyContinue
#Get a subset of services into $s and export them
[System.Collections.ArrayList]$s = Get-service | Select-Object -first 25 -Property *
$s | Export-Excel -Path $env:temp\server1.xlsx
#$s is a zero based array, excel rows are 1 based and excel has a header row so Excel rows will be 2 + index in $s.
#Change a row. Add a row. Delete a row. And export the changed $s to a second file.
$s[2].DisplayName = "Changed from the orginal" #This will be row 4 in Excel - this should be highlighted as a change
$d = $s[-1] | Select-Object -Property *
$d.DisplayName = "Dummy Service"
$d.Name = "Dummy"
$s.Insert(3,$d) #This will be row 5 in Excel - this should be highlighted as a new item
$s.RemoveAt(5) #This will be row 7 in Excel - this should be highlighted as deleted item
$s | Export-Excel -Path $env:temp\server2.xlsx
#This use of Merge-worksheet Assumes a default worksheet name, (sheet1) We will check and output Name (the key), DisplayName and StartType and ignore other properties.
Merge-Worksheet -Referencefile "$env:temp\server1.xlsx" -Differencefile "$env:temp\Server2.xlsx" -OutputFile "$env:temp\combined1.xlsx" -Property name,displayname,startType -Key name -Show
Examples/MergeWorkSheet/Merge_3_Servers_Services.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Remove-Item -Path "$env:temp\server*.xlsx" , "$env:temp\Combined*.xlsx" -ErrorAction SilentlyContinue
#Get a subset of services into $s and export them
[System.Collections.ArrayList]$s = Get-service | Select-Object -first 25 -Property Name,DisplayName,StartType
$s | Export-Excel -Path $env:temp\server1.xlsx
#$s is a zero based array, excel rows are 1 based and excel has a header row so Excel rows will be 2 + index in $s.
#Change a row. Add a row. Delete a row. And export the changed $s to a second file.
$row4Displayname = $s[2].DisplayName
$s[2].DisplayName = "Changed from the orginal" #This will be excel row 4 and Server 2 will show as changed.
$d = $s[-1] | Select-Object -Property *
$d.DisplayName = "Dummy Service"
$d.Name = "Dummy"
$s.Insert(3,$d) #This will be Excel row 5 and server 2 will show as changed - so will Server 3
$s.RemoveAt(5) #This will be Excel row 7 and Server 2 will show as missing.
$s | Export-Excel -Path $env:temp\server2.xlsx
#Make some more changes to $s and export it to a third file
$s[2].displayname = $row4Displayname #Server 3 row 4 will match server 1 so won't be highlighted
$d = $s[-1] | Select-Object -Property *
$d.DisplayName = "Second Service"
$d.Name = "Service2"
$s.Insert(6,$d) #This will be an extra row in Server 3 at row 8. It will show as missing in Server 2.
$s.RemoveAt(8) #This will show as missing in Server 3 at row 11 ()
$s | Export-Excel -Path $env:temp\server3.xlsx
#Now bring the three files together.
Merge-MultipleSheets -Path "$env:temp\server1.xlsx", "$env:temp\Server2.xlsx","$env:temp\Server3.xlsx" -OutputFile "$env:temp\combined3.xlsx" -Property name,displayname,startType -Key name -Show
Examples/MortgageCalculator/MortgageCalculator.ps1
<#
Fixed Rate Loan/Mortgage Calculator in Excel
#>
param(
$Amount = 400000,
$InterestRate = .065,
$Term = 30
)
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
function New-CellData {
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification='Does not change system state')]
param(
$Range,
$Value,
$Format
)
$setFormatParams = @{
Worksheet = $ws
Range = $Range
NumberFormat = $Format
}
if ($Value -is [string] -and $Value.StartsWith('=')) {
$setFormatParams.Formula = $Value
}
else {
$setFormatParams.Value = $Value
}
Set-ExcelRange @setFormatParams
}
$f = "$PSScriptRoot\mortgage.xlsx"
Remove-Item $f -ErrorAction SilentlyContinue
$pkg = "" | Export-Excel $f -Title 'Fixed Rate Loan Payments' -PassThru -AutoSize
$ws = $pkg.Workbook.Worksheets["Sheet1"]
New-CellData -Range A3 -Value 'Amount'
New-CellData -Range B3 -Value $Amount -Format '$#,##0'
New-CellData -Range A4 -Value "Interest Rate"
New-CellData -Range B4 -Value $InterestRate -Format 'Percentage'
New-CellData -Range A5 -Value "Term (Years)"
New-CellData -Range B5 -Value $Term
New-CellData -Range D3 -Value "Monthly Payment"
New-CellData -Range F3 -Value "=-PMT(F4, B5*12, B3)" -Format '$#,##0.#0'
New-CellData -Range D4 -Value "Monthly Rate"
New-CellData -Range F4 -Value "=((1+B4)^(1/12))-1" -Format 'Percentage'
Close-ExcelPackage $pkg -Show
Examples/MoveSheets/MoveSheets.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfile = "$env:TEMP\testThis.xlsx"
Remove-Item $xlfile -ErrorAction Ignore
1..10 | Export-Excel $xlfile -WorkSheetname First #'First' will be the only sheet
11..20 | Export-Excel $xlfile -WorkSheetname Second -MoveToStart #'Second' is moved before first so the order is 'Second', 'First'
21..30 | Export-Excel $xlfile -WorkSheetname Third -MoveBefore First #'Second' is moved before first so the order is 'Second', 'Third', 'First'
31..40 | Export-Excel $xlfile -WorkSheetname Fourth -MoveAfter Third -Show #'Fourth' is moved after third so the order is ' 'Second', 'Third', 'Fourth' First'
Examples/Nasa/FireBalls.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$header = @(
'Date/Time - Peak Brightness (UT)' ,
'Latitude (Deg)' ,
'Longitude (Deg)' ,
'Altitude (km)' ,
'Velocity (km/s)' ,
'Velocity Components (km/s) vx' ,
'Velocity Components (km/s) vy' ,
'Velocity Components (km/s) vz' ,
'Total Radiated Energy (J)' ,
'Calculated Total Impact Energy (kt)'
)
$splat=@{
url='http://neo.jpl.nasa.gov/fireballs/'
index=5
Header=$header
FirstDataRow=1
}
Import-Html @splat
Examples/New-PSItem.ps1
function New-PSItem {
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification='Does not change system state')]
param()
$totalArgs = $args.Count
if($args[-1] -is [array]) {
$script:PSItemHeader=$args[-1]
$totalArgs-=1
}
$h=[ordered]@{}
for ($idx = 0; $idx -lt $totalArgs; $idx+=1) {
if($PSItemHeader) {
$key = $PSItemHeader[$idx]
} else {
$key = "P$($idx+1)"
}
$h.$key=$args[$idx]
}
[PSCustomObject]$h
}
Examples/NumberFormat/ColorizeNumbers.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "$env:TEMP\disks.xlsx"
Remove-Item $file -ErrorAction Ignore
$data = $(
New-PSItem 100 -100
New-PSItem 1 -1
New-PSItem 1.2 -1.1
New-PSItem -3.2 -4.1
New-PSItem -5.2 6.1
)
#Set the numbers throughout the sheet to format as positive in blue with a + sign, negative in Red with a - sign.
$data | Export-Excel -Path $file -Show -AutoSize -NumberFormat "[Blue]+0.#0;[Red]-0.#0"
Examples/NumberFormat/CurrencyFormat.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "$env:temp\disks.xlsx"
Remove-Item $file -ErrorAction Ignore
$data = $(
New-PSItem 100 -100
New-PSItem 1 -1
New-PSItem 1.2 -1.1
New-PSItem -3.2 -4.1
New-PSItem -5.2 6.1
New-PSItem 1000 -2000
)
#Number format can expand terms like Currency, to the local currency format
$data | Export-Excel -Path $file -Show -AutoSize -NumberFormat 'Currency'
Examples/NumberFormat/PercentagFormat.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "disks.xlsx"
Remove-Item $file -ErrorAction Ignore
$data = $(
New-PSItem 1
New-PSItem .5
New-PSItem .3
New-PSItem .41
New-PSItem .2
New-PSItem -.12
)
$data | Export-Excel -Path $file -Show -AutoSize -NumberFormat "0.0%;[Red]-0.0%"
Examples/NumberFormat/PosNegNumbers.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "disks.xlsx"
Remove-Item $file -ErrorAction Ignore
$data = $(
New-PSItem 100 -100
New-PSItem 1 -1
New-PSItem 1.2 -1.1
)
$data | Export-Excel -Path $file -Show -AutoSize -NumberFormat "0.#0;-0.#0"
Examples/NumberFormat/Win32LogicalDisk.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "disks.xlsx"
Remove-Item $file -ErrorAction Ignore
Get-CimInstance win32_logicaldisk -filter "drivetype=3" |
Select-Object DeviceID,Volumename,Size,Freespace |
Export-Excel -Path $file -Show -AutoSize -NumberFormat "0"
Examples/NumberFormat/Win32LogicalDiskFormatted.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "disks.xlsx"
Remove-Item $file -ErrorAction Ignore
Get-CimInstance win32_logicaldisk -filter "drivetype=3" |
Select-Object DeviceID,Volumename,Size,Freespace |
Export-Excel -Path $file -Show -AutoSize
Examples/OpenExcelPackage/EnableFeatures.ps1
# How to use Enable-ExcelAutoFilter and Enable-ExcelAutofit
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return }
$data = ConvertFrom-Csv @"
RegionInfo,StateInfo,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@
$xlfile = "$PSScriptRoot\enableFeatures.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$data | Export-Excel $xlfile
$excel = Open-ExcelPackage $xlfile
Enable-ExcelAutoFilter -Worksheet $excel.Sheet1
Enable-ExcelAutofit -Worksheet $excel.Sheet1
Close-ExcelPackage $excel -Show
Examples/OutTabulator/demo.txt
# ConvertFrom-Excel
''
.\test.xlsx
Import-Excel .\test.xlsx | ft
ConvertFrom-Excel -ExcelFile .\test.xlsx -outFile .\targetout.html
# Create a column definition
$columnOptions = @()
$columnOptions += New-ColumnOption -ColumnName Progress -formatter progress
ConvertFrom-Excel -ExcelFile .\test.xlsx -outFile .\targetout.html -columnOptions $columnOptions
$columnOptions += New-ColumnOption Activity -formatter lineFormatter
ConvertFrom-Excel -ExcelFile .\test.xlsx -outFile .\targetout.html -columnOptions $columnOptions
$columnOptions += New-ColumnOption -ColumnName Rating -formatter star
$columnOptions += New-ColumnOption Driver -formatter tickCross
ConvertFrom-Excel -ExcelFile .\test.xlsx -outFile .\targetout.html -columnOptions $columnOptions
ConvertFrom-Excel -ExcelFile .\test.xlsx -outFile .\targetout.html -columnOptions $columnOptions -groupBy Gender
Examples/OutTabulator/start-demo.ps1
## Start-Demo.ps1
##################################################################################################
## This is an overhaul of Jeffrey Snover's original Start-Demo script by Joel "Jaykul" Bennett
##
## I've switched it to using ReadKey instead of ReadLine (you don't have to hit Enter each time)
## As a result, I've changed the names and keys for a lot of the operations, so that they make
## sense with only a single letter to tell them apart (sorry if you had them memorized).
##
## I've also been adding features as I come across needs for them, and you'll contribute your
## improvements back to the PowerShell Script repository as well.
##################################################################################################
## Revision History (version 3.3)
## 3.3.3 Fixed: Script no longer says "unrecognized key" when you hit shift or ctrl, etc.
## Fixed: Blank lines in script were showing as errors (now printed like comments)
## 3.3.2 Fixed: Changed the "x" to match the "a" in the help text
## 3.3.1 Fixed: Added a missing bracket in the script
## 3.3 - Added: Added a "Clear Screen" option
## - Added: Added a "Rewind" function (which I'm not using much)
## 3.2 - Fixed: Put back the trap { continue; }
## 3.1 - Fixed: No Output when invoking Get-Member (and other cmdlets like it???)
## 3.0 - Fixed: Commands which set a variable, like: $files = ls
## - Fixed: Default action doesn't continue
## - Changed: Use ReadKey instead of ReadLine
## - Changed: Modified the option prompts (sorry if you had them memorized)
## - Changed: Various time and duration strings have better formatting
## - Enhance: Colors are settable: prompt, command, comment
## - Added: NoPauseAfterExecute switch removes the extra pause
## If you set this, the next command will be displayed immediately
## - Added: Auto Execute mode (FullAuto switch) runs the rest of the script
## at an automatic speed set by the AutoSpeed parameter (or manually)
## - Added: Automatically append an empty line to the end of the demo script
## so you have a chance to "go back" after the last line of you demo
##################################################################################################
##
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingWriteHost', '', Justification='Correct and desirable usage')]
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingInvokeExpression', '', Justification='Correct and desirable usage')]
param(
$file=".\demo.txt",
[int]$command=0,
[System.ConsoleColor]$promptColor="Yellow",
[System.ConsoleColor]$commandColor="White",
[System.ConsoleColor]$commentColor="Green",
[switch]$FullAuto,
[int]$AutoSpeed = 3,
[switch]$NoPauseAfterExecute
)
$RawUI = $Host.UI.RawUI
$hostWidth = $RawUI.BufferSize.Width
# A function for reading in a character
function Read-Char() {
$_OldColor = $RawUI.ForeGroundColor
$RawUI.ForeGroundColor = "Red"
$inChar=$RawUI.ReadKey("IncludeKeyUp")
# loop until they press a character, so Shift or Ctrl, etc don't terminate us
while($inChar.Character -eq 0){
$inChar=$RawUI.ReadKey("IncludeKeyUp")
}
$RawUI.ForeGroundColor = $_OldColor
return $inChar.Character
}
function Rewind($lines, $index, $steps = 1) {
$started = $index;
$index -= $steps;
while(($index -ge 0) -and ($lines[$index].Trim(" `t").StartsWith("#"))){
$index--
}
if( $index -lt 0 ) { $index = $started }
return $index
}
$file = Resolve-Path $file
while(-not(Test-Path $file)) {
$file = Read-Host "Please enter the path of your demo script (Crtl+C to cancel)"
$file = Resolve-Path $file
}
Clear-Host
$_lines = Get-Content $file
# Append an extra (do nothing) line on the end so we can still go back after the last line.
$_lines += "Write-Host 'The End'"
$_starttime = [DateTime]::now
$FullAuto = $false
Write-Host -nonew -back black -fore $promptColor $(" " * $hostWidth)
Write-Host -nonew -back black -fore $promptColor @"
$(' ' * ($hostWidth -(18 + $(split-path $file -leaf).Length)))
"@
Write-Host -nonew -back black -fore $promptColor "Press"
Write-Host -nonew -back black -fore Red " ? "
Write-Host -nonew -back black -fore $promptColor "for help.$(' ' * ($hostWidth -17))"
Write-Host -nonew -back black -fore $promptColor $(" " * $hostWidth)
# We use a FOR and an INDEX ($_i) instead of a FOREACH because
# it is possible to start at a different location and/or jump
# around in the order.
for ($_i = $Command; $_i -lt $_lines.count; $_i++)
{
# Put the current command in the Window Title along with the demo duration
$Dur = [DateTime]::Now - $_StartTime
$RawUI.WindowTitle = "$(if($dur.Hours -gt 0){'{0}h '})$(if($dur.Minutes -gt 0){'{1}m '}){2}s {3}" -f
$dur.Hours, $dur.Minutes, $dur.Seconds, $($_Lines[$_i])
# Echo out the commmand to the console with a prompt as though it were real
Write-Host -nonew -fore $promptColor "[$_i]$([char]0x2265) "
if ($_lines[$_i].Trim(" ").StartsWith("#") -or $_lines[$_i].Trim(" ").Length -le 0) {
Write-Host -fore $commentColor "$($_Lines[$_i]) "
continue
} else {
Write-Host -nonew -fore $commandColor "$($_Lines[$_i]) "
}
if( $FullAuto ) { Start-Sleep $autoSpeed; $ch = [char]13 } else { $ch = Read-Char }
switch($ch)
{
"?" {
Write-Host -Fore $promptColor @"
Running demo: $file
(n) Next (p) Previous
(q) Quit (s) Suspend
(t) Timecheck (v) View $(split-path $file -leaf)
(g) Go to line by number
(f) Find lines by string
(a) Auto Execute mode
(c) Clear Screen
"@
$_i-- # back a line, we're gonna step forward when we loop
}
"n" { # Next (do nothing)
Write-Host -Fore $promptColor ""
}
"p" { # Previous
Write-Host -Fore $promptColor ""
while ($_lines[--$_i].Trim(" ").StartsWith("#")){}
$_i-- # back a line, we're gonna step forward when we loop
}
"a" { # EXECUTE (Go Faster)
$AutoSpeed = [int](Read-Host "Pause (seconds)")
$FullAuto = $true;
Write-Host -Fore $promptColor ""
$_i-- # Repeat this line, and then just blow through the rest
}
"q" { # Quit
Write-Host -Fore $promptColor ""
$_i = $_lines.count;
break;
}
"v" { # View Source
$lines[0..($_i-1)] | Write-Host -Fore Yellow
$lines[$_i] | Write-Host -Fore Green
$lines[($_i+1)..$lines.Count] | Write-Host -Fore Yellow
$_i-- # back a line, we're gonna step forward when we loop
}
"t" { # Time Check
$dur = [DateTime]::Now - $_StartTime
Write-Host -Fore $promptColor $(
"{3} -- $(if($dur.Hours -gt 0){'{0}h '})$(if($dur.Minutes -gt 0){'{1}m '}){2}s" -f
$dur.Hours, $dur.Minutes, $dur.Seconds, ([DateTime]::Now.ToShortTimeString()))
$_i-- # back a line, we're gonna step forward when we loop
}
"s" { # Suspend (Enter Nested Prompt)
Write-Host -Fore $promptColor ""
$Host.EnterNestedPrompt()
$_i-- # back a line, we're gonna step forward when we loop
}
"g" { # GoTo Line Number
$i = [int](Read-Host "line number")
if($i -le $_lines.Count) {
if($i -gt 0) {
# extra line back because we're gonna step forward when we loop
$_i = Rewind -lines $_lines -index $_i -steps (($_i-$i)+1)
} else {
$_i = -1 # Start negative, because we step forward when we loop
}
}
}
"f" { # Find by pattern
$match = $_lines | Select-String (Read-Host "search string")
if($null -eq $match) {
Write-Host -Fore Red "Can't find a matching line"
} else {
$match | ForEach-Object { Write-Host -Fore $promptColor $("[{0,2}] {1}" -f ($_.LineNumber - 1), $_.Line) }
if($match.Count -lt 1) {
$_i = $match.lineNumber - 2 # back a line, we're gonna step forward when we loop
} else {
$_i-- # back a line, we're gonna step forward when we loop
}
}
}
"c" {
Clear-Host
$_i-- # back a line, we're gonna step forward when we loop
}
"$([char]13)" { # on enter
Write-Host
trap [System.Exception] {Write-Error $_; continue;}
Invoke-Expression ($_lines[$_i]) | out-default
if(-not $NoPauseAfterExecute -and -not $FullAuto) {
$null = $RawUI.ReadKey("NoEcho,IncludeKeyUp") # Pause after output for no apparent reason... ;)
}
}
default
{
Write-Host -Fore Green "`nKey not recognized. Press ? for help, or ENTER to execute the command."
$_i-- # back a line, we're gonna step forward when we loop
}
}
}
$dur = [DateTime]::Now - $_StartTime
Write-Host -Fore $promptColor $(
"" -f
$dur.Hours, $dur.Minutes, $dur.Seconds, [DateTime]::Now.ToLongTimeString())
Write-Host -Fore $promptColor $([DateTime]::now)
Write-Host
Examples/OutTabulator/targetout.html
Out-TabulatorView
Examples/OutTabulator/tryConvertFromExcel.ps1
[CmdletBinding()]
param($outFile = "$PSScriptRoot\targetout.html")
$columnOptions = @()
$columnOptions += New-ColumnOption -ColumnName Progress -formatter progress
$columnOptions += New-ColumnOption -ColumnName Activity -formatter lineFormatter
ConvertFrom-Excel -ExcelFile $PSScriptRoot\test.xlsx -outFile $PSScriptRoot\targetout.html -columnOptions $columnOptions
Examples/PassThru/TryPassThru.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "$env:Temp\sales.xlsx"
Remove-Item $file -ErrorAction Ignore
#Using -Passthru with Export-Excel returns an Excel Package object.
$xlPkg = Import-Csv .\sales.csv | Export-Excel $file -PassThru
#We add script properties to the package so $xlPkg.Sheet1 is equivalent to $xlPkg.Workbook.WorkSheets["Sheet1"]
$ws = $xlPkg.Sheet1
#We can manipulate the cells ...
$ws.Cells["E1"].Value = "TotalSold"
$ws.Cells["F1"].Value = "Add 10%"
#This is for illustration - there are more efficient ways to do this.
2..($ws.Dimension.Rows) |
ForEach-Object {
$ws.Cells["E$_"].Formula = "=C$_+D$_"
$ws.Cells["F$_"].Formula = "=E$_+(10%*(C$_+D$_))"
}
$ws.Cells.AutoFitColumns()
#You can call close-ExcelPackage $xlPkg -show, but here we will do the ssteps explicitly
$xlPkg.Save()
$xlPkg.Dispose()
Invoke-Item $file
Examples/PassThru/sales.csv
"Region","Item","UnitSold","UnitCost"
"South","Banana","54","0.46"
"West","Banana","74","0.56"
"West","Apple","26","0.7"
"East","Banana","38","0.26"
"East","Kale","71","0.69"
"East","Apple","35","0.55"
"East","Potato","48","0.48"
"West","Banana","59","0.49"
"West","Potato","56","0.62"
"North","Apple","40","0.68"
"South","Pear","39","0.44"
"West","Banana","60","0.64"
"West","Pear","32","0.29"
"North","Kale","55","0.35"
"West","Apple","73","0.26"
"South","Potato","33","0.46"
"West","Banana","49","0.59"
"West","Pear","65","0.35"
"North","Banana","33","0.31"
"East","Kale","41","0.74"
"South","Banana","49","0.31"
"West","Apple","60","0.34"
"South","Apple","38","0.59"
"North","Pear","29","0.74"
"West","Kale","67","0.38"
Examples/Pester-To-XLSx.ps1
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSPossibleIncorrectComparisonWithNull', '', Justification = 'Intentional use to select non null array items')]
[CmdletBinding(DefaultParameterSetName = 'Default')]
param(
[Parameter(Position = 0)]
[string]$XLFile,
[Parameter(ParameterSetName = 'Default', Position = 1)]
[Alias('Path', 'relative_path')]
[object[]]$Script = '.',
[Parameter(ParameterSetName = 'Existing', Mandatory = $true)]
[switch]
$UseExisting,
[Parameter(ParameterSetName = 'Default', Position = 2)]
[Parameter(ParameterSetName = 'Existing', Position = 2, Mandatory = $true)]
[string]$OutputFile,
[Parameter(ParameterSetName = 'Default')]
[Alias("Name")]
[string[]]$TestName,
[Parameter(ParameterSetName = 'Default')]
[switch]$EnableExit,
[Parameter(ParameterSetName = 'Default')]
[Alias('Tags')]
[string[]]$Tag,
[string[]]$ExcludeTag,
[Parameter(ParameterSetName = 'Default')]
[switch]$Strict,
[string]$WorkSheetName = 'PesterResults',
[switch]$append,
[switch]$Show
)
$InvokePesterParams = @{OutputFormat = 'NUnitXml' } + $PSBoundParameters
if (-not $InvokePesterParams['OutputFile']) {
$InvokePesterParams['OutputFile'] = Join-Path -ChildPath 'Pester.xml'-Path ([environment]::GetFolderPath([System.Environment+SpecialFolder]::MyDocuments))
}
if ($InvokePesterParams['Show'] ) { }
if ($InvokePesterParams['XLFile']) { $InvokePesterParams.Remove('XLFile') }
else { $XLFile = $InvokePesterParams['OutputFile'] -replace '.xml$', '.xlsx' }
if (-not $UseExisting) {
$InvokePesterParams.Remove('Append')
$InvokePesterParams.Remove('UseExisting')
$InvokePesterParams.Remove('Show')
$InvokePesterParams.Remove('WorkSheetName')
Invoke-Pester @InvokePesterParams
}
if (-not (Test-Path -Path $InvokePesterParams['OutputFile'])) {
throw "Could not output file $($InvokePesterParams['OutputFile'])"; return
}
$resultXML = ([xml](Get-Content $InvokePesterParams['OutputFile'])).'test-results'
$startDate = [datetime]$resultXML.date
$startTime = $resultXML.time
$machine = $resultXML.environment.'machine-name'
#$user = $resultXML.environment.'user-domain' + '\' + $resultXML.environment.user
$os = $resultXML.environment.platform -replace '\|.*$', " $($resultXML.environment.'os-version')"
<#hierarchy goes
root, [date], start [time], [Name] (always "Pester"), test results broken down as [total],[errors],[failures],[not-run] etc.
Environment (user & machine info)
Culture-Info (current, and currentUi culture)
Test-Suite [name] = "Pester" [result], [time] to execute, etc.
Results
Test-Suite [name] = filename,[result], [Time] to Execute etc
Results
Test-Suite [Name] = Describe block Name, [result], [Time] to execute etc..
Results
Test-Suite [Name] = Context block name [result], [Time] to execute etc.
Results
Test-Case [name] = Describe.Context.It block names [description]= it block name, result], [Time] to execute etc
or if the tests are parameterized
Test suite [description] - name in the the it block with not filled in
Results
Test-case [description] - name as rendered for display with filled in
#>
$testResults = foreach ($test in $resultXML.'test-suite'.results.'test-suite') {
$testPs1File = $test.name
#Test if there are context blocks in the hierarchy OR if we go straight from Describe to test-case
if ($test.results.'test-suite'.results.'test-suite' -ne $null) {
foreach ($suite in $test.results.'test-suite') {
$Describe = $suite.description
foreach ($subsuite in $suite.results.'test-suite') {
$Context = $subsuite.description
if ($subsuite.results.'test-suite'.results.'test-case') {
$testCases = $subsuite.results.'test-suite'.results.'test-case'
}
else { $testCases = $subsuite.results.'test-case' }
$testCases | ForEach-Object {
New-Object -TypeName psobject -Property ([ordered]@{
Machine = $machine ; OS = $os
Date = $startDate ; Time = $startTime
Executed = $(if ($_.executed -eq 'True') { 1 })
Success = $(if ($_.success -eq 'True') { 1 })
Duration = $_.time
File = $testPs1File; Group = $Describe
SubGroup = $Context ; Name = ($_.Description -replace '\s{2,}', ' ')
Result = $_.result ; FullDesc = '=Group&" "&SubGroup&" "&Name'
})
}
}
}
}
else {
$test.results.'test-suite' | ForEach-Object {
$Describe = $_.description
$_.results.'test-case' | ForEach-Object {
New-Object -TypeName psobject -Property ([ordered]@{
Machine = $machine ; OS = $os
Date = $startDate ; Time = $startTime
Executed = $(if ($_.executed -eq 'True') { 1 })
Success = $(if ($_.success -eq 'True') { 1 })
Duration = $_.time
File = $testPs1File; Group = $Describe
SubGroup = $null ; Name = ($_.Description -replace '\s{2,}', ' ')
Result = $_.result ; FullDesc = '=Group&" "&Test'
})
}
}
}
}
if (-not $testResults) { Write-Warning 'No Results found' ; return }
$clearSheet = -not $Append
$excel = $testResults | Export-Excel -Path $xlFile -WorkSheetname $WorkSheetName -ClearSheet:$clearSheet -Append:$append -PassThru -BoldTopRow -FreezeTopRow -AutoSize -AutoFilter -AutoNameRange
$ws = $excel.Workbook.Worksheets[$WorkSheetName]
<# Worksheet should look like ..
|A |B |C D |E |F |G |H |I |J |K |L |M
1|Machine |OS |Date Time |Executed |Success |Duration |File |Group |SubGroup |Name |Result |FullDescription
2|Flatfish |Name_Version |[run started] |Boolean |Boolean |In seconds |xx.ps1 |Describe |Context |It |Success |Desc_Context_It
#>
#Display Date as a date, not a date time
Set-Column -Worksheet $ws -Column 3 -NumberFormat 'Short Date' # -AutoSize
#Hide columns E to J (Executed, Success, Duration, File, Group and Subgroup)
(5..10) | ForEach-Object { Set-ExcelColumn -Worksheet $ws -Column $_ -Hide }
#Use conditional formatting to make Failures red, and Successes green (skipped remains black ) ... and save
$endRow = $ws.Dimension.End.Row
Add-ConditionalFormatting -WorkSheet $ws -range "L2:L$endrow" -RuleType ContainsText -ConditionValue "Failure" -BackgroundPattern None -ForegroundColor Red -Bold
Add-ConditionalFormatting -WorkSheet $ws -range "L2:L$endRow" -RuleType ContainsText -ConditionValue "Success" -BackgroundPattern None -ForeGroundColor Green
Close-ExcelPackage -ExcelPackage $excel -Show:$show
Examples/PesterTestReport/Pester test report.ps1
#Requires -Modules @{ ModuleName='Pester'; ModuleVersion='5.1' }
<#
.SYNOPSIS
Run Pester tests and export the results to an Excel file.
.DESCRIPTION
Use the `PesterConfigurationFile` to configure Pester to your requirements.
(Set the Path to the folder containing the tests, ...). Pester will be
invoked with the configuration you defined.
Each Pester 'it' clause will be exported to a row in an Excel file
containing the details of the test (Path, Duration, Result, ...).
.EXAMPLE
$params = @{
PesterConfigurationFile = 'C:\TestResults\PesterConfiguration.json'
ExcelFilePath = 'C:\TestResults\Tests.xlsx'
WorkSheetName = 'Tests'
}
& 'Pester test report.ps1' @params
# Content 'C:\TestResults\PesterConfiguration.json':
{
"Run": {
"Path": "C:\Scripts"
}
Executing the script with this configuration file will generate 1 file:
- 'C:\TestResults\Tests.xlsx' created by this script with Export-Excel
.EXAMPLE
$params = @{
PesterConfigurationFile = 'C:\TestResults\PesterConfiguration.json'
ExcelFilePath = 'C:\TestResults\Tests.xlsx'
WorkSheetName = 'Tests'
}
& 'Pester test report.ps1' @params
# Content 'C:\TestResults\PesterConfiguration.json':
{
"Run": {
"Path": "C:\Scripts"
},
"TestResult": {
"Enabled": true,
"OutputFormat": "NUnitXml",
"OutputPath": "C:/TestResults/PesterTestResults.xml",
"OutputEncoding": "UTF8"
}
}
Executing the script with this configuration file will generate 2 files:
- 'C:\TestResults\PesterTestResults.xml' created by Pester
- 'C:\TestResults\Tests.xlsx' created by this script with Export-Excel
.LINK
https://pester-docs.netlify.app/docs/commands/Invoke-Pester#-configuration
#>
[CmdletBinding()]
Param (
[String]$PesterConfigurationFile = 'PesterConfiguration.json',
[String]$WorkSheetName = 'PesterTestResults',
[String]$ExcelFilePath = 'PesterTestResults.xlsx'
)
Begin {
Function Get-PesterTests {
[CmdLetBinding()]
Param (
$Container
)
if ($testCaseResults = $Container.Tests) {
foreach ($result in $testCaseResults) {
Write-Verbose "Result '$($result.result)' duration '$($result.time)' name '$($result.name)'"
$result
}
}
if ($containerResults = $Container.Blocks) {
foreach ($result in $containerResults) {
Get-PesterTests -Container $result
}
}
}
#region Import Pester configuration file
Try {
Write-Verbose 'Import Pester configuration file'
$getParams = @{
Path = $PesterConfigurationFile
Raw = $true
}
[PesterConfiguration]$pesterConfiguration = Get-Content @getParams |
ConvertFrom-Json
}
Catch {
throw "Failed importing the Pester configuration file '$PesterConfigurationFile': $_"
}
#endregion
}
Process {
#region Execute Pester tests
Try {
Write-Verbose 'Execute Pester tests'
$pesterConfiguration.Run.PassThru = $true
$invokePesterParams = @{
Configuration = $pesterConfiguration
ErrorAction = 'Stop'
}
$invokePesterResult = Invoke-Pester @invokePesterParams
}
Catch {
throw "Failed to execute the Pester tests: $_ "
}
#endregion
#region Get Pester test results for the it clauses
$pesterTestResults = foreach (
$container in $invokePesterResult.Containers
) {
Get-PesterTests -Container $container |
Select-Object -Property *,
@{name = 'Container'; expression = { $container } }
}
#endregion
}
End {
if ($pesterTestResults) {
#region Export Pester test results to an Excel file
$exportExcelParams = @{
Path = $ExcelFilePath
WorksheetName = $WorkSheetName
ClearSheet = $true
PassThru = $true
BoldTopRow = $true
FreezeTopRow = $true
AutoSize = $true
AutoFilter = $true
AutoNameRange = $true
}
Write-Verbose "Export Pester test results to Excel file '$($exportExcelParams.Path)'"
$excel = $pesterTestResults | Select-Object -Property @{
name = 'FilePath'; expression = { $_.container.Item.FullName }
},
@{name = 'FileName'; expression = { $_.container.Item.Name } },
@{name = 'Path'; expression = { $_.ExpandedPath } },
@{name = 'Name'; expression = { $_.ExpandedName } },
@{name = 'Date'; expression = { $_.ExecutedAt } },
@{name = 'Time'; expression = { $_.ExecutedAt } },
Result,
Passed,
Skipped,
@{name = 'Duration'; expression = { $_.Duration.TotalSeconds } },
@{name = 'TotalDuration'; expression = { $_.container.Duration } },
@{name = 'Tag'; expression = { $_.Tag -join ', ' } },
@{name = 'Error'; expression = { $_.ErrorRecord -join ', ' } } |
Export-Excel @exportExcelParams
#endregion
#region Format the Excel worksheet
$ws = $excel.Workbook.Worksheets[$WorkSheetName]
# Display ExecutedAt in Date and Time format
Set-Column -Worksheet $ws -Column 5 -NumberFormat 'Short Date'
Set-Column -Worksheet $ws -Column 6 -NumberFormat 'hh:mm:ss'
# Display Duration in seconds with 3 decimals
Set-Column -Worksheet $ws -Column 10 -NumberFormat '0.000'
# Add comment to Duration column title
$comment = $ws.Cells['J1:J1'].AddComment('Total seconds', $env:USERNAME)
$comment.AutoFit = $true
# Set the width for column Path
$ws.Column(3) | Set-ExcelRange -Width 29
# Center the column titles
Set-ExcelRange -Address $ws.Row(1) -Bold -HorizontalAlignment Center
# Hide columns FilePath, Name, Passed and Skipped
(1, 4, 8, 9) | ForEach-Object {
Set-ExcelColumn -Worksheet $ws -Column $_ -Hide
}
# Set the color to red when 'Result' is 'Failed'
$endRow = $ws.Dimension.End.Row
$formattingParams = @{
Worksheet = $ws
range = "G2:L$endRow"
RuleType = 'ContainsText'
ConditionValue = "Failed"
BackgroundPattern = 'None'
ForegroundColor = 'Red'
Bold = $true
}
Add-ConditionalFormatting @formattingParams
# Set the color to green when 'Result' is 'Passed'
$endRow = $ws.Dimension.End.Row
$formattingParams = @{
Worksheet = $ws
range = "G2:L$endRow"
RuleType = 'ContainsText'
ConditionValue = "Passed"
BackgroundPattern = 'None'
ForegroundColor = 'Green'
}
Add-ConditionalFormatting @formattingParams
#endregion
#region Save the formatted Excel file
Close-ExcelPackage -ExcelPackage $excel
#endregion
}
else {
Write-Warning 'No Pester test results to export'
}
}
Examples/PivotTable/MultiplePivotTables.ps1
$data = ConvertFrom-Csv @"
Region,Date,Fruit,Sold
North,1/1/2017,Pears,50
South,1/1/2017,Pears,150
East,4/1/2017,Grapes,100
West,7/1/2017,Bananas,150
South,10/1/2017,Apples,200
North,1/1/2018,Pears,100
East,4/1/2018,Grapes,200
West,7/1/2018,Bananas,300
South,10/1/2018,Apples,400
"@ | Select-Object -Property Region, @{n = "Date"; e = {[datetime]::ParseExact($_.Date, "M/d/yyyy", (Get-Culture))}}, Fruit, Sold
$xlfile = "$env:temp\multiplePivotTables.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$excel = $data | Export-Excel $xlfile -PassThru -AutoSize -TableName FruitData
$pivotTableParams = @{
PivotTableName = "ByRegion"
Address = $excel.Sheet1.cells["F1"]
SourceWorkSheet = $excel.Sheet1
PivotRows = @("Region", "Fruit", "Date")
PivotData = @{'sold' = 'sum'}
PivotTableStyle = 'Light21'
GroupDateRow = "Date"
GroupDatePart = @("Years", "Quarters")
}
$pt = Add-PivotTable @pivotTableParams -PassThru
#$pt.RowHeaderCaption ="By Region,Fruit,Date"
$pt.RowHeaderCaption = "By " + ($pivotTableParams.PivotRows -join ",")
$pivotTableParams.PivotTableName = "ByFruit"
$pivotTableParams.Address = $excel.Sheet1.cells["J1"]
$pivotTableParams.PivotRows = @("Fruit", "Region", "Date")
$pt = Add-PivotTable @pivotTableParams -PassThru
$pt.RowHeaderCaption = "By Fruit,Region"
$pivotTableParams.PivotTableName = "ByDate"
$pivotTableParams.Address = $excel.Sheet1.cells["N1"]
$pivotTableParams.PivotRows = @("Date", "Region", "Fruit")
$pt = Add-PivotTable @pivotTableParams -PassThru
$pt.RowHeaderCaption = "By Date,Region,Fruit"
$pivotTableParams.PivotTableName = "ByYears"
$pivotTableParams.Address = $excel.Sheet1.cells["S1"]
$pivotTableParams.GroupDatePart = "Years"
$pt = Add-PivotTable @pivotTableParams -PassThru
$pt.RowHeaderCaption = "By Years,Region"
Close-ExcelPackage $excel -Show
Examples/PivotTable/PivotTableWithName.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$ExcelParams = @{
Path = "$env:TEMP\test1.xlsx"
IncludePivotTable = $true
PivotRows = 'Company'
PivotTableName = 'MyTable'
PivotData = @{'Handles' = 'sum'}
Show = $true
Activate = $true
}
Remove-Item $ExcelParams.Path -ErrorAction Ignore
Get-Process | Select-Object Company, Handles | Export-Excel @ExcelParams
<# Builds a pivot table that looks like this:
Sum of Handles
Row Labels Total
Adobe Systems Incorporated 3100
(blank) 214374
Apple Inc. 215
etc
etc
Grand Total 365625
#>
Examples/PivotTable/TableAndPivotTable.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore
#Export some sales data to Excel, format it as a table and put a data-bar in. For this example we won't create the pivot table during the export
$excel = ConvertFrom-Csv @"
Product, City, Gross, Net
Apple, London , 300, 250
Orange, London , 400, 350
Banana, London , 300, 200
Orange, Paris, 600, 500
Banana, Paris, 300, 200
Apple, New York, 1200,700
"@ | Export-Excel -PassThru -Path $xlSourcefile -TableStyle Medium13 -tablename "RawData" -ConditionalFormat @{Range="C2:C7"; DataBarColor="Green"}
#Add a pivot table, specify its address to put it on the same sheet, use the data that was just exported set the table style and number format.
#Use the "City" for the row names, and "Product" for the columnnames, and sum both the gross and net values for each City/Product combination; add grand totals to rows and columns.
# activate the sheet and add a pivot chart (defined in a hash table)
Add-PivotTable -Address $excel.Sheet1.Cells["F1"] -SourceWorkSheet $Excel.Sheet1 -SourceRange $Excel.Sheet1.Dimension.Address -PivotTableName "Sales" -PivotTableStyle "Medium12" -PivotNumberFormat "$#,##0.00" `
-PivotRows "City" -PivotColumns "Product" -PivotData @{Gross="Sum";Net="Sum"}-PivotTotals "Both" -Activate -PivotChartDefinition @{
Title="Gross and net by city and product";
ChartType="ColumnClustered";
Column=11; Width=500; Height=360;
YMajorUnit=500; YMinorUnit=100; YAxisNumberformat="$#,##0"
LegendPosition="Bottom"}
#Save and open in excel
Close-ExcelPackage $excel -Show
Examples/PivotTableFilters/testPivotFilter.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlFile="$env:TEMP\testPivot.xlsx"
Remove-Item $xlFile -ErrorAction Ignore
$data =@"
Region,Area,Product,Units,Cost
North,A1,Apple,100,.5
South,A2,Pear,120,1.5
East,A3,Grape,140,2.5
West,A4,Banana,160,3.5
North,A1,Pear,120,1.5
North,A1,Grape,140,2.5
"@ | ConvertFrom-Csv
$data |
Export-Excel $xlFile -Show `
-AutoSize -AutoFilter `
-IncludePivotTable `
-PivotRows Product `
-PivotData @{"Units"="sum"} -PivotFilter Region, Area -Activate
<#
Creates a Pivot table that looks like
Region All^
Area All^
Sum of Units
Row Labels Total
Apple 100
Pear 240
Grape 280
Banana 160
Grand Total 780
#>
Examples/Plot/PlotCos.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$plt = New-Plot
$plt.Plot((Get-Range 0 5 .02|Foreach-Object {[math]::Cos(2*[math]::pi*$_)}))
$plt.SetChartSize(800,300)
$plt.Show()
Examples/PsGallery.ps1
$top1000 = foreach ($p in 1..50) {
$c = Invoke-WebRequest -Uri "https://www.powershellgallery.com/packages" -method Post -Body "q=&sortOrder=package-download-count&page=$p"
[regex]::Matches($c.Content,'', [System.Text.RegularExpressions.RegexOptions]::Singleline) | foreach {
$name = [regex]::Match($_, "(?<=)").value
$n = [regex]::replace($_,'^.*By:\s*','', [System.Text.RegularExpressions.RegexOptions]::Singleline)
$n = [regex]::replace($n,'.*$','', [System.Text.RegularExpressions.RegexOptions]::Singleline)
$by = [regex]::match($n,'(?<=">).*(?=)').value
$qty = [regex]::match($n,'\S*(?= downloads)').value
[PSCustomObject]@{
Name = $name
by = $by
Downloads = $qty
}
}
}
del "~\Documents\gallery.xlsx"
$pivotdef = New-PivotTableDefinition -PivotTableName 'Summary' -PivotRows by -PivotData @{name="Count"
Downloads="Sum"} -PivotDataToColumn -Activate -ChartType ColumnClustered -PivotNumberFormat '#,###'
$top1000 | export-excel -path '~\Documents\gallery.xlsx' -Numberformat '#,###' -PivotTableDefinition $pivotdef -TableName 'TopDownloads' -Show
Examples/ReadAllSheets/GenerateXlsx.ps1
param(
[Parameter(Mandatory)]
$path
)
$sheet1 = ConvertFrom-Csv @"
Region,Item,TotalSold
West,melon,27
North,avocado,21
West,kiwi,84
East,melon,23
North,kiwi,8
North,nail,29
North,kiwi,46
South,nail,83
East,pear,10
South,avocado,40
"@
$sheet2 = ConvertFrom-Csv @"
Region,Item,TotalSold
West,lemon,24
North,hammer,41
East,nail,87
West,lemon,68
North,screwdriver,9
North,drill,76
West,lime,28
West,pear,78
North,apple,95
South,melon,40
"@
$sheet3 = ConvertFrom-Csv @"
Region,Item,TotalSold
South,drill,100
East,saw,22
North,saw,5
West,orange,78
East,saw,27
North,screwdriver,57
South,hammer,66
East,saw,62
West,nail,98
West,nail,98
"@
Remove-Item $path -ErrorAction SilentlyContinue
$sheet1 | Export-Excel $path -WorksheetName Sheet1
$sheet2 | Export-Excel $path -WorksheetName Sheet2
$sheet3 | Export-Excel $xlfile -WorksheetName Sheet3
$path
Examples/ReadAllSheets/Get-ExcelSheets.ps1
# Get-ExcelSheets
param(
[Parameter(Mandatory)]
$path
)
$hash = @{ }
$e = Open-ExcelPackage $path
foreach ($sheet in $e.workbook.worksheets) {
$hash[$sheet.name] = Import-Excel -ExcelPackage $e -WorksheetName $sheet.name
}
Close-ExcelPackage $e -NoSave
$hash
Examples/ReadAllSheets/ReadAllSheets.ps1
$xlfile = "$env:TEMP\MultipleSheets.xlsx"
.\GenerateXlsx.ps1 $xlfile
.\Get-ExcelSheets.ps1 $xlfile
Examples/SQL+FillColumns+Pivot/Example.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$sql = @"
SELECT rootfile.baseName , rootfile.extension , Image.fileWidth AS width , image.fileHeight AS height ,
metadata.dateDay , metadata.dateMonth , metadata.dateYear , Image.captureTime AS dateTaken,
metadata.hasGPS , metadata.GPSLatitude , metadata.GPSLongitude ,
metadata.focalLength , metadata.flashFired , metadata.ISOSpeedRating AS ISOSpeed,
metadata.Aperture AS apertureValue , metadata.ShutterSpeed AS shutterSpeedValue,
Image.bitdepth , image.colorLabels ,
Camera.Value AS cameraModel , LensRef.value AS lensModel
FROM Adobe_images image
JOIN AgLibraryFile rootFile ON rootfile.id_local = image.rootFile
JOIN AgharvestedExifMetadata metadata ON image.id_local = metadata.image
LEFT JOIN AgInternedExifLens LensRef ON LensRef.id_Local = metadata.lensRef
LEFT JOIN AgInternedExifCameraModel Camera ON Camera.id_local = metadata.cameraModelRef
"@
#Sql Statement gets 20 columns of data from Adobe lightroom database
#Define a pivot table and chart for total pictures with each lens.
$pt = @{"LensPivot" = @{ "PivotTableName" = "LensPivot";
"SourceWorkSheet" = "Sheet1" ;
"PivotRows" = "LensModel" ;
"PivotData" = @{"basename" = "Count"} ;
"IncludePivotChart" = $true ;
"NoLegend" = $true ;
"ShowPercent" = $true ;
"ChartType" = "Pie" ;
"ChartTitle" = "Split by Lens" }
}
#we want to add 3 columns, translate Apperture value and Shutter speed value into familar f/ and seconds notation, and use these and ISO to calculate EV level
$Avalue = {"=IF(P$ROW>6.63,TEXT(ROUND(Sqrt(Power(2,O$ROW)),1),`"`"`"f/`"`"0.0`")," +
"TEXT(ROUND(Sqrt(Power(2,O$ROW)),1),`"`"`"f/`"`"0.0`"))"}
$Svalue = {"=IF(P$ROW>2,TEXT(ROUND(POWER(2,P$ROW),0),`"`"`"1/`"`"0`"`"sec`"`"`"),"+
"IF(P$ROW>3.32,TEXT(ROUND(1/POWER(2,P$ROW),2),`"0.0`"`"Sec`"`"`"),"+
"TEXT(ROUND(1/POWER(2,P$ROW),2),`"0`"`"Sec`"`"`")))"}
$evValue = {"=ROUND(P$Row+O$Row-(LOG(N$Row/100,2)),0)" }
#remove and recreate the file
Remove-Item -Path "~\Documents\temp.xlsx" -ErrorAction SilentlyContinue
#Open a connection to the ODBC source "LR" (which points to the SQLLite DB for Lightroom), run the SQL query, and drop into Excel - in sheet1, autosizing columns.
$e = Send-SQLDataToExcel -Path "~\Documents\temp.xlsx" -WorkSheetname "Sheet1" -Connection "DSN=LR" -SQL $sql -AutoSize -Passthru
#Add columns, then format them and hide the ones which aren't of interest.
Set-ExcelColumn -Worksheet $e.workbook.Worksheets["sheet1"] -Column 21 -Value $Avalue -Heading "Apperture"
Set-ExcelColumn -Worksheet $e.workbook.Worksheets["sheet1"] -Column 22 -Value $Svalue -Heading "Shutter"
Set-ExcelColumn -Worksheet $e.workbook.Worksheets["sheet1"] -Column 23 -Value $Evvalue -Heading "Ev"
Set-ExcelRange -Address $e.workbook.Worksheets["sheet1" ].Column(21) -HorizontalAlignment Left -AutoFit
Set-ExcelRange -Address $e.workbook.Worksheets["sheet1" ].Column(22) -HorizontalAlignment Right -AutoFit
@(5,6,7,13,15,16,17,18) | ForEach-Object {
Set-ExcelRange -Address $e.workbook.Worksheets["sheet1" ].Column($_) -Hidden
}
#Center the column labels.
Set-ExcelRange -Address $e.workbook.Worksheets["sheet1" ].Row(1) -HorizontalAlignment Center
#Format the data as a nice Table, Create the pivot table & chart defined above, show the file in Excel in excel after saving.
Export-Excel -ExcelPackage $e -WorkSheetname "sheet1" -TableName "Table" -PivotTableDefinition $pt -Show
############################################################
Remove-Item .\demo3.xlsx
#Database query to get race wins, Poles and fastest lapes for the 25 best drivers; we already have a connection to the DB in $dbSessions
$session = $DbSessions["f1"]
$SQL = @"
SELECT TOP 25 DriverName,
Count(RaceDate) AS Races,
Count(Win) AS Wins,
Count(Pole) AS Poles,
Count(FastestLap) AS Fastlaps
FROM Results
GROUP BY DriverName
ORDER BY (Count(win)) DESC
"@
#Run the query and put the results in workshet "Winners", autosize the columns and hold on to the ExcelPackage object
$Excel = Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo3.xlsx -WorkSheetname "Winners" -AutoSize -Passthru
#Create and format columns for the ratio of Wins to poles and fast laps.
Set-ExcelColumn -ExcelPackage $Excel -WorkSheetname "Winners" -column 6 -Heading "WinsToPoles" -Value {"=D$row/C$row"}
Set-ExcelColumn -ExcelPackage $Excel -WorkSheetname "Winners" -column 7 -Heading "WinsToFast" -Value {"=E$row/C$row"}
6..7 | ForEach-Object {
Set-ExcelRange -Address $Excel.Workbook.Worksheets["Winners"].column($_) -NumberFormat "0.0%" -AutoFit }
#Define a chart to show the relationship of lest on an XY Grid, create the ranges required in the, add the chart and show the file in Excel in excel after saving.
$chart = New-ExcelChartDefinition -NoLegend -ChartType XYScatter -XRange WinsToFast -YRange WinsToPoles -ShowCategory -Column 7 -Width 2000 -Height 700
Export-Excel -ExcelPackage $Excel -WorkSheetname "Winners" -AutoNameRange -ExcelChartDefinition $chart -Show
Examples/SQL+FillColumns+Pivot/Example2.ps1
#requires -modules "getSql"
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#download f1Results from https://1drv.ms/f/s!AhfYu7-CJv4egbt5FD7Cdxi8jSz3aQ and update the path below
Get-SQL -Session f1 -Excel -Connection C:\Users\mcp\OneDrive\Public\F1\f1Results.xlsx -showtables -Verbose
Remove-Item .\demo3.xlsx
$session = $DbSessions["f1"]
$SQL = "SELECT top 25 DriverName, Count(RaceDate) as Races ,
Count(Win) as Wins, Count(Pole) as Poles, Count(FastestLap) as Fastlaps
FROM Results GROUP BY DriverName
order by (count(win)) desc"
$Excel = Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo3.xlsx -WorkSheetname "Winners" -AutoSize -AutoNameRange -BoldTopRow -FreezeTopRow -Passthru
$ws = $Excel.Workbook.Worksheets["Winners"]
Set-ExcelRow -Worksheet $ws -Heading "Average" -Value {"=Average($columnName`2:$columnName$endrow)"} -NumberFormat "0.0" -Bold
Set-ExcelColumn -Worksheet $ws -Heading "WinsToPoles" -Value {"=D$row/C$row"} -Column 6 -AutoSize -AutoNameRange
Set-ExcelColumn -Worksheet $ws -Heading "WinsToFast" -Value {"=E$row/C$row"} -Column 7 -AutoSize -AutoNameRange
Set-ExcelRange -Worksheet $ws -Range "F2:G50" -NumberFormat "0.0%"
$chart = New-ExcelChartDefinition -NoLegend -ChartType XYScatter -XRange WinsToFast -YRange WinsToPoles -Column 7 -Width 2000 -Height 700 -Title "Poles vs fastlaps"
Export-Excel -ExcelPackage $Excel -WorkSheetname "Winners" -ExcelChartDefinition $chart -Show
Examples/SetColumnBackgroundColor/SetColumnBackgroundColor.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$path = "$env:TEMP\testBackgroundColor.xlsx"
$p = Get-Process | Select-Object Company, Handles | Export-Excel $path -ClearSheet -PassThru
$ws = $p.Workbook.WorkSheets[1]
$totalRows = $ws.Dimension.Rows
#Set the range from B2 to the last active row. s
Set-ExcelRange -Range $ws.Cells["B2:B$($totalRows)"] -BackgroundColor LightBlue
Export-Excel -ExcelPackage $p -show -AutoSize
Examples/Sparklines/SalesByQuarter.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfile = "$env:TEMP\SalesByQuarter.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$data = ConvertFrom-Csv @"
Region,Q1,Q2,Q3,Q4,YTDPerformance
Asia,1400,7200,5700,6900
Europe,3400,2300,9400,7300
Midwest,4700,9300,3700,8600
Northeast,2300,4300,4600,5600
"@
$excel = $data | Export-Excel $xlfile -Passthru -AutoSize -TableName SalesByQuarter
$ws = $excel.Sheet1
Set-ExcelRange -Worksheet $ws -Range "B2:E5" -NumberFormat "$#,##0" -AutoSize
$sparkLineType = "line"
$null = $ws.SparklineGroups.Add( $sparkLineType, $ws.Cells["F2"], $ws.Cells["B2:E2"] )
$null = $ws.SparklineGroups.Add( $sparkLineType, $ws.Cells["F3"], $ws.Cells["B3:E3"] )
$null = $ws.SparklineGroups.Add( $sparkLineType, $ws.Cells["F4"], $ws.Cells["B4:E4"] )
$null = $ws.SparklineGroups.Add( $sparkLineType, $ws.Cells["F5"], $ws.Cells["B5:E5"] )
Close-ExcelPackage $excel -Show
Examples/Sparklines/Sparklines.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
class data {
[datetime]$Date
[Double]$AUD
[Double]$CAD
[Double]$CHF
[Double]$DKK
[Double]$EUR
[Double]$GBP
[Double]$HKD
[Double]$JPY
[Double]$MYR
[Double]$NOK
[Double]$NZD
[Double]$RUB
[Double]$SEK
[Double]$THB
[Double]$TRY
[Double]$USD
}
[data[]]$data = ConvertFrom-Csv @"
Date,AUD,CAD,CHF,DKK,EUR,GBP,HKD,JPY,MYR,NOK,NZD,RUB,SEK,THB,TRY,USD
2016-03-01,6.17350,6.42084,8.64785,1.25668,9.37376,12.01683,1.11067,0.07599,2.06900,0.99522,5.69227,0.11665,1.00000,0.24233,2.93017,8.63185
2016-03-02,6.27223,6.42345,8.63480,1.25404,9.35350,12.14970,1.11099,0.07582,2.07401,0.99311,5.73277,0.11757,1.00000,0.24306,2.94083,8.63825
2016-03-07,6.33778,6.38403,8.50245,1.24980,9.32373,12.05756,1.09314,0.07478,2.07171,0.99751,5.77539,0.11842,1.00000,0.23973,2.91088,8.48885
2016-03-08,6.30268,6.31774,8.54066,1.25471,9.36254,12.03361,1.09046,0.07531,2.05625,0.99225,5.72501,0.11619,1.00000,0.23948,2.91067,8.47020
2016-03-09,6.32630,6.33698,8.46118,1.24399,9.28125,11.98879,1.08544,0.07467,2.04128,0.98960,5.71601,0.11863,1.00000,0.23893,2.91349,8.42945
2016-03-10,6.24241,6.28817,8.48684,1.25260,9.34350,11.99193,1.07956,0.07392,2.04500,0.98267,5.58145,0.11769,1.00000,0.23780,2.89150,8.38245
2016-03-11,6.30180,6.30152,8.48295,1.24848,9.31230,12.01194,1.07545,0.07352,2.04112,0.98934,5.62335,0.11914,1.00000,0.23809,2.90310,8.34510
2016-03-15,6.19790,6.21615,8.42931,1.23754,9.22896,11.76418,1.07026,0.07359,2.00929,0.97129,5.49278,0.11694,1.00000,0.23642,2.86487,8.30540
2016-03-16,6.18508,6.22493,8.41792,1.23543,9.21149,11.72470,1.07152,0.07318,2.01179,0.96907,5.49138,0.11836,1.00000,0.23724,2.84767,8.31775
2016-03-17,6.25214,6.30642,8.45981,1.24327,9.26623,11.86396,1.05571,0.07356,2.01706,0.98159,5.59544,0.12024,1.00000,0.23543,2.87595,8.18825
2016-03-18,6.25359,6.32400,8.47826,1.24381,9.26976,11.91322,1.05881,0.07370,2.02554,0.98439,5.59067,0.12063,1.00000,0.23538,2.86880,8.20950
"@
$xlfile = "$env:TEMP\sparklines.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$excel = $data | Export-Excel $xlfile -WorksheetName SEKRates -AutoSize -PassThru
# Add a column sparkline for all currencies
Set-ExcelRange -Worksheet $excel.SEKRates -Range "A2:A12" -NumberFormat "yyyy-mm-dd" -AutoSize
Set-ExcelRange -Worksheet $excel.SEKRates -Range A15 -Value Column -AutoSize
$sparklineCol = $excel.SEKRates.SparklineGroups.Add(
"Column",
$excel.SEKRates.Cells["B15:Q15"],
$excel.SEKRates.Cells["B2:Q12"]
)
$sparklineCol.High = $true
$sparklineCol.ColorHigh.SetColor("Red")
# Add a line sparkline for all currencies
Set-ExcelRange -Worksheet $excel.SEKRates -Range A16 -Value Line -AutoSize
$sparklineLine = $excel.SEKRates.SparklineGroups.Add(
"Line",
$excel.SEKRates.Cells["B16:Q16"],
$excel.SEKRates.Cells["B2:Q12"]
)
$sparklineLine.DateAxisRange = $excel.SEKRates.Cells["A2:A12"]
# Add some more random values and add a stacked sparkline.
Set-ExcelRange -Worksheet $excel.SEKRates -Range A17 -Value Stacked -AutoSize
$numbers = 2, -1, 3, -4, 8, 5, -12, 18, 99, 1, -4, 12, -8, 9, 0, -8
$col = 2 # Column B
foreach ($n in $numbers) {
$excel.SEKRates.Cells[17, $col++].Value = $n
}
$sparklineStacked = $excel.SEKRates.SparklineGroups.Add(
"Stacked",
$excel.SEKRates.Cells["R17"],
$excel.SEKRates.Cells["B17:Q17"]
)
$sparklineStacked.High = $true
$sparklineStacked.ColorHigh.SetColor("Red")
$sparklineStacked.Low = $true
$sparklineStacked.ColorLow.SetColor("Green")
$sparklineStacked.Negative = $true
$sparklineStacked.ColorNegative.SetColor("Blue")
Set-ExcelRange -Worksheet $excel.SEKRates -Range "A15:A17" -Bold -Height 50 -AutoSize
$v = @"
High - Red
Low - Green
Negative - Blue
"@
Set-ExcelRange -Worksheet $excel.SEKRates -Range S17 -Value $v -WrapText -Width 20 -HorizontalAlignment Center -VerticalAlignment Center
Close-ExcelPackage $excel -Show
Examples/SpreadsheetCells/CalculatedFields.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
#. ..\New-PSItem.ps1
Remove-Item "$env:temp\functions.xlsx" -ErrorAction SilentlyContinue
$(
New-PSItem 12001 Nails 37 3.99 =C2*D2 @("ID", "Product", "Quantity", "Price", "Total")
New-PSItem 12002 Hammer 5 12.10 =C3*D3
New-PSItem 12003 Saw 12 15.37 =C4*D4
New-PSItem 12010 Drill 20 8 =C5*D5
New-PSItem 12011 Crowbar 7 23.48 =C6*D6
) | Export-Excel "$env:temp\functions.xlsx"-AutoSize -Show
Examples/SpreadsheetCells/ExcelFormulasUsingAddMember.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Remove-Item .\testFormula.xlsx -ErrorAction Ignore
@"
id,item,units,cost
12001,Nails,37,3.99
12002,Hammer,5,12.10
12003,Saw,12,15.37
12010,Drill,20,8
12011,Crowbar,7,23.48
"@ | ConvertFrom-Csv |
Add-Member -PassThru -MemberType NoteProperty -Name Total -Value "=units*cost" |
Export-Excel -Path .\testFormula.xlsx -Show -AutoSize -AutoNameRange
Examples/SpreadsheetCells/ExcelFunctions.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Remove-Item "$env:temp\functions.xlsx" -ErrorAction SilentlyContinue
$(
New-PSItem =2%/12 60 500000 "=pmt(rate,nper,pv)" @("rate", "nper", "pv", "pmt")
New-PSItem =3%/12 60 500000 "=pmt(rate,nper,pv)"
New-PSItem =4%/12 60 500000 "=pmt(rate,nper,pv)"
New-PSItem =5%/12 60 500000 "=pmt(rate,nper,pv)"
New-PSItem =6%/12 60 500000 "=pmt(rate,nper,pv)"
New-PSItem =7%/12 60 500000 "=pmt(rate,nper,pv)"
) | Export-Excel "$env:temp\functions.xlsx" -AutoNameRange -AutoSize -Show
Examples/SpreadsheetCells/HyperLink.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
Remove-Item "$env:temp\hyperlink.xlsx" -ErrorAction SilentlyContinue
$(
New-PSItem '=Hyperlink("http://dougfinke.com/blog","Doug Finke")' @("Link")
New-PSItem '=Hyperlink("http://blogs.msdn.com/b/powershell/","PowerShell Blog")'
New-PSItem '=Hyperlink("http://blogs.technet.com/b/heyscriptingguy/","Hey, Scripting Guy")'
) | Export-Excel "$env:temp\hyperlink.xlsx" -AutoSize -Show
Examples/Stocks/Get-StockInfo.ps1
function Get-StockInfo {
param(
[Parameter(Mandatory)]
$symbols,
[ValidateSet('open', 'close', 'high', 'low', 'avgTotalVolume')]
$dataPlot = "close"
)
$xlfile = "$env:TEMP\stocks.xlsx"
Remove-Item -Path $xlfile -ErrorAction Ignore
$result = Invoke-RestMethod "https://api.iextrading.com/1.0/stock/market/batch?symbols=$($symbols)&types=quote&last=1"
$ecd = New-ExcelChartDefinition -Row 1 -Column 1 -SeriesHeader $dataPlot `
-XRange symbol -YRange $dataPlot `
-Title "$($dataPlot)`r`n As Of $((Get-Date).ToShortDateString())"
$(foreach ($name in $result.psobject.Properties.name) {
$result.$name.quote
}) | Export-Excel $xlfile -AutoNameRange -AutoSize -Show -ExcelChartDefinition $ecd -StartRow 21 -StartColumn 2
}
Examples/Stocks/GetStocksAvgTotVol.ps1
. $PSScriptRoot\Get-StockInfo.ps1
Get-StockInfo -symbols "msft,ibm,ge,xom,aapl" -dataPlot avgTotalVolume
Examples/Styles/MultipleStyles.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfile = "$env:TEMP\test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$data = ConvertFrom-Csv @"
Region,Item,TotalSold
North,melon,38
South,screwdriver,21
South,peach,33
South,saw,81
South,kiwi,70
North,orange,59
North,avocado,25
South,lime,48
South,nail,83
North,apple,2
"@
$styleParams = @{
FontSize = 13
Bold = $true
}
$styles = $(
New-ExcelStyle -BackgroundColor LightBlue -FontSize 14 -Bold -Range "A1:H1" -HorizontalAlignment Center -Merge
New-ExcelStyle -BackgroundColor LimeGreen -Range "B10" @styleParams
New-ExcelStyle -BackgroundColor PeachPuff -Range "B5" @styleParams
New-ExcelStyle -BackgroundColor Orange -Range "B8" @styleParams
New-ExcelStyle -BackgroundColor Red -Range "B12" @styleParams
)
$reportTitle = "This is a report Title"
$data | Export-Excel $xlfile -Show -AutoSize -AutoFilter -Title $reportTitle -Style $styles
Examples/Styles/NewExcelStyle.ps1
# https://raw.githubusercontent.com/dfinke/ImportExcel/master/images/NewExcelStyle.png
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfile = "$env:TEMP\test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$data = ConvertFrom-Csv @"
Region,Item,TotalSold
North,melon,38
South,screwdriver,21
South,peach,33
South,saw,81
South,kiwi,70
North,orange,59
North,avocado,25
South,lime,48
South,nail,83
North,apple,2
"@
$reportTitle = "This is a report Title"
$style = New-ExcelStyle -BackgroundColor LightBlue -FontSize 14 -Bold -Range "A1:H1" -HorizontalAlignment Center -Merge
$data | Export-Excel $xlfile -Show -AutoSize -AutoFilter -Title $reportTitle -Style $style
Examples/Subtotals.ps1
$Data = ConvertFrom-Csv @'
Product, City, Gross, Net
Apple, London , 300, 250
Orange, London , 400, 350
Banana, London , 300, 200
Grape, Munich, 100, 100
Orange, Paris, 600, 500
Banana, Paris, 300, 200
Apple, New York, 1200,700
'@
$ExcelPath = "$env:temp\subtotal.xlsx"
$SheetName = 'Sheet1'
Remove-Item -Path $ExcelPath -ErrorAction SilentlyContinue
$GroupByFieldName = 'City'
$TotalSingleRows = $false
$GrandTotal = $false
$SubtotalRowHeight = 0 #If non zero will set subtotals to this height
$Subtotals =@{ 'Net' = {"=SUBTOTAL(3,D{0}:D{1})" -f $from, $to}
}
$SubtotalFieldName = 'Net'
$SubtotalFormula = '=SUBTOTAL(3,D{0}:D{1})' # {0} and {1} are placeholders for the first and last row. D is the column to total in
# 1=AVERAGE; 2=COUNT; 3=COUNTA; 4=MAX; 5=MIN; 6=PRODUCT; 7=STDEV; 8=STDEVP; 9=SUM; 10=VAR; 11=VARP add 100 to ignore hidden values
#at each change in the Group by field, insert a subtotal (count) formula in the title column & send to excel - list those rows and make them half height after export
$currentRow = 2
$lastChangeRow = 2
$insertedRows = @()
#$hideRows = @()
$lastValue = $Data[0].$GroupByFieldName
$excel = $Data | ForEach-Object -Process {
if ($_.$GroupByFieldName -ne $lastvalue) {
if ($lastChangeRow -lt ($currentrow - 1) -or $totalSingleRows) {
$formula = $SubtotalFormula -f $lastChangeRow, ($currentrow - 1)
$insertedRows += $currentRow
[pscustomobject]@{$SubtotalFieldName = $formula}
$currentRow += 1
}
$lastChangeRow = $currentRow
$lastValue = $_.$GroupByFieldName
}
$_
$currentRow += 1
} -end {
$formula = $SubtotalFormula -f $lastChangeRow, ($currentrow - 1)
[pscustomobject]@{$SubtotalFieldName=$formula}
if ($GrandTotal) {
$formula = $SubtotalFormula -f $lastChangeRow, ($currentrow - 1)
[pscustomobject]@{$SubtotalFieldName=$formula}
}
} | Export-Excel -Path $ExcelPath -PassThru -AutoSize -AutoFilter -BoldTopRow -WorksheetName $sheetName
#We kept a lists of the total rows. Since single rows won't get expanded/collapsed hide them.
if ($subtotalrowHeight) {
foreach ($r in $insertedrows) { $excel.WorkItems.Row($r).Height = $SubtotalRowHeight}
}
#foreach ($r in $hideRows) { $excel.$SheetName.Row($r).hidden = $true}
$range = $excel.$SheetName.Dimension.Address
$sheetIndex = $excel.Sheet1.Index
Close-ExcelPackage -ExcelPackage $excel
try { $excelApp = New-Object -ComObject "Excel.Application" }
catch { Write-Warning "Could not start Excel application - which usually means it is not installed." ; return }
try { $excelWorkBook = $excelApp.Workbooks.Open($ExcelPath) }
catch { Write-Warning -Message "Could not Open $ExcelPath." ; return }
$ws = $excelWorkBook.Worksheets.Item($sheetIndex)
$null = $ws.Range($range).Select()
$null = $excelapp.Selection.AutoOutline()
$excelWorkBook.Save()
$excelWorkBook.Close()
$excelApp.Quit()
Start-Process $ExcelPath
Examples/Tables/MultipleTables.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfile = "$env:Temp\testData.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$r = Get-ChildItem C:\WINDOWS\system32
$BySize=@{}
$r | ForEach-Object{ $BySize.($_.extension)+=$_.length }
$top10BySize = $BySize.GetEnumerator() |
ForEach-Object{ [PSCustomObject]@{Name=$_.key;Size=[double]$_.value} } |
Sort-Object size -Descending |
Select-Object -First 10
$top10ByCount = $r.extension |
Group-Object |
Sort-Object count -Descending |
Select-Object -First 10 Name, count
$top10ByFileSize = $r |
Sort-Object length -Descending |
Select-Object -First 10 Name, @{n="Size";e={$_.Length}} #,Extension,Path
$xlPkg = $top10BySize | Export-Excel -path $xlfile -WorkSheetname FileInfo -TableName ExtSize -PassThru
$xlPkg = $top10ByCount | Export-Excel -ExcelPackage $xlPkg -WorkSheetname FileInfo -StartRow 13 -TableName ExtCount -PassThru
$xlPkg = $top10ByFileSize | Export-Excel -ExcelPackage $xlPkg -WorkSheetname FileInfo -StartRow 25 -TableName FileSize -PassThru -AutoSize
#worksheets.tables["Name1","Name2"] returns 2 tables. Set-ExcelRange can process those and will set the number format over both
Set-ExcelRange -Range $xlpkg.Workbook.Worksheets[1].Tables["ExtSize","FileSize"] -NumberFormat '0,,"MB"'
$ps = Get-Process | Where-Object Company
$ps |
Sort-Object handles -Descending |
Select-Object -First 10 company, handles |
Export-Excel -ExcelPackage $xlPkg -WorkSheetname Handles -AutoSize -TableName Handles
$ps |
Sort-Object PM -Descending |
Select-Object -First 10 company, PM |
Export-Excel $xlfile -WorkSheetname Handles -AutoSize -TableName PM -StartRow 13 -Show
Examples/Tables/SalesData-WithTotalRow.ps1
try { Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 } catch { throw ; return }
$data = ConvertFrom-Csv @"
OrderId,Category,Sales,Quantity,Discount
1,Cosmetics,744.01,07,0.7
2,Grocery,349.13,25,0.3
3,Apparels,535.11,88,0.2
4,Electronics,524.69,60,0.1
5,Electronics,439.10,41,0.0
6,Apparels,56.84,54,0.8
7,Electronics,326.66,97,0.7
8,Cosmetics,17.25,74,0.6
9,Grocery,199.96,39,0.4
10,Grocery,731.77,20,0.3
"@
$xlfile = "$PSScriptRoot\TotalsRow.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$TableTotalSettings = @{
Quantity = 'Sum'
Category = '=COUNTIF([Category],"<>Electronics")' # Count the number of categories not equal to Electronics
Sales = @{
Function = '=SUMIF([Category],"<>Electronics",[Sales])'
Comment = "Sum of sales for everything that is NOT Electronics"
}
}
$data | Export-Excel -Path $xlfile -TableName Sales -TableStyle Medium10 -TableTotalSettings $TableTotalSettings -AutoSize -Show
Examples/Tables/TotalsRow.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$r = Get-ChildItem C:\WINDOWS\system32 -File
$TotalSettings = @{
Name = "Count"
# You can create the formula in an Excel workbook first and copy-paste it here
# This syntax can only be used for the Custom type
Extension = "=COUNTIF([Extension];`".exe`")"
Length = @{
Function = "=SUMIF([Extension];`".exe`";[Length])"
Comment = "Sum of all exe sizes"
}
}
$r | Export-Excel -TableName system32files -TableStyle Medium10 -TableTotalSettings $TotalSettings -Show
Examples/TestRestAPI/PSExcelPester.psm1
function ConvertTo-PesterTest {
param(
[parameter(Mandatory)]
$XlFilename,
$WorksheetName = 'Sheet1'
)
$testFileName = "{0}.tests.ps1" -f (Get-date).ToString("yyyyMMddHHmmss")
$records = Import-Excel $XlFilename
$params = @{}
$blocks = $(foreach ($record in $records) {
foreach ($propertyName in $record.psobject.properties.name) {
if ($propertyName -notmatch 'ExpectedResult|QueryString') {
$params.$propertyName = $record.$propertyName
}
}
if ($record.QueryString) {
$params.Uri += "?{0}" -f $record.QueryString
}
@"
it "Should have the expected result '$($record.ExpectedResult)'" {
`$target = '$($params | ConvertTo-Json -compress)' | ConvertFrom-Json
`$target.psobject.Properties.name | ForEach-Object {`$p=@{}} {`$p.`$_=`$(`$target.`$_)}
Invoke-RestMethod @p | Should -Be '$($record.ExpectedResult)'
}
"@
})
@"
Describe "Tests from $($XlFilename) in $($WorksheetName)" {
$($blocks)
}
"@ | Set-Content -Encoding Ascii $testFileName
[PSCustomObject]@{
TestFileName = (Get-ChildItem $testFileName).FullName
}
}
function Show-PesterResult {
param(
[Parameter(ValueFromPipelineByPropertyName, Mandatory)]
$TestFileName
)
Begin {
$xlfilename = ".\test.xlsx"
Remove-Item $xlfilename -ErrorAction SilentlyContinue
$ConditionalText = @()
$ConditionalText += New-ConditionalText -Range "Result" -Text failed -BackgroundColor red -ConditionalTextColor black
$ConditionalText += New-ConditionalText -Range "Result" -Text passed -BackgroundColor green -ConditionalTextColor black
$ConditionalText += New-ConditionalText -Range "Result" -Text pending -BackgroundColor gray -ConditionalTextColor black
$xlParams = @{
Path = $xlfilename
WorkSheetname = 'PesterTests'
ConditionalText = $ConditionalText
PivotRows = 'Result', 'Name'
PivotData = @{'Result' = 'Count'}
IncludePivotTable = $true
AutoSize = $true
AutoNameRange = $true
AutoFilter = $true
Show = $true
}
}
End {
$(foreach ($result in (Invoke-Pester -Script $TestFileName -PassThru -Show None).TestResult) {
[PSCustomObject][Ordered]@{
Description = $result.Describe
Name = $result.Name
Result = $result.Result
Messge = $result.FailureMessage
StackTrace = $result.StackTrace
}
}) | Export-Excel @xlParams
}
}
Examples/TestRestAPI/RunAndShowUnitTests.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$xlfilename=".\test.xlsx"
Remove-Item $xlfilename -ErrorAction Ignore
$ConditionalText = @()
$ConditionalText += New-ConditionalText -Range "C:C" -Text failed -BackgroundColor red -ConditionalTextColor black
$ConditionalText += New-ConditionalText -Range "C:C" -Text passed -BackgroundColor green -ConditionalTextColor black
$r = .\TryIt.ps1
$xlPkg = $(foreach($result in $r.TestResult) {
[PSCustomObject]@{
Name = $result.Name
#Time = $result.Time
Result = $result.Result
Messge = $result.FailureMessage
StackTrace = $result.StackTrace
}
}) | Export-Excel -Path $xlfilename -AutoSize -ConditionalText $ConditionalText -PassThru
$sheet1 = $xlPkg.Workbook.Worksheets["sheet1"]
$sheet1.View.ShowGridLines = $false
$sheet1.View.ShowHeaders = $false
Set-ExcelRange -Address $sheet1.Cells["A:A"] -AutoSize
Set-ExcelRange -Address $sheet1.Cells["B:D"] -WrapText
$sheet1.InsertColumn(1, 1)
Set-ExcelRange -Address $sheet1.Cells["A:A"] -Width 5
Set-ExcelRange -Address $sheet1.Cells["B1:E1"] -HorizontalAlignment Center -BorderBottom Thick -BorderColor Cyan
Close-ExcelPackage $xlPkg -Show
Examples/TestRestAPI/ShowPesterResults.ps1
function Show-PesterResults {
[Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "", Justification="No suitable singular")]
Param()
$xlfilename = ".\test.xlsx"
Remove-Item $xlfilename -ErrorAction Ignore
$ConditionalText = @()
$ConditionalText += New-ConditionalText -Range "Result" -Text failed -BackgroundColor red -ConditionalTextColor black
$ConditionalText += New-ConditionalText -Range "Result" -Text passed -BackgroundColor green -ConditionalTextColor black
$ConditionalText += New-ConditionalText -Range "Result" -Text pending -BackgroundColor gray -ConditionalTextColor black
$xlParams = @{
Path = $xlfilename
WorkSheetname = 'PesterTests'
ConditionalText = $ConditionalText
PivotRows = 'Result', 'Name'
PivotData = @{'Result' = 'Count'}
IncludePivotTable = $true
AutoSize = $true
AutoNameRange = $true
AutoFilter = $true
Show = $true
}
$(foreach ($result in (Invoke-Pester -PassThru -Show None).TestResult) {
[PSCustomObject]@{
Description = $result.Describe
Name = $result.Name
Result = $result.Result
Messge = $result.FailureMessage
StackTrace = $result.StackTrace
}
}) | Sort-Object Description | Export-Excel @xlParams
}
Examples/TestRestAPI/TestAPIReadXls.ps1
function Test-APIReadXls {
[Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns", "", Justification="False Positive")]
param(
[parameter(Mandatory)]
$XlFilename,
$WorksheetName = 'Sheet1'
)
$testFileName = "{0}.tests.ps1" -f (Get-date).ToString("yyyyMMddHHmmss")
$records = Import-Excel $XlFilename
$params = @{}
$blocks = $(foreach ($record in $records) {
foreach ($propertyName in $record.psobject.properties.name) {
if ($propertyName -notmatch 'ExpectedResult|QueryString') {
$params.$propertyName = $record.$propertyName
}
}
if ($record.QueryString) {
$params.Uri += "?{0}" -f $record.QueryString
}
@"
it "Should have the expected result '$($record.ExpectedResult)'" {
`$target = '$($params | ConvertTo-Json -compress)' | ConvertFrom-Json
`$target.psobject.Properties.name | ForEach-Object {`$p=@{}} {`$p.`$_=`$(`$target.`$_)}
Invoke-RestMethod @p | Should -Be '$($record.ExpectedResult)'
}
"@
})
@"
Describe "Tests from $($XlFilename) in $($WorksheetName)" {
$($blocks)
}
"@ | Set-Content -Encoding Ascii $testFileName
(Get-ChildItem $testFileName).FullName
}
Examples/TestRestAPI/TryIt.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
. $PSScriptRoot\TestAPIReadXls.ps1
Test-APIReadXls $PSScriptRoot\testlist.xlsx | Foreach-Object {
Invoke-Pester -Script $_.fullname -PassThru -Show None
}
Examples/TryMultiplePivotTables.ps1
# To ship, is to choose
#ipmo .\ImportExcel.psd1 -Force
$pt=[ordered]@{}
$pt.ServiceInfo=@{
SourceWorkSheet='Services'
PivotRows = "Status"
PivotData= @{'Status'='count'}
IncludePivotChart=$true
ChartType='BarClustered3D'
}
$pt.ProcessInfo=@{
SourceWorkSheet='Processes'
PivotRows = "Company"
PivotData= @{'Company'='count'}
IncludePivotChart=$true
ChartType='PieExploded3D'
}
$gsv=Get-Service | Select-Object status, Name, displayName, starttype
$ps=Get-Process | Select-Object Name,Company, Handles
$file = "c:\temp\testPT.xlsx"
Remove-Item $file -ErrorAction Ignore
$gsv| Export-Excel -Path $file -AutoSize -WorkSheetname Services
$ps | Export-Excel -Path $file -AutoSize -WorkSheetname Processes -PivotTableDefinition $pt -Show
Examples/TryMultiplePivotTablesFromOneSheet.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
$file = "C:\Temp\test.xlsx"
Remove-Item $file -ErrorAction Ignore -Force
$base = @{
SourceWorkSheet = 'gsv'
PivotData = @{'Status' = 'count'}
IncludePivotChart = $true
# ChartType = 'BarClustered3D'
}
$ptd = [ordered]@{}
# $ptd.gpt1 = $base + @{ PivotRows = "ServiceType" }
# $ptd.gpt2 = $base + @{ PivotRows = "Status" }
# $ptd.gpt3 = $base + @{ PivotRows = "StartType" }
# $ptd.gpt4 = $base + @{ PivotRows = "CanStop" }
$ptd += New-PivotTableDefinition @base servicetype -PivotRows servicetype -ChartType Area3D
$ptd += New-PivotTableDefinition @base status -PivotRows status -ChartType PieExploded3D
$ptd += New-PivotTableDefinition @base starttype -PivotRows starttype -ChartType BarClustered3D
$ptd += New-PivotTableDefinition @base canstop -PivotRows canstop -ChartType ConeColStacked
Get-Service | Export-Excel -path $file -WorkSheetname gsv -Show -PivotTableDefinition $ptd
Examples/VBA/AddModuleMultipleWorksheetVBA.ps1
$xlfile = "$env:temp\test.xlsm"
Remove-Item $xlfile -ErrorAction SilentlyContinue
ConvertFrom-Csv @"
Region,Item,TotalSold
West,screwdriver,98
West,kiwi,19
North,kiwi,47
West,screws,48
West,avocado,52
East,avocado,40
South,drill,61
North,orange,92
South,drill,29
South,saw,36
"@ | Export-Excel $xlfile -TableName 'Sales' -WorksheetName 'Sales' -AutoSize
$Excel = ConvertFrom-Csv @"
Supplier,Item,TotalBought
Hardware,screwdriver,98
Groceries,kiwi,19
Hardware,screws,48
Groceries,avocado,52
Hardware,drill,61
Groceries,orange,92
Hardware,drill,29
HArdware,saw,36
"@ | Export-Excel $xlfile -TableName 'Purchases' -WorksheetName 'Purchases' -PassThru -AutoSize
$wb = $Excel.Workbook
$wb.CreateVBAProject()
# Create a module with a sub to highlight the selected row & column of the active table.
# https://docs.microsoft.com/en-gb/office/vba/excel/Concepts/Cells-and-Ranges/highlight-the-active-cell-row-or-column
$codeModule = @"
Public Sub HighlightSelection(ByVal Target As Range)
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
With ActiveCell
' Highlight the row and column that contain the active cell, within the current region
Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 38
Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 24
End With
Application.ScreenUpdating = True
End Sub
"@
$module = $wb.VbaProject.Modules.AddModule("PSExcelModule")
$module.Code = $codeModule
# Add a call to the row & column highlight sub on each worksheet.
$codeSheet = @"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
HighlightSelection Target
End Sub
"@
foreach ($sheet in $wb.Worksheets) {
$sheet.CodeModule.Code = $codeSheet
}
Close-ExcelPackage $Excel -Show
Examples/VBA/AddWorksheetVBA.ps1
$xlfile = "$env:temp\test.xlsm"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$Excel = ConvertFrom-Csv @"
Region,Item,TotalSold
West,screwdriver,98
West,kiwi,19
North,kiwi,47
West,screws,48
West,avocado,52
East,avocado,40
South,drill,61
North,orange,92
South,drill,29
South,saw,36
"@ | Export-Excel $xlfile -TableName 'Sales' -WorksheetName 'Sales' -AutoSize -PassThru
$wb = $Excel.Workbook
$sheet = $wb.Worksheets["Sales"]
$wb.CreateVBAProject()
# Add a sub to the 'Worksheet_SelectionChange' event of the worksheet to highlight the selected row & column of the active table.
# https://docs.microsoft.com/en-gb/office/vba/excel/Concepts/Cells-and-Ranges/highlight-the-active-cell-row-or-column
$code = @"
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
With ActiveCell
' Highlight the row and column that contain the active cell, within the current region
Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 38
Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 24
End With
Application.ScreenUpdating = True
End Sub
"@
$sheet.CodeModule.Code = $code
Close-ExcelPackage $Excel -Show
Examples/VBA/ChangePivotTablesVBA.ps1
<#
Excel VBA macro which changes all PivotTables in the workbook to Tabular form, disables subtotals and repeats item labels.
https://github.com/dfinke/ImportExcel/issues/1196#issuecomment-1156320581
#>
$ExcelFile = "$ENV:TEMP\test.xlsm"
Remove-Item -Path $ExcelFile -ErrorAction SilentlyContinue
$Macro = @"
Private Sub Workbook_Open()
'
' ChangePivotTables Macro
' Runs when the Excel workbook is opened.
'
' Changes all PivotTables in the workbook to Tabular form, repeats labels
' and disables Subtotals.
'
' Declare variables
Dim Ws As Worksheet
Dim Pt As PivotTable
Dim Pf As PivotField
' Disable screen updates
Application.ScreenUpdating = False
' Continue even if an error occurs
On Error Resume Next
For Each Ws In ActiveWorkbook.Worksheets
For Each Pt In Ws.PivotTables
Pt.RowAxisLayout xlTabularRow
Pt.RepeatAllLabels xlRepeatLabels
For Each Pf In Pt.PivotFields
Pf.Subtotals(1) = False
Next
Next
Next
Application.ScreenUpdating = True
End Sub
"@
$Data = ConvertFrom-Csv -InputObject @"
Region,Item,TotalSold
West,screwdriver,98
West,kiwi,19
North,kiwi,47
West,screws,48
West,avocado,52
East,avocado,40
South,drill,61
North,orange,92
South,drill,29
South,saw,36
"@
$ExcelPackage = $Data | Export-Excel -Path $ExcelFile -TableName "Sales" -WorksheetName "Sales" -AutoSize -PassThru
# Add Macro to the ThisWorkbook module
$ExcelPackage.Workbook.CreateVBAProject()
$VBAThisWorkbookModule = $ExcelPackage.Workbook.VbaProject.Modules | Where-Object -FilterScript { $_.Name -eq "ThisWorkbook" }
$VBAThisWorkbookModule.Code = $Macro
# Create PivotTable example
Add-PivotTable -PivotTableName "SalesPivot" -Address $ExcelPackage.Sales.Cells["E1"] -SourceWorksheet $ExcelPackage.Sales `
-SourceRange $ExcelPackage.Sales.Tables[0].Address -PivotRows "Region", "Item" -PivotData @{ "TotalSold" = "Sum" }
Close-ExcelPackage -ExcelPackage $ExcelPackage -Show
Examples/VBA/HelloWorldVBA.ps1
$xlfile = "$env:temp\test.xlsm"
Remove-Item $xlfile -ErrorAction SilentlyContinue
$Excel = ConvertFrom-Csv @"
Region,Item,TotalSold
West,screwdriver,98
West,kiwi,19
North,kiwi,47
West,screws,48
West,avocado,52
East,avocado,40
South,drill,61
North,orange,92
South,drill,29
South,saw,36
"@ | Export-Excel $xlfile -PassThru -AutoSize
$wb = $Excel.Workbook
$sheet = $wb.Worksheets["Sheet1"]
$wb.CreateVBAProject()
$code = @"
Public Function HelloWorld() As String
HelloWorld = "Hello World"
End Function
Public Function DoSum() As Integer
DoSum = Application.Sum(Range("C:C"))
End Function
"@
$module = $wb.VbaProject.Modules.AddModule("PSExcelModule")
$module.Code = $code
Set-ExcelRange -Worksheet $sheet -Range "h7" -Formula "HelloWorld()" -AutoSize
Set-ExcelRange -Worksheet $sheet -Range "h8" -Formula "DoSum()" -AutoSize
Close-ExcelPackage $Excel -Show
Examples/XlRangeToImage/XlRangeToImage.ps1
try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}
. .\ConvertExcelToImageFile.ps1
$xlFileName = "C:\Temp\testPNG.xlsx"
Remove-Item C:\Temp\testPNG.xlsx -ErrorAction Ignore
$range = @"
Region,Item,Cost
North,Pear,1
South,Apple,2
East,Grapes,3
West,Berry,4
North,Pear,1
South,Apple,2
East,Grapes,3
West,Berry,4
"@ | ConvertFrom-Csv |
Export-Excel $xlFileName -ReturnRange `
-ConditionalText (New-ConditionalText Apple), (New-ConditionalText Berry -ConditionalTextColor White -BackgroundColor Purple)
Convert-ExcelXlRangeToImage -Path $xlFileName -workSheetname sheet1 -range $range -Show