5 Replies Latest reply on Jun 4, 2019 4:46 AM by LucD

    Add Rows and Columns in Powershell Script

    piyushranusri Novice

      Hi all

      have one script which is collecting data storage inventory with space information but am not able to add rows and columns like excel view in the script, right now the script gather the info and exporting using convert command.

      as multiple vcenter to all html is saving with individual vcenter name/Ip format. current output is like this.




      Aim: insert or view the Rows and Columns. Put a Header "Data Storage Information"


      Script i using


      Import-Module VMware.VimAutomation.Vds

      $vcenterip = @("all vcenter IP or Hostname")


      foreach ($vcenter in $vcenterip){

      Write-Output 'Before connecting'

      Connect-VIServer -Server "$vcenter"

      Write-Output "Connected to $vcenter"


      Get-Datastore | select Name, Datacenter, CapacityGB, Type, FreeSpaceGB, @{N="Provisioned (GB)"; E={[math]::round(($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB,2) }} |  ConvertTo-Html | Out-File c:\temp\$vcenter.html

      disconnect-viserver -confirm:$false



      foreach ($vcenter in $vcenterip){


      $html1 = Get-Content "C:\temp\$vcenter.html"

      $coloroutfile = "c:\temp\$vcenter.html"

      $null > $coloroutfile

      $header = ($html1 |? {$_ -like "*<th>*"}) -split "/"

      $capacityLN = ($header | Select-String "CapacityGB").LineNumber

      $ProvisionedLN = ($header | Select-String "Provisioned").LineNumber

      $colour = "style='background-color:#FF8080'"

      $colour2 =  "style='background-color:#FFFF00'"


      foreach($line in $html1)


      if($line -like "<tr><td>*"){

          [double]$provisioned = ((($line -split "</td>") -replace "<td>","") -replace "<tr>","")[$ProvisionedLN - 1]

          [double]$capacity = ((($line -split "</td>") -replace "<td>","") -replace "<tr>","")[$capacityLN - 1]


          if(($Provisioned -ge ($capacity * .8)) -and ($Provisioned -lt $capacity)) { $line -replace "<td>$provisioned</td>","<td $colour2>$provisioned</td>"  >>  $coloroutfile   }

          elseif($Provisioned -gt $capacity) { $line -replace "<td>$provisioned</td>","<td $colour>$provisioned</td>"  >>  $coloroutfile   }

          else{ $line >> $coloroutfile }



      else { $line >> $coloroutfile }