Powershell - Can't Collect and Compile XML attributes into an Excel File

Question

I'm aiming to accomplish the following with a PowerShell script. Most sub folders in "C:\Example-Path\src" contain a single .csproj file that includes a "Property Include" and "Version" attribute. I want to gather these attributes from every instance of a .csproj file across all sub folder and compile them into a spreadsheet, 1 column for each attribute. I've revised my work quite a bit to where I'm not getting errors anymore, however after the program runs the final excel sheets turns up empty and I can't seem to figure out why. My code is below, any input is greatly appreciated!

$Directory = dir C:\Example-Path\src -Directory
foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    Get-ChildItem -Path "$($d.fullname)\*" -File -Recurse -filter  '.csproj' |
        ForEach-Object {
           [xml]$file = get-content .\$_
           $xmlProperties = $file.SelectNodes("/Project/ItemGroup/PackageReference")
           $xmlProperties | Select-Object -Property Include, Version 
      } | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter
} 

---

Here is an example of the .csproj file I'm attempting to read from:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
    <GeneratePackageOnBuild>True</GeneratePackageOnBuild>
  </PropertyGroup>
  <PropertyGroup>
    <GenerateAssemblyInfo>false</GenerateAssemblyInfo>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="LazyCache.AspNetCore" Version="2.1.3" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\Caching.Core\Caching.Core.csproj" />
 <ProjectReference Include="..\Caching.SharedKernel\ Caching.SharedKernel.csproj" />
  </ItemGroup>
</Project>

Answer 1

Give this a try, hopefully it fixes your issue. One thing to note is that you're using Export-Excel within an inner loop and it's opening, replacing the content and closing the Excel file on each iteration (as you will see, I have moved that line as the step of the script), if you were using -Append it would at least not replace the contents of the .xlsx however it would be quite slow.

$Directory = Get-ChildItem C:\Example-Path\src -Directory
$ErrorFiles = [System.Collections.Generic.List[string]]::new()

$result = foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    $files = Get-ChildItem -Path $d.FullName -File -Recurse -Filter *.csproj
    foreach($file in $files) {
        try {
            $xml = [xml](Get-Content $file.FullName -Raw)
            $xml.SelectNodes("/Project/ItemGroup/PackageReference") |
            Select-Object -Property Include, Version
        }
        catch {
            Write-Warning $_.Exception.Message
            $ErrorFiles.Add($file.FullName)
        }
    }
}

$result | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter

Answer 2

Santiago essentially solved it! I'm not sure why but changing his $file/$files variable to $folder/$folders was the magic trick! With the main issue out of the way, now I'm unsure how to add an excel column for the name of the .csproj file so I know where the version data is coming from.

$Directory = Get-ChildItem C:\Example-Path\src -Directory
$ErrorFiles = [System.Collections.Generic.List[string]]::new()

$result = foreach ($d in $Directory) {
    Write-Host "Working on directory $($d.FullName)..."
    $folders = Get-ChildItem -Path $d.FullName -File -Recurse -Filter *.csproj
    foreach($folder in $folders) {
        try {
            $xml = [xml](Get-Content $file.FullName -Raw)
            $xml.SelectNodes("/Project/ItemGroup/PackageReference") |
            Select-Object -Property Include, Version
        }
        catch {
            Write-Warning $_.Exception.Message
            $ErrorFiles.Add($file.FullName)
        }
    }
}

$result | Export-Excel -Path C:\Temp\ExamplExcel.xlsx -AutoSize -AutoFilter