{"action":"created","issue":{"url":"https://api.github.com/repos/dfinke/ImportExcel/issues/471","repository_url":"https://api.github.com/repos/dfinke/ImportExcel","labels_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/471/labels{/name}","comments_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/471/comments","events_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/471/events","html_url":"https://github.com/dfinke/ImportExcel/issues/471","id":370630400,"node_id":"MDU6SXNzdWUzNzA2MzA0MDA=","number":471,"title":"Not understanding Set-Excelrow","user":{"login":"liquidd-OU","id":44178622,"node_id":"MDQ6VXNlcjQ0MTc4NjIy","avatar_url":"https://avatars2.githubusercontent.com/u/44178622?v=4","gravatar_id":"","url":"https://api.github.com/users/liquidd-OU","html_url":"https://github.com/liquidd-OU","followers_url":"https://api.github.com/users/liquidd-OU/followers","following_url":"https://api.github.com/users/liquidd-OU/following{/other_user}","gists_url":"https://api.github.com/users/liquidd-OU/gists{/gist_id}","starred_url":"https://api.github.com/users/liquidd-OU/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/liquidd-OU/subscriptions","organizations_url":"https://api.github.com/users/liquidd-OU/orgs","repos_url":"https://api.github.com/users/liquidd-OU/repos","events_url":"https://api.github.com/users/liquidd-OU/events{/privacy}","received_events_url":"https://api.github.com/users/liquidd-OU/received_events","type":"User","site_admin":false},"labels":[{"id":525836157,"node_id":"MDU6TGFiZWw1MjU4MzYxNTc=","url":"https://api.github.com/repos/dfinke/ImportExcel/labels/Investigate","name":"Investigate","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":11,"created_at":"2018-10-16T14:06:23Z","updated_at":"2018-11-24T11:45:42Z","closed_at":null,"author_association":"NONE","body":"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.\r\n\r\nI am very new to PS, so it may be a simple question.  If so, I am sorry, but I do not understand.  \r\n\r\nset-excellrow -worksheetname ProgramsPivotTable -Row 2 -TextRotation 90 -Headingbold\r\n\r\nI am unable to pass the information to the sheet in any way that I can find.  \r\n\r\n`$Date = Get-Date\r\n$files = Get-ChildItem -path $PSScriptRoot\\servers\\*.*\r\n$Filename = \"-\" + $Date.Year + \"-\" + $Date.Month + \"-\" + $Date.Day + \"_\" + $date.Hour + $date.Minute\r\n$DataHotfix = ''\r\n\r\n\r\nFunction Get-RemoteProgram { }\r\n\r\ncls\r\n#Check to see if the Output directory exists.\r\nIf (Test-Path $PSScriptRoot\\output\\){  \r\n#path exists do nothing \r\n$PSScriptRoot\r\nWrite-Host \"Output Directory exists at $PSScriptRoot\" -ForegroundColor Green\r\n}\r\nElse\r\n{\r\nWrite-Host \"Output Directory Being Created at \"$PSScriptRoot -ForegroundColor Green\r\nNew-Item -ItemType directory -Path $PSScriptRoot\\output\r\n}\r\n\r\nforeach ($file in $files){\r\n    $servers = get-content $file\r\n    $file.Name\r\n    #Write Hotfixes to sheet\r\n    Write-Host 'Getting Hotfix data' -BackgroundColor Green\r\n    $DataHotfix  = Get-Hotfix -ComputerName $servers\r\n    $DataHotfix  | Select CSName, Description,HotFixID,InstalledOn | Sort CSName | Sort HotFixID | Export-Excel -Path \"$PSScriptRoot\\output\\$($file.basename)-list-$Filename.xlsx\" -WorksheetName HotFixes -Append \r\n    #write Progams to Sheet\r\n    Write-Host 'Getting Program data' -BackgroundColor Green\r\n    $DataProgram = Get-RemoteProgram -ComputerName $servers\r\n    $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  \r\n            \r\n}\r\n\r\nInvoke-Item \"$PSScriptRoot\\output\\\"\r\n\r\n`\r\nIt always asks for the **ExcelPackage**\r\nIf you have some example code of the two working together, It would be greatly appreciated.\r\n\r\nThanks, \r\nKeith"},"comment":{"url":"https://api.github.com/repos/dfinke/ImportExcel/issues/comments/441362101","html_url":"https://github.com/dfinke/ImportExcel/issues/471#issuecomment-441362101","issue_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/471","id":441362101,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MTM2MjEwMQ==","user":{"login":"jhoneill","id":16209593,"node_id":"MDQ6VXNlcjE2MjA5NTkz","avatar_url":"https://avatars1.githubusercontent.com/u/16209593?v=4","gravatar_id":"","url":"https://api.github.com/users/jhoneill","html_url":"https://github.com/jhoneill","followers_url":"https://api.github.com/users/jhoneill/followers","following_url":"https://api.github.com/users/jhoneill/following{/other_user}","gists_url":"https://api.github.com/users/jhoneill/gists{/gist_id}","starred_url":"https://api.github.com/users/jhoneill/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jhoneill/subscriptions","organizations_url":"https://api.github.com/users/jhoneill/orgs","repos_url":"https://api.github.com/users/jhoneill/repos","events_url":"https://api.github.com/users/jhoneill/events{/privacy}","received_events_url":"https://api.github.com/users/jhoneill/received_events","type":"User","site_admin":false},"created_at":"2018-11-24T11:45:42Z","updated_at":"2018-11-24T11:45:42Z","author_association":"CONTRIBUTOR","body":"@liquidd-OU   You're welcome. \r\n\r\nThe `-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. \r\n\r\nI've been slightly lazy with this one and it would be better to explain it as `^.*\\d+$`  and read it from right to left \r\n`$` means `end of the of string`. \r\n` \\d`  means _digit_ and `\\d+` means _digit repeated at least once_  so `\\d+$ ` means _ending with digit(s)_  \r\n`. ` means  _any character_  and `.*` means _any character repeated any number of times, even 0_ so\r\n`.*\\d+$ ` means _anything which ends with digit(s)_ \r\n`^` 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  …\"_\r\nBut 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. \r\n\r\n`-replace '.*(\\d+)$', '$1' `\r\ntranslates 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+)`\r\nthen `-As [INT]` says turn that string of digits into a number\r\n\r\nA lot of explaining for a couple of dozen characters ! \r\n\r\nJames\r\n\r\n  \r\n\r\n"}}
{"actor":{"display_login":"jhoneill"}}