llms-examples.txt

<?xml version="1.0" encoding="UTF-8"?>
<documents>
    <document index='1'>
        <source>Examples/AddImage/Add-ExcelImage.ps1</source>
        <document_content>
            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)
        }
    }
}
        </document_content>
    </document>
    <document index='2'>
        <source>Examples/AddImage/AddImage.ps1</source>
        <document_content>
            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
    }
}

        </document_content>
    </document>
    <document index='3'>
        <source>Examples/AddImage/README.md</source>
        <document_content>
            # 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.

        </document_content>
    </document>
    <document index='4'>
        <source>Examples/AddWorkSheet/AddMultiWorkSheet.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='5'>
        <source>Examples/AddWorkSheet/AddWorkSheet.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='6'>
        <source>Examples/Charts/ChartAndTrendlines.ps1</source>
        <document_content>
            # 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

        </document_content>
    </document>
    <document index='7'>
        <source>Examples/Charts/ChartDataSeparatePage.ps1</source>
        <document_content>
            $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

        </document_content>
    </document>
    <document index='8'>
        <source>Examples/Charts/MultiSeries.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='9'>
        <source>Examples/Charts/MultiSeries1.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='10'>
        <source>Examples/Charts/MultipleCharts.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='11'>
        <source>Examples/Charts/NumberOfVisitors.ps1</source>
        <document_content>
            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


        </document_content>
    </document>
    <document index='12'>
        <source>Examples/Charts/plot.ps1</source>
        <document_content>
            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)
        </document_content>
    </document>
    <document index='13'>
        <source>Examples/CommunityContributions/MultipleWorksheets.ps1</source>
        <document_content>
            <#
    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

        </document_content>
    </document>
    <document index='14'>
        <source>Examples/ConditionalFormatting/CodeGenExamples.ps1</source>
        <document_content>
            "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"
    }

        </document_content>
    </document>
    <document index='15'>
        <source>Examples/ConditionalFormatting/ConditionalFormattingIcontSetOnlyIcon.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='16'>
        <source>Examples/ConditionalFormatting/ConditionalText.ps1</source>
        <document_content>
            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
    )
        </document_content>
    </document>
    <document index='17'>
        <source>Examples/ConditionalFormatting/ContainsBlanks.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='18'>
        <source>Examples/ConditionalFormatting/Databar.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='19'>
        <source>Examples/ConditionalFormatting/FormatCalculations.ps1</source>
        <document_content>
            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 <parameters> .... ; Export-excel -ConditionalText $variable <other parameters>
# the syntax is used is Export-excel -ConditionalText (New-Conditional text <parameters>) <other parameters>


