This project is read-only.

Installation : 

For version 0.5+ :
Just launch the installer ;-)

For version prior to 0.5 :
Unzip the zip file in the standard Powershell Module Directory %systemroot%\System32\WindowsPowerShell\v1.0\Modules so the psd1, psm1, ps1 files should be located here %systemroot%\System32\WindowsPowerShell\v1.0\Modules\ExcelPSLib\

Download the EPPLUS Dynamic Link Library here https://epplus.codeplex.com/downloads/get/418377 and put it within %systemroot%\System32\WindowsPowerShell\v1.0\Modules\ExcelPSLib\

Cmdlets Available :

Add-OOXMLConditionalFormatting [-WorkSheet] <ExcelWorksheet> [-Addresses] <String[]> [-RuleType] {BeginsWith | ContainsBlanks | ContainsErrors | ContainsText | EndsWith
| Equal | Expression | GreaterThan | GreaterThanOrEqual | LessThan | LessThanOrEqual | NotContainsBlanks | NotContainsErrors | NotContainsText | NotEqual} [-StyleSheet]
<ExcelNamedStyleXml> [-ConditionValue] <String> [<CommonParameters>]

Add-OOXMLWorksheet [[-DefColWidth] <Int32>] [[-DefRowHeight] <Int32>] [[-AutofilterRange] <String>] [-WorkSheetName] <String> [-ExcelInstance] <ExcelPackage>
[<CommonParameters>]

Convert-OOXMLCellsCoordinates -StartRow <Int32> -StartCol <Int32> [<CommonParameters>]
Convert-OOXMLCellsCoordinates -StartRow <Int32> -StartCol <Int32> -EndRow <Int32> -EndCol <Int32> [<CommonParameters>]

Convert-OOXMLOldScripts [-InputFile] <String> [-OutputFile] <String> [<CommonParameters>]

Export-OOXML [-InputObject] <Object> [-FileFullPath] <String> [[-DocumentName] <String>] [[-WorksheetName] <String>] [[-ConditionalFormatings] <Object[]>] [-AutoFit]
[<CommonParameters>]

Get-OOXMLColumnString [[-ColNumber] <Int32>] [<CommonParameters>]

Get-OOXMLConditonalFormattingCustomObject [-Name] <String> [-Style] <String> [-Condition] {BeginsWith | ContainsBlanks | ContainsErrors | ContainsText | EndsWith |
Equal | Expression | GreaterThan | GreaterThanOrEqual | LessThan | LessThanOrEqual | NotContainsBlanks | NotContainsErrors | NotContainsText | NotEqual} [-Value]
<String> [<CommonParameters>]

Get-OOXMLDeprecatedCommand [<CommonParameters>]

Get-OOXMLHelp [<CommonParameters>]

Get-OOXMLWorkbook [-ExcelInstance] <ExcelPackage> [<CommonParameters>]

New-OOXMLPackage [-Author] <String> [-Title] <String> [[-Comment] <String>] [[-Path] <String>] [<CommonParameters>]

New-OOXMLPivotTable [-WorkSheet] <ExcelWorksheet> [-Origin] <String> [-Datas] <String> [-Name] <String> [<CommonParameters>]

New-OOXMLStyleSheet [-WorkBook] <ExcelWorkbook> [-Name] <String> [[-HAlign] {General | Left | Center | CenterContinuous | Right | Fill | Distributed | Justify}]
[[-VAlign] {Top | Center | Bottom | Distributed | Justify}] [[-NFormat] <String>] [-Wrap] [-Shrink] [-Locked] [-Bold] [-Italic] [-Underline] [-Strike] [[-Size]
<Single>] [[-ForeGroundColor] <Color>] [[-FillType] {None | Solid | DarkGray | MediumGray | LightGray | Gray125 | Gray0625 | DarkVertical | DarkHorizontal | DarkDown |
DarkUp | DarkGrid | DarkTrellis | LightVertical | LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis}] [[-BackGroundColor] <Color>] [[-borderStyle] {None
| Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium | Double}] [[-BorderColor] <Color>]
[<CommonParameters>]

Save-OOXMLPackage [[-FileFullPath] <String>] [-ExcelInstance] <ExcelPackage> [-Dispose] [<CommonParameters>]

Select-OOXMLWorkSheet -WorkBook <ExcelWorkbook> -WorkSheetNumber <Int32> [<CommonParameters>]
Select-OOXMLWorkSheet -WorkBook <ExcelWorkbook> -WorkSheetName <String> [<CommonParameters>]

