Not understanding Set-Excelrow

[liquidd-OU]

I have created an excel file using your product, and it works amazingly great and easy. I am however trying to modify a row of the pivot table so that I can compare which apps are on which servers so that I can make certain they are all the same. I want it to rotate the Program name row +90, and change column width to fit.

I am very new to PS, so it may be a simple question. If so, I am sorry, but I do not understand.

set-excellrow -worksheetname ProgramsPivotTable -Row 2 -TextRotation 90 -Headingbold

I am unable to pass the information to the sheet in any way that I can find.

`$Date = Get-Date $files = Get-ChildItem -path $PSScriptRoot\servers*.* $Filename = "-" + $Date.Year + "-" + $Date.Month + "-" + $Date.Day + "_" + $date.Hour + $date.Minute $DataHotfix = ''

Function Get-RemoteProgram { }

cls

Check to see if the Output directory exists.

If (Test-Path $PSScriptRoot\output){

path exists do nothing

$PSScriptRoot Write-Host "Output Directory exists at $PSScriptRoot" -ForegroundColor Green } Else { Write-Host "Output Directory Being Created at "$PSScriptRoot -ForegroundColor Green New-Item -ItemType directory -Path $PSScriptRoot\output }

foreach ($file in $files){ $servers = get-content $file $file.Name

Write Hotfixes to sheet

Write-Host 'Getting Hotfix data' -BackgroundColor Green
$DataHotfix  = Get-Hotfix -ComputerName $servers
$DataHotfix  | Select CSName, Description,HotFixID,InstalledOn | Sort CSName | Sort HotFixID | Export-Excel -Path "$PSScriptRoot\output\$($file.basename)-list-$Filename.xlsx" -WorksheetName HotFixes -Append 
#write Progams to Sheet
Write-Host 'Getting Program data' -BackgroundColor Green
$DataProgram = Get-RemoteProgram -ComputerName $servers
$DataProgram | Sort ComputerName | Sort ProgramName | Export-Excel -Path "$PSScriptRoot\output\$($file.basename)-list-$Filename.xlsx" -WorksheetName Programs -Append   -IncludePivotTable -PivotRows computername -PivotColumns programname -PivotData @{programname=”count”} -FreezePane 3  

}

Invoke-Item "$PSScriptRoot\output\"

` It always asks for the ExcelPackage If you have some example code of the two working together, It would be greatly appreciated.

Thanks, Keith

[jhoneill]

When you do $DataProgram | Sort ComputerName | Sort ProgramName | Export-Excel -Path "$PSScriptRoot\output$($file.basename)-list-$Filename.xlsx" -WorksheetName Programs -Append -IncludePivotTable -PivotRows computername -PivotColumns programname -PivotData @{programname=”count”} -FreezePane 3

It writes the file straight away.

You need to do $PKG = $DataProgram | Sort ComputerName | Sort ProgramName | Export-Excel -Path "$PSScriptRoot\output$($file.basename)-list-$Filename.xlsx" -WorksheetName Programs -Append -IncludePivotTable -PivotRows computername -PivotColumns programname -PivotData @{programname=”count”} -FreezePane 3 -passthru

The -passthru says "Don't write yet, give me an object which represents the file" and that object gets stored in $pkg.

Set-Row (and similar) need to have an object which represents the file (you can do Open-ExcelPackage, or use -Passthru) so now you can do Set-ExcelRow -ExcelPackage $pkg -worksheetname ProgramsPivotTable -Row 2 -TextRotation 90 -bold

Headingbold is if you have a label for the row - I think you just want the whole of Row 2 in bold type.

[liquidd-OU]

I changed the code the way that I think you are meaning, and I am now getting a new error.