#$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)

        </document_content>
    </document>
    <document index='20'>
        <source>Examples/ConditionalFormatting/GenDates.ps1</source>
        <document_content>
            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'

        </document_content>
    </document>
    <document index='21'>
        <source>Examples/ConditionalFormatting/GetConditionalFormatting.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='22'>
        <source>Examples/ConditionalFormatting/GetProcess.ps1</source>
        <document_content>
            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
        )

        </document_content>
    </document>
    <document index='23'>
        <source>Examples/ConditionalFormatting/Highlight-DiffCells.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='24'>
        <source>Examples/ConditionalFormatting/Highlight-Last7Days.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='25'>
        <source>Examples/ConditionalFormatting/Highlight-LastMonth.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='26'>
        <source>Examples/ConditionalFormatting/Highlight-LastWeek.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='27'>
        <source>Examples/ConditionalFormatting/Highlight-NextMonth.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='28'>
        <source>Examples/ConditionalFormatting/Highlight-NextWeek.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='29'>
        <source>Examples/ConditionalFormatting/Highlight-ThisMonth.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='30'>
        <source>Examples/ConditionalFormatting/Highlight-ThisWeek.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='31'>
        <source>Examples/ConditionalFormatting/Highlight-Today.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='32'>
        <source>Examples/ConditionalFormatting/Highlight-Tomorrow.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='33'>
        <source>Examples/ConditionalFormatting/Highlight-Yesterday.ps1</source>
        <document_content>
            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
    )

        </document_content>
    </document>
    <document index='34'>
        <source>Examples/ConditionalFormatting/HighlightDuplicates.ps1</source>
        <document_content>
            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)
        </document_content>
    </document>
    <document index='35'>
        <source>Examples/ConditionalFormatting/MonthlyTemperatuesDatabar.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='36'>
        <source>Examples/ConditionalFormatting/RangeFormatting.ps1</source>
        <document_content>
            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
    )
        </document_content>
    </document>
    <document index='37'>
        <source>Examples/ConditionalFormatting/SalesReportWithDatabar.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='38'>
        <source>Examples/ConditionalFormatting/TextComparisons.ps1</source>
        <document_content>
                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
    )

        </document_content>
    </document>
    <document index='39'>
        <source>Examples/ConditionalFormatting/Top10-DataBar-TwoColorScale.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='40'>
        <source>Examples/ConvertFrom/ConvertFrom.ps1</source>
        <document_content>
            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"
}
        </document_content>
    </document>
    <document index='41'>
        <source>Examples/CustomNumbers/ShortenNumbers.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='42'>
        <source>Examples/CustomReporting/CustomReport.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='43'>
        <source>Examples/CustomizeExportExcel/Out-Excel.ps1</source>
        <document_content>
            <#
    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

    #>
}
        </document_content>
    </document>
    <document index='44'>
        <source>Examples/ExcelBuiltIns/DSUM.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='45'>
        <source>Examples/ExcelBuiltIns/VLOOKUP.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='46'>
        <source>Examples/ExcelDataValidation/MutipleValidations.ps1</source>
        <document_content>
            #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
        </document_content>
    </document>
    <document index='47'>
        <source>Examples/ExcelToSQLInsert/DemoSQLInsert.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='48'>
        <source>Examples/Experimental/Export-MultipleExcelSheets.ps1</source>
        <document_content>
            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 }
}

        </document_content>
    </document>
    <document index='49'>
        <source>Examples/Experimental/tryExportMultipleExcelSheets.ps1</source>
        <document_content>
            . "$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
        </document_content>
    </document>
    <document index='50'>
        <source>Examples/Extra/Get-ModuleStats.ps1</source>
        <document_content>
            <#
    .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')

        </document_content>
    </document>
    <document index='51'>
        <source>Examples/Fibonacci/ShowFibonacci.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='52'>
        <source>Examples/FormatCellStyles/ApplyFormatInScriptBlock.ps1</source>
        <document_content>
            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
        }
    }
        </document_content>
    </document>
    <document index='53'>
        <source>Examples/FormatCellStyles/ApplyStyle.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='54'>
        <source>Examples/FormatCellStyles/PassInScriptBlock.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='55'>
        <source>Examples/FormatResults/GetAsMarkdownTable.ps1</source>
        <document_content>
            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 
        </document_content>
    </document>
    <document index='56'>
        <source>Examples/FormatResults/GetAsYaml.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='57'>
        <source>Examples/FormatResults/Sample.csv</source>
        <document_content>
            "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"

        </document_content>
    </document>
    <document index='58'>
        <source>Examples/Freeze/FreezePane.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='59'>
        <source>Examples/GenerateData/GenDataForCustomReport.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='60'>
        <source>Examples/Grouping/First10Races.csv</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='61'>
        <source>Examples/Grouping/GroupDateColumn.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='62'>
        <source>Examples/Grouping/GroupDateRow.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='63'>
        <source>Examples/Grouping/GroupNumericColumn.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='64'>
        <source>Examples/Grouping/GroupNumericRow.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='65'>
        <source>Examples/Grouping/TimestampBucket.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='66'>
        <source>Examples/HyperLinks/First10Races.csv</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='67'>
        <source>Examples/HyperLinks/Hyperlinks.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='68'>
        <source>Examples/HyperLinks/Races.ps1</source>
        <document_content>
            
#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


        </document_content>
    </document>
    <document index='69'>
        <source>Examples/Import-Excel/ImportMultipleSheetsAsArray.ps1</source>
        <document_content>
            Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 -Force

$xlfile = "$PSScriptRoot\yearlySales.xlsx"

$result = Import-Excel -Path $xlfile -WorksheetName * -Raw