Set-OOXMLPrinterSettings [-HorizontalCentered] [-VerticalCentered] [-ShowGridLines] [-BlackAndWhite] [-FitToPage] [[-RowRange] <ExcelRangeBase>] [[-ColRange]
<ExcelRangeBase>] [-WorkSheet] <ExcelWorksheet> [<CommonParameters>]

Set-OOXMLRangeBorder [-BorderStyle] {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium |
Double} [-CellRange] <ExcelRangeBase> [-Color] <Color> [<CommonParameters>]

Set-OOXMLRangeBorderBottom [-borderStyle] {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick |
Medium | Double} [-cellRange] <ExcelRangeBase> [-color] <Color> [<CommonParameters>]

Set-OOXMLRangeBorderLeft [-borderStyle] {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium
| Double} [-cellRange] <ExcelRangeBase> [[-color] <Color>] [<CommonParameters>]

Set-OOXMLRangeBorderRight [-borderStyle] {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium
| Double} [-cellRange] <ExcelRangeBase> [-color] <Color> [<CommonParameters>]

Set-OOXMLRangeBorderTop [-borderStyle] {None | Hair | Dotted | DashDot | Thin | DashDotDot | Dashed | MediumDashDotDot | MediumDashed | MediumDashDot | Thick | Medium |
Double} [-cellRange] <ExcelRangeBase> [-color] <Color> [<CommonParameters>]

Set-OOXMLRangeFill [-Type] {None | Solid | DarkGray | MediumGray | LightGray | Gray125 | Gray0625 | DarkVertical | DarkHorizontal | DarkDown | DarkUp | DarkGrid |
DarkTrellis | LightVertical | LightHorizontal | LightDown | LightUp | LightGrid | LightTrellis} [-color] <Color> [-cellRange] <ExcelRangeBase> [-Pass]
[<CommonParameters>]

Set-OOXMLRangeFont [-bold] [-italic] [-underline] [-strike] [[-size] <Single>] [[-color] <Color>] [-cellRange] <ExcelRangeBase> [<CommonParameters>]

Set-OOXMLRangeTextOptions [[-HAlign] {General | Left | Center | CenterContinuous | Right | Fill | Distributed | Justify}] [[-VAlign] {Top | Center | Bottom |
Distributed | Justify}] [[-NFormat] <String>] [-Wrap] [-Shrink] [-Locked] [-cellRange] <ExcelRangeBase> [<CommonParameters>]

Set-OOXMLRangeValue [-Row] <String> [-Col] <String> [-Value] <Object> [-WorkSheet] <ExcelWorksheet> [-Uri] [[-StyleSheet] <ExcelNamedStyleXml>] [[-StyleSheetName]
<String>] [[-OutlineLevel] <Int32>] [<CommonParameters>]

Set-OOXMLStyleSheet -cellRange <ExcelRangeBase> -StyleSheetName <String> [<CommonParameters>]
Set-OOXMLStyleSheet -cellRange <ExcelRangeBase> -StyleSheet <ExcelNamedStyleXml> [<CommonParameters>]

 

 

Usage Example :

 

 
<!--

Code highlighting produced by Actipro SyntaxEditor
http://www.ActiproSoftware.com/Products/DotNet/

-->#Require ExcelPSLib 0.5.5
Import-Module ExcelPSLib -Force

$ComputerList = @("LOCALHOST")

$RowPosition = 2

$OutputFileName = "c:\temp\EXCELPSLIB_Demo.xlsx"
[OfficeOpenXml.ExcelPackage]$excel = New-OOXMLPackage -author "Avalon77" -title "ComputerInfos"
[OfficeOpenXml.ExcelWorkbook]$book = $excel | Get-OOXMLWorkbook

$excel | Add-OOXMLWorksheet -WorkSheetName "Local HDD" -AutofilterRange "A2:E2"
$sheet = $book | Select-OOXMLWorkSheet -WorkSheetNumber 1

$StyleGreen = New-OOXMLStyleSheet -WorkBook $book -Name "GirlStyle" -Bold -ForeGroundColor Black -FillType Solid -BackGroundColor Green -borderStyle Thin -BorderColor Black -NFormat "#,##0.00"
$StyleRed = New-OOXMLStyleSheet -WorkBook $book -Name "BoyStyle" -Bold -ForeGroundColor Black -FillType Solid -BackGroundColor Red -borderStyle Thin -BorderColor Black -NFormat "#,##0.00"
$StyleHeader = New-OOXMLStyleSheet -WorkBook $book -Name "HeaderStyle" -Bold -ForeGroundColor White -BackGroundColor Black -Size 14 -HAlign Center -VAlign Center -FillType Solid
$StyleNormal = New-OOXMLStyleSheet -WorkBook $book -Name "NormalStyle" -borderStyle Thin -BorderColor Black
$StyleNumber = New-OOXMLStyleSheet -WorkBook $book -Name "Float" -NFormat "#,##0.00"
$StyleConditionalFormatting = New-OOXMLStyleSheet -WorkBook $book -Name "ConditionalF" -Bold -ForeGroundColor Black -FillType Solid -BackGroundColor Orange -borderStyle Double -BorderColor Blue -NFormat "#,##0.0000" -Italic

$sheet | Set-OOXMLRangeValue -row $RowPosition -col 1 -value "Computer" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(1).Width = 22
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value "Drive" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(2).Width = 16
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value "Space" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(3).Width = 22
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value "Freespace" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(4).Width = 22
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value "SpaceRatio" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(5).Width = 22

$RowPosition++

foreach($Computer in $ComputerList){

    if(Test-Connection -ComputerName $Computer -Count 1 -BufferSize 16){
        $LocaHardDrive = Get-WmiObject -query "Select * FROM win32_logicaldisk" | Select-Object -Property *
        $VolumeSerialNumbers = @()

        foreach($Disk in $LocaHardDrive){
    
            if(($Disk.size -gt 0) -and ($VolumeSerialNumbers -notcontains $Disk.VolumeSerialNumber)){

                $VolumeSerialNumbers += $Disk.VolumeSerialNumber

                $FreeSpace = $Disk.freespace
                $TotalSpace = $Disk.size
                $Caption = $Disk.caption
                $FreeSpaceRatio = $FreeSpace / $TotalSpace * 100

                $sheet | Set-OOXMLRangeValue -Row $RowPosition -Col 1 -Value $Computer -StyleSheet $StyleGreen | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value $Caption -StyleSheet $StyleGreen | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value $($TotalSpace / 1GB) -StyleSheet $StyleGreen | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value $($FreeSpace / 1GB) -StyleSheet $StyleGreen | Out-Null

                if($FreeSpaceRatio -lt 10){
                    $sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value $FreeSpaceRatio -StyleSheet $StyleRed | Out-Null
                }else{
                    $sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value $FreeSpaceRatio -StyleSheet $StyleGreen | Out-Null
                }

                $RowPosition++
            }

        }

        Export-OOXML -InputObject $LocaHardDrive -FileFullPath "C:\Temp\$computer.xlsx" -ConditionalFormating @([PSCustomObject]@{Name="DeviceID";Style="Red";Condition="BeginsWith";Value="L"},[PSCustomObject]@{Name="DeviceID";Style="Green";Condition="BeginsWith";Value="B"})

    }else{

        $sheet | Set-OOXMLRangeValue -Row $RowPosition -Col 1 -Value $Computer -StyleSheet $StyleRed | Out-Null
        $sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value "N/A" -StyleSheet $StyleRed | Out-Null
        $sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value 0 -StyleSheet $StyleRed | Out-Null
        $sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value 0 -StyleSheet $StyleRed | Out-Null

        $RowPosition++
    }

    $sheet | Add-OOXMLConditionalFormatting -Addresses "E3:E$($RowPosition-1)" -StyleSheet $StyleConditionalFormatting -RuleType GreaterThanOrEqual -ConditionValue "50"
    $sheet | Add-OOXMLConditionalFormatting -Addresses "B3:B$($RowPosition-1)" -StyleSheet $StyleConditionalFormatting -RuleType BeginsWith -ConditionValue "L"
    
}



$excel | Save-OOXMLPackage -FileFullPath $OutputFileName -Dispose

Last edited Sep 25, 2014 at 12:51 PM by Avalon77, version 21

Comments

Avalon77 Aug 16, 2016 at 6:23 AM 
Ooops I just saw your comment... About one year later ^^ You are right and I'll add it in version 0.6.2 !

bkp Nov 19, 2015 at 7:35 AM 
Nice work! I just started playing with it, but GetValue requires row,col but does not allow address whereas SetValue takes either type.