Import-Excel function performance (again)

[scriptingstudio]

Direct assignment wins over +=, especially on large collections.
Although += was optimized in PS7.5 (https://learn.microsoft.com/en-us/powershell/scripting/whats-new/what-s-new-in-powershell-75?view=powershell-7.5#performance-improvements).

Proposed technical implementation details

line 216
Insert $xlBook["$targetSheetname"] = before foreach

line 238
Remove substring $xlBook["$targetSheetname"] +=

This is just another and effective way to collect data in the same variable of the same type without additional lines.

Update: code cleaning

Expression @ line 251 is equivalent to $xlbook.Values

[scriptingstudio]

I've found one of the old versions (~2022) where data put directly to the pipeline and the user saw their data immediately, so I got the idea that the data output needs to be refactored for a common use case.

CURRENT OUTPUT IMPLEMENTATION: universal, where all data first put to a variable and then to the pipeline. Besides, the more data the slower a collector works.
COMMON USE CASE: just one Excel's sheet in the request so we don't need an intermediate collector, our data go directly to the pipeline. Visually it looks as it's happening in real time, especially for big grids.

The new schema looks like this:

#$dataminer = {foreach ($R in $rows) {}}

try {
	$xlBook = [Ordered]@{}
	foreach ($sheet in $Worksheet) {
		if ($Worksheet.Count -eq 1) {
			foreach ($R in $rows) {}
			#& $dataminer
		} else {
			$xlBook["$targetSheetname"] = foreach ($R in $rows) {}
			#$xlBook["$targetSheetname"] = & $dataminer
		}
	}
}
catch {}
finally {
	if ($Worksheet.Count -gt 1) {
		if ($Raw) {
			$xlbook.Values
		} else {
			$xlBook
		}
	}
}

The data extractor remains the same, no changes, but a restructured output schema.
Yes, the block foreach ($R in $rows) {} duplicates, but this is the only "disadvantage" and it takes just 20 lines. This block can be put in scriptblock to reuse code.

Import-Excel-new.ps1.txt