$result | Measure-Object
        </document_content>
    </document>
    <document index='70'>
        <source>Examples/Import-Excel/ImportMultipleSheetsAsHashtable.ps1</source>
        <document_content>
            Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 -Force

$xlfile = "$PSScriptRoot\yearlySales.xlsx"

$result = Import-Excel -Path $xlfile -WorksheetName *

foreach ($sheet in $result.Values) {
    $sheet
}
        </document_content>
    </document>
    <document index='71'>
        <source>Examples/ImportByColumns/import-by-columns.ps1</source>
        <document_content>
            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}
        }
    }
}

        </document_content>
    </document>
    <document index='72'>
        <source>Examples/ImportColumns/ImportColumns.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='73'>
        <source>Examples/ImportHtml/DemoGraphics.ps1</source>
        <document_content>
            try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}


Import-Html "http://en.wikipedia.org/wiki/Demographics_of_India" 4
        </document_content>
    </document>
    <document index='74'>
        <source>Examples/ImportHtml/PeriodicElements.ps1</source>
        <document_content>
            try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

Import-Html "http://www.science.co.il/PTelements.asp" 1
        </document_content>
    </document>
    <document index='75'>
        <source>Examples/ImportHtml/StarTrek.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='76'>
        <source>Examples/Index - Music.ps1</source>
        <document_content>
            #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

        </document_content>
    </document>
    <document index='77'>
        <source>Examples/InteractWithOtherModules/Pester/Analyze_that.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='78'>
        <source>Examples/InteractWithOtherModules/ScriptAnalyzer/Analyze_this.ps1</source>
        <document_content>
            <#
  .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
}

        </document_content>
    </document>
    <document index='79'>
        <source>Examples/InvokeExcelQuery/Examples.ps1</source>
        <document_content>
            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  
}

        </document_content>
    </document>
    <document index='80'>
        <source>Examples/JoinWorksheet/EastSales.csv</source>
        <document_content>
            "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"
        </document_content>
    </document>
    <document index='81'>
        <source>Examples/JoinWorksheet/Join-Worksheet.sample.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='82'>
        <source>Examples/JoinWorksheet/Join-worksheet-blocks.sample.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='83'>
        <source>Examples/JoinWorksheet/JoinSalesData.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='84'>
        <source>Examples/JoinWorksheet/NorthSales.csv</source>
        <document_content>
            "Region","Item","UnitSold","UnitCost"
"North","Apple","40","0.68"
"North","Kale","55","0.35"
"North","Banana","33","0.31"
"North","Pear","29","0.74"
        </document_content>
    </document>
    <document index='85'>
        <source>Examples/JoinWorksheet/SouthSales.csv</source>
        <document_content>
            "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"
        </document_content>
    </document>
    <document index='86'>
        <source>Examples/JoinWorksheet/WestSales.csv</source>
        <document_content>
            "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"
        </document_content>
    </document>
    <document index='87'>
        <source>Examples/JustCharts/CentralLimitTheorem.ps1</source>
        <document_content>
            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)
        </document_content>
    </document>
    <document index='88'>
        <source>Examples/JustCharts/PieChartHandles.ps1</source>
        <document_content>
            # 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)

        </document_content>
    </document>
    <document index='89'>
        <source>Examples/JustCharts/PieChartPM.ps1</source>
        <document_content>
            # 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)


        </document_content>
    </document>
    <document index='90'>
        <source>Examples/JustCharts/TargetData.csv</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='91'>
        <source>Examples/JustCharts/TargetData.ps1</source>
        <document_content>
            $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

        </document_content>
    </document>
    <document index='92'>
        <source>Examples/JustCharts/TryBarChart.ps1</source>
        <document_content>
            try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

BarChart (.\TargetData.ps1) "A BarChart"
        </document_content>
    </document>
    <document index='93'>
        <source>Examples/JustCharts/TryColumnChart.ps1</source>
        <document_content>
            try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

ColumnChart (.\TargetData.ps1) "A ColumnChart"

        </document_content>
    </document>
    <document index='94'>
        <source>Examples/JustCharts/TryPieChart.ps1</source>
        <document_content>
            try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