` Write-Host 'Getting Program data' -BackgroundColor Green

$DataProgram = Get-RemoteProgram -ComputerName $servers

$PKG = $DataProgram | Sort ComputerName | Sort ProgramName | Export-Excel -Path "$PSScriptRoot\output\$($file.basename)-list$Filename.xlsx" -WorksheetName Programs -Append -IncludePivotTable -PivotRows computername -PivotColumns programname -PivotData @{programname=”count”} -FreezePane 3 -FreezeFirstColumn -passthru        

Set-ExcelRow -ExcelPackage $pkg -worksheetname ProgramsPivotTable -Row 2 -TextRotation 90 -bold`

I now receive the messages

Getting Program data

The property 'Bold' cannot be found on this object. Verify that the property exists and can be set. At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\5.3.4\SetFormat.ps1:128 char:17 etc...

The property 'TextRotation' cannot be found on this object. Verify that the property exists and can be set. At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\5.3.4\SetFormat.ps1:146 char:17 etc...

I get the same messages if I Open-ExcelPackage.

[liquidd-OU]

I did not mean to close this.

[jhoneill]

I'll take a look at this shortly.

[jhoneill]

Sorry. I tried to answer your original question without testing it, and I've ended up misleading you.

Because the way pivot tables are handled, there is no data in target sheet until you open the file in Excel and it processes the Pivot table, and this will cause all the formatting functions to fail You can use

$Pkg.ProgramsPivotTable .Row(2).style.font.bold  = $true
$Pkg.ProgramsPivotTable .Row(2).style.textRotation = 90 

But when the pivot table is created it will have a table style applied to it so the part of row 2 outside the table will get the formatting, but the bit you want won't. Unfortunately the scope to format a pivot table, is limited to number format and table style.

[liquidd-OU]

If it only occurs when the sheet is initially opened, could I possibly use the use the Open-excelpackage, or does it have to be physically opened? Do you have a method of closing an excel sheet if it is open?

Or, Maybe use the -show option then close it for the script to continue.

[jhoneill]

It has to be opened in Excel. You can open the sheet and then use the Excel Com model to make changes or to close it again.

$objExcel   = New-Object -ComObject Excel.Application
$objworkbook = $objExcel.Workbooks.Open("C:\Users\mcp\AppData\Local\Temp\test.xlsx")
$objworkbook.Save()
$objworkbook.close($false)

I'm not sure how that makes the file look to epplus I tried putting this between the open and save

$worksheet.Range("2:2").font.bold= $true
$worksheet.Range("2:2").orientation = -4171
$worksheet.Range("2:2").horizontalalignment = -4108

(you can see the codes used for orientation at https://docs.microsoft.com/en-us/office/vba/api/excel.xlticklabelorientation ) I added the horizontal centring so the label doesn't clash with the pull down control It seems the heading in the table is always bold. You can italic on or off but bold is always on even when you try to make it $false

[liquidd-OU]

I am still working on the sheet listed above, and I am still struggling with some things that I feel are my not understanding some of the code that you have in your tests and your examples. I promise that I have read so much of your code and your examples.

I have read that you are talking about writing a manual for others to purchase for profit, and I would be willing to purchase one If it is something I can understand.

I am including part of my code, because I think that If I can fix it, I can fix the other parts myself.

Function Check-Reg-Values(){
    foreach ($RegLine in $RegCSV){

        $array =@() 
        $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($RegLine.Hive, $server) 
        $key = $reg.OpenSubkey($RegLine.RegKey)
           If ($key){
           $value = $key.GetValue($RegLine.RegValue)  
           $array += $value
           #Write-output $array -BackgroundColor Yellow -ForegroundColor Black
                If ($array -eq $RegLine.WhatShouldBe){

                $RegLine.Lookingfor, $RegLine.RegKey , $array , $RegLine.WhatShouldBe
                }
                Else
                {
                $RegLine.Lookingfor,$RegLine.RegKey , $array, $RegLine.WhatShouldBe 
                }
           }
           Else
           {
           $T = "Registry Key "  + $RegLine.RegKey + "does not exist"
           $T
           }
    }
}

foreach ($file in $files){
    $servers = get-content $file
    $file.Name | Write-Host -ForegroundColor Red
    $ExcelParams = @{
        Path    = "$PSScriptRoot\output\$($file.basename)-list$Filename.xlsx"
        Show    = $False
        Verbose = $true
}    
    Write-Host 'Getting Hotfix data' -BackgroundColor Green
    Write-Host 'Getting Program data' -BackgroundColor Green
    $DataProgram = Get-RemoteProgram -ComputerName $servers -Property DisplayVersion,VersionMajor -ExcludeSimilar

    #write Each server to its Own Sheet
    $DataProgram | Sort ComputerName | Sort ProgramName | Export-Excel -Path $excelParams.Path -WorksheetName Programs -Append   -IncludePivotTable -PivotRows computername -PivotColumns programname -PivotData @{programname=”count”} -FreezePane 3 -AutoSize

    Write-Host 'Getting individual Server data' -BackgroundColor Green
    ForEach ($Server in $Servers){ 
    $server
    $StripName = $server -replace "[^0-9]" 
    $Adobe = Get-RemoteProgram -ComputerName $server -Property DisplayVersion,VersionMajor -ExcludeSimilar -IncludeProgram *adobe*
    $EpicInstall = Get-Epic-Install $Server
    $CheckRegOut = Check-Reg-Values $Server

    $PKG += Write-Output "Adobe Version" |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append
    $PKG += Write-Output $Adobe |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output 'Check Registry Value' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append
    $PKG += Write-Output $CheckRegOut |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append
    #$PKG += Write-Output 'Epic Software/Updates found on server' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append
    $PKG += Write-Output 'Epic Version' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append 
    $PKG += Write-Output ' ' |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append  
    $PKG += Write-Output `t$EpicInstall |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append -PassThru

    #Keep Incase I get it to work.
    #Set-ExcelRow -ExcelPackage $PKG -Worksheetname $StripName -Row 3
    #Set-ExcelColumn -ExcelPackage $PKG -Column 1 -AutoSize -Worksheetname $StripName 

    Close-ExcelPackage -ExcelPackage $PKG 
    }
}

The Function will read a CSV file and look for variables to compare. Originally it would output to screen using Write-Host with colors, and it worked well. The first portion of the IF was if the registry matched the expected value, it would turn green. Otherwise the Else would turn the output line red. However, Now I am wanting to send it to your excel module for a historical save. However, when I output from what did look nice, and format correctly, now will return to multiple rows. (1)

The CSV file has four fields description of what I am looking for, Which registry Hive to check, Key, Value, and What I want it to read.

Is it possible to either format the outputs with color, or be allowed to do a conditional formatting that will compare two columns. I normally use the "Use a formula to determine which cells to format" rule and enter the code =$A1<>$B1 when I do this manually. (2)

Lastly, when I try to change the code to label the sheets, I always loose the first "comment" that I have entered. I can either have the title to the page, or the header, but not both. (3)

$PKG = Write-Output "Adobe Version" |Export-Excel -Path $excelParams.Path -WorksheetName $StripName -Append -Title $Server -TitleBackgroundColor Red

I have already asked people, but not managed to get help.

I greatly appreciate your work, and any guidance you can give me.

Thanks, Keith

[dfinke]

Hey Keith.

Check out this example, see if it helps.

https://github.com/dfinke/ImportExcel/blob/master/Examples/CustomReporting/CustomReport.ps1

I prefer to collect all my data, like below, then export it in one shot, if possible and then apply the formatting using Set-Format.

Data

$data = @"
From,To,RDollars,RPercent,MDollars,MPercent,Revenue,Margin
Atlanta,New York,3602000,.0809,955000,.09,245,65
New York,Washington,4674000,.105,336000,.03,222,16
Chicago,New York,4674000,.0804,1536000,.14,550,43
New York,Philadelphia,12180000,.1427,-716000,-.07,321,-25
New York,San Francisco,3221000,.0629,1088000,.04,436,21
New York,Phoneix,2782000,.0723,467000,.10,674,33
"@ | ConvertFrom-Csv

Result

[jhoneill]

OK. Some tips in addition to Doug's. Remember any time you do "Write-host" - and that includes any time you set colors - it is something to show the user which is not data. Write-Output is almost always a Tautology - and writing one cell at a time like this is a short path to insanity. If you take Doug's data, your check-reg-values function is sending back the equivalent of

Atlanta
New York 
3602000 
.0809 
955000
.09 
245 
65

So let's start by fixing that

If ($key){ 
    $regline | select-object -Property Lookingfor, RegKey  , @{n="Exists"; e={$true}},  @{n="Value" ; e={ $key.GetValue($RegLine.RegValue)  }} , WhatShouldBe
                }
else {
    $regline | select-object -Property Lookingfor, RegKey   , @{n="Exists"; e={$false}},  @{n="Value" ; e={ $null }} WhatShouldBe

}

I can't see what "Get-Epic-Install" or Get-Remote program are doing but I can take a guess: here's how I'd write it.

$r = Get-RemoteProgram -ComputerName $server -Property DisplayVersion,VersionMajor -ExcludeSimilar -IncludeProgram *adobe* | export-excel - $excelParams.Path -WorksheetName $StripName -title "Adobe Version"  -returnRange 

$row = $r -replace '.*(\d+)$','$1' -as [int]    

$ExcelPackage = Check-Reg-Values $Server | export-excel - $excelParams.Path -WorksheetName $StripName -title "Check Registry Value" -startrow ($row+1) -passthru

-returnRange means after the first command $r will hold something like A1: D4 The -replace keeps only the digits at the end, and converts it to a number Then the next command saves the Excel package through to the next command.

Note that -append is for when you want to put additional rows onto a table where you have headers and a title.-Titleis ignored when you specify-Append. So instead we have to say create new headings and a new title and don't over-write the data in A1:D4

Which puts in your registry values like this, starting from row 5 Check Registry value Lookingfor | Regkey | <<value(s)>> | TRUE | <>

So now you can set a conditional format on column c

$address = "C{0}:C{1}" -f ($row+3) , $excelPackage.Workbook.Worksheets[$StripName].Dimension.End.Row
$formula = "=C{0}=E{0}" -f ($row + 3) 
Add-ConditionalFormatting -Address $address -WorkSheet $excelPackage.Workbook.Worksheets[$stripname] -RuleType Expression -ConditionValue $formula -ForegroundColor Green

(I don't like using -f , but it is the neatest way to put the row numbers in at place holders in the address and formula strings). So now if the text matches it goes green .
You could have $formula = "=C{0}<>E{0}" -f ($row + 3) and use red instead. Hope that helps

[liquidd-OU]

@jhoneill

Sorry for the slow response. I have been busy with the holiday.

This helped tremendously. I had been looking at the examples, and they did all of the cell assignments as a static point, where all of my set rows, and columns are dependent upon the number of variables returned.

I however have not figured out how to get the $row to work correctly after using the -passthru. Do I need to close the package, and return the value, or can I continue to set my conditional format range, ad colors, etc? However, I do not get any information back on the range when I do that.

Lastly, the section that you included to determine the line row, Where would be good reading to find out about that? -replace '.*(\d+)$','$1' -as [int]

Thank you soo much for your help with this.

Keith

[jhoneill]

@liquidd-OU You're welcome.

The -replace operator uses regular expressions, which is a whole new subject in itself, there are whole books and plenty of internet articles out there. If you read up on them .NET provides a regex object and Powershell has -match and -replace operators. The object is case sensitive, but the operators are insensitive.

I've been slightly lazy with this one and it would be better to explain it as ^.*\d+$ and read it from right to left $ means end of the of string. \d means digit and \d+ means digit repeated at least once so \d+$ means ending with digit(s)
. means any character and .* means any character repeated any number of times, even 0 so .*\d+$ means anything which ends with digit(s) ^ in that means the start so I could be explicit that I mean the whole string if it ends with digits. but I'm saying "Replace anything which ends with digits with …" But with what exactly ? $1 means the first thing wrapped in () (note it is in single quotes to stop PowerShell will treating $1 as a variable name- you can have $2, $3, and so on). I could have "Row $1". So I've changed \d+ to (\d) in the search expression.

-replace '.*(\d+)$', '$1' translates to "If it ends with digits, replace EVERYTHING with those digits" which turns a returned range like "A1:AZ101" into "101" - A1:AZ matches the .*, and 101 is lassoed by (\d+) then -As [INT] says turn that string of digits into a number

A lot of explaining for a couple of dozen characters !

James

[liquidd-OU]

@jhoneill Is it possible to -ReturnRange while I am using the -PassThru command? I have added it to the end of my output page, and it works, but it is important information, so I would like for it to be in the visible area when the tab is clicked on. Even if I need to count the rows inside as they are written, and then add them to the last $Row that I returned.

Also, the -replace '.*(\d+)$', '$1' only returns the last digit. I eventually googled and with the help of a friend found $row = [regex]::Matches($FilesOut, "\d+(?!.*\d+)").value that returned the information, but did not work until I added the -as [int] that you recommended, and it worked.

Here is the full section of code that I am talking about.

$FilesOut = Chk-files-and-Dirs $Server | ConvertFrom-Csv|
        Export-Excel -path $ExcelParams.Path -WorksheetName $StripName -title "Files and Directories"  -ReturnRange -Startrow ($row+2) -TitleBackgroundColor LightGray
    $row = [regex]::Matches($FilesOut, "\d+(?!.*\d+)").value -as [int]

    $ExcelPackage = Check-Reg-Values $Server | 
        Export-Excel -Path $ExcelParams.Path -WorksheetName $StripName -Title "Registry Values" -ReturnRange -startrow ($row+2) -passthru -TitleBackgroundColor LightGray 
    $address = "D{0}:D{1}" -f ($row+4) , $excelPackage.Workbook.Worksheets[$StripName].Dimension.End.Row
    $formula = "=D{0}<>E{0}" -f ($row + 4) #Highlights cells if they are different
    Set-ExcelRange -Range $address -BackgroundColor yellowgreen -WorkSheet $excelPackage.Workbook.Worksheets[$stripname]
    Add-ConditionalFormatting -Address $address -WorkSheet $excelPackage.Workbook.Worksheets[$stripname] -RuleType Expression -ConditionValue $formula -ForegroundColor Black -BackgroundColor Tomato
    Close-ExcelPackage -ExcelPackage $ExcelPackage

Is it possible to open and close the excel sheet to cause the sheet to write the "Pivot Table Information" as mentioned above? I have not been successful at getting it to work.

Thank you again. Keith

[dfinke]

You can get the range, after using -PassThru.

$data = ConvertFrom-Csv @'
Item,Quantity,Price,Total Cost
Footballs,9,21.95,197.55
Cones,36,7.99,287.64
Shin Guards,14,10.95,153.3
Turf Shoes,22,79.95,1758.9
Baseballs,68,7.99,543.32
Baseball Gloves,31,65.00,2015.00
Baseball Bats,38,159.00,6042.00
'@

$f = "$env:TEMP\styles.xlsx"
rm $f -ErrorAction SilentlyContinue

$pkg = $data | Export-Excel -Path $f -PassThru 

$pkg.Workbook.Worksheets["Sheet1"].Dimension

Here's what the Dimension property has on the worksheet.

Start   : OfficeOpenXml.ExcelCellAddress
End     : OfficeOpenXml.ExcelCellAddress
Table   : 
Address : A1:D8
IsName  : False
Rows    : 8
Columns : 4