Sorting Columns Conditionally with Custom Parameters

[nevertestinprod]

I haven't found any documentation or examples covering sorting by columns. If it does exist, please post a link in a reply.

I have a pair of reports that can be generated from .nessus Vulnerability and Compliance scan files. I've used Import-Excel to format them about 95% of the way, and I have to do the final step manually. I'd like to know if there's a way to sort columns based on custom properties. If that exists, I'll be able to automate the entire process.

Vuln Script Example:

Sort Order	Column	Custom Properties to sort on
First	Severity	Critical, High, Medium, Low, None
Second	Exploited	true, false, (Blanks)

Compliance Script Example:

Sort Order	Column	Custom Properties to sort on
First	Result	FAILED, WARNING, ERROR, PASSED
Second	CAT	1, 2, 3

If it doesn't exist, I'd like to request that this be investigated and added as a feature if possible.

Here's the section of the Compliance script that takes care of the formatting. The vuln script is similar:

` $tempfilepath = "$WorkDir$ScanFile-PreFormat.xlsx"
$excelpath = "$WorkDir$ReportFile"
Write-Host "Generating report for $($entries.Count) items..."
$entries | Export-Excel $tempfilepath -WorksheetName "Compliance Report"
$TotalRows = $entries.Count + 1 # Add 1 for the Header Row

$ConditionalFormat = $(
    New-ConditionalText -Text '1' -Range E:E -BackgroundColor White -ConditionalTextColor Red
    New-ConditionalText -Text '2' -Range E:E -BackgroundColor White -ConditionalTextColor Orange
    New-ConditionalText -Text 'FAILED' -Range F:F -BackgroundColor White -ConditionalTextColor Red
    New-ConditionalText -Text 'WARNING' -Range F:F -BackgroundColor White -ConditionalTextColor Orange
    New-ConditionalText -Text 'HIGH' -Range N:N -BackgroundColor White -ConditionalTextColor Red
)

$excel = Import-Excel $tempfilepath | Select-Object -Property *
| Export-Excel -Path $excelpath -ClearSheet -WorksheetName "Compliance Report" -ConditionalFormat $ConditionalFormat -AutoFilter -BoldTopRow -FreezeTopRow -PassThru
$sheet = $excel.Workbook.Worksheets["Compliance Report"]
foreach ($c in 1) { Set-ExcelRange -Address $sheet.Column($c) -Width 10 }
foreach ($c in 8, 12, 13) { Set-ExcelRange -Address $sheet.Column($c) -WrapText -Width 11 }
foreach ($c in 9, 10, 11) { Set-ExcelRange -Address $sheet.Column($c) -WrapText -Width 16 }
foreach ($c in 17, 18, 19, 20, 21) { Set-ExcelRange -Address $sheet.Column($c) -Width 30 -WrapText -VerticalAlignment Top }
foreach ($c in 1, 2, 3, 4, 5, 6, 7, 11, 14, 15, 16) { Set-ExcelRange -Address $sheet.Column($c) -VerticalAlignment Center }
foreach ($c in 2, 3, 4, 5, 6, 7, 14, 15, 16) { Set-ExcelRange -Address $sheet.Column($c) -Autofit }
foreach ($c in 4, 12, 13, 17 ) { Set-ExcelRange -Address $sheet.Column($c) -HorizontalAlignment Left }
foreach ($c in 1, 2, 3, 5, 6, 7, 14, 15) { Set-ExcelRange -Address $sheet.Column($c) -HorizontalAlignment Center }
foreach ($r in 2..$TotalRows) { Set-ExcelRange -Address $sheet.Row($r) -Height 15 }
foreach ($r in 1) { Set-ExcelRange -Address $sheet.Row($r) -HorizontalAlignment Center -VerticalAlignment Center -WrapText -Height 30 }
Set-ExcelRange -Worksheet $sheet -Range A1:U1 -BorderAround Thick -BorderLeft Thick -BorderRight Thick -BackgroundColor lightblue
Set-ExcelRange -Worksheet $sheet -Range A2:U$TotalRows -BorderAround Thin -BorderLeft Thin -BorderRight Thin -BorderTop Thin -BorderBottom Thin
$excel.Save()
# Column Widths: E=5, J=10, O=15, T=20`

[nevertestinprod]

Based on Doug's suggestion in another thread to look at the XML in the spreadsheet, here are the pertinent column sort entries:
NOTE: A1=Severity, A2=Exploited.

<sortState ref="A2:B6" xmlns:xlrd2="http://schemas.microsoft.com/office/spreadsheetml/2017/richdata2">
<sortCondition ref="A2:A6" customList="Critical,High,Medium,Low,None"/>
<sortCondition ref="B2:B6" customList="true,false,(Blanks)"/></sortState>

https://epplussoftware.com/docs/6.2/api/OfficeOpenXml.Sorting.SortCondition.html

[dfinke]

I've always deferred sorting to PowerShell Sort-Object before exporting.

Did you figure it out?

[nevertestinprod]

Not yet. Work got in the way...will let you know how it goes.

How it's going....ugly. The last row varies with every scan, so that has to be taken into consideration (it is, above using $TotalRows).
I've been looking at the EPPlus site, sheet1.xml from my test and full scans, and openxml on learn.ms...drinking from the firehose. And the Import-Excel Public folder files to see if it helps make any sense of what I'm trying to accomplish.
I'm not in any rush, and for now, it's a learning exercise more than anything else.

[nevertestinprod]

I've spent the last week going through the OfficeXML references and trying to come to terms with what is involved with the sorting. As my tech-writer sister-in-law once said, "There are no answers. Only Cross-References". I've spent time looking at the files in the Public folder and have an inkling of where I'm headed (slowly).

The EPPlus.dll included with ImportExcel is v4.5.3.2, so I went back to the archived site for references and will try to keep it compatible with that version. I have Office 2016 on my system, which may or may not cause confusion when looking at the ImportExcel output files vs Excel. Looking at the sheet1.xml from each of them has actually helped, though. There are some differences.

I'm thinking it will be a separate Set-SortCondition module eventually, with as much parity as I can figure out how to build in. There's a lot more to it than just ascending/descending and Custom List...

I am struggling looking through the modules trying to figure out how things within them relate. I did some Googling and keyboard head-bashing, and came up with the attached script. It will produce a .csv for each of the (56) .ps1 files in the Public folder. The Function Name, ParameterName, Type, Default Values, and the Script Contents used to derive the last three are enumerated for each file. When I get mine going, I can use the script to monitor progress and verify values are what they should be. Rename from .txt to .ps1 and edit the path variables to suit your system. I run them in VS Code. (Edit) I added another script that combines all of the Public scripts into a single file, in case anyone would rather not create individual files.

Export-ModuleInfo.txt
Export-ModuleInfo-AllInOneFile.txt
All_Scripts_Combined.csv

[dfinke]

Wow, one heck of a rabitt hole. Hope you strapped a two by four to your butt so you don't fall too deep.

[nevertestinprod]

I think I've hit a wall. There are no SortCondition or CT_SortCondition elements in EPPlus.dll 4.5.3.2 as far as I can find in the .cs files.

If something exists in EPPlus.dll and is loaded with ImportExcel, I can validate it using Get-Member. Examples that produce results
[OfficeOpenXml.Style.ExcelUnderLineType] | Get-Member
[OfficeOpenXml.Style.ExcelBorderStyle] | Get-Member
[OfficeOpenXml.ExcelColumn] | Get-Member
[OfficeOpenXml.ConditionalFormatting.eExcelConditionalFormattingRuleType] | Get-Member

There seem to be some related XML elements missing too. I can't find them by these labels.
richSortCondition / CT_RichSortCondition
CT_SortRule / CT_SortRules
CT_NamedSheetView / CT_NamedSheetViews
NsvFilter / CT_NsvFilter
CT_IconFilter

With the parameters from the XML Schema, maybe I can parse out a function in PowerShell to do what I want...

It's a journey...

This is a magic file :-) depending on whether the extension is .ps1 or .xml. It changes which text blocks are color-coded. These are the XML Schema that I think are involved, with references to the docs that contain them. The PowerShell-ish blocks are basically swapping xsd:.../ for [Parameter...] as a thought exercise. That's as far as I got with this file; I spent time after that looking at ImportExcel, EPPlus 4.x and pondering. I'm still a novice, so I do a lot of pondering and trial/error. While that's going on, sometimes the framework coalesces and I can start building on it. Having a purpose/objective/goal in mind helps that a lot.

Set-SortCondition - Copy .txt