PieChart (.\TargetData.ps1) "A PieChart"
        </document_content>
    </document>
    <document index='95'>
        <source>Examples/MergeWorkSheet/MergeCSV.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='96'>
        <source>Examples/MergeWorkSheet/Merge_2_Servers_Services.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='97'>
        <source>Examples/MergeWorkSheet/Merge_3_Servers_Services.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='98'>
        <source>Examples/MortgageCalculator/MortgageCalculator.ps1</source>
        <document_content>
            <#
  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
        </document_content>
    </document>
    <document index='99'>
        <source>Examples/MoveSheets/MoveSheets.ps1</source>
        <document_content>
            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'
        </document_content>
    </document>
    <document index='100'>
        <source>Examples/Nasa/FireBalls.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='101'>
        <source>Examples/New-PSItem.ps1</source>
        <document_content>
            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
}
        </document_content>
    </document>
    <document index='102'>
        <source>Examples/NumberFormat/ColorizeNumbers.ps1</source>
        <document_content>
            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"

        </document_content>
    </document>
    <document index='103'>
        <source>Examples/NumberFormat/CurrencyFormat.ps1</source>
        <document_content>
            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'

        </document_content>
    </document>
    <document index='104'>
        <source>Examples/NumberFormat/PercentagFormat.ps1</source>
        <document_content>
            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%"

        </document_content>
    </document>
    <document index='105'>
        <source>Examples/NumberFormat/PosNegNumbers.ps1</source>
        <document_content>
            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"
        </document_content>
    </document>
    <document index='106'>
        <source>Examples/NumberFormat/Win32LogicalDisk.ps1</source>
        <document_content>
            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"
        </document_content>
    </document>
    <document index='107'>
        <source>Examples/NumberFormat/Win32LogicalDiskFormatted.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='108'>
        <source>Examples/OpenExcelPackage/EnableFeatures.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='109'>
        <source>Examples/OutTabulator/demo.txt</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='110'>
        <source>Examples/OutTabulator/start-demo.ps1</source>
        <document_content>
            ## 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 @"
<Demo Started :: $(split-path $file -leaf)>$(' ' * ($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 "<Skipping Line>"
		}
		"p" { # Previous
			Write-Host -Fore $promptColor "<Back one Line>"
			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 "<eXecute Remaining Lines>"
			$_i-- # Repeat this line, and then just blow through the rest
		}
		"q" { # Quit
			Write-Host -Fore $promptColor "<Quiting demo>"
			$_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 "<Suspending demo - type 'Exit' to resume>"
			$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 $(
   "<Demo Complete -- $(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.ToLongTimeString())
Write-Host -Fore $promptColor $([DateTime]::now)
Write-Host
        </document_content>
    </document>
    <document index='111'>
        <source>Examples/OutTabulator/targetout.html</source>
        <document_content>
            <!doctype html>

<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Out-TabulatorView</title>
</head>
<body>

<script type="text/javascript" src="file:///C:\Program Files\WindowsPowerShell\Modules\OutTabulatorView\js\jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="file:///C:\Program Files\WindowsPowerShell\Modules\OutTabulatorView\js\jquery-ui.min.js"></script>
<script type="text/javascript" src="file:///C:\Program Files\WindowsPowerShell\Modules\OutTabulatorView\js\tabulator.min.js"></script>
<script type="text/javascript" src="file:///C:\Program Files\WindowsPowerShell\Modules\OutTabulatorView\js\jquery.sparkline.min.js"></script>

<link href="file:///C:\Program Files\WindowsPowerShell\Modules\OutTabulatorView\css\tabulator.min.css" rel="stylesheet">


<div id="example-table"></div>

<script type="text/javascript">
    var lineFormatter = function(cell, formatterParams){
        setTimeout(function(){ //give cell enough time to be added to the DOM before calling sparkline formatter
        	cell.getElement().sparkline(cell.getValue(), {width:"100%", type:"line", disableTooltips:true});
        }, 10);
    };

  var tabledata = [{"Name":"Alan Francis","Progress":90,"Activity":[4,17,11,7,6,12,14,13,11,10,9,6,11,12,0,5,12,14,18,11],"Gender":"male","Rating":3,"Color":"blue","dob":"07/08/1972","Driver":"true"},{"Name":"Brendon Philips","Progress":100,"Activity":[3,7,9,1,4,8,2,6,4,2,1,3,1,3,3,1,1,3,1,3],"Gender":"male","Rating":1,"Color":"orange","dob":"01/08/1980","Driver":""},{"Name":"Christine Lobowski","Progress":42,"Activity":[1,2,5,4,1,16,4,2,1,3,3,7,9,1,4,8,2,6,4,2],"Gender":"female","Rating":0,"Color":"green","dob":"22/05/1982","Driver":"true"},{"Name":"Ed White","Progress":70,"Activity":[20,17,15,11,16,9,4,17,11,12,0,5,12,14,18,11,12,14,20,12],"Gender":"male","Rating":0,"Color":"yellow","dob":"19/06/1976","Driver":""},{"Name":"Emily Sykes","Progress":42,"Activity":[11,15,19,20,17,16,16,5,3,2,1,2,3,4,5,4,2,5,9,8],"Gender":"female","Rating":1,"Color":"maroon","dob":"11/11/1970","Driver":""},{"Name":"Emma Netwon","Progress":40,"Activity":[3,7,9,1,4,8,3,7,9,1,4,8,2,6,4,2,2,6,4,2],"Gender":"female","Rating":4,"Color":"brown","dob":"07/10/1963","Driver":"true"},{"Name":"Frank Harbours","Progress":38,"Activity":[20,17,15,11,16,9,12,14,20,12,11,7,6,12,14,13,11,10,9,6],"Gender":"male","Rating":4,"Color":"red","dob":"12/05/1966","Driver":1},{"Name":"Gemma Jane","Progress":60,"Activity":[4,17,11,12,0,5,12,14,18,11,11,15,19,20,17,16,16,5,3,2],"Gender":"female","Rating":0,"Color":"red","dob":"22/05/1982","Driver":"true"},{"Name":"Hannah Farnsworth","Progress":30,"Activity":[1,2,5,4,1,16,10,12,14,16,13,9,7,11,10,13,4,2,1,3],"Gender":"female","Rating":1,"Color":"pink","dob":"11/02/1991","Driver":""},{"Name":"James Newman","Progress":73,"Activity":[1,20,5,3,10,13,17,15,9,11,1,2,3,4,5,4,2,5,9,8],"Gender":"male","Rating":5,"Color":"red","dob":"22/03/1998","Driver":""},{"Name":"Jamie Newhart","Progress":23,"Activity":[11,7,6,12,14,13,11,10,9,6,4,17,11,12,0,5,12,14,18,11],"Gender":"male","Rating":3,"Color":"green","dob":"14/05/1985","Driver":"true"},{"Name":"Jenny Green","Progress":56,"Activity":[11,15,19,20,17,15,11,16,9,12,14,20,12,20,17,16,16,5,3,2],"Gender":"female","Rating":4,"Color":"indigo","dob":"12/11/1998","Driver":"true"},{"Name":"John Phillips","Progress":80,"Activity":[11,7,6,12,14,1,20,5,3,10,13,17,15,9,1,13,11,10,9,6],"Gender":"male","Rating":1,"Color":"green","dob":"24/09/1950","Driver":"true"},{"Name":"Margret Marmajuke","Progress":16,"Activity":[1,3,1,3,3,1,1,3,1,3,20,17,15,11,16,9,12,14,20,12],"Gender":"female","Rating":5,"Color":"yellow","dob":"31/01/1999","Driver":""},{"Name":"Martin Barryman","Progress":20,"Activity":[1,2,3,4,5,4,11,7,6,12,14,13,11,10,9,6,2,5,9,8],"Gender":"male","Rating":5,"Color":"violet","dob":"04/04/2001","Driver":""},{"Name":"Mary May","Progress":1,"Activity":[10,12,14,16,13,9,7,11,10,13,1,2,5,4,1,16,4,2,1,3],"Gender":"female","Rating":2,"Color":"blue","dob":"14/05/1982","Driver":"true"},{"Name":"Oli Bob","Progress":12,"Activity":[1,20,5,3,10,13,17,15,9,11,10,12,14,16,13,9,7,11,10,13],"Gender":"male","Rating":1,"Color":"red","dob":"19/02/1984","Driver":1},{"Name":"Paul Branderson","Progress":60,"Activity":[1,3,1,3,3,1,11,15,19,20,17,16,16,5,3,2,1,3,1,3],"Gender":"male","Rating":5,"Color":"orange","dob":"01/01/1982","Driver":""},{"Name":"Victoria Bath","Progress":20,"Activity":[10,12,14,16,13,9,7,1,2,3,4,5,4,2,5,9,8,11,10,13],"Gender":"female","Rating":2,"Color":"purple","dob":"22/03/1986","Driver":null}]

  $("#example-table").tabulator(
        {
    "outFile":  ".\\targetout.html",
    "columns":  [
                    {
                        "field":  "Name",
                        "title":  "Name"
                    },
                    {
                        "field":  "Progress",
                        "title":  "Progress",
                        "formatter":  "progress"
                    },
                    {
                        "field":  "Activity",
                        "title":  "Activity",
                        "formatter":  lineFormatter
                    },
                    {
                        "field":  "Gender",
                        "title":  "Gender"
                    },
                    {
                        "field":  "Rating",
                        "title":  "Rating",
                        "formatter":  "star"
                    },
                    {
                        "field":  "Color",
                        "title":  "Color"
                    },
                    {
                        "field":  "dob",
                        "title":  "dob"
                    },
                    {
                        "field":  "Driver",
                        "title":  "Driver",
                        "formatter":  "tickCross"
                    }
                ],
    "groupBy":  "Gender"

});

$("#example-table").tabulator("setData", tabledata);

</script>
</body>
</html>

        </document_content>
    </document>
    <document index='112'>
        <source>Examples/OutTabulator/tryConvertFromExcel.ps1</source>
        <document_content>
            [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
        </document_content>
    </document>
    <document index='113'>
        <source>Examples/PassThru/TryPassThru.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='114'>
        <source>Examples/PassThru/sales.csv</source>
        <document_content>
            "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"
        </document_content>
    </document>
    <document index='115'>
        <source>Examples/Pester-To-XLSx.ps1</source>
        <document_content>
            [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 <vars> not filled in
                          Results
                             Test-case  [description] - name as rendered for display with <vars> 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

        </document_content>
    </document>
    <document index='116'>
        <source>Examples/PesterTestReport/Pester test report.ps1</source>
        <document_content>
            #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'
    }
}
        </document_content>
    </document>
    <document index='117'>
        <source>Examples/PivotTable/MultiplePivotTables.ps1</source>
        <document_content>
            $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
        </document_content>
    </document>
    <document index='118'>
        <source>Examples/PivotTable/PivotTableWithName.ps1</source>
        <document_content>
            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
#>
        </document_content>
    </document>
    <document index='119'>
        <source>Examples/PivotTable/TableAndPivotTable.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='120'>
        <source>Examples/PivotTableFilters/testPivotFilter.ps1</source>
        <document_content>
            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
#>
        </document_content>
    </document>
    <document index='121'>
        <source>Examples/Plot/PlotCos.ps1</source>
        <document_content>
            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()
        </document_content>
    </document>
    <document index='122'>
        <source>Examples/PsGallery.ps1</source>
        <document_content>
            
$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,'<table class="width-hundred-percent">.*?</table>', [System.Text.RegularExpressions.RegexOptions]::Singleline) | foreach {
        $name = [regex]::Match($_, "(?<=<h1><a href=.*?>).*(?=</a></h1>)").value
        $n =    [regex]::replace($_,'^.*By:\s*<li role="menuitem">','', [System.Text.RegularExpressions.RegexOptions]::Singleline)
        $n =    [regex]::replace($n,'</div>.*$','', [System.Text.RegularExpressions.RegexOptions]::Singleline)
        $by =   [regex]::match($n,'(?<=">).*(?=</a>)').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
        </document_content>
    </document>
    <document index='123'>
        <source>Examples/ReadAllSheets/GenerateXlsx.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='124'>
        <source>Examples/ReadAllSheets/Get-ExcelSheets.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='125'>
        <source>Examples/ReadAllSheets/ReadAllSheets.ps1</source>
        <document_content>
            $xlfile = "$env:TEMP\MultipleSheets.xlsx"

.\GenerateXlsx.ps1 $xlfile
.\Get-ExcelSheets.ps1 $xlfile
        </document_content>
    </document>
    <document index='126'>
        <source>Examples/SQL+FillColumns+Pivot/Example.ps1</source>
        <document_content>
            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


        </document_content>
    </document>
    <document index='127'>
        <source>Examples/SQL+FillColumns+Pivot/Example2.ps1</source>
        <document_content>
            #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
        </document_content>
    </document>
    <document index='128'>
        <source>Examples/SetColumnBackgroundColor/SetColumnBackgroundColor.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='129'>
        <source>Examples/Sparklines/SalesByQuarter.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='130'>
        <source>Examples/Sparklines/Sparklines.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='131'>
        <source>Examples/SpreadsheetCells/CalculatedFields.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='132'>
        <source>Examples/SpreadsheetCells/ExcelFormulasUsingAddMember.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='133'>
        <source>Examples/SpreadsheetCells/ExcelFunctions.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='134'>
        <source>Examples/SpreadsheetCells/HyperLink.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='135'>
        <source>Examples/Stocks/Get-StockInfo.ps1</source>
        <document_content>
            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
}

        </document_content>
    </document>
    <document index='136'>
        <source>Examples/Stocks/GetStocksAvgTotVol.ps1</source>
        <document_content>
            . $PSScriptRoot\Get-StockInfo.ps1

Get-StockInfo -symbols "msft,ibm,ge,xom,aapl" -dataPlot avgTotalVolume
        </document_content>
    </document>
    <document index='137'>
        <source>Examples/Styles/MultipleStyles.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='138'>
        <source>Examples/Styles/NewExcelStyle.ps1</source>
        <document_content>
            # 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
        </document_content>
    </document>
    <document index='139'>
        <source>Examples/Subtotals.ps1</source>
        <document_content>
            $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
        </document_content>
    </document>
    <document index='140'>
        <source>Examples/Tables/MultipleTables.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
    <document index='141'>
        <source>Examples/Tables/SalesData-WithTotalRow.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='142'>
        <source>Examples/Tables/TotalsRow.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='143'>
        <source>Examples/TestRestAPI/PSExcelPester.psm1</source>
        <document_content>
            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
    }
}
        </document_content>
    </document>
    <document index='144'>
        <source>Examples/TestRestAPI/RunAndShowUnitTests.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='145'>
        <source>Examples/TestRestAPI/ShowPesterResults.ps1</source>
        <document_content>
            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
}
        </document_content>
    </document>
    <document index='146'>
        <source>Examples/TestRestAPI/TestAPIReadXls.ps1</source>
        <document_content>
            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
}
        </document_content>
    </document>
    <document index='147'>
        <source>Examples/TestRestAPI/TryIt.ps1</source>
        <document_content>
            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
}
        </document_content>
    </document>
    <document index='148'>
        <source>Examples/TryMultiplePivotTables.ps1</source>
        <document_content>
            # 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

        </document_content>
    </document>
    <document index='149'>
        <source>Examples/TryMultiplePivotTablesFromOneSheet.ps1</source>
        <document_content>
            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
        </document_content>
    </document>
    <document index='150'>
        <source>Examples/VBA/AddModuleMultipleWorksheetVBA.ps1</source>
        <document_content>
            $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
        </document_content>
    </document>
    <document index='151'>
        <source>Examples/VBA/AddWorksheetVBA.ps1</source>
        <document_content>
            $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
        </document_content>
    </document>
    <document index='152'>
        <source>Examples/VBA/ChangePivotTablesVBA.ps1</source>
        <document_content>
            <#
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
        </document_content>
    </document>
    <document index='153'>
        <source>Examples/VBA/HelloWorldVBA.ps1</source>
        <document_content>
            $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
        </document_content>
    </document>
    <document index='154'>
        <source>Examples/XlRangeToImage/XlRangeToImage.ps1</source>
        <document_content>
            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

        </document_content>
    </document>
</documents>