Looking to Filter data displayed in Excel

[Shanefe]

Using your GetProcess example, how would you go about only showing Running processes?
I do not want to eliminate all the other data (i.e. sorting in PS), I just want the data hidden, i.e. just filter on "running", by default.
I need to be able to show a simplified view that will work most of the time, but the users will be able to change the 'default' filter if needed.

Get-Service | Select-Object -Property Status,Name,DisplayName,StartType |
Export-Excel -Path .\test.xlsx -AutoSize -AutoFilter -Show

Seems like it should be some type of conditional check, but I have been unable to find it.
-ConditionalText $(
New-ConditionalText Running -ConditionalTextColor Green)

[dfinke]

Thanks for trying ImportExcel and the question.

I always forget. Setting filters for either for Auto Filter and Pivot Filters can only be set after Excel renders the xlsx.

I believe it is both.

@jhoneill Did the deeper dive on this.

[jhoneill]

Yes, it is both.

What a lot of people would like is to be able to create a table with a filter, or a pivot table and set "Order By" or "Select Where" on it.
However the file created by library we that use is a "recipe" for the tables, and until the Excel loads and renders them there isn't a possibility to do filter/sort until its in Excel itself. I have pulled the XML apart looking for way to mimic what happens in Excel but I couldn't find a way to do it. It's probably the most wanted thing we haven't been able to do.

[Shanefe]

Hmm... Not what I wanted to hear, but it will stop me from looking into trying to get this done.

Thanks for the complete answer. The LLM model said it was easy.

Looks like there is some fairly simple C# calls that can be made to do it (using Microsoft.Office.Interop.Excel), but looks like it would require opening and closing the file when switching between the C# and ImportExcel code. I will test this out to see if it will work.

Thanks again.

• Shane

[jhoneill]

@Shanefe - you can poke stuff into a running copy of Excel from PowerShell - last time I did that it was done with COM objects, and I think that's what the Interop libraries do. [Obviously] we try to avoid things in the module which have dependencies on having excel installed - I was just doing something a server where we can put Excel to mail results to people as spreadsheets. Often the quickest way to get the code you want is to record it as a macro in Excel and then translate those operations into com calls.
But yes, doing that is a pain, and it's one we share, sadly. It's a gap between what we can write as .XLSX file (module), and what needs to be done by Excel.exe (Com stuff).

[Shanefe]

Here is some sample code that works as long as you have the Microsoft.Office.Interop.Excel.dll accessible.

function SortTable {
param (
[string] $TableName
)
Add-Type -AssemblyName "Microsoft.Office.Interop.Excel"

$Path = "D:\src\CSS-Exchange\Calendar\"
$File = $Path + $script:FileName

Write-Host -for Green "Opening [$File], Sheet [$TableName] and sorting the table [$TableName]."

$excelApp = New-Object -ComObject Excel.Application
$excelApp.Visible = $false
$workbook = $excelApp.Workbooks.Open($File)
$worksheet = $workbook.Sheets.Item($TableName)
$table = $worksheet.ListObjects.Item($TableName)

# Column index to filter (1 for the first column, 2 for the second, etc.)
$filterColumn = 3
$filterValue = "Running"

# Apply the filter
$table.Range.AutoFilter($filterColumn, $filterValue)

# Save and close the workbook
$workbook.Save()
$workbook.Close()
$excelApp.Quit()

# Release COM objects
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($table) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excelApp) | Out-Null

}

[dfinke]

Thanks! I really miss those ReleaseComObject calls ;-)

Unfortunate that it needs to drop into -Com mode, good that it only a few lines of code, and no C#.