mirror of
https://github.com/dfinke/ImportExcel.git
synced 2025-12-06 00:23:20 +00:00
Compare commits
4 Commits
copilot/fi
...
Fix-Import
| Author | SHA1 | Date | |
|---|---|---|---|
|
ebdfd3d090 | ||
|
|
a1986de292 | ||
|
|
30f2597f0d | ||
|
|
09c22654ab |
3
.github/workflows/ci.yml
vendored
3
.github/workflows/ci.yml
vendored
@@ -3,11 +3,8 @@ on:
|
||||
branches:
|
||||
- master
|
||||
- Set-up-GHA-CI/CD
|
||||
paths-ignore:
|
||||
- 'Examples/**'
|
||||
|
||||
pull_request:
|
||||
|
||||
|
||||
jobs:
|
||||
validate:
|
||||
|
||||
@@ -1,35 +0,0 @@
|
||||
$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
|
||||
@@ -1,22 +0,0 @@
|
||||
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
|
||||
@@ -1,29 +0,0 @@
|
||||
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
|
||||
@@ -1,16 +0,0 @@
|
||||
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
|
||||
@@ -1,152 +0,0 @@
|
||||
# Working with Gradient Fills in ImportExcel
|
||||
|
||||
## Overview
|
||||
|
||||
ImportExcel now provides enhanced support for working with Excel gradient fills through three new functions that address EPPlus 4.x limitations with gradient color reading.
|
||||
|
||||
## The Issue
|
||||
|
||||
In EPPlus 4.x (used by ImportExcel), while gradient colors can be set using `SetColor()`, they cannot be read back via properties like `Rgb`, `Top`, `Bottom`, `Left`, `Right`. This prevents copying gradients between cells.
|
||||
|
||||
## The Solution
|
||||
|
||||
Three new functions provide a complete gradient workflow:
|
||||
|
||||
### Set-ExcelGradientFill
|
||||
Sets gradient fill properties for Excel ranges.
|
||||
|
||||
```powershell
|
||||
# Basic linear gradient
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
# Path gradient
|
||||
Set-ExcelGradientFill -Range $ws.Cells["B1"] -Type Path -Color1 Red -Color2 Yellow
|
||||
|
||||
# Advanced linear gradient with positioning
|
||||
Set-ExcelGradientFill -Range $ws.Cells["C1"] -Type Linear -Degree 45 -Color1 Purple -Color2 Orange -Top 0.1 -Bottom 0.9
|
||||
```
|
||||
|
||||
### Get-ExcelGradientFill
|
||||
Reads gradient properties that can be reliably extracted from EPPlus.
|
||||
|
||||
```powershell
|
||||
$gradient = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
# Returns: Type, Degree, Top, Bottom, Left, Right
|
||||
# Note: Color1 and Color2 are null due to EPPlus limitations
|
||||
```
|
||||
|
||||
### Copy-ExcelGradientFill
|
||||
Copies gradient fills between ranges using two approaches:
|
||||
|
||||
#### Approach 1: Copy Geometric Properties Only
|
||||
```powershell
|
||||
# Copies Type, Degree, and positioning but warns about colors
|
||||
Copy-ExcelGradientFill -SourceRange $ws.Cells["A1"] -TargetRange $ws.Cells["B1"]
|
||||
```
|
||||
|
||||
#### Approach 2: Complete Gradient Definition
|
||||
```powershell
|
||||
# Create a complete gradient definition
|
||||
$gradientDef = @{
|
||||
Type = "Linear"
|
||||
Degree = 45
|
||||
Color1 = [System.Drawing.Color]::Blue
|
||||
Color2 = [System.Drawing.Color]::Green
|
||||
Top = 0.2
|
||||
Bottom = 0.8
|
||||
}
|
||||
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["C1:E3"] -GradientDefinition $gradientDef
|
||||
```
|
||||
|
||||
## Complete Example
|
||||
|
||||
```powershell
|
||||
Import-Module ImportExcel
|
||||
|
||||
$xl = Open-ExcelPackage -Path "gradients.xlsx" -Create
|
||||
$ws = Add-WorkSheet -ExcelPackage $xl -WorksheetName "Gradients"
|
||||
|
||||
# Set original gradient
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
# Get gradient properties (geometric only)
|
||||
$gradientInfo = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
|
||||
# Create complete definition for copying
|
||||
$completeDef = @{
|
||||
Type = $gradientInfo.Type
|
||||
Degree = $gradientInfo.Degree
|
||||
Top = $gradientInfo.Top
|
||||
Bottom = $gradientInfo.Bottom
|
||||
Left = $gradientInfo.Left
|
||||
Right = $gradientInfo.Right
|
||||
Color1 = [System.Drawing.Color]::Blue # Must specify colors
|
||||
Color2 = [System.Drawing.Color]::Green
|
||||
}
|
||||
|
||||
# Copy to other cells
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["B1:D3"] -GradientDefinition $completeDef
|
||||
|
||||
Close-ExcelPackage -ExcelPackage $xl -SaveAs "gradients.xlsx"
|
||||
```
|
||||
|
||||
## Migration from Direct EPPlus Access
|
||||
|
||||
### Before (Limited by EPPlus 4.x):
|
||||
```powershell
|
||||
# Setting worked
|
||||
$Sheet.Cells["A1"].Style.Fill.Gradient.Color1.SetColor("BLUE")
|
||||
$Sheet.Cells["A1"].Style.Fill.Gradient.Color2.SetColor("GREEN")
|
||||
|
||||
# Reading failed - returned empty values
|
||||
$rgb = $Sheet.Cells["A1"].Style.Fill.Gradient.Color1.Rgb # Empty!
|
||||
```
|
||||
|
||||
### After (Using ImportExcel functions):
|
||||
```powershell
|
||||
# Setting is easier and more robust
|
||||
Set-ExcelGradientFill -Range $Sheet.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
# Reading works for geometric properties
|
||||
$gradient = Get-ExcelGradientFill -Range $Sheet.Cells["A1"]
|
||||
# $gradient.Type, $gradient.Degree work; colors need to be specified separately
|
||||
|
||||
# Copying now possible with complete definitions
|
||||
$gradientDef = @{
|
||||
Type = $gradient.Type
|
||||
Degree = $gradient.Degree
|
||||
Color1 = [System.Drawing.Color]::Blue
|
||||
Color2 = [System.Drawing.Color]::Green
|
||||
}
|
||||
Copy-ExcelGradientFill -TargetRange $Sheet.Cells["B1"] -GradientDefinition $gradientDef
|
||||
```
|
||||
|
||||
## Parameters Reference
|
||||
|
||||
### Set-ExcelGradientFill Parameters
|
||||
- **Range**: Target Excel range (required)
|
||||
- **Type**: "Linear" or "Path" (required)
|
||||
- **Degree**: Angle for linear gradients (0-360, default 90)
|
||||
- **Color1**: First color - Color object or string name (required)
|
||||
- **Color2**: Second color - Color object or string name (required)
|
||||
- **Top/Bottom/Left/Right**: Position values (0-1, optional)
|
||||
|
||||
### Copy-ExcelGradientFill Parameters
|
||||
- **SourceRange**: Source range (for copying geometric properties)
|
||||
- **TargetRange**: Target range (required)
|
||||
- **GradientDefinition**: Hashtable with gradient properties (alternative to SourceRange)
|
||||
|
||||
## Limitations
|
||||
|
||||
- Color properties cannot be read from existing gradients due to EPPlus 4.x limitations
|
||||
- When copying gradients, colors must be specified manually in the gradient definition
|
||||
- Functions provide warnings when color limitations affect operations
|
||||
|
||||
## Benefits
|
||||
|
||||
- Clean, PowerShell-friendly interface for gradient operations
|
||||
- Comprehensive parameter validation
|
||||
- Clear error messages and warnings
|
||||
- Supports both string color names and Color objects
|
||||
- Enables gradient copying workflows that weren't possible before
|
||||
@@ -6,7 +6,7 @@
|
||||
RootModule = 'ImportExcel.psm1'
|
||||
|
||||
# Version number of this module.
|
||||
ModuleVersion = '7.8.10'
|
||||
ModuleVersion = '7.8.2'
|
||||
|
||||
# ID used to uniquely identify this module
|
||||
GUID = '60dd4136-feff-401a-ba27-a84458c57ede'
|
||||
@@ -46,7 +46,6 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
'ConvertFrom-ExcelSheet',
|
||||
'ConvertFrom-ExcelToSQLInsert',
|
||||
'ConvertTo-ExcelXlsx',
|
||||
'Copy-ExcelGradientFill',
|
||||
'Copy-ExcelWorksheet',
|
||||
'DoChart',
|
||||
'Enable-ExcelAutoFilter',
|
||||
@@ -55,9 +54,7 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
'Export-Excel',
|
||||
'Export-ExcelSheet',
|
||||
'Get-ExcelColumnName',
|
||||
'Get-ExcelFileSchema',
|
||||
'Get-ExcelFileSummary',
|
||||
'Get-ExcelGradientFill',
|
||||
'Get-ExcelSheetDimensionAddress',
|
||||
'Get-ExcelSheetInfo',
|
||||
'Get-ExcelWorkbookInfo',
|
||||
@@ -91,10 +88,8 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
'Remove-Worksheet',
|
||||
'Select-Worksheet',
|
||||
'Send-SQLDataToExcel',
|
||||
'Set-CellComment',
|
||||
'Set-CellStyle',
|
||||
'Set-ExcelColumn',
|
||||
'Set-ExcelGradientFill',
|
||||
'Set-ExcelRange',
|
||||
'Set-ExcelRow',
|
||||
'Set-WorksheetProtection',
|
||||
@@ -126,7 +121,8 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
'.\GetExcelTable.ps1',
|
||||
'.\ImportExcel.psd1',
|
||||
'.\ImportExcel.psm1',
|
||||
'.\LICENSE.txt',
|
||||
'.\LICENSE.txt',
|
||||
'.\README.md',
|
||||
'.\Plot.ps1',
|
||||
'.\Private',
|
||||
'.\Public',
|
||||
@@ -153,7 +149,7 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
Tags = @("Excel", "EPPlus", "Export", "Import")
|
||||
|
||||
# The web address of an icon which can be used in galleries to represent this module
|
||||
#IconUri =
|
||||
#IconUri = "http://pesterbdd.com/images/Pester.png"
|
||||
|
||||
# The web address of this module's project or support homepage.
|
||||
ProjectUri = "https://github.com/dfinke/ImportExcel"
|
||||
@@ -218,4 +214,4 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
|
||||
# Default prefix for commands exported from this module. Override the default prefix using Import-Module -Prefix.
|
||||
# DefaultCommandPrefix = ''
|
||||
|
||||
}
|
||||
}
|
||||
@@ -67,8 +67,9 @@ function WorksheetArgumentCompleter {
|
||||
param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
|
||||
$xlPath = $fakeBoundParameter['Path']
|
||||
if (Test-Path -Path $xlPath) {
|
||||
$xlSheet = Get-ExcelSheetInfo -Path $xlPath
|
||||
$WorksheetNames = $xlSheet.Name
|
||||
$xlpkg = Open-ExcelPackage -ReadOnly -Path $xlPath
|
||||
$WorksheetNames = $xlPkg.Workbook.Worksheets.Name
|
||||
Close-ExcelPackage -nosave -ExcelPackage $xlpkg
|
||||
$WorksheetNames.where( { $_ -like "*$wordToComplete*" }) | foreach-object {
|
||||
New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'",
|
||||
$_ , ([System.Management.Automation.CompletionResultType]::ParameterValue) , $_
|
||||
|
||||
@@ -23,7 +23,6 @@
|
||||
[Parameter(ParameterSetName = "FourIconSet")]
|
||||
[Parameter(ParameterSetName = "FiveIconSet")]
|
||||
[switch]$Reverse,
|
||||
[switch]$ShowIconOnly,
|
||||
[Parameter(ParameterSetName = "NamedRule",Position = 2)]
|
||||
$ConditionValue,
|
||||
[Parameter(ParameterSetName = "NamedRule",Position = 3)]
|
||||
@@ -85,9 +84,6 @@
|
||||
elseif ($PSBoundParameters.ContainsKey("FourIconsSet" ) ) {$rule = $Worksheet.ConditionalFormatting.AddFourIconSet( $Address , $FourIconsSet )}
|
||||
elseif ($PSBoundParameters.ContainsKey("FiveIconsSet" ) ) {$rule = $Worksheet.ConditionalFormatting.AddFiveIconSet( $Address , $FiveIconsSet )}
|
||||
else {$rule = ($Worksheet.ConditionalFormatting)."Add$RuleType"($Address ) }
|
||||
If($ShowIconOnly) {
|
||||
$rule.ShowValue = $false
|
||||
}
|
||||
if ($Reverse) {
|
||||
if ($rule.type -match 'IconSet$' ) {$rule.reverse = $true}
|
||||
elseif ($rule.type -match 'ColorScale$') {$temp =$rule.LowValue.Color ; $rule.LowValue.Color = $rule.HighValue.Color; $rule.HighValue.Color = $temp}
|
||||
|
||||
@@ -9,7 +9,7 @@ function Add-ExcelTable {
|
||||
[Switch]$ShowHeader ,
|
||||
[Switch]$ShowFilter,
|
||||
[Switch]$ShowTotal,
|
||||
[hashtable]$TableTotalSettings,
|
||||
[hashtable]$TotalSettings,
|
||||
[Switch]$ShowFirstColumn,
|
||||
[Switch]$ShowLastColumn,
|
||||
[Switch]$ShowRowStripes,
|
||||
@@ -51,65 +51,14 @@ function Add-ExcelTable {
|
||||
}
|
||||
#it seems that show total changes some of the others, so the sequence matters.
|
||||
if ($PSBoundParameters.ContainsKey('ShowHeader')) {$tbl.ShowHeader = [bool]$ShowHeader}
|
||||
if ($PSBoundParameters.ContainsKey('TableTotalSettings') -And $Null -ne $TableTotalSettings) {
|
||||
if ($PSBoundParameters.ContainsKey('TotalSettings')) {
|
||||
$tbl.ShowTotal = $true
|
||||
foreach ($k in $TableTotalSettings.keys) {
|
||||
|
||||
# Get the Function to be added in the totals row
|
||||
if ($TableTotalSettings[$k] -is [HashTable]) {
|
||||
If ($TableTotalSettings[$k].Keys -contains "Function") {
|
||||
$TotalFunction = $TableTotalSettings[$k]["Function"]
|
||||
}
|
||||
Else { Write-Warning -Message "TableTotalSettings parameter for column '$k' needs a key 'Function'" }
|
||||
}
|
||||
else {
|
||||
$TotalFunction = [String]($TableTotalSettings[$k])
|
||||
}
|
||||
|
||||
# Add the totals row
|
||||
foreach ($k in $TotalSettings.keys) {
|
||||
if (-not $tbl.Columns[$k]) {Write-Warning -Message "Table does not have a Column '$k'."}
|
||||
elseif ($TotalFunction -match "^=") {
|
||||
### A function in Excel uses ";" between parameters but the OpenXML parameter separator is ","
|
||||
### Only replace semicolon when it's NOT somewhere between quotes quotes.
|
||||
# Get all text between quotes
|
||||
$QuoteMatches = [Regex]::Matches($TotalFunction,"`"[^`"]*`"|'[^']*'")
|
||||
# Create array with all indexes of characters between quotes (and the quotes themselves)
|
||||
$QuoteCharIndexes = $(
|
||||
Foreach ($QuoteMatch in $QuoteMatches) {
|
||||
(($QuoteMatch.Index)..($QuoteMatch.Index + $QuoteMatch.Length - 1))
|
||||
}
|
||||
)
|
||||
|
||||
# Get all semicolons
|
||||
$SemiColonMatches = [Regex]::Matches($TotalFunction, ";")
|
||||
# Replace the semicolons of which the index is not in the list of quote-text indexes
|
||||
Foreach ($SemiColonMatch in $SemiColonMatches.Index) {
|
||||
If ($QuoteCharIndexes -notcontains $SemiColonMatch) {
|
||||
$TotalFunction = $TotalFunction.remove($SemiColonMatch,1).Insert($SemiColonMatch,",")
|
||||
}
|
||||
}
|
||||
|
||||
# Configure the formula. The TotalsRowFunction is automatically set to "Custom" when the TotalsRowFormula is set.
|
||||
$tbl.Columns[$k].TotalsRowFormula = $TotalFunction
|
||||
}
|
||||
elseif ($TotalFunction -notin @("Average", "Count", "CountNums", "Max", "Min", "None", "StdDev", "Sum", "Var") ) {
|
||||
Write-Warning -Message "'$($TotalFunction)' is not a valid total function."
|
||||
}
|
||||
else {$tbl.Columns[$k].TotalsRowFunction = $TotalFunction}
|
||||
|
||||
# Set comment on totals row
|
||||
If ($TableTotalSettings[$k] -is [HashTable] -and $TableTotalSettings[$k].Keys -contains "Comment" -and ![String]::IsNullOrEmpty($TableTotalSettings[$k]["Comment"])) {
|
||||
$ColumnLetter = [officeOpenXml.ExcelAddress]::GetAddressCol(($tbl.columns | ? { $_.name -eq $k }).Id, $False)
|
||||
$CommentRange = "{0}{1}" -f $ColumnLetter, $tbl.Address.End.Row
|
||||
|
||||
$CellCommentParams = @{
|
||||
Worksheet = $tbl.WorkSheet
|
||||
Range = $CommentRange
|
||||
Text = $TableTotalSettings[$k]["Comment"]
|
||||
}
|
||||
|
||||
Set-CellComment @CellCommentParams
|
||||
elseif ($TotalSettings[$k] -notin @("Average", "Count", "CountNums", "Max", "Min", "None", "StdDev", "Sum", "Var") ) {
|
||||
Write-Warning -Message "'$($TotalSettings[$k])' is not a valid total function."
|
||||
}
|
||||
else {$tbl.Columns[$k].TotalsRowFunction = $TotalSettings[$k]}
|
||||
}
|
||||
}
|
||||
elseif ($PSBoundParameters.ContainsKey('ShowTotal')) {$tbl.ShowTotal = [bool]$ShowTotal}
|
||||
|
||||
@@ -8,8 +8,7 @@ function ConvertFrom-ExcelData {
|
||||
[ScriptBlock]$ScriptBlock,
|
||||
[Alias("Sheet")]
|
||||
$WorksheetName = 1,
|
||||
[Alias('HeaderRow', 'TopRow')]
|
||||
[int]$StartRow = 1,
|
||||
[int]$HeaderRow = 1,
|
||||
[string[]]$Header,
|
||||
[switch]$NoHeader,
|
||||
[switch]$DataOnly
|
||||
|
||||
@@ -45,18 +45,11 @@ function ConvertTo-ExcelXlsx {
|
||||
throw "Could not create Excel.Application ComObject. Please verify that Excel is installed."
|
||||
}
|
||||
|
||||
try {
|
||||
$Excel.Visible = $false
|
||||
$null = $Excel.Workbooks.Open($xlsFile.FullName, $null, $true)
|
||||
$Excel.ActiveWorkbook.SaveAs($xlsxPath, $xlFixedFormat)
|
||||
}
|
||||
finally {
|
||||
if ($null -ne $Excel.ActiveWorkbook) {
|
||||
$Excel.ActiveWorkbook.Close()
|
||||
}
|
||||
|
||||
$Excel.Quit()
|
||||
}
|
||||
$Excel.Visible = $false
|
||||
$null = $Excel.Workbooks.Open($xlsFile.FullName)
|
||||
$Excel.ActiveWorkbook.SaveAs($xlsxPath, $xlFixedFormat)
|
||||
$Excel.ActiveWorkbook.Close()
|
||||
$Excel.Quit()
|
||||
}
|
||||
}
|
||||
|
||||
|
||||
@@ -1,119 +0,0 @@
|
||||
function Copy-ExcelGradientFill {
|
||||
<#
|
||||
.SYNOPSIS
|
||||
Copies gradient fill settings from one Excel range to another.
|
||||
|
||||
.DESCRIPTION
|
||||
Due to limitations in EPPlus 4.x, gradient color properties cannot be read directly.
|
||||
This function provides a workaround by storing gradient definitions and applying them
|
||||
to target ranges.
|
||||
|
||||
.PARAMETER SourceRange
|
||||
The source range with the gradient fill to copy.
|
||||
|
||||
.PARAMETER TargetRange
|
||||
The target range where the gradient fill should be applied.
|
||||
|
||||
.PARAMETER GradientDefinition
|
||||
A hashtable containing gradient definition with properties:
|
||||
- Type: Linear or Path
|
||||
- Degree: Angle for linear gradients (0-360)
|
||||
- Top, Bottom, Left, Right: Position values (0-1)
|
||||
- Color1: First color (System.Drawing.Color or color name)
|
||||
- Color2: Second color (System.Drawing.Color or color name)
|
||||
|
||||
.EXAMPLE
|
||||
# Copy gradient from A1 to B1:C2
|
||||
Copy-ExcelGradientFill -SourceRange $ws.Cells["A1"] -TargetRange $ws.Cells["B1:C2"]
|
||||
|
||||
.EXAMPLE
|
||||
# Apply a custom gradient definition
|
||||
$gradient = @{
|
||||
Type = "Linear"
|
||||
Degree = 45
|
||||
Color1 = [System.Drawing.Color]::Blue
|
||||
Color2 = [System.Drawing.Color]::Green
|
||||
Top = 0
|
||||
Bottom = 1
|
||||
Left = 0
|
||||
Right = 1
|
||||
}
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["A1:B2"] -GradientDefinition $gradient
|
||||
|
||||
.NOTES
|
||||
This function works around EPPlus 4.x limitations with gradient color reading.
|
||||
#>
|
||||
[CmdletBinding(DefaultParameterSetName = 'CopyFromSource')]
|
||||
param(
|
||||
[Parameter(ParameterSetName = 'CopyFromSource', Mandatory)]
|
||||
$SourceRange,
|
||||
|
||||
[Parameter(Mandatory)]
|
||||
$TargetRange,
|
||||
|
||||
[Parameter(ParameterSetName = 'ApplyDefinition', Mandatory)]
|
||||
[hashtable]$GradientDefinition
|
||||
)
|
||||
|
||||
if ($PSCmdlet.ParameterSetName -eq 'CopyFromSource') {
|
||||
# Extract gradient properties from source
|
||||
$sourceGradient = $SourceRange.Style.Fill.Gradient
|
||||
|
||||
# Create gradient definition from source
|
||||
$GradientDefinition = @{
|
||||
Type = $sourceGradient.Type
|
||||
Degree = $sourceGradient.Degree
|
||||
Top = $sourceGradient.Top
|
||||
Bottom = $sourceGradient.Bottom
|
||||
Left = $sourceGradient.Left
|
||||
Right = $sourceGradient.Right
|
||||
}
|
||||
|
||||
# Note: Colors cannot be copied due to EPPlus limitation
|
||||
Write-Warning "Gradient colors cannot be copied due to EPPlus 4.x limitations. Only geometric properties (Type, Degree, positioning) are copied. Please use Set-ExcelGradientFill to set colors."
|
||||
}
|
||||
|
||||
# Apply gradient definition to target
|
||||
$targetGradient = $TargetRange.Style.Fill.Gradient
|
||||
|
||||
if ($GradientDefinition.Type) {
|
||||
$targetGradient.Type = [OfficeOpenXml.Style.ExcelFillGradientType]::$($GradientDefinition.Type)
|
||||
}
|
||||
|
||||
if ($GradientDefinition.ContainsKey('Degree')) {
|
||||
$targetGradient.Degree = $GradientDefinition.Degree
|
||||
}
|
||||
|
||||
if ($GradientDefinition.ContainsKey('Top')) {
|
||||
$targetGradient.Top = $GradientDefinition.Top
|
||||
}
|
||||
|
||||
if ($GradientDefinition.ContainsKey('Bottom')) {
|
||||
$targetGradient.Bottom = $GradientDefinition.Bottom
|
||||
}
|
||||
|
||||
if ($GradientDefinition.ContainsKey('Left')) {
|
||||
$targetGradient.Left = $GradientDefinition.Left
|
||||
}
|
||||
|
||||
if ($GradientDefinition.ContainsKey('Right')) {
|
||||
$targetGradient.Right = $GradientDefinition.Right
|
||||
}
|
||||
|
||||
# Set colors if provided
|
||||
if ($GradientDefinition.Color1) {
|
||||
$color1 = $GradientDefinition.Color1
|
||||
if ($color1 -is [string]) {
|
||||
$color1 = [System.Drawing.Color]::$color1
|
||||
}
|
||||
$targetGradient.Color1.SetColor($color1)
|
||||
}
|
||||
|
||||
if ($GradientDefinition.Color2) {
|
||||
$color2 = $GradientDefinition.Color2
|
||||
if ($color2 -is [string]) {
|
||||
$color2 = [System.Drawing.Color]::$color2
|
||||
}
|
||||
$targetGradient.Color2.SetColor($color2)
|
||||
}
|
||||
}
|
||||
@@ -56,7 +56,6 @@
|
||||
[Alias('Table')]
|
||||
$TableName,
|
||||
[OfficeOpenXml.Table.TableStyles]$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium6,
|
||||
[HashTable]$TableTotalSettings,
|
||||
[Switch]$BarChart,
|
||||
[Switch]$PieChart,
|
||||
[Switch]$LineChart ,
|
||||
@@ -79,7 +78,6 @@
|
||||
[Switch]$NoAliasOrScriptPropeties,
|
||||
[Switch]$DisplayPropertySet,
|
||||
[String[]]$NoNumberConversion,
|
||||
[String[]]$NoHyperLinkConversion,
|
||||
[Object[]]$ConditionalFormat,
|
||||
[Object[]]$ConditionalText,
|
||||
[Object[]]$Style,
|
||||
@@ -213,7 +211,7 @@
|
||||
$row ++
|
||||
$null = $ws.Cells[$row, $StartColumn].LoadFromDataTable($InputObject, $false )
|
||||
if ($TableName -or $PSBoundParameters.ContainsKey('TableStyle')) {
|
||||
Add-ExcelTable -Range $ws.Cells[$ws.Dimension] -TableName $TableName -TableStyle $TableStyle -TableTotalSettings $TableTotalSettings
|
||||
Add-ExcelTable -Range $ws.Cells[$ws.Dimension] -TableName $TableName -TableStyle $TableStyle
|
||||
}
|
||||
}
|
||||
else {
|
||||
@@ -319,9 +317,7 @@
|
||||
$ws.Cells[$row, $ColumnIndex].Value = $v
|
||||
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
|
||||
}
|
||||
elseif ($v -is [uri] -and
|
||||
$NoHyperLinkConversion -ne '*' -and
|
||||
$NoHyperLinkConversion -notcontains $Name ) {
|
||||
elseif ($v -is [uri] ) {
|
||||
$ws.Cells[$row, $ColumnIndex].HyperLink = $v
|
||||
$ws.Cells[$row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
|
||||
$ws.Cells[$row, $ColumnIndex].Style.Font.UnderLine = $true
|
||||
@@ -334,10 +330,7 @@
|
||||
$ws.Cells[$row, $ColumnIndex].Formula = ($v -replace '^=', '')
|
||||
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
|
||||
}
|
||||
elseif ( $NoHyperLinkConversion -ne '*' -and # Put the check for 'NoHyperLinkConversion is null' first to skip checking for wellformedstring
|
||||
$NoHyperLinkConversion -notcontains $Name -and
|
||||
[System.Uri]::IsWellFormedUriString($v , [System.UriKind]::Absolute)
|
||||
) {
|
||||
elseif ( [System.Uri]::IsWellFormedUriString($v , [System.UriKind]::Absolute) ) {
|
||||
if ($v -match "^xl://internal/") {
|
||||
$referenceAddress = $v -replace "^xl://internal/" , ""
|
||||
$display = $referenceAddress -replace "!A1$" , ""
|
||||
@@ -350,8 +343,8 @@
|
||||
}
|
||||
else {
|
||||
$number = $null
|
||||
if ( $NoNumberConversion -ne '*' -and # Check if NoNumberConversion isn't specified. Put this first as it's going to stop the if clause. Quicker than putting regex check first
|
||||
$numberRegex.IsMatch($v) -and # and if it contains digit(s) - this syntax is quicker than -match for many items and cuts out slow checks for non numbers
|
||||
if ( $numberRegex.IsMatch($v) -and # if it contains digit(s) - this syntax is quicker than -match for many items and cuts out slow checks for non numbers
|
||||
$NoNumberConversion -ne '*' -and # and NoNumberConversion isn't specified
|
||||
$NoNumberConversion -notcontains $Name -and
|
||||
[Double]::TryParse($v, [System.Globalization.NumberStyles]::Any, [System.Globalization.NumberFormatInfo]::CurrentInfo, [Ref]$number)
|
||||
) {
|
||||
@@ -431,7 +424,7 @@
|
||||
if ($null -ne $TableName -or $PSBoundParameters.ContainsKey('TableStyle')) {
|
||||
#Already inserted Excel table if input was a DataTable
|
||||
if ($InputObject -isnot [System.Data.DataTable]) {
|
||||
Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName -TableStyle $TableStyle -TableTotalSettings $TableTotalSettings
|
||||
Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName -TableStyle $TableStyle
|
||||
}
|
||||
}
|
||||
elseif ($AutoFilter) {
|
||||
@@ -500,7 +493,7 @@
|
||||
}
|
||||
elseif ($FreezeTopRow) {
|
||||
if ($Title) {
|
||||
$ws.View.FreezePanes(3, 1)
|
||||
$ws.View.FreezePanes(2, 1)
|
||||
Write-Verbose -Message "Froze title and header rows"
|
||||
}
|
||||
else {
|
||||
@@ -643,9 +636,9 @@
|
||||
}
|
||||
elseif ($c.formatter) {
|
||||
switch ($c.formatter) {
|
||||
"ThreeIconSet" { Add-ConditionalFormatting -Worksheet $ws -ThreeIconsSet $c.IconType -range $c.range -reverse:$c.reverse -ShowIconOnly:$c.ShowIconOnly}
|
||||
"FourIconSet" { Add-ConditionalFormatting -Worksheet $ws -FourIconsSet $c.IconType -range $c.range -reverse:$c.reverse -ShowIconOnly:$c.ShowIconOnly}
|
||||
"FiveIconSet" { Add-ConditionalFormatting -Worksheet $ws -FiveIconsSet $c.IconType -range $c.range -reverse:$c.reverse -ShowIconOnly:$c.ShowIconOnly}
|
||||
"ThreeIconSet" { Add-ConditionalFormatting -Worksheet $ws -ThreeIconsSet $c.IconType -range $c.range -reverse:$c.reverse }
|
||||
"FourIconSet" { Add-ConditionalFormatting -Worksheet $ws -FourIconsSet $c.IconType -range $c.range -reverse:$c.reverse }
|
||||
"FiveIconSet" { Add-ConditionalFormatting -Worksheet $ws -FiveIconsSet $c.IconType -range $c.range -reverse:$c.reverse }
|
||||
}
|
||||
Write-Verbose -Message "Added conditional formatting to range $($c.range)"
|
||||
}
|
||||
|
||||
@@ -1,47 +0,0 @@
|
||||
function Get-ExcelFileSchema {
|
||||
<#
|
||||
.SYNOPSIS
|
||||
Gets the schema of an Excel file.
|
||||
|
||||
.DESCRIPTION
|
||||
The Get-ExcelFileSchema function gets the schema of an Excel file by returning the property names of the first row of each worksheet in the file.
|
||||
|
||||
.PARAMETER Path
|
||||
Specifies the path to the Excel file.
|
||||
|
||||
.PARAMETER Compress
|
||||
Indicates whether to compress the json output.
|
||||
|
||||
.OUTPUTS
|
||||
Json
|
||||
|
||||
.EXAMPLE
|
||||
Get-ExcelFileSchema -Path .\example.xlsx
|
||||
#>
|
||||
|
||||
[CmdletBinding()]
|
||||
param(
|
||||
[Parameter(ValueFromPipelineByPropertyName, Mandatory)]
|
||||
[Alias('FullName')]
|
||||
$Path,
|
||||
[Switch]$Compress
|
||||
)
|
||||
|
||||
Begin {
|
||||
$result = @()
|
||||
}
|
||||
|
||||
Process {
|
||||
$excelFiles = Get-ExcelFileSummary $Path
|
||||
|
||||
foreach ($excelFile in $excelFiles) {
|
||||
$data = Import-Excel $Path -WorksheetName $excelFile.WorksheetName | Select-Object -First 1
|
||||
$names = $data[0].PSObject.Properties.name
|
||||
$result += $excelFile | Add-Member -MemberType NoteProperty -Name "PropertyNames" -Value $names -PassThru
|
||||
}
|
||||
}
|
||||
|
||||
End {
|
||||
$result | ConvertTo-Json -Compress:$Compress
|
||||
}
|
||||
}
|
||||
@@ -1,52 +0,0 @@
|
||||
function Get-ExcelGradientFill {
|
||||
<#
|
||||
.SYNOPSIS
|
||||
Gets gradient fill properties from an Excel range.
|
||||
|
||||
.DESCRIPTION
|
||||
This function extracts gradient fill properties that can be reliably read from EPPlus 4.x.
|
||||
Due to EPPlus limitations, gradient colors cannot be read directly and will return $null.
|
||||
|
||||
.PARAMETER Range
|
||||
The Excel range to read gradient properties from.
|
||||
|
||||
.EXAMPLE
|
||||
# Get gradient properties from a cell
|
||||
$gradientInfo = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
|
||||
.EXAMPLE
|
||||
# Get gradient properties and use them to copy to another range
|
||||
$gradient = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
# Add colors since they can't be read
|
||||
$gradient.Color1 = [System.Drawing.Color]::Blue
|
||||
$gradient.Color2 = [System.Drawing.Color]::Green
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["B1:C2"] -GradientDefinition $gradient
|
||||
|
||||
.NOTES
|
||||
Due to EPPlus 4.x limitations, Color1 and Color2 properties will always be $null.
|
||||
This function is provided for completeness and to work with Copy-ExcelGradientFill.
|
||||
#>
|
||||
[CmdletBinding()]
|
||||
param(
|
||||
[Parameter(Mandatory, ValueFromPipeline)]
|
||||
$Range
|
||||
)
|
||||
|
||||
process {
|
||||
$gradient = $Range.Style.Fill.Gradient
|
||||
|
||||
$result = [PSCustomObject]@{
|
||||
Type = $gradient.Type
|
||||
Degree = $gradient.Degree
|
||||
Top = $gradient.Top
|
||||
Bottom = $gradient.Bottom
|
||||
Left = $gradient.Left
|
||||
Right = $gradient.Right
|
||||
Color1 = $null # Cannot be read due to EPPlus 4.x limitation
|
||||
Color2 = $null # Cannot be read due to EPPlus 4.x limitation
|
||||
ColorLimitationNote = "Color properties cannot be read due to EPPlus 4.x limitations"
|
||||
}
|
||||
|
||||
return $result
|
||||
}
|
||||
}
|
||||
@@ -50,9 +50,7 @@ function Get-HtmlTable {
|
||||
else {
|
||||
$h = ConvertFrom-Html -Content $r.Content
|
||||
if ($TableIndex -is [valuetype]) { $TableIndex += 1}
|
||||
$rows = try {
|
||||
$h.SelectSingleNode("//table[$TableIndex]").SelectNodes(".//tr")
|
||||
} catch {}
|
||||
$rows = $h.SelectNodes("//table[$TableIndex]//tr")
|
||||
if (-not $rows) {Write-Warning "Could not find rows for `"//table[$TableIndex]`" in $Url ."}
|
||||
if ( -not $propertyNames) {
|
||||
if ( $tableHeaders = $rows[$FirstDataRow].SelectNodes("th")) {
|
||||
|
||||
@@ -235,7 +235,13 @@
|
||||
# Write-Verbose "Import cell '$($Worksheet.Cells[$R, $P.Column].Address)' with property name '$($p.Value)' and value '$($Worksheet.Cells[$R, $P.Column].Value)'."
|
||||
}
|
||||
}
|
||||
$xlBook["$targetSheetname"] += [PSCustomObject]$NewRow
|
||||
|
||||
if ($WorksheetName -eq '*') {
|
||||
$xlBook["$targetSheetname"] += [PSCustomObject]$NewRow
|
||||
}
|
||||
else {
|
||||
[PSCustomObject]$NewRow
|
||||
}
|
||||
}
|
||||
#endregion
|
||||
}
|
||||
@@ -247,16 +253,19 @@
|
||||
# $EndColumn = 0
|
||||
if ($Path) { $stream.close(); $ExcelPackage.Dispose() }
|
||||
|
||||
if ($Raw) {
|
||||
foreach ($entry in $xlbook.GetEnumerator()) {
|
||||
$entry.Value
|
||||
if ($WorksheetName -eq '*') {
|
||||
|
||||
if ($Raw) {
|
||||
foreach ($entry in $xlbook.GetEnumerator()) {
|
||||
$entry.Value
|
||||
}
|
||||
}
|
||||
elseif ($Worksheet.Count -eq 1) {
|
||||
$xlBook["$targetSheetname"]
|
||||
}
|
||||
else {
|
||||
$xlBook
|
||||
}
|
||||
}
|
||||
elseif ($Worksheet.Count -eq 1) {
|
||||
$xlBook["$targetSheetname"]
|
||||
}
|
||||
else {
|
||||
$xlBook
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
@@ -1,12 +1,11 @@
|
||||
function New-ConditionalFormattingIconSet {
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification = 'Does not change system State')]
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '',Justification='Does not change system State')]
|
||||
param(
|
||||
[Parameter(Mandatory = $true)]
|
||||
[Parameter(Mandatory=$true)]
|
||||
$Range,
|
||||
[ValidateSet("ThreeIconSet", "FourIconSet", "FiveIconSet")]
|
||||
[ValidateSet("ThreeIconSet","FourIconSet","FiveIconSet")]
|
||||
$ConditionalFormat,
|
||||
[Switch]$Reverse,
|
||||
[Switch]$ShowIconOnly
|
||||
[Switch]$Reverse
|
||||
)
|
||||
|
||||
DynamicParam {
|
||||
@@ -41,14 +40,13 @@ function New-ConditionalFormattingIconSet {
|
||||
|
||||
End {
|
||||
|
||||
$bp = @{} + $PSBoundParameters
|
||||
$bp = @{}+$PSBoundParameters
|
||||
|
||||
$obj = [PSCustomObject]@{
|
||||
Range = $Range
|
||||
Formatter = $ConditionalFormat
|
||||
IconType = $bp.IconType
|
||||
Reverse = $Reverse
|
||||
ShowIconOnly = $ShowIconOnly
|
||||
Range = $Range
|
||||
Formatter = $ConditionalFormat
|
||||
IconType = $bp.IconType
|
||||
Reverse = $Reverse
|
||||
}
|
||||
|
||||
$obj.pstypenames.Clear()
|
||||
|
||||
@@ -1,70 +0,0 @@
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Scope='Function', Target='Set*', Justification='Does not change system state')]
|
||||
param()
|
||||
|
||||
function Set-CellComment {
|
||||
[CmdletBinding(DefaultParameterSetName = "Range")]
|
||||
param(
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")]
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")]
|
||||
[Parameter(Mandatory = $False, ParameterSetName = "Range")]
|
||||
[OfficeOpenXml.ExcelWorkSheet]$Worksheet,
|
||||
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "Range", ValueFromPipeline = $true,Position=0)]
|
||||
[Alias("Address")]
|
||||
$Range,
|
||||
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")]
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")]
|
||||
[Int]$Row,
|
||||
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")]
|
||||
[String]$ColumnLetter,
|
||||
|
||||
[Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")]
|
||||
[Int]$ColumnNumber,
|
||||
|
||||
[Parameter(Mandatory = $True)]
|
||||
[String]$Text
|
||||
)
|
||||
|
||||
If ($PSCmdlet.ParameterSetName -eq "Range") {
|
||||
Write-Verbose "Using 'Range' Parameter Set"
|
||||
if ($Range -is [Array]) {
|
||||
$null = $PSBoundParameters.Remove("Range")
|
||||
$Range | Set-CellComment @PSBoundParameters
|
||||
}
|
||||
else {
|
||||
#We should accept, a worksheet and a name of a range or a cell address; a table; the address of a table; a named range; a row, a column or .Cells[ ]
|
||||
if ($Range -is [OfficeOpenXml.Table.ExcelTable]) {$Range = $Range.Address}
|
||||
elseif ($Worksheet -and $Range -is [string]) {
|
||||
# Convert range as string to OfficeOpenXml.ExcelAddress
|
||||
$Range = [OfficeOpenXml.ExcelAddress]::new($Range)
|
||||
}
|
||||
elseif ($Range -is [string]) {Write-Warning -Message "The range parameter you have specified also needs a worksheet parameter." ;return}
|
||||
#else we assume $Range is a OfficeOpenXml.ExcelAddress
|
||||
}
|
||||
}
|
||||
ElseIf ($PSCmdlet.ParameterSetName -eq "ColumnNumber") {
|
||||
$Range = [OfficeOpenXml.ExcelAddress]::new($Row, $ColumnNumber, $Row, $ColumnNumber)
|
||||
}
|
||||
ElseIf ($PSCmdlet.ParameterSetName -eq "ColumnLetter") {
|
||||
$Range = [OfficeOpenXml.ExcelAddress]::new(("{0}{1}" -f $ColumnLetter,$Row))
|
||||
}
|
||||
|
||||
If ($Range -isnot [Array]) {
|
||||
Foreach ($c in $Worksheet.Cells[$Range]) {
|
||||
write-verbose $c.address
|
||||
Try {
|
||||
If ($Null -eq $c.comment) {
|
||||
[Void]$c.AddComment($Text, "ImportExcel")
|
||||
}
|
||||
Else {
|
||||
$c.Comment.Text = $Text
|
||||
$c.Comment.Author = "ImportExcel"
|
||||
}
|
||||
$c.Comment.AutoFit = $True
|
||||
}
|
||||
Catch { "Could not add comment to cell {0}: {1}" -f $c.Address, $_.ToString() }
|
||||
}
|
||||
}
|
||||
}
|
||||
@@ -1,128 +0,0 @@
|
||||
function Set-ExcelGradientFill {
|
||||
<#
|
||||
.SYNOPSIS
|
||||
Sets gradient fill properties for an Excel range.
|
||||
|
||||
.DESCRIPTION
|
||||
This function provides a complete interface for setting gradient fills in Excel ranges,
|
||||
working around EPPlus 4.x limitations with gradient handling.
|
||||
|
||||
.PARAMETER Range
|
||||
The Excel range to apply the gradient fill to.
|
||||
|
||||
.PARAMETER Type
|
||||
The gradient type: Linear or Path.
|
||||
|
||||
.PARAMETER Degree
|
||||
The angle for linear gradients (0-360 degrees).
|
||||
|
||||
.PARAMETER Color1
|
||||
The first gradient color. Can be a System.Drawing.Color object or color name.
|
||||
|
||||
.PARAMETER Color2
|
||||
The second gradient color. Can be a System.Drawing.Color object or color name.
|
||||
|
||||
.PARAMETER Top
|
||||
The top position for gradient positioning (0-1).
|
||||
|
||||
.PARAMETER Bottom
|
||||
The bottom position for gradient positioning (0-1).
|
||||
|
||||
.PARAMETER Left
|
||||
The left position for gradient positioning (0-1).
|
||||
|
||||
.PARAMETER Right
|
||||
The right position for gradient positioning (0-1).
|
||||
|
||||
.EXAMPLE
|
||||
# Set a simple blue to green linear gradient
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
.EXAMPLE
|
||||
# Set a more complex gradient with positioning
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1:C3"] -Type Linear -Degree 45 -Color1 ([System.Drawing.Color]::Red) -Color2 ([System.Drawing.Color]::Yellow) -Top 0.2 -Bottom 0.8
|
||||
|
||||
.EXAMPLE
|
||||
# Set a path gradient
|
||||
Set-ExcelGradientFill -Range $ws.Cells["B1:D3"] -Type Path -Color1 "DarkBlue" -Color2 "LightBlue"
|
||||
|
||||
.NOTES
|
||||
This function addresses the EPPlus 4.x limitation where gradient colors cannot be read back.
|
||||
#>
|
||||
[CmdletBinding()]
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Justification = 'Does not change system state')]
|
||||
param(
|
||||
[Parameter(Mandatory, ValueFromPipeline)]
|
||||
$Range,
|
||||
|
||||
[Parameter(Mandatory)]
|
||||
[ValidateSet('Linear', 'Path')]
|
||||
[string]$Type,
|
||||
|
||||
[Parameter()]
|
||||
[ValidateRange(0, 360)]
|
||||
[double]$Degree = 90,
|
||||
|
||||
[Parameter(Mandatory)]
|
||||
$Color1,
|
||||
|
||||
[Parameter(Mandatory)]
|
||||
$Color2,
|
||||
|
||||
[Parameter()]
|
||||
[ValidateRange(0, 1)]
|
||||
[double]$Top,
|
||||
|
||||
[Parameter()]
|
||||
[ValidateRange(0, 1)]
|
||||
[double]$Bottom,
|
||||
|
||||
[Parameter()]
|
||||
[ValidateRange(0, 1)]
|
||||
[double]$Left,
|
||||
|
||||
[Parameter()]
|
||||
[ValidateRange(0, 1)]
|
||||
[double]$Right
|
||||
)
|
||||
|
||||
process {
|
||||
$gradient = $Range.Style.Fill.Gradient
|
||||
|
||||
# Set gradient type
|
||||
$gradient.Type = [OfficeOpenXml.Style.ExcelFillGradientType]::$Type
|
||||
|
||||
# Set degree for linear gradients
|
||||
if ($Type -eq 'Linear') {
|
||||
$gradient.Degree = $Degree
|
||||
}
|
||||
|
||||
# Set positioning if provided
|
||||
if ($PSBoundParameters.ContainsKey('Top')) {
|
||||
$gradient.Top = $Top
|
||||
}
|
||||
|
||||
if ($PSBoundParameters.ContainsKey('Bottom')) {
|
||||
$gradient.Bottom = $Bottom
|
||||
}
|
||||
|
||||
if ($PSBoundParameters.ContainsKey('Left')) {
|
||||
$gradient.Left = $Left
|
||||
}
|
||||
|
||||
if ($PSBoundParameters.ContainsKey('Right')) {
|
||||
$gradient.Right = $Right
|
||||
}
|
||||
|
||||
# Set colors
|
||||
if ($Color1 -is [string]) {
|
||||
$Color1 = [System.Drawing.Color]::$Color1
|
||||
}
|
||||
$gradient.Color1.SetColor($Color1)
|
||||
|
||||
if ($Color2 -is [string]) {
|
||||
$Color2 = [System.Drawing.Color]::$Color2
|
||||
}
|
||||
$gradient.Color2.SetColor($Color2)
|
||||
}
|
||||
}
|
||||
34
README.md
34
README.md
@@ -4,33 +4,15 @@
|
||||
|
||||
<br/>
|
||||
|
||||
Has the ImportExcel module helped you?
|
||||
- Made you look good to the boss?
|
||||
- Saved you time?
|
||||
- Made you more productive?
|
||||
|
||||
Consider donating. Thank you!
|
||||
<br>
|
||||
<br>
|
||||
<a href="https://www.paypal.com/paypalme/DougCharlesFinke"><img src="https://img.shields.io/badge/Donate-PayPal-green.svg" alt="Donate" height="28"></a>
|
||||
<br>
|
||||
<br>
|
||||
|
||||
<br>
|
||||
<br>
|
||||
|
||||
[](https://twitter.com/dfinke)
|
||||
[](https://youtube.com/@dougfinke/)
|
||||
<br/>
|
||||
<br/>
|
||||
[](https://dougfinke.visualstudio.com/ImportExcel/_build)
|
||||
[](https://www.powershellgallery.com/packages/ImportExcel)
|
||||
[](https://www.powershellgallery.com/packages/ImportExcel)
|
||||
[](https://github.com/dfinke/ImportExcel/tree/70ab9e46c776e96fb287682d5b9b4b51a0ec3bac/LICENSE.txt)
|
||||
<a href="https://www.paypal.com/paypalme/DougCharlesFinke"><img src="https://img.shields.io/badge/Donate-PayPal-green.svg" alt="Donate"></a>
|
||||
|
||||
# Overview
|
||||
|
||||
Automate Excel with PowerShell without having Excel installed. Works on Windows, Linux and Mac. Creating Tables, Pivot Tables, Charts and much more just got a lot easier.
|
||||
Automate Excel with PowerShell without having Excel installed. Works on Windows, Linux and MAC. Creating Tables, Pivot Tables, Charts and much more just got a lot easier.
|
||||
|
||||
## Examples ✨
|
||||
Check out the [more than 100 examples](Examples/) on ways to create amazing reports as well as make you more productive with PowerShell and Excel.
|
||||
@@ -191,14 +173,4 @@ ForEach-Object { $_.Value | Export-Csv ($_.key + '.csv') }
|
||||
## Contributing
|
||||
Contributions are welcome! Open a pull request to fix a bug, or open an issue to discuss a new feature or change.
|
||||
|
||||
## New Features
|
||||
|
||||
### Gradient Fill Support
|
||||
ImportExcel now includes enhanced gradient fill support with three new functions:
|
||||
- `Set-ExcelGradientFill` - Apply gradient fills to Excel ranges
|
||||
- `Get-ExcelGradientFill` - Read gradient properties from ranges
|
||||
- `Copy-ExcelGradientFill` - Copy gradients between ranges
|
||||
|
||||
These functions work around EPPlus 4.x limitations with gradient color reading. See [GRADIENT_GUIDE.md](GRADIENT_GUIDE.md) for detailed usage examples.
|
||||
|
||||
Original [README.md](./README.original.md)
|
||||
@@ -1,4 +1,4 @@
|
||||
#Requires -Modules @{ ModuleName="Pester"; ModuleVersion="4.0.0" }
|
||||
#Requires -Modules Pester
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification = 'False Positives')]
|
||||
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidAssignmentToAutomaticVariable', '', Justification = 'Only executes on versions without the automatic variable')]
|
||||
param()
|
||||
@@ -205,7 +205,7 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
else { $OtherCurrencySymbol = "£" }
|
||||
$path = "TestDrive:\test.xlsx"
|
||||
$warnVar = $null
|
||||
#Test correct export of different data types and number formats; test hyperlinks, test -NoNumberConversion and -NoHyperLinkConversion test object is converted to a string with no warnings, test calcuation of formula
|
||||
#Test correct export of different data types and number formats; test hyperlinks, test -NoNumberConversion test object is converted to a string with no warnings, test calcuation of formula
|
||||
Remove-item -Path $path -ErrorAction SilentlyContinue
|
||||
[PSCustOmobject][Ordered]@{
|
||||
Date = Get-Date
|
||||
@@ -233,10 +233,9 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
Link3 = "xl://internal/sheet1!A1"
|
||||
Link4 = "xl://internal/sheet1!C5"
|
||||
Link5 = (New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList "Sheet1!E2" , "Display Text")
|
||||
Link6 = "xl://internal/sheet1!C5"
|
||||
Process = (Get-Process -Id $PID)
|
||||
TimeSpan = [datetime]::Now.Subtract([datetime]::Today)
|
||||
} | Export-Excel -NoNumberConversion IPAddress, StrLeadZero, StrAltPhone2 -NoHyperLinkConversion Link6 -Path $path -Calculate -WarningVariable $warnVar
|
||||
} | Export-Excel -NoNumberConversion IPAddress, StrLeadZero, StrAltPhone2 -Path $path -Calculate -WarningVariable $warnVar
|
||||
}
|
||||
|
||||
BeforeEach {
|
||||
@@ -254,7 +253,7 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
}
|
||||
it "Created the worksheet with the expected name, number of rows and number of columns " {
|
||||
$ws.Name | Should -Be "sheet1"
|
||||
$ws.Dimension.Columns | Should -Be 28
|
||||
$ws.Dimension.Columns | Should -Be 27
|
||||
$ws.Dimension.Rows | Should -Be 2
|
||||
}
|
||||
it "Set a date in Cell A2 " {
|
||||
@@ -293,9 +292,6 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
$ws.Cells[2, 25].Hyperlink.ReferenceAddress | Should -Be "sheet1!E2"
|
||||
$ws.Cells[2, 25].Hyperlink.Display | Should -Be "Display Text"
|
||||
}
|
||||
it "Create no link in cell Z2 (no hyperlink conversion) " {
|
||||
$ws.Cells[2, 26].Hyperlink | Should -BeNullOrEmpty
|
||||
}
|
||||
it "Processed thousands according to local settings (Cells H2 and I2) " {
|
||||
if ((Get-Culture).NumberFormat.NumberGroupSeparator -EQ ",") {
|
||||
($ws.Cells[2, 8].Value -is [valuetype] ) | Should -Be $true
|
||||
@@ -318,13 +314,13 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
($ws.Cells[2, 19].Value -is [valuetype] ) | Should -Be $true
|
||||
($ws.Cells[2, 20].Value -is [valuetype] ) | Should -Be $true
|
||||
}
|
||||
it "Converted a nested object to a string (AA2) " {
|
||||
$ws.Cells[2, 27].Value | Should -Match '^System\.Diagnostics\.Process\s+\(.*\)$'
|
||||
it "Converted a nested object to a string (Y2) " {
|
||||
$ws.Cells[2, 26].Value | Should -Match '^System\.Diagnostics\.Process\s+\(.*\)$'
|
||||
}
|
||||
it "Processed a timespan object (AB2) " {
|
||||
$ws.cells[2, 28].Value.ToOADate() | Should -BeGreaterThan 0
|
||||
$ws.cells[2, 28].Value.ToOADate() | Should -BeLessThan 1
|
||||
$ws.cells[2, 28].Style.Numberformat.Format | Should -Be '[h]:mm:ss'
|
||||
it "Processed a timespan object (Z2) " {
|
||||
$ws.cells[2, 27].Value.ToOADate() | Should -BeGreaterThan 0
|
||||
$ws.cells[2, 27].Value.ToOADate() | Should -BeLessThan 1
|
||||
$ws.cells[2, 27].Style.Numberformat.Format | Should -Be '[h]:mm:ss'
|
||||
}
|
||||
}
|
||||
|
||||
@@ -348,9 +344,8 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
PhoneNr1 = '+32 44'
|
||||
PhoneNr2 = '+32 4 4444 444'
|
||||
PhoneNr3 = '+3244444444'
|
||||
Link1 = [uri]"https://github.com/dfinke/ImportExcel"
|
||||
Link2 = [uri]"https://github.com/dfinke/ImportExcel"
|
||||
} | Export-Excel -NoHyperLinkConversion Link2 -NoNumberConversion IPAddress, Number1 -Path $path -NoHeader
|
||||
Link = [uri]"https://github.com/dfinke/ImportExcel"
|
||||
} | Export-Excel -NoNumberConversion IPAddress, Number1 -Path $path -NoHeader
|
||||
}
|
||||
|
||||
BeforeEach {
|
||||
@@ -368,7 +363,7 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
|
||||
it "Created the worksheet with the expected name, number of rows and number of columns " {
|
||||
$ws.Name | Should -Be "sheet1"
|
||||
$ws.Dimension.Columns | Should -Be 15
|
||||
$ws.Dimension.Columns | Should -Be 14
|
||||
$ws.Dimension.Rows | Should -Be 1
|
||||
}
|
||||
|
||||
@@ -392,10 +387,6 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
it "Set a hyperlink in Cell N1 " {
|
||||
$ws.Cells[1, 14].Hyperlink | Should -Be "https://github.com/dfinke/ImportExcel"
|
||||
}
|
||||
|
||||
it "Does not set a hyperlink in Cell O1 " {
|
||||
$ws.Cells[1, 15].Hyperlink | Should -BeNullOrEmpty
|
||||
}
|
||||
}
|
||||
|
||||
Context "#Example 5 # Adding a single conditional format " {
|
||||
@@ -692,72 +683,6 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
}
|
||||
}
|
||||
|
||||
Context "#Example 10 # Creates a file with a table with a 'totals' row".PadRight(87) {
|
||||
BeforeEach {
|
||||
$path = "TestDrive:\test.xlsx"
|
||||
Remove-item -Path $path -ErrorAction SilentlyContinue
|
||||
|
||||
#Test with a maximum of 50 processes for speed; export limited set of properties.
|
||||
$processes = Get-Process | Where-Object { $_.StartTime } | Select-Object -First 50
|
||||
|
||||
# Export as table with a totals row with a set of possibilities
|
||||
$TableTotalSettings = @{
|
||||
Id = "COUNT"
|
||||
WS = "SUM"
|
||||
Handles = "AVERAGE"
|
||||
CPU = '=COUNTIF([CPU];"<1")'
|
||||
NPM = @{
|
||||
Function = '=SUMIF([Name];"=Chrome";[NPM])'
|
||||
Comment = "Sum of Non-Paged Memory (NPM) for all chrome processes"
|
||||
}
|
||||
}
|
||||
$Processes | Export-Excel $path -TableName "processes" -TableTotalSettings $TableTotalSettings
|
||||
$TotalRows = $Processes.count + 2 # Column header + Data (50 processes) + Totals row
|
||||
$Excel = Open-ExcelPackage -Path $path
|
||||
$ws = $Excel.Workbook.Worksheets[1]
|
||||
}
|
||||
|
||||
it "Totals row was created".PadRight(87) {
|
||||
$ws.Tables[0].Address.Rows | Should -Be $TotalRows
|
||||
$ws.tables[0].ShowTotal | Should -Be $True
|
||||
}
|
||||
|
||||
it "Added four calculations in the totals row".PadRight(87) {
|
||||
$IDcolumn = $ws.Tables[0].Columns | Where-Object { $_.Name -eq "id" }
|
||||
$WScolumn = $ws.Tables[0].Columns | Where-Object { $_.Name -eq "WS" }
|
||||
$HandlesColumn = $ws.Tables[0].Columns | Where-Object { $_.Name -eq "Handles" }
|
||||
$CPUColumn = $ws.Tables[0].Columns | Where-Object { $_.Name -eq "CPU" }
|
||||
$NPMColumn = $ws.Tables[0].Columns | Where-Object { $_.Name -eq "NPM" }
|
||||
|
||||
# Testing column properties
|
||||
$IDcolumn | Select-Object -ExpandProperty TotalsRowFunction | Should -Be "Count"
|
||||
$WScolumn | Select-Object -ExpandProperty TotalsRowFunction | Should -Be "Sum"
|
||||
$HandlesColumn | Select-Object -ExpandProperty TotalsRowFunction | Should -Be "Average"
|
||||
$CPUColumn | Select-Object -ExpandProperty TotalsRowFunction | Should -Be "Custom"
|
||||
$CPUColumn | Select-Object -ExpandProperty TotalsRowFormula | Should -Be 'COUNTIF([CPU],"<1")'
|
||||
$NPMColumn | Select-Object -ExpandProperty TotalsRowFunction | Should -Be "Custom"
|
||||
$NPMColumn | Select-Object -ExpandProperty TotalsRowFormula | Should -Be 'SUMIF([Name],"=Chrome",[NPM])'
|
||||
|
||||
# Testing actual cell properties
|
||||
$CountAddress = "{0}{1}" -f (Get-ExcelColumnName -ColumnNumber $IDcolumn.Id).ColumnName, $TotalRows
|
||||
$SumAddress = "{0}{1}" -f (Get-ExcelColumnName -ColumnNumber $WScolumn.Id).ColumnName, $TotalRows
|
||||
$AverageAddress = "{0}{1}" -f (Get-ExcelColumnName -ColumnNumber $HandlesColumn.Id).ColumnName, $TotalRows
|
||||
$CustomAddress = "{0}{1}" -f (Get-ExcelColumnName -ColumnNumber $CPUColumn.Id).ColumnName, $TotalRows
|
||||
$CustomCommentAddress = "{0}{1}" -f (Get-ExcelColumnName -ColumnNumber $NPMColumn.Id).ColumnName, $TotalRows
|
||||
|
||||
$ws.Cells[$CountAddress].Formula | Should -Be "SUBTOTAL(103,processes[Id])"
|
||||
$ws.Cells[$SumAddress].Formula | Should -Be "SUBTOTAL(109,processes[Ws])"
|
||||
$ws.Cells[$AverageAddress].Formula | Should -Be "SUBTOTAL(101,processes[Handles])"
|
||||
$ws.Cells[$CustomAddress].Formula | Should -Be 'COUNTIF([CPU],"<1")'
|
||||
$ws.Cells[$CustomCommentAddress].Formula | Should -Be 'SUMIF([Name],"=Chrome",[NPM])'
|
||||
$ws.Cells[$CustomCommentAddress].Comment.Text | Should -Not -BeNullOrEmpty
|
||||
}
|
||||
|
||||
AfterEach {
|
||||
Close-ExcelPackage -ExcelPackage $Excel
|
||||
}
|
||||
}
|
||||
|
||||
# Context "#Example 11 # Create and append with title, inc ranges and Pivot table" {
|
||||
# $path = "TestDrive:\test.xlsx"
|
||||
# #Test New-PivotTableDefinition builds definition using -Pivotfilter and -PivotTotals options.
|
||||
@@ -770,13 +695,7 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
$path = "TestDrive:\test.xlsx"
|
||||
Remove-Item $path -ErrorAction SilentlyContinue
|
||||
#Test freezing top row/first column, adding formats and a pivot table - from Add-Pivot table not a specification variable - after the export
|
||||
$Ex13Data = Get-Process | Select-Object -Property Name, Company, Handles, CPU, PM, NPM, WS
|
||||
$excel = $Ex13Data | Export-Excel -Path $path -ClearSheet -WorkSheetname "Processes" -FreezeTopRowFirstColumn -PassThru
|
||||
# Add extra worksheets for testing 'Freeze Top Row' and 'Freeze First Column' with or without title
|
||||
$excel = Export-Excel -InputObject $Ex13Data -ExcelPackage $excel -WorksheetName "FreezeTopRow" -FreezeTopRow -Passthru
|
||||
$excel = Export-Excel -InputObject $Ex13Data -ExcelPackage $excel -WorksheetName "FreezeFirstColumn" -FreezeFirstColumn -Passthru
|
||||
$excel = Export-Excel -InputObject $Ex13Data -Title "Freeze Top Row" -ExcelPackage $excel -WorksheetName "FreezeTopRowTitle" -FreezeTopRow -Passthru
|
||||
$excel = Export-Excel -InputObject $Ex13Data -Title "Freeze Top Row First Column" -ExcelPackage $excel -WorksheetName "FreezeTRFCTitle" -FreezeTopRowFirstColumn -Passthru
|
||||
$excel = Get-Process | Select-Object -Property Name, Company, Handles, CPU, PM, NPM, WS | Export-Excel -Path $path -ClearSheet -WorkSheetname "Processes" -FreezeTopRowFirstColumn -PassThru
|
||||
$sheet = $excel.Workbook.Worksheets["Processes"]
|
||||
if ($isWindows) { $sheet.Column(1) | Set-ExcelRange -Bold -AutoFit }
|
||||
else { $sheet.Column(1) | Set-ExcelRange -Bold }
|
||||
@@ -796,10 +715,6 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
|
||||
$excel = Open-ExcelPackage $path
|
||||
$sheet = $excel.Workbook.Worksheets["Processes"]
|
||||
$sheetftr = $excel.Workbook.Worksheets["FreezeTopRow"]
|
||||
$sheetffc = $excel.Workbook.Worksheets["FreezeFirstColumn"]
|
||||
$sheetftrt = $excel.Workbook.Worksheets["FreezeTopRowTitle"]
|
||||
$sheetftrfct = $excel.Workbook.Worksheets["FreezeTRFCTitle"]
|
||||
}
|
||||
it "Returned the rule when calling Add-ConditionalFormatting -passthru " {
|
||||
$rule | Should -Not -BeNullOrEmpty
|
||||
@@ -846,27 +761,7 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
$sheet.Names[6].Name | Should -Be $sheet.Cells['G1'].Value
|
||||
}
|
||||
it "Froze the panes " {
|
||||
$sheetPaneInfo = $sheet.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
$sheetftrPaneInfo = $sheetftr.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
$sheetffcPaneInfo = $sheetffc.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
$sheetftrtPaneInfo = $sheetftrt.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
$sheetftrfctPaneInfo = $sheetftrfct.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
$sheet.view.Panes.Count | Should -Be 3 # Don't know if this actually checks anything
|
||||
$sheetPaneInfo.xSplit | Should -Be 1
|
||||
$sheetPaneInfo.ySplit | Should -Be 1
|
||||
$sheetPaneInfo.topLeftCell | Should -Be "B2"
|
||||
$sheetftrPaneInfo.xSplit | Should -BeNullOrEmpty
|
||||
$sheetftrPaneInfo.ySplit | Should -Be 1
|
||||
$sheetftrPaneInfo.topLeftCell | Should -Be "A2"
|
||||
$sheetffcPaneInfo.xSplit | Should -Be 1
|
||||
$sheetffcPaneInfo.ySplit | Should -BeNullOrEmpty
|
||||
$sheetffcPaneInfo.topLeftCell | Should -Be "B1"
|
||||
$sheetftrtPaneInfo.xSplit | Should -BeNullOrEmpty
|
||||
$sheetftrtPaneInfo.ySplit | Should -Be 2
|
||||
$sheetftrtPaneInfo.topLeftCell | Should -Be "A3"
|
||||
$sheetftrfctPaneInfo.xSplit | Should -Be 1
|
||||
$sheetftrfctPaneInfo.ySplit | Should -Be 2
|
||||
$sheetftrfctPaneInfo.topLeftCell | Should -Be "B3"
|
||||
$sheet.view.Panes.Count | Should -Be 3
|
||||
}
|
||||
|
||||
it "Created the pivot table " {
|
||||
@@ -1237,120 +1132,4 @@ Describe ExportExcel -Tag "ExportExcel" {
|
||||
Close-ExcelPackage $excel -NoSave
|
||||
}
|
||||
}
|
||||
|
||||
It "Should have hyperlink created" -Tag hyperlink {
|
||||
$path = "TestDrive:\testHyperLink.xlsx"
|
||||
|
||||
$license = "cognc:MCOMEETADV_GOV,cognc:M365_G3_GOV,cognc:ENTERPRISEPACK_GOV,cognc:RIGHTSMANAGEMENT_ADHOC"
|
||||
$ms365 = [PSCustomObject]@{
|
||||
DisplayName = "Test Subject"
|
||||
UserPrincipalName = "test@contoso.com"
|
||||
licenses = $license
|
||||
}
|
||||
|
||||
$ms365 | Export-Excel $path
|
||||
|
||||
$excel = Open-ExcelPackage $Path
|
||||
|
||||
$ws = $excel.Sheet1
|
||||
|
||||
$ws.Dimension.Rows | Should -Be 2
|
||||
$ws.Dimension.Columns | Should -Be 3
|
||||
|
||||
$ws.Cells["C2"].Hyperlink | Should -BeExactly $license
|
||||
|
||||
Close-ExcelPackage $excel
|
||||
|
||||
Remove-Item $path
|
||||
}
|
||||
|
||||
It "Should have no hyperlink created" -Tag hyperlink {
|
||||
$path = "TestDrive:\testHyperLink.xlsx"
|
||||
|
||||
$license = "cognc:MCOMEETADV_GOV,cognc:M365_G3_GOV,cognc:ENTERPRISEPACK_GOV,cognc:RIGHTSMANAGEMENT_ADHOC"
|
||||
$ms365 = [PSCustomObject]@{
|
||||
DisplayName = "Test Subject"
|
||||
UserPrincipalName = "test@contoso.com"
|
||||
licenses = $license
|
||||
}
|
||||
|
||||
$ms365 | Export-Excel $path -NoHyperLinkConversion licenses
|
||||
|
||||
$excel = Open-ExcelPackage $Path
|
||||
|
||||
$ws = $excel.Sheet1
|
||||
|
||||
$ws.Dimension.Rows | Should -Be 2
|
||||
$ws.Dimension.Columns | Should -Be 3
|
||||
|
||||
$ws.Cells["C2"].Hyperlink | Should -BeNullOrEmpty
|
||||
|
||||
Close-ExcelPackage $excel
|
||||
Remove-Item $path
|
||||
}
|
||||
|
||||
It "Should have no hyperlink created using wild card" -Tag hyperlink {
|
||||
$path = "TestDrive:\testHyperLink.xlsx"
|
||||
|
||||
$license = "cognc:MCOMEETADV_GOV,cognc:M365_G3_GOV,cognc:ENTERPRISEPACK_GOV,cognc:RIGHTSMANAGEMENT_ADHOC"
|
||||
$ms365 = [PSCustomObject]@{
|
||||
DisplayName = "Test Subject"
|
||||
UserPrincipalName = "test@contoso.com"
|
||||
licenses = $license
|
||||
}
|
||||
|
||||
$ms365 | Export-Excel $path -NoHyperLinkConversion *
|
||||
|
||||
$excel = Open-ExcelPackage $Path
|
||||
|
||||
$ws = $excel.Sheet1
|
||||
|
||||
$ws.Dimension.Rows | Should -Be 2
|
||||
$ws.Dimension.Columns | Should -Be 3
|
||||
|
||||
$ws.Cells["A2"].Value | Should -BeExactly "Test Subject"
|
||||
$ws.Cells["B2"].Value | Should -BeExactly "test@contoso.com"
|
||||
$ws.Cells["C2"].Hyperlink | Should -BeNullOrEmpty
|
||||
|
||||
Close-ExcelPackage $excel
|
||||
Remove-Item $path
|
||||
}
|
||||
|
||||
It "Should freeze the correct rows" -tag Freeze {
|
||||
<#
|
||||
Export-Excel -InputObject $Data -Path $OutputFile -TableName $SheetName.Replace(' ', '_') -WorksheetName $SheetName -AutoSize -FreezeTopRow -TableStyle $TableStyle -Title $SheetName -TitleBold -TitleSize 18
|
||||
#>
|
||||
|
||||
$path = "TestDrive:\testFreeze.xlsx"
|
||||
|
||||
$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
|
||||
"@
|
||||
|
||||
Export-Excel -InputObject $data -Path $path -TableName 'TestTable' -WorksheetName 'TestSheet' -AutoSize -TableStyle Medium2 -Title 'Test Title' -TitleBold -TitleSize 18 -FreezeTopRow
|
||||
|
||||
$excel = Open-ExcelPackage -Path $path
|
||||
$ws = $excel.TestSheet
|
||||
|
||||
$r = $ws.worksheetxml.worksheet.sheetViews.sheetView.pane
|
||||
|
||||
$r | Should -Not -BeNullOrEmpty
|
||||
$r.ySplit | Should -Be 2
|
||||
$r.topLeftCell | Should -BeExactly 'A3'
|
||||
$r.state | Should -BeExactly 'frozen'
|
||||
$r.activePane | Should -BeExactly 'bottomLeft'
|
||||
|
||||
Close-ExcelPackage $excel
|
||||
|
||||
Remove-Item $path
|
||||
}
|
||||
}
|
||||
@@ -1,54 +0,0 @@
|
||||
if (-not (Get-command Import-Excel -ErrorAction SilentlyContinue)) {
|
||||
Import-Module $PSScriptRoot\..\ImportExcel.psd1
|
||||
}
|
||||
|
||||
Describe "Test getting the schema of an Excel file" -Tag GetExcelFileSchema {
|
||||
|
||||
BeforeAll {
|
||||
$script:excelFile = "TestDrive:\test.xlsx"
|
||||
$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
|
||||
"@
|
||||
$data | Export-Excel $excelFile
|
||||
}
|
||||
|
||||
It "Test Get-ExcelFileSchema function exists" {
|
||||
$function = Get-Command Get-ExcelFileSchema -ErrorAction SilentlyContinue
|
||||
$function | Should -Not -Be $null
|
||||
}
|
||||
|
||||
It "Test Get-ExcelFileSchema returns json" {
|
||||
$actual = Get-ExcelFileSchema -Path $excelFile
|
||||
$actual | Should -Not -Be $null
|
||||
$actual | Should -BeOfType [string]
|
||||
}
|
||||
|
||||
It "Test Get-ExcelFileSchema correct json" {
|
||||
$actual = Get-ExcelFileSchema -Path $excelFile
|
||||
$actual = $actual | ConvertFrom-Json
|
||||
|
||||
$actual.ExcelFile | Should -BeExactly "test.xlsx"
|
||||
$actual.WorksheetName | Should -BeExactly "Sheet1"
|
||||
$actual.Visible | Should -Be $true
|
||||
$actual.Rows | Should -Be 10
|
||||
$actual.Columns | Should -Be 4
|
||||
$actual.Address | Should -BeExactly "A1:D10"
|
||||
|
||||
$actual.Path | Should -BeExactly ("TestDrive:" + [System.IO.Path]::DirectorySeparatorChar)
|
||||
|
||||
$actual.PropertyNames.Count | Should -Be 4
|
||||
$actual.PropertyNames[0] | Should -BeExactly "Region"
|
||||
$actual.PropertyNames[1] | Should -BeExactly "State"
|
||||
$actual.PropertyNames[2] | Should -BeExactly "Units"
|
||||
$actual.PropertyNames[3] | Should -BeExactly "Price"
|
||||
}
|
||||
}
|
||||
@@ -1,219 +0,0 @@
|
||||
Describe "Gradient Fill Functions" {
|
||||
BeforeAll {
|
||||
$script:testPath = "TestDrive:\GradientTest.xlsx"
|
||||
Remove-Item -Path $testPath -ErrorAction SilentlyContinue
|
||||
|
||||
# Import the module
|
||||
Import-Module "$PSScriptRoot\..\ImportExcel.psd1" -Force
|
||||
}
|
||||
|
||||
Context "Set-ExcelGradientFill Tests" {
|
||||
BeforeEach {
|
||||
$script:xl = Open-ExcelPackage -Path $testPath -Create
|
||||
$script:ws = Add-WorkSheet -ExcelPackage $xl -WorksheetName "GradientTest"
|
||||
}
|
||||
|
||||
AfterEach {
|
||||
Close-ExcelPackage -ExcelPackage $xl
|
||||
Remove-Item -Path $testPath -ErrorAction SilentlyContinue
|
||||
}
|
||||
|
||||
It "Should set linear gradient with basic properties" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
$gradient = $ws.Cells["A1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Linear"
|
||||
$gradient.Degree | Should -Be 90
|
||||
}
|
||||
|
||||
It "Should set path gradient" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Path -Color1 Red -Color2 Yellow
|
||||
|
||||
$gradient = $ws.Cells["A1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Path"
|
||||
}
|
||||
|
||||
It "Should set linear gradient with positioning" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 45 -Color1 Blue -Color2 Green -Top 0.2 -Bottom 0.8 -Left 0.1 -Right 0.9
|
||||
|
||||
$gradient = $ws.Cells["A1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Linear"
|
||||
$gradient.Degree | Should -Be 45
|
||||
$gradient.Top | Should -Be 0.2
|
||||
$gradient.Bottom | Should -Be 0.8
|
||||
$gradient.Left | Should -Be 0.1
|
||||
$gradient.Right | Should -Be 0.9
|
||||
}
|
||||
|
||||
It "Should accept System.Drawing.Color objects" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Color1 ([System.Drawing.Color]::Red) -Color2 ([System.Drawing.Color]::Blue)
|
||||
|
||||
$gradient = $ws.Cells["A1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Linear"
|
||||
}
|
||||
|
||||
It "Should work with ranges" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1:C3"] -Type Linear -Degree 135 -Color1 "Purple" -Color2 "Orange"
|
||||
|
||||
$gradient = $ws.Cells["A1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Linear"
|
||||
$gradient.Degree | Should -Be 135
|
||||
}
|
||||
|
||||
It "Should validate degree range" {
|
||||
{ Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 400 -Color1 Blue -Color2 Green } | Should -Throw
|
||||
}
|
||||
|
||||
It "Should validate position range" {
|
||||
{ Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Color1 Blue -Color2 Green -Top 1.5 } | Should -Throw
|
||||
}
|
||||
}
|
||||
|
||||
Context "Get-ExcelGradientFill Tests" {
|
||||
BeforeEach {
|
||||
$script:xl = Open-ExcelPackage -Path $testPath -Create
|
||||
$script:ws = Add-WorkSheet -ExcelPackage $xl -WorksheetName "GradientTest"
|
||||
}
|
||||
|
||||
AfterEach {
|
||||
Close-ExcelPackage -ExcelPackage $xl
|
||||
Remove-Item -Path $testPath -ErrorAction SilentlyContinue
|
||||
}
|
||||
|
||||
It "Should get gradient properties that can be read" {
|
||||
# Set a gradient first
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 45 -Color1 Blue -Color2 Green -Top 0.3 -Bottom 0.7
|
||||
|
||||
$result = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
|
||||
$result.Type | Should -Be "Linear"
|
||||
$result.Degree | Should -Be 45
|
||||
$result.Top | Should -Be 0.3
|
||||
$result.Bottom | Should -Be 0.7
|
||||
$result.Color1 | Should -BeNullOrEmpty
|
||||
$result.Color2 | Should -BeNullOrEmpty
|
||||
$result.ColorLimitationNote | Should -Match "EPPlus.*limitation"
|
||||
}
|
||||
|
||||
It "Should return PSCustomObject with expected properties" {
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Path -Color1 Red -Color2 Blue
|
||||
|
||||
$result = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
|
||||
$result | Should -BeOfType [PSCustomObject]
|
||||
$result.PSObject.Properties.Name | Should -Contain "Type"
|
||||
$result.PSObject.Properties.Name | Should -Contain "Degree"
|
||||
$result.PSObject.Properties.Name | Should -Contain "Color1"
|
||||
$result.PSObject.Properties.Name | Should -Contain "Color2"
|
||||
$result.PSObject.Properties.Name | Should -Contain "ColorLimitationNote"
|
||||
}
|
||||
}
|
||||
|
||||
Context "Copy-ExcelGradientFill Tests" {
|
||||
BeforeEach {
|
||||
$script:xl = Open-ExcelPackage -Path $testPath -Create
|
||||
$script:ws = Add-WorkSheet -ExcelPackage $xl -WorksheetName "GradientTest"
|
||||
}
|
||||
|
||||
AfterEach {
|
||||
Close-ExcelPackage -ExcelPackage $xl
|
||||
Remove-Item -Path $testPath -ErrorAction SilentlyContinue
|
||||
}
|
||||
|
||||
It "Should copy gradient geometric properties from source to target" {
|
||||
# Set up source
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 60 -Color1 Blue -Color2 Green -Top 0.1 -Bottom 0.9
|
||||
|
||||
# Copy to target (should warn about colors)
|
||||
$warningVar = $null
|
||||
Copy-ExcelGradientFill -SourceRange $ws.Cells["A1"] -TargetRange $ws.Cells["B1"] -WarningVariable warningVar
|
||||
|
||||
$targetGradient = $ws.Cells["B1"].Style.Fill.Gradient
|
||||
$targetGradient.Type | Should -Be "Linear"
|
||||
$targetGradient.Degree | Should -Be 60
|
||||
$targetGradient.Top | Should -Be 0.1
|
||||
$targetGradient.Bottom | Should -Be 0.9
|
||||
|
||||
$warningVar | Should -Match "cannot be copied"
|
||||
}
|
||||
|
||||
It "Should apply custom gradient definition" {
|
||||
$gradientDef = @{
|
||||
Type = "Linear"
|
||||
Degree = 120
|
||||
Color1 = [System.Drawing.Color]::Purple
|
||||
Color2 = [System.Drawing.Color]::Yellow
|
||||
Top = 0.25
|
||||
Bottom = 0.75
|
||||
Left = 0.2
|
||||
Right = 0.8
|
||||
}
|
||||
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["C1"] -GradientDefinition $gradientDef
|
||||
|
||||
$gradient = $ws.Cells["C1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Linear"
|
||||
$gradient.Degree | Should -Be 120
|
||||
$gradient.Top | Should -Be 0.25
|
||||
$gradient.Bottom | Should -Be 0.75
|
||||
$gradient.Left | Should -Be 0.2
|
||||
$gradient.Right | Should -Be 0.8
|
||||
}
|
||||
|
||||
It "Should apply gradient definition with string colors" {
|
||||
$gradientDef = @{
|
||||
Type = "Path"
|
||||
Color1 = "Red"
|
||||
Color2 = "Blue"
|
||||
}
|
||||
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["D1:F3"] -GradientDefinition $gradientDef
|
||||
|
||||
$gradient = $ws.Cells["D1"].Style.Fill.Gradient
|
||||
$gradient.Type | Should -Be "Path"
|
||||
}
|
||||
}
|
||||
|
||||
Context "Integration Tests" {
|
||||
BeforeEach {
|
||||
$script:xl = Open-ExcelPackage -Path $testPath -Create
|
||||
$script:ws = Add-WorkSheet -ExcelPackage $xl -WorksheetName "GradientTest"
|
||||
}
|
||||
|
||||
AfterEach {
|
||||
Close-ExcelPackage -ExcelPackage $xl
|
||||
Remove-Item -Path $testPath -ErrorAction SilentlyContinue
|
||||
}
|
||||
|
||||
It "Should work with the original issue scenario" {
|
||||
# Reproduce the original issue scenario but with new functions
|
||||
Set-ExcelGradientFill -Range $ws.Cells["A1"] -Type Linear -Degree 90 -Color1 Blue -Color2 Green
|
||||
|
||||
# Get gradient info (which will have limited color info)
|
||||
$gradientInfo = Get-ExcelGradientFill -Range $ws.Cells["A1"]
|
||||
|
||||
# Should be able to copy geometric properties
|
||||
$gradientInfo.Type | Should -Be "Linear"
|
||||
$gradientInfo.Degree | Should -Be 90
|
||||
|
||||
# Should be able to create a complete gradient definition for copying
|
||||
$completeDef = @{
|
||||
Type = $gradientInfo.Type
|
||||
Degree = $gradientInfo.Degree
|
||||
Top = $gradientInfo.Top
|
||||
Bottom = $gradientInfo.Bottom
|
||||
Left = $gradientInfo.Left
|
||||
Right = $gradientInfo.Right
|
||||
Color1 = [System.Drawing.Color]::Blue # Need to specify colors
|
||||
Color2 = [System.Drawing.Color]::Green
|
||||
}
|
||||
|
||||
# Should be able to copy to another cell
|
||||
Copy-ExcelGradientFill -TargetRange $ws.Cells["B1"] -GradientDefinition $completeDef
|
||||
|
||||
$targetGradient = $ws.Cells["B1"].Style.Fill.Gradient
|
||||
$targetGradient.Type | Should -Be "Linear"
|
||||
$targetGradient.Degree | Should -Be 90
|
||||
}
|
||||
}
|
||||
}
|
||||
12
__tests__/ImportExcelTests/ImportExcelTiming.tests.ps1
Normal file
12
__tests__/ImportExcelTests/ImportExcelTiming.tests.ps1
Normal file
@@ -0,0 +1,12 @@
|
||||
Import-Module $PSScriptRoot\..\..\ImportExcel.psd1 -Force
|
||||
|
||||
Describe "Tests Import-Excel Timings" -Tag Timing {
|
||||
It "Should read the 20k xlsx in -le 2100 milliseconds" {
|
||||
$timer = Measure-Command {
|
||||
$data = Import-Excel $PSScriptRoot\TimingRows20k.xlsx
|
||||
}
|
||||
|
||||
$timer.TotalMilliseconds | Should -BeLessOrEqual 2100
|
||||
$data.Count | Should -Be 19999
|
||||
}
|
||||
}
|
||||
BIN
__tests__/ImportExcelTests/TimingRows20k.xlsx
Normal file
BIN
__tests__/ImportExcelTests/TimingRows20k.xlsx
Normal file
Binary file not shown.
@@ -1,66 +0,0 @@
|
||||
if (-not (Get-command Import-Excel -ErrorAction SilentlyContinue)) {
|
||||
Import-Module $PSScriptRoot\..\ImportExcel.psd1
|
||||
}
|
||||
|
||||
Describe "Test New Conditional Formatting IconSet" -Tag ConditionalFormattingIconSet {
|
||||
BeforeEach {
|
||||
$xlFilename = "TestDrive:\ConditionalFormattingIconSet.xlsx"
|
||||
Remove-Item $xlFilename -ErrorAction SilentlyContinue
|
||||
|
||||
$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
|
||||
"@
|
||||
}
|
||||
|
||||
It "Should set ThreeIconSet" {
|
||||
# $cfi1 = New-ConditionalFormattingIconSet -Range C:C -ConditionalFormat ThreeIconSet -IconType Symbols -ShowIconOnly
|
||||
$cfi1 = New-ConditionalFormattingIconSet -Range C:C -ConditionalFormat ThreeIconSet -IconType Symbols
|
||||
|
||||
$data | Export-Excel $xlFilename -ConditionalFormat $cfi1
|
||||
$actual = Import-Excel $xlFilename
|
||||
$actual.count | Should -Be 9
|
||||
|
||||
$xl = Open-ExcelPackage $xlFilename
|
||||
$xl.Workbook.Worksheets.Count | Should -Be 1
|
||||
$targetSheet = $xl.Workbook.Worksheets[1]
|
||||
|
||||
$targetSheet.Name | Should -Be "Sheet1"
|
||||
$targetSheet.ConditionalFormatting.Count | Should -Be 1
|
||||
$targetSheet.ConditionalFormatting[0].Type | Should -Be "ThreeIconSet"
|
||||
$targetSheet.ConditionalFormatting[0].IconSet | Should -Be "Symbols"
|
||||
$targetSheet.ConditionalFormatting[0].Reverse | Should -BeFalse
|
||||
$targetSheet.ConditionalFormatting[0].ShowValue | Should -BeTrue
|
||||
|
||||
Close-ExcelPackage $xl -NoSave
|
||||
}
|
||||
|
||||
It "Should set ThreeIconSet with ShowOnlyIcon" {
|
||||
$cfi1 = New-ConditionalFormattingIconSet -Range C:C -ConditionalFormat ThreeIconSet -IconType Symbols -ShowIconOnly
|
||||
|
||||
$data | Export-Excel $xlFilename -ConditionalFormat $cfi1
|
||||
$actual = Import-Excel $xlFilename
|
||||
$actual.count | Should -Be 9
|
||||
|
||||
$xl = Open-ExcelPackage $xlFilename
|
||||
$xl.Workbook.Worksheets.Count | Should -Be 1
|
||||
$targetSheet = $xl.Workbook.Worksheets[1]
|
||||
|
||||
$targetSheet.Name | Should -Be "Sheet1"
|
||||
$targetSheet.ConditionalFormatting.Count | Should -Be 1
|
||||
$targetSheet.ConditionalFormatting[0].Type | Should -Be "ThreeIconSet"
|
||||
$targetSheet.ConditionalFormatting[0].IconSet | Should -Be "Symbols"
|
||||
$targetSheet.ConditionalFormatting[0].Reverse | Should -BeFalse
|
||||
$targetSheet.ConditionalFormatting[0].ShowValue | Should -BeFalse
|
||||
|
||||
Close-ExcelPackage $xl -NoSave
|
||||
}
|
||||
}
|
||||
@@ -1,12 +1,11 @@
|
||||
Describe "Test reading relative paths" {
|
||||
BeforeAll {
|
||||
$script:xlfileName = "TestR.xlsx"
|
||||
If ([String]::IsNullOrEmpty($PWD)) { $PWD = $PSScriptRoot }
|
||||
@{data = 1 } | Export-Excel (Join-Path $PWD "TestR.xlsx")
|
||||
@{data = 1 } | Export-Excel (Join-Path $PWD "TestR.xlsx")
|
||||
}
|
||||
|
||||
AfterAll {
|
||||
Remove-Item (Join-Path $PWD "$($script:xlfileName)")
|
||||
Remove-Item (Join-Path $PWD "$($script:xlfileName)")
|
||||
}
|
||||
|
||||
It "Should read local file".PadRight(90) {
|
||||
|
||||
@@ -1,44 +0,0 @@
|
||||
if (-not (Get-command Import-Excel -ErrorAction SilentlyContinue)) {
|
||||
Import-Module $PSScriptRoot\..\ImportExcel.psd1
|
||||
}
|
||||
|
||||
Describe "Test setting comment on cells in different ways" -Tag SetCellComment {
|
||||
BeforeAll {
|
||||
$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
|
||||
"@
|
||||
|
||||
$Excel = $data | Export-Excel -PassThru
|
||||
$ws = $Excel.Workbook.Worksheets | Select-Object -First 1
|
||||
}
|
||||
|
||||
AfterAll {
|
||||
Close-ExcelPackage $Excel
|
||||
}
|
||||
|
||||
It "Should add comments to multiple cells".PadRight(87) {
|
||||
Set-CellComment -Range "A1" -Worksheet $ws -Text "This was added with a single cell range"
|
||||
Set-CellComment -Range "A2:C2" -Worksheet $ws -Text "This was added with a multiple cell range"
|
||||
Set-CellComment -ColumnLetter A -Row 3 -Worksheet $ws -Text "This was added using a column letter and rownumber"
|
||||
Set-CellComment -ColumnNumber 1 -Row 4 -Worksheet $ws -Text "This was added using a column number and row number"
|
||||
|
||||
Set-CellComment -Range "B2" -Worksheet $ws -Text "This demonstrates an overwrite of a previously set comment"
|
||||
|
||||
$ws.Cells["A1"].Comment.Text | Should -BeExactly "This was added with a single cell range"
|
||||
$ws.Cells["A2"].Comment.Text | Should -BeExactly "This was added with a multiple cell range"
|
||||
$ws.Cells["B2"].Comment.Text | Should -BeExactly "This demonstrates an overwrite of a previously set comment"
|
||||
$ws.Cells["C2"].Comment.Text | Should -BeExactly "This was added with a multiple cell range"
|
||||
$ws.Cells["A3"].Comment.Text | Should -BeExactly "This was added using a column letter and rownumber"
|
||||
$ws.Cells["A4"].Comment.Text | Should -BeExactly "This was added using a column number and row number"
|
||||
}
|
||||
}
|
||||
@@ -132,11 +132,6 @@ Describe "Set-ExcelColumn, Set-ExcelRow and Set-ExcelRange" {
|
||||
12011,Crowbar,7,23.48
|
||||
"@
|
||||
|
||||
# Pester errors for countries with ',' as decimal separator
|
||||
Foreach ($datarow in $data) {
|
||||
$datarow.Price = [decimal]($datarow.Price)
|
||||
}
|
||||
|
||||
$DriverData = convertFrom-CSv @"
|
||||
Name,Wikipage,DateOfBirth
|
||||
Fernando Alonso,/wiki/Fernando_Alonso,1981-07-29
|
||||
@@ -397,7 +392,7 @@ Describe "Table Formatting" {
|
||||
$excel = $data2 | Export-excel -path $path -WorksheetName Hardware -AutoNameRange -AutoSize -BoldTopRow -FreezeTopRow -PassThru
|
||||
$ws = $excel.Workbook.Worksheets[1]
|
||||
#test showfilter & TotalSettings
|
||||
$Table = Add-ExcelTable -PassThru -Range $ws.Cells[$($ws.Dimension.address)] -TableStyle Light1 -TableName HardwareTable -TableTotalSettings @{"Total" = "Sum"} -ShowFirstColumn -ShowFilter:$false
|
||||
$Table = Add-ExcelTable -PassThru -Range $ws.Cells[$($ws.Dimension.address)] -TableStyle Light1 -TableName HardwareTable -TotalSettings @{"Total" = "Sum"} -ShowFirstColumn -ShowFilter:$false
|
||||
#test expnading named number formats
|
||||
Set-ExcelColumn -Worksheet $ws -Column 4 -NumberFormat 'Currency'
|
||||
Set-ExcelColumn -Worksheet $ws -Column 5 -NumberFormat 'Currency'
|
||||
|
||||
75
changelog.md
75
changelog.md
@@ -1,78 +1,3 @@
|
||||
# 7.8.10
|
||||
|
||||
- Thank you https://github.com/evenmartinsen for the PR to fix the AV
|
||||
|
||||
# 7.8.9
|
||||
|
||||
- Thanks to (Edward Miller)[https://github.com/edwardmiller-mesirow] for improving `ConvertTo-ExcelXlsx`and making it more robust
|
||||
|
||||
# 7.8.8
|
||||
|
||||
- Fix the release
|
||||
|
||||
# 7.8.7
|
||||
|
||||
- Thanks to [Phil Bossman](https://github.com/pbossman) for the PR and fixing this.
|
||||
|
||||
Now, back again, you can type `Import-Excel .\yearlySales.xlsx`, press <ctrl+space> and get a list of the worksheets in the Excel file
|
||||
|
||||
|
||||
|
||||
# Infrastructure change
|
||||
|
||||
- Thank you to [RipFence](https://github.com/RipFence) who asked how to place a chart on a different sheet from the data and then did a PR adding the example.
|
||||
- added `ignore` so files checked into examples do not trigger a CI run
|
||||
|
||||
# 7.8.6
|
||||
|
||||
- Thank you [John Boyne](https://github.com/kyllath)
|
||||
- Add missing parameter aliases to align with caller/callee
|
||||
|
||||
# 7.8.5
|
||||
|
||||
- Added `Get-ExcelFileSchema` to get the schema of an Excel file.
|
||||
- This was added to support interacting with `ChatGPT`. Passing the schema to the `ChatGPT` via `PowerShellAI` let's you ask questions about the data including generating code based on the schema.
|
||||
|
||||
```powershell
|
||||
Get-ExcelFileSchema .\salesData.xlsx
|
||||
```
|
||||
|
||||
```json
|
||||
{
|
||||
"ExcelFile": "salesData.xlsx",
|
||||
"WorksheetName": "Sheet1",
|
||||
"Visible": true,
|
||||
"Rows": 10,
|
||||
"Columns": 4,
|
||||
"Address": "A1:D10",
|
||||
"Path": ".",
|
||||
"PropertyNames": [
|
||||
"Region",
|
||||
"State",
|
||||
"Units",
|
||||
"Price"
|
||||
]
|
||||
}
|
||||
```
|
||||
|
||||
# 7.8.x
|
||||
|
||||
Thanks to [Thomas Hofkens](https://github.com/thkn-hofa)
|
||||
- Added `-NoHyperLinkConversion` to `Export-Excel` to no convert data to hyperlinks. [#1316](https://github.com/dfinke/ImportExcel/issues/1316)
|
||||
|
||||
# 7.8.4
|
||||
|
||||
- Add -ShowOnlyIcon to `New-ConditionalFormattingIconSet` does not show data in the cell, just the icon. Based on this discussion https://github.com/dfinke/ImportExcel/discussions/1340
|
||||
|
||||
# 7.8.3
|
||||
|
||||
Thanks [Thomas Hofkens](https://github.com/thkn-hofa)
|
||||
|
||||
- Extended Export-Excel with parameter TableTotalSettings
|
||||
- New Feature: Set-CellComment
|
||||
- Fix Pester error for countries with ',' as decimal separator
|
||||
- Fix Pester error for Windows PowerShell 5.1
|
||||
|
||||
# 7.8.2
|
||||
|
||||
- Fix docs [#1254](https://github.com/dfinke/ImportExcel/pull/1251)`Add-Worksheet` warning. Thank you [Wilson Stewart](https://github.com/WilsonStewart)
|
||||
|
||||
File diff suppressed because it is too large
Load Diff
Binary file not shown.
|
Before Width: | Height: | Size: 11 KiB |
5592
llms-examples.txt
5592
llms-examples.txt
File diff suppressed because it is too large
Load Diff
@@ -66,7 +66,7 @@ PS\> $excel = $avdata | Export-Excel -Path (Join-path $FilePath "\Machines.XLSX"
|
||||
$excel.Save() ; $excel.Dispose()
|
||||
```
|
||||
|
||||
Here Export-Excel is called with the -PassThru parameter, so the ExcelPackage object representing Machines.XLSX is stored in $Excel. The desired worksheet is selected, and then columns" B" and "I" are conditionally formatted \(excluding the top row\) to show red text if they contain "2003" or "Disabled" respectively.
|
||||
Here Export-Excel is called with the -PassThru parameter, so the ExcelPackage object representing Machines.XLSX is stored in $Excel.The desired worksheet is selected, and then columns" B" and "I" are conditionally formatted \(excluding the top row\) to show red text if they contain "2003" or "Disabled" respectively.
|
||||
|
||||
A fixed date format is then applied to columns D to G, and the top row is formatted.
|
||||
|
||||
@@ -83,7 +83,7 @@ Again Export-Excel has been called with -PassThru leaving a package object in $E
|
||||
|
||||
This time B1:B100 has been conditionally formatted with 3 icons, using the "Flags" Icon-Set.
|
||||
|
||||
Add-ConditionalFormatting does not provide access to every option in the formatting rule, so -PassThru has been used and the rule is modified to apply the flags in reverse order, and transitions between flags are set to 100 and 1000.
|
||||
Add-ConditionalFormatting does not provide accessto every option in the formatting rule, so -PassThru has been used and the rule is modified to apply the flags in reverse order, and transitions between flags are set to 100 and 1000.
|
||||
|
||||
### EXAMPLE 3
|
||||
|
||||
|
||||
@@ -81,7 +81,7 @@ PS\> $Excel = Invoke-Sum (Get-Process) Company Handles, PM, VirtualMemorySize |
|
||||
|
||||
The first line exports information to a table in new file; and captures the excel Package object in $Excel
|
||||
|
||||
The second line creates a chart on the first page of the work sheet, using the notation "TableName\[ColumnName\]" to refer to the data, the labels come Name column in the table, and the data series from its PM and VirtualMemorySize columns. The display names for these in the header are set to 'PM' and 'VMSize'.
|
||||
The second line creates a chart on the first page of the work sheet, using the notation "TableName\[ColumnnName\]" to refer to the data, the labels come Name column in the table, and the data series from its PM and VirtualMemorySize columns. The display names for these in the header are set to 'PM' and 'VMSize'.
|
||||
|
||||
### EXAMPLE 4
|
||||
|
||||
@@ -112,7 +112,7 @@ An existing Sheet where the chart will be created.
|
||||
|
||||
```yaml
|
||||
Type: ExcelWorksheet
|
||||
Parameter Sets: Worksheet
|
||||
Parameter Sets: Workshet
|
||||
Aliases:
|
||||
|
||||
Required: True
|
||||
|
||||
@@ -14,7 +14,7 @@ Adds Tables to Excel workbooks.
|
||||
## SYNTAX
|
||||
|
||||
```text
|
||||
Add-ExcelTable [-Range] <ExcelRange> [[-TableName] <String>] [[-TableStyle] <TableStyles>] [-ShowHeader] [-ShowFilter] [-ShowTotal] [[-TableTotalSettings] <Hashtable>] [-ShowFirstColumn] [-ShowLastColumn] [-ShowRowStripes] [-ShowColumnStripes] [-PassThru] [<CommonParameters>]
|
||||
Add-ExcelTable [-Range] <ExcelRange> [[-TableName] <String>] [[-TableStyle] <TableStyles>] [-ShowHeader] [-ShowFilter] [-ShowTotal] [[-TotalSettings] <Hashtable>] [-ShowFirstColumn] [-ShowLastColumn] [-ShowRowStripes] [-ShowColumnStripes] [-PassThru] [<CommonParameters>]
|
||||
```
|
||||
|
||||
## DESCRIPTION
|
||||
@@ -142,19 +142,9 @@ Accept pipeline input: False
|
||||
Accept wildcard characters: False
|
||||
```
|
||||
|
||||
### -TableTotalSettings
|
||||
### -TotalSettings
|
||||
|
||||
A HashTable in the form of either
|
||||
|
||||
- ColumnName = "Average"\|"Count"\|"CountNums"\|"Max"\|"Min"\|"None"\|"StdDev"\|"Sum"\|"Var"|\<Custom Excel function starting with "="\>
|
||||
- ```powershell
|
||||
ColumnName = @{
|
||||
Function = "Average"\|"Count"\|"CountNums"\|"Max"\|"Min"\|"None"\|"StdDev"\|"Sum"\|"Var"|<Custom Excel function starting with "=">
|
||||
Comment = $HoverComment
|
||||
}
|
||||
```
|
||||
|
||||
if specified, -ShowTotal is not needed.
|
||||
A HashTable in the form ColumnName = "Average"\|"Count"\|"CountNums"\|"Max"\|"Min"\|"None"\|"StdDev"\|"Sum"\|"Var" - if specified, -ShowTotal is not needed.
|
||||
|
||||
```yaml
|
||||
Type: Hashtable
|
||||
|
||||
@@ -16,19 +16,19 @@ Exports data to an Excel worksheet.
|
||||
### Default \(Default\)
|
||||
|
||||
```text
|
||||
Export-Excel [[-Path] <String>] [-InputObject <Object>] [-Calculate] [-Show] [-WorksheetName <String>] [-Password <String>] [-ClearSheet] [-Append] [-Title <String>] [-TitleFillPattern <ExcelFillStyle>] [-TitleBold] [-TitleSize <Int32>] [-TitleBackgroundColor <Object>][-IncludePivotTable] [-PivotTableName <String>] [-PivotRows <String[]> [-PivotColumns <String[]>] [-PivotData <Object>] [-PivotFilter <String[]>] [-PivotDataToColumn] [-PivotTableDefinition <Hashtable>] [-IncludePivotChart] [-ChartType <eChartType>] [-NoLegend] [-ShowCategory] [-ShowPercent] [-AutoSize] [-MaxAutoSizeRows <Object>] [-NoClobber] [-FreezeTopRow] [-FreezeFirstColumn] [-FreezeTopRowFirstColumn] [-FreezePane <Int32[]>] [-AutoFilter] [-BoldTopRow] [-NoHeader] [-RangeName <String>] [-TableName <Object>] [-TableStyle <TableStyles>] [-TableTotalSettings <HashTable>] [-Barchart] [-PieChart] [-LineChart] [-ColumnChart] [-ExcelChartDefinition <Object[]>] [-HideSheet <String[]>] [-UnHideSheet <String[]>] [-MoveToStart] [-MoveToEnd] [-MoveBefore <Object>] [-MoveAfter <Object>] [-KillExcel] [-AutoNameRange] [-StartRow <Int32>] [-StartColumn <Int32>] [-PassThru] [-Numberformat <String>] [-ExcludeProperty <String[]>] [-NoAliasOrScriptPropeties] [-DisplayPropertySet] [-NoNumberConversion <String[]>] [-NoHyperLinkConversion <String[]>] [-ConditionalFormat <Object[]>] [-ConditionalText <Object[]>] [-Style <Object[]>] [-CellStyleSB <ScriptBlock>] [-Activate] [-Now] [-ReturnRange] [-PivotTotals <String>] [-NoTotalsInPivot] [-ReZip] [<CommonParameters>]
|
||||
Export-Excel [[-Path] <String>] [-InputObject <Object>] [-Calculate] [-Show] [-WorksheetName <String>] [-Password <String>] [-ClearSheet] [-Append] [-Title <String>] [-TitleFillPattern <ExcelFillStyle>] [-TitleBold] [-TitleSize <Int32>] [-TitleBackgroundColor <Object>][-IncludePivotTable] [-PivotTableName <String>] [-PivotRows <String[]> [-PivotColumns <String[]>] [-PivotData <Object>] [-PivotFilter <String[]>] [-PivotDataToColumn] [-PivotTableDefinition <Hashtable>] [-IncludePivotChart] [-ChartType <eChartType>] [-NoLegend] [-ShowCategory] [-ShowPercent] [-AutoSize] [-MaxAutoSizeRows <Object>] [-NoClobber] [-FreezeTopRow] [-FreezeFirstColumn] [-FreezeTopRowFirstColumn] [-FreezePane <Int32[]>] [-AutoFilter] [-BoldTopRow] [-NoHeader] [-RangeName <String>] [-TableName <Object>] [-TableStyle <TableStyles>] [-Barchart] [-PieChart] [-LineChart] [-ColumnChart] [-ExcelChartDefinition <Object[]>] [-HideSheet <String[]>] [-UnHideSheet <String[]>] [-MoveToStart] [-MoveToEnd] [-MoveBefore <Object>] [-MoveAfter <Object>] [-KillExcel] [-AutoNameRange] [-StartRow <Int32>] [-StartColumn <Int32>] [-PassThru] [-Numberformat <String>] [-ExcludeProperty <String[]>] [-NoAliasOrScriptPropeties] [-DisplayPropertySet] [-NoNumberConversion <String[]>] [-ConditionalFormat <Object[]>] [-ConditionalText <Object[]>] [-Style <Object[]>] [-CellStyleSB <ScriptBlock>] [-Activate] [-Now] [-ReturnRange] [-PivotTotals <String>] [-NoTotalsInPivot] [-ReZip] [<CommonParameters>]
|
||||
```
|
||||
|
||||
### Package
|
||||
|
||||
```text
|
||||
Export-Excel -ExcelPackage <ExcelPackage> [-InputObject <Object>] [-Calculate] [-Show] [-WorksheetName <String>] [-Password <String>] [-ClearSheet] [-Append] [-Title <String>] [-TitleFillPattern <ExcelFillStyle>] [-TitleBold] [-TitleSize <Int32>] [-TitleBackgroundColor <Object>] [-IncludePivotTable] [-PivotTableName <String>] [-PivotRows <String[]>] [-PivotColumns <String[]>] [-PivotData <Object>] [-PivotFilter <String[]>] [-PivotDataToColumn] [-PivotTableDefinition <Hashtable>] [-IncludePivotChart] [-ChartType <eChartType>] [-NoLegend] [-ShowCategory] [-ShowPercent] [-AutoSize] [-MaxAutoSizeRows <Object>] [-NoClobber] [-FreezeTopRow] [-FreezeFirstColumn] [-FreezeTopRowFirstColumn] [-FreezePane <Int32[]>] [-AutoFilter] [-BoldTopRow] [-NoHeader] [-RangeName <String>] [-TableName <Object>] [-TableStyle <TableStyles>] [-TableTotalSettings <HashTable>] [-Barchart] [-PieChart] [-LineChart] [-ColumnChart] [-ExcelChartDefinition <Object[]>] [-HideSheet <String[]>] [-UnHideSheet <String[]>] [-MoveToStart] [-MoveToEnd] [-MoveBefore <Object>] [-MoveAfter <Object>] [-KillExcel] [-AutoNameRange] [-StartRow <Int32>] [-StartColumn <Int32>] [-PassThru] [-Numberformat <String>] [-ExcludeProperty <String[]>]
|
||||
[-NoAliasOrScriptPropeties] [-DisplayPropertySet] [-NoNumberConversion <String[]>] [-NoHyperLinkConversion <String[]>] [-ConditionalFormat <Object[]>] [-ConditionalText <Object[]>] [-Style <Object[]>] [-CellStyleSB <ScriptBlock>] [-Activate] [-ReturnRange] [-PivotTotals <String>] [-NoTotalsInPivot] [-ReZip] [<CommonParameters>]
|
||||
Export-Excel -ExcelPackage <ExcelPackage> [-InputObject <Object>] [-Calculate] [-Show] [-WorksheetName <String>] [-Password <String>] [-ClearSheet] [-Append] [-Title <String>] [-TitleFillPattern <ExcelFillStyle>] [-TitleBold] [-TitleSize <Int32>] [-TitleBackgroundColor <Object>] [-IncludePivotTable] [-PivotTableName <String>] [-PivotRows <String[]>] [-PivotColumns <String[]>] [-PivotData <Object>] [-PivotFilter <String[]>] [-PivotDataToColumn] [-PivotTableDefinition <Hashtable>] [-IncludePivotChart] [-ChartType <eChartType>] [-NoLegend] [-ShowCategory] [-ShowPercent] [-AutoSize] [-MaxAutoSizeRows <Object>] [-NoClobber] [-FreezeTopRow] [-FreezeFirstColumn] [-FreezeTopRowFirstColumn] [-FreezePane <Int32[]>] [-AutoFilter] [-BoldTopRow] [-NoHeader] [-RangeName <String>] [-TableName <Object>] [-TableStyle <TableStyles>] [-Barchart] [-PieChart] [-LineChart] [-ColumnChart] [-ExcelChartDefinition <Object[]>] [-HideSheet <String[]>] [-UnHideSheet <String[]>] [-MoveToStart] [-MoveToEnd] [-MoveBefore <Object>] [-MoveAfter <Object>] [-KillExcel] [-AutoNameRange] [-StartRow <Int32>] [-StartColumn <Int32>] [-PassThru] [-Numberformat <String>] [-ExcludeProperty <String[]>]
|
||||
[-NoAliasOrScriptPropeties] [-DisplayPropertySet] [-NoNumberConversion <String[]>] [-ConditionalFormat <Object[]>] [-ConditionalText <Object[]>] [-Style <Object[]>] [-CellStyleSB <ScriptBlock>] [-Activate] [-ReturnRange] [-PivotTotals <String>] [-NoTotalsInPivot] [-ReZip] [<CommonParameters>]
|
||||
```
|
||||
|
||||
## DESCRIPTION
|
||||
|
||||
Exports data to an Excel file and where possible tries to convert numbers in text fields so Excel recognizes them as numbers instead of text. After all: Excel is a spreadsheet program used for number manipulation and calculations. The parameter -NoNumberConversion \* can be used if number conversion is not desired. In the same way the parameter NoHyperLinkConversion \* can be used if hyperlink conversion is not desired
|
||||
Exports data to an Excel file and where possible tries to convert numbers in text fields so Excel recognizes them as numbers instead of text. After all: Excel is a spreadsheet program used for number manipulation and calculations. The parameter -NoNumberConversion \* can be used if number conversion is not desired.
|
||||
|
||||
## EXAMPLES
|
||||
|
||||
@@ -108,10 +108,10 @@ PS\> [PSCustOmobject][Ordered]@{
|
||||
PhoneNr1 = '+32 44'
|
||||
PhoneNr2 = '+32 4 4444 444'
|
||||
PhoneNr3 = '+3244444444'
|
||||
} | Export-Excel @ExcelParams -NoNumberConversion * -NoHyperLinkConversion *
|
||||
} | Export-Excel @ExcelParams -NoNumberConversion *
|
||||
```
|
||||
|
||||
Exports all data to the Excel file 'Excel.xslx' as is, no number or hyperlink conversion will take place. This means that Excel will show the exact same data that you handed over to the 'Export-Excel' function.
|
||||
Exports all data to the Excel file 'Excel.xslx' as is, no number conversion will take place. This means that Excel will show the exact same data that you handed over to the 'Export-Excel' function.
|
||||
|
||||
### EXAMPLE 5
|
||||
|
||||
@@ -156,29 +156,6 @@ If neither condition is met, the color will be the default, black text on a whit
|
||||
|
||||
### EXAMPLE 7
|
||||
|
||||
```text
|
||||
PS\> $r = Get-ChildItem C:\WINDOWS\system32 -File
|
||||
|
||||
PS\> $TotalSettings = @{
|
||||
Name = "Count"
|
||||
Extension = "=COUNTIF([Extension];`".exe`")"
|
||||
Length = @{
|
||||
Function = "=SUMIF([Extension];`".exe`";[Length])"
|
||||
Comment = "Sum of all exe sizes"
|
||||
}
|
||||
}
|
||||
|
||||
PS\> $r | Export-Excel -TableName system32files -TableStyle Medium10 -TableTotalSettings $TotalSettings -Show
|
||||
```
|
||||
|
||||
Exports a list of files with a totals row with three calculated totals:
|
||||
|
||||
- Total count of names
|
||||
- Count of files with the extension ".exe"
|
||||
- Total size of all file with extension ".exe" and add a comment as to not be mistaken that is is the total size of all files
|
||||
|
||||
### EXAMPLE 8
|
||||
|
||||
```text
|
||||
PS\> $ExcelParams = @{
|
||||
Path = $env:TEMP + '\Excel.xlsx'
|
||||
@@ -212,25 +189,25 @@ PS\> $Array | Update-FirstObjectProperties | Export-Excel @ExcelParams -Workshee
|
||||
|
||||
Updates the first object of the array by adding property 'Member3' and 'Member4'. Afterwards, all objects are exported to an Excel file and all column headers are visible.
|
||||
|
||||
### EXAMPLE 9
|
||||
### EXAMPLE 8
|
||||
|
||||
```text
|
||||
PS\> Get-Process | Export-Excel .\test.xlsx -WorksheetName Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM
|
||||
```
|
||||
|
||||
### EXAMPLE 10
|
||||
### EXAMPLE 9
|
||||
|
||||
```text
|
||||
PS\> Get-Process | Export-Excel .\test.xlsx -WorksheetName Processes -ChartType PieExploded3D -IncludePivotChart -IncludePivotTable -Show -PivotRows Company -PivotData PM
|
||||
```
|
||||
|
||||
### EXAMPLE 11
|
||||
### EXAMPLE 10
|
||||
|
||||
```text
|
||||
PS\> Get-Service | Export-Excel 'c:\temp\test.xlsx' -Show -IncludePivotTable -PivotRows status -PivotData @{status='count'}
|
||||
```
|
||||
|
||||
### EXAMPLE 12
|
||||
### EXAMPLE 11
|
||||
|
||||
```text
|
||||
PS\> $pt = [ordered]@{}
|
||||
@@ -260,7 +237,7 @@ Then it puts Service data on Sheet1 with one call to Export-Excel and Process Da
|
||||
|
||||
The third and final call adds the two PivotTables and opens the spreadsheet in Excel.
|
||||
|
||||
### EXAMPLE 13
|
||||
### EXAMPLE 12
|
||||
|
||||
```text
|
||||
PS\> Remove-Item -Path .\test.xlsx
|
||||
@@ -281,7 +258,7 @@ It then uses the package object to apply formatting, and then saves the workbook
|
||||
|
||||
Note: Other commands in the module remove the need to work directly with the package object in this way.
|
||||
|
||||
### EXAMPLE 14
|
||||
### EXAMPLE 13
|
||||
|
||||
```text
|
||||
PS\> Remove-Item -Path .\test.xlsx -ErrorAction Ignore
|
||||
@@ -304,7 +281,7 @@ This a more sophisticated version of the previous example showing different ways
|
||||
|
||||
In the final command a PivotChart is added and the workbook is opened in Excel.
|
||||
|
||||
### EXAMPLE 15
|
||||
### EXAMPLE 14
|
||||
|
||||
```text
|
||||
PS\> 0..360 | ForEach-Object {[pscustomobject][ordered]@{X=$_; Sinx="=Sin(Radians(x)) "} } |
|
||||
@@ -313,7 +290,7 @@ PS\> 0..360 | ForEach-Object {[pscustomobject][ordered]@{X=$_; Sinx="=Sin(Radian
|
||||
|
||||
Creates a line chart showing the value of Sine\(x\) for values of X between 0 and 360 degrees.
|
||||
|
||||
### EXAMPLE 16
|
||||
### EXAMPLE 15
|
||||
|
||||
```text
|
||||
PS\> Invoke-Sqlcmd -ServerInstance localhost\DEFAULT -Database AdventureWorks2014 -Query "select * from sys.tables" -OutputAs DataRows |
|
||||
@@ -360,7 +337,7 @@ Accept wildcard characters: False
|
||||
|
||||
### -InputObject
|
||||
|
||||
Data is usually piped into Export-Excel, but it also accepts data through the InputObject parameter
|
||||
Date is usually piped into Export-Excel, but it also accepts data through the InputObject parameter
|
||||
|
||||
```yaml
|
||||
Type: Object
|
||||
@@ -975,32 +952,6 @@ Accept pipeline input: False
|
||||
Accept wildcard characters: False
|
||||
```
|
||||
|
||||
### -TableTotalSettings
|
||||
|
||||
A HashTable in the form of either
|
||||
|
||||
- ColumnName = "Average"\|"Count"\|"CountNums"\|"Max"\|"Min"\|"None"\|"StdDev"\|"Sum"\|"Var"|\<Custom Excel function starting with "="\>
|
||||
- ```powershell
|
||||
ColumnName = @{
|
||||
Function = "Average"\|"Count"\|"CountNums"\|"Max"\|"Min"\|"None"\|"StdDev"\|"Sum"\|"Var"|<Custom Excel function starting with "=">
|
||||
Comment = $HoverComment
|
||||
}
|
||||
```
|
||||
|
||||
if specified, -ShowTotal is not needed.
|
||||
|
||||
```yaml
|
||||
Type: Hashtable
|
||||
Parameter Sets: (All)
|
||||
Aliases:
|
||||
|
||||
Required: False
|
||||
Position: 4
|
||||
Default value: None
|
||||
Accept pipeline input: False
|
||||
Accept wildcard characters: False
|
||||
```
|
||||
|
||||
### -Barchart
|
||||
|
||||
Creates a "quick" bar chart using the first text column as labels and the first numeric column as values.
|
||||
@@ -1357,24 +1308,6 @@ Accept pipeline input: False
|
||||
Accept wildcard characters: False
|
||||
```
|
||||
|
||||
### -NoHyperLinkConversion
|
||||
|
||||
By default the command will convert all URIs to hyperlinks if possible, but this isn't always desirable. -NoHyperLinkConversion allows you to add exceptions for the conversion.
|
||||
|
||||
The only Wildcard allowed is \* for all properties
|
||||
|
||||
```yaml
|
||||
Type: String[]
|
||||
Parameter Sets: (All)
|
||||
Aliases:
|
||||
|
||||
Required: False
|
||||
Position: Named
|
||||
Default value: None
|
||||
Accept pipeline input: False
|
||||
Accept wildcard characters: False
|
||||
```
|
||||
|
||||
### -ConditionalFormat
|
||||
|
||||
One or more conditional formatting rules defined with New-ConditionalFormattingIconSet.
|
||||
|
||||
@@ -64,7 +64,6 @@ If the default behavior is not desired and you want to import the complete works
|
||||
### EXAMPLE 1
|
||||
|
||||
```text
|
||||
|
||||
----------------------------------------------
|
||||
| File: Movies.xlsx - Sheet: Actors |
|
||||
----------------------------------------------
|
||||
@@ -90,7 +89,6 @@ Notice that column 'B' is not imported because there's no value in cell 'B1' tha
|
||||
### EXAMPLE 2
|
||||
|
||||
```text
|
||||
|
||||
----------------------------------------------
|
||||
| File: Movies.xlsx - Sheet: Actors |
|
||||
----------------------------------------------
|
||||
@@ -122,7 +120,6 @@ Notice that the column header \(row 1\) is imported as an object too.
|
||||
### EXAMPLE 3
|
||||
|
||||
```text
|
||||
|
||||
----------------------------------------------------------
|
||||
| File: Movies.xlsx - Sheet: Movies |
|
||||
----------------------------------------------------------
|
||||
@@ -163,7 +160,6 @@ Notice that empty rows are imported and that data for the property 'Genre' is no
|
||||
### EXAMPLE 4
|
||||
|
||||
```text
|
||||
|
||||
----------------------------------------------------------
|
||||
| File: Movies.xlsx - Sheet: Movies |
|
||||
----------------------------------------------------------
|
||||
@@ -196,7 +192,6 @@ Notice that empty rows and empty columns are not imported.
|
||||
### EXAMPLE 5
|
||||
|
||||
```text
|
||||
|
||||
----------------------------------------------------------
|
||||
| File: Movies.xlsx - Sheet: Actors |
|
||||
----------------------------------------------------------
|
||||
|
||||
Reference in New Issue
Block a user