forked from dfinke/ImportExcel
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSetFormat.ps1
377 lines (368 loc) · 21.3 KB
/
SetFormat.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
Function Set-ExcelRange {
<#
.SYNOPSIS
Applies number, font, alignment and/or color formatting, values or formulas to a range of Excel cells.
.DESCRIPTION
Set-ExcelRange was created to set the style elements for a range of cells,
this includes auto-sizing and hiding, setting font elements (Name, Size,
Bold, Italic, Underline & UnderlineStyle and Subscript & SuperScript),
font and background colors, borders, text wrapping, rotation, alignment
within cells, and number format.
It was orignally named "Set-Format", but it has been extended to set
Values, Formulas and ArrayFormulas (sometimes called Ctrl-shift-Enter
[CSE] formulas); because of this, the name has become Set-ExcelRange
but the old name of Set-Format is preserved as an alias.
.EXAMPLE
$sheet.Column(3) | Set-ExcelRange -HorizontalAlignment Right -NumberFormat "#,###" -AutoFit
Selects column 3 from a sheet object (within a workbook object, which
is a child of the ExcelPackage object) and passes it to Set-ExcelRange
which formats numbers as a integers with comma-separated groups,
aligns it right, and auto-fits the column to the contents.
.EXAMPLE
Set-ExcelRange -Range $sheet.Cells["E1:H1048576"] -HorizontalAlignment Right -NumberFormat "#,###"
Instead of piping the address, this version specifies a block of cells
and applies similar formatting.
.EXAMPLE
Set-ExcelRange $excel.Workbook.Worksheets[1].Tables["Processes"] -Italic
This time instead of specifying a range of cells, a table is selected
by name and formatted as italic.
#>
[cmdletbinding()]
[Alias("Set-Format")]
Param (
#One or more row(s), Column(s) and/or block(s) of cells to format.
[Parameter(ValueFromPipeline = $true,Position=0)]
[Alias("Address")]
$Range ,
#The worksheet where the format is to be applied.
[OfficeOpenXml.ExcelWorksheet]$WorkSheet ,
#Number format to apply to cells e.g. "dd/MM/yyyy HH:mm", "£#,##0.00;[Red]-£#,##0.00", "0.00%" , "##/##" , "0.0E+0" etc.
[Alias("NFormat")]
$NumberFormat,
#Style of border to draw around the range.
[OfficeOpenXml.Style.ExcelBorderStyle]$BorderAround,
#Color of the border.
$BorderColor=[System.Drawing.Color]::Black,
#Style for the bottom border.
[OfficeOpenXml.Style.ExcelBorderStyle]$BorderBottom,
#Style for the top border.
[OfficeOpenXml.Style.ExcelBorderStyle]$BorderTop,
#Style for the left border.
[OfficeOpenXml.Style.ExcelBorderStyle]$BorderLeft,
#Style for the right border.
[OfficeOpenXml.Style.ExcelBorderStyle]$BorderRight,
#Colour for the text - if none is specified it will be left as it is.
$FontColor,
#Value for the cell.
$Value,
#Formula for the cell.
$Formula,
#Specifies formula should be an array formula (a.k.a CSE [ctrl-shift-enter] formula).
[Switch]$ArrayFormula,
#Clear Bold, Italic, StrikeThrough and Underline and set color to Black.
[Switch]$ResetFont,
#Make text bold; use -Bold:$false to remove bold.
[Switch]$Bold,
#Make text italic; use -Italic:$false to remove italic.
[Switch]$Italic,
#Underline the text using the underline style in -UnderlineType; use -Underline:$false to remove underlining.
[Switch]$Underline,
#Specifies whether underlining should be single or double, normal or accounting mode. The default is "Single".
[OfficeOpenXml.Style.ExcelUnderLineType]$UnderLineType = [OfficeOpenXml.Style.ExcelUnderLineType]::Single,
#Strike through text; use -Strikethru:$false to remove Strike through
[Switch]$StrikeThru,
#Subscript or Superscript (or none).
[OfficeOpenXml.Style.ExcelVerticalAlignmentFont]$FontShift,
#Font to use - Excel defaults to Calibri.
[String]$FontName,
#Point size for the text.
[float]$FontSize,
#Change background color.
$BackgroundColor,
#Background pattern - Solid by default.
[OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid ,
#Secondary color for background pattern.
[Alias("PatternColour")]
$PatternColor,
#Turn on Text-Wrapping; use -WrapText:$false to turn off wrapping.
[Switch]$WrapText,
#Position cell contents to Left, Right, Center etc. default is 'General'.
[OfficeOpenXml.Style.ExcelHorizontalAlignment]$HorizontalAlignment,
#Position cell contents to Top, Bottom or Center.
[OfficeOpenXml.Style.ExcelVerticalAlignment]$VerticalAlignment,
#Degrees to rotate text. Up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise.
[ValidateRange(-90, 90)]
[int]$TextRotation ,
#Autofit cells to width (columns or ranges only).
[Alias("AutoFit")]
[Switch]$AutoSize,
#Set cells to a fixed width (columns or ranges only), ignored if Autosize is specified.
[float]$Width,
#Set cells to a fixed height (rows or ranges only).
[float]$Height,
#Hide a row or column (not a range); use -Hidden:$false to unhide.
[Switch]$Hidden
)
process {
if ($Range -is [Array]) {
[void]$PSBoundParameters.Remove("Range")
$Range | Set-ExcelRange @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] -or $Range -is [OfficeOpenXml.ExcelAddress])) {
$Range = $WorkSheet.Cells[$Range]
}
elseif ($Range -is [string]) {Write-Warning -Message "The range pararameter you have specified also needs a worksheet parameter."}
if ($ResetFont) {
$Range.Style.Font.Color.SetColor( ([System.Drawing.Color]::Black))
$Range.Style.Font.Bold = $false
$Range.Style.Font.Italic = $false
$Range.Style.Font.UnderLine = $false
$Range.Style.Font.Strike = $false
$Range.Style.Font.VerticalAlign = [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]::None
}
if ($PSBoundParameters.ContainsKey('Underline')) {
$Range.Style.Font.UnderLine = [boolean]$Underline
$Range.Style.Font.UnderLineType = $UnderLineType
}
if ($PSBoundParameters.ContainsKey('Bold')) {
$Range.Style.Font.Bold = [boolean]$bold
}
if ($PSBoundParameters.ContainsKey('Italic')) {
$Range.Style.Font.Italic = [boolean]$italic
}
if ($PSBoundParameters.ContainsKey('StrikeThru')) {
$Range.Style.Font.Strike = [boolean]$StrikeThru
}
if ($PSBoundParameters.ContainsKey('FontSize')){
$Range.Style.Font.Size = $FontSize
}
if ($PSBoundParameters.ContainsKey('FontName')){
$Range.Style.Font.Name = $FontName
}
if ($PSBoundParameters.ContainsKey('FontShift')){
$Range.Style.Font.VerticalAlign = $FontShift
}
if ($PSBoundParameters.ContainsKey('FontColor')){
if ($FontColor -is [string]) {$FontColor = [System.Drawing.Color]::$FontColor }
$Range.Style.Font.Color.SetColor( $FontColor)
}
if ($PSBoundParameters.ContainsKey('TextRotation')) {
$Range.Style.TextRotation = $TextRotation
}
if ($PSBoundParameters.ContainsKey('WrapText')) {
$Range.Style.WrapText = [boolean]$WrapText
}
if ($PSBoundParameters.ContainsKey('HorizontalAlignment')) {
$Range.Style.HorizontalAlignment = $HorizontalAlignment
}
if ($PSBoundParameters.ContainsKey('VerticalAlignment')) {
$Range.Style.VerticalAlignment = $VerticalAlignment
}
if ($PSBoundParameters.ContainsKey('Value')) {
if ($Value -match '^=') {$PSBoundParameters["Formula"] = $Value -replace '^=','' }
else {
$Range.Value = $Value
if ($Value -is [datetime]) { $Range.Style.Numberformat.Format = 'm/d/yy h:mm' }# This is not a custom format, but a preset recognized as date and localized. It might be overwritten in a moment
if ($Value -is [timespan]) { $Range.Style.Numberformat.Format = '[h]:mm:ss' }
}
}
if ($PSBoundParameters.ContainsKey('Formula')) {
if ($ArrayFormula) {$Range.CreateArrayFormula(($Formula -replace '^=','')) }
else {$Range.Formula = ($Formula -replace '^=','') }
}
if ($PSBoundParameters.ContainsKey('NumberFormat')) {
$Range.Style.Numberformat.Format = (Expand-NumberFormat $NumberFormat)
}
if ($BorderColor -is [string]) {$BorderColor = [System.Drawing.Color]::$BorderColor }
if ($PSBoundParameters.ContainsKey('BorderAround')) {
$Range.Style.Border.BorderAround($BorderAround, $BorderColor)
}
if ($PSBoundParameters.ContainsKey('BorderBottom')) {
$Range.Style.Border.Bottom.Style=$BorderBottom
$Range.Style.Border.Bottom.Color.SetColor($BorderColor)
}
if ($PSBoundParameters.ContainsKey('BorderTop')) {
$Range.Style.Border.Top.Style=$BorderTop
$Range.Style.Border.Top.Color.SetColor($BorderColor)
}
if ($PSBoundParameters.ContainsKey('BorderLeft')) {
$Range.Style.Border.Left.Style=$BorderLeft
$Range.Style.Border.Left.Color.SetColor($BorderColor)
}
if ($PSBoundParameters.ContainsKey('BorderRight')) {
$Range.Style.Border.Right.Style=$BorderRight
$Range.Style.Border.Right.Color.SetColor($BorderColor)
}
if ($PSBoundParameters.ContainsKey('BackgroundColor')) {
$Range.Style.Fill.PatternType = $BackgroundPattern
if ($BackgroundColor -is [string]) {$BackgroundColor = [System.Drawing.Color]::$BackgroundColor }
$Range.Style.Fill.BackgroundColor.SetColor($BackgroundColor)
if ($PatternColor) {
if ($PatternColor -is [string]) {$PatternColor = [System.Drawing.Color]::$PatternColor }
$Range.Style.Fill.PatternColor.SetColor( $PatternColor)
}
}
if ($PSBoundParameters.ContainsKey('Height')) {
if ($Range -is [OfficeOpenXml.ExcelRow] ) {$Range.Height = $Height }
elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
($Range.Start.Row)..($Range.Start.Row + $Range.Rows) |
ForEach-Object {$Range.WorkSheet.Row($_).Height = $Height }
}
else {Write-Warning -Message ("Can set the height of a row or a range but not a {0} object" -f ($Range.GetType().name)) }
}
if ($Autosize) {
if ($Range -is [OfficeOpenXml.ExcelColumn]) {$Range.AutoFit() }
elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
$Range.AutoFitColumns()
}
else {Write-Warning -Message ("Can autofit a column or a range but not a {0} object" -f ($Range.GetType().name)) }
}
elseif ($PSBoundParameters.ContainsKey('Width')) {
if ($Range -is [OfficeOpenXml.ExcelColumn]) {$Range.Width = $Width}
elseif ($Range -is [OfficeOpenXml.ExcelRange] ) {
($Range.Start.Column)..($Range.Start.Column + $Range.Columns - 1) |
ForEach-Object {
#$ws.Column($_).Width = $Width
$Range.Worksheet.Column($_).Width = $Width
}
}
else {Write-Warning -Message ("Can set the width of a column or a range but not a {0} object" -f ($Range.GetType().name)) }
}
if ($PSBoundParameters.ContainsKey('Hidden')) {
if ($Range -is [OfficeOpenXml.ExcelRow] -or
$Range -is [OfficeOpenXml.ExcelColumn] ) {$Range.Hidden = [boolean]$Hidden}
else {Write-Warning -Message ("Can hide a row or a column but not a {0} object" -f ($Range.GetType().name)) }
}
}
}
}
Function NumberFormatCompletion {
param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
$numformats = [ordered]@{
"General" = "General" # format ID 0
"Number" = "0.00" # format ID 2
"Percentage" = "0.00%" # format ID 10
"Scientific" = "0.00E+00" # format ID 11
"Fraction" = "# ?/?" # format ID 12
"Short Date" = "Localized" # format ID 14 - will be translated to "mm-dd-yy" which is localized on load by Excel.
"Short Time" = "Localized" # format ID 20 - will be translated to "h:mm" which is localized on load by Excel.
"Long Time" = "Localized" # format ID 21 - will be translated to "h:mm:ss" which is localized on load by Excel.
"Date-Time" = "Localized" # format ID 22 - will be translated to "m/d/yy h:mm" which is localized on load by Excel.
"Currency" = [cultureinfo]::CurrentCulture.NumberFormat.CurrencySymbol + "#,##0.00"
"Text" = "@" # format ID 49
"h:mm AM/PM" = "h:mm AM/PM" # format ID 18
"h:mm:ss AM/PM" = "h:mm:ss AM/PM" # format ID 19
"mm:ss" = "mm:ss" # format ID 45
"[h]:mm:ss" = "Elapsed hours" # format ID 46
"mm:ss.0" = "mm:ss.0" # format ID 47
"d-mmm-yy" = "Localized" # format ID 15 which is localized on load by Excel.
"d-mmm" = "Localized" # format ID 16 which is localized on load by Excel.
"mmm-yy" = "mmm-yy" # format ID 17 which is localized on load by Excel.
"0" = "Whole number" # format ID 1
"0.00" = "Number, 2 decimals" # format ID 2 or "number"
"#,##0" = "Thousand separators" # format ID 3
"#,##0.00" = "Thousand separators and 2 decimals" # format ID 4
"#," = "Whole thousands"
"#.0,," = "Millions, 1 Decimal"
"0%" = "Nearest whole percentage" # format ID 9
"0.00%" = "Percentage with decimals" # format ID 10 or "Percentage"
"00E+00" = "Scientific" # format ID 11 or "Scientific"
"# ?/?" = "One Digit fraction" # format ID 12 or "Fraction"
"# ??/??" = "Two Digit fraction" # format ID 13
"@" = "Text" # format ID 49 or "Text"
}
$numformats.keys.where({$_ -like "$wordToComplete*"} ) | ForEach-Object {
New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'" , $_ ,
([System.Management.Automation.CompletionResultType]::ParameterValue) , $numformats[$_]
}
}
if (Get-Command -ErrorAction SilentlyContinue -name Register-ArgumentCompleter) {
Register-ArgumentCompleter -CommandName Add-ConditionalFormatting -ParameterName NumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Export-Excel -ParameterName NumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Set-ExcelRange -ParameterName NumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Set-ExcelColumn -ParameterName NumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Set-ExcelRow -ParameterName NumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Add-PivotTable -ParameterName PivotNumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName New-PivotTableDefinition -ParameterName PivotNumberFormat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName New-ExcelChartDefinition -ParameterName XAxisNumberformat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName New-ExcelChartDefinition -ParameterName YAxisNumberformat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Add-ExcelChart -ParameterName XAxisNumberformat -ScriptBlock $Function:NumberFormatCompletion
Register-ArgumentCompleter -CommandName Add-ExcelChart -ParameterName YAxisNumberformat -ScriptBlock $Function:NumberFormatCompletion
}
Function Expand-NumberFormat {
<#
.SYNOPSIS
Converts short names for number formats to the formatting strings used in Excel
.DESCRIPTION
Where you can type a number format you can write, for example, 'Short-Date'
and the module will translate it into the format string used by Excel.
Some formats, like Short-Date change how they are presented when Excel
loads (so date will use the local ordering of year, month and Day). Other
formats change how they appear when loaded with different cultures
(depending on the country "," or "." or " " may be the thousand seperator
although Excel always stores it as ",")
.EXAMPLE
Expand-NumberFormat percentage
Returns "0.00%"
.EXAMPLE
Expand-NumberFormat Currency
Returns the currency format specified in the local regional settings. This
may not be the same as Excel uses. The regional settings set the currency
symbol and then whether it is before or after the number and separated with
a space or not; for negative numbers the number may be wrapped in parentheses
or a - sign might appear before or after the number and symbol.
So this returns $#,##0.00;($#,##0.00) for English US, #,##0.00 €;€#,##0.00-
for French. (Note some Eurozone countries write €1,23 and others 1,23€ )
In French the decimal point will be rendered as a "," and the thousand
separator as a space.
#>
[cmdletbinding()]
[OutputType([String])]
param (
#the format string to Expand
$NumberFormat
)
switch ($NumberFormat) {
"Currency" {
#https://msdn.microsoft.com/en-us/library/system.globalization.numberformatinfo.currencynegativepattern(v=vs.110).aspx
$sign = [cultureinfo]::CurrentCulture.NumberFormat.CurrencySymbol
switch ([cultureinfo]::CurrentCulture.NumberFormat.CurrencyPositivePattern) {
0 {$pos = "$Sign#,##0.00" ; break }
1 {$pos = "#,##0.00$Sign" ; break }
2 {$pos = "$Sign #,##0.00" ; break }
3 {$pos = "#,##0.00 $Sign" ; break }
}
switch ([cultureinfo]::CurrentCulture.NumberFormat.CurrencyPositivePattern) {
0 {return "$pos;($Sign#,##0.00)" }
1 {return "$pos;-$Sign#,##0.00" }
2 {return "$pos;$Sign-#,##0.00" }
3 {return "$pos;$Sign#,##0.00-" }
4 {return "$pos;(#,##0.00$Sign)" }
5 {return "$pos;-#,##0.00$Sign" }
6 {return "$pos;#,##0.00-$Sign" }
7 {return "$pos;#,##0.00$Sign-" }
8 {return "$pos;-#,##0.00 $Sign" }
9 {return "$pos;-$Sign #,##0.00" }
10 {return "$pos;#,##0.00 $Sign-" }
11 {return "$pos;$Sign #,##0.00-" }
12 {return "$pos;$Sign -#,##0.00" }
13 {return "$pos;#,##0.00- $Sign" }
14 {return "$pos;($Sign #,##0.00)" }
15 {return "$pos;(#,##0.00 $Sign)" }
}
}
"Number" {return "0.00" } # format id 2
"Percentage" {return "0.00%" } # format id 10
"Scientific" {return "0.00E+00" } # format id 11
"Fraction" {return "# ?/?" } # format id 12
"Short Date" {return "mm-dd-yy" } # format id 14 localized on load by Excel.
"Short Time" {return "h:mm" } # format id 20 localized on load by Excel.
"Long Time" {return "h:mm:ss" } # format id 21 localized on load by Excel.
"Date-Time" {return "m/d/yy h:mm"} # format id 22 localized on load by Excel.
"Text" {return "@" } # format ID 49
Default {return $NumberFormat}
}
}