1
Vote

Issue with autofit columns

description

Hi guys,

I just started with this library and already like it allot.
Only I cannot get the Columns that are setup to Autofit.
Could you maybe help me with this at the moment I have the following script setup:
#Require ExcelPSLib 0.5.7
Import-Module ExcelPSLib -Force

$users = import-csv -Path 'C:\Users\sesa312941\Desktop\PS\excel\Users_Baarn&Aartselaar.csv'

$RowPosition = 1
$OutputFileName = "C:\Users\sesa312941\Desktop\PS\excel\Users_Baarn&Aartselaar.xlsx"

[OfficeOpenXml.ExcelPackage]$excel = New-OOXMLPackage -author "Avalon77" -title "ComputerInfos" 
[OfficeOpenXml.ExcelWorkbook]$book = $excel | Get-OOXMLWorkbook

$excel | Add-OOXMLWorksheet -WorkSheetName "Users" -AutofilterRange "A1:G1"
$sheet = $book | Select-OOXMLWorkSheet -WorkSheetNumber 1 

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

$sheet | Set-OOXMLRangeValue -row $RowPosition -col 1 -value "SESA" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(1).Width = 12
#$sheet.Column(1).Autofit()
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value "Displayname" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(2).Width = 30
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value "Mail" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(3).Width = 40
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value "Department" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(4).Width = 30
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value "City" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(5).Width = 20
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 6 -value "LastLogonTimestamp" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(6).Width = 22
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 7 -value "AccountIsDisabled" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(7).Width = 20


$RowPosition++

$users |

ForEach-Object{

                $sheet | Set-OOXMLRangeValue -Row $RowPosition -Col 1 -Value $_.sAMAccountName -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value $_.Displayname -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value $_.Mail -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value $_.Department -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value $_.City -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 6 -value $_.LastLogonTimestamp -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 7 -value $_.AccountIsDisabled -StyleSheet $StyleNormal | Out-Null


                $RowPosition++
            }


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

comments

Avalon77 wrote Nov 16, 2016 at 6:50 AM

Hi,

Autofit can only be set on a range of cells when the data are already in place. Before saving your XLSX file you should do something like this :
#Require ExcelPSLib 0.5.7
Import-Module ExcelPSLib -Force

$users = import-csv -Path 'C:\Users\sesa312941\Desktop\PS\excel\Users_Baarn&Aartselaar.csv'

$RowPosition = 1
$OutputFileName = "C:\Users\sesa312941\Desktop\PS\excel\Users_Baarn&Aartselaar.xlsx"

[OfficeOpenXml.ExcelPackage]$excel = New-OOXMLPackage -author "Avalon77" -title "ComputerInfos" 
[OfficeOpenXml.ExcelWorkbook]$book = $excel | Get-OOXMLWorkbook

$excel | Add-OOXMLWorksheet -WorkSheetName "Users" -AutofilterRange "A1:G1"
$sheet = $book | Select-OOXMLWorkSheet -WorkSheetNumber 1 

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

$sheet | Set-OOXMLRangeValue -row $RowPosition -col 1 -value "SESA" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(1).Width = 12
#$sheet.Column(1).Autofit()
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value "Displayname" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(2).Width = 30
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value "Mail" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(3).Width = 40
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value "Department" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(4).Width = 30
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value "City" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(5).Width = 20
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 6 -value "LastLogonTimestamp" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(6).Width = 22
$sheet | Set-OOXMLRangeValue -row $RowPosition -col 7 -value "AccountIsDisabled" -StyleSheet $StyleHeader | Out-Null
$sheet.Column(7).Width = 20


$RowPosition++

$users |

ForEach-Object{

                $sheet | Set-OOXMLRangeValue -Row $RowPosition -Col 1 -Value $_.sAMAccountName -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 2 -value $_.Displayname -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 3 -value $_.Mail -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 4 -value $_.Department -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 5 -value $_.City -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 6 -value $_.LastLogonTimestamp -StyleSheet $StyleNormal | Out-Null
                $sheet | Set-OOXMLRangeValue -row $RowPosition -col 7 -value $_.AccountIsDisabled -StyleSheet $StyleNormal | Out-Null


                $RowPosition++
            }

$EndColumn = Get-OOXMLColumnString -ColNumber 7
$FirstColumn = Get-OOXMLColumnString -ColNumber 1
$LastRow = $RowPosition
$Sheet.Cells["$FirstColumn$($Sheet.Dimension.Start.Row):$EndColumn$LastRow"].AutoFitColumns()

$excel | Save-OOXMLPackage -FileFullPath $OutputFileName -Dispose
This should work perfectly. In the next version I'll update the demo.ps1 file to make it more complete and more up to date ^^