Having difficult creating excel sheet while looping thru names

Viewed 37

I have bunch of server names. Then i am looping thru these names and creating sheet by each server name. But getting error. below is my code

create excel spreadsheet

$xlsx = Join-Path -Path (Get-Location).Path -ChildPath "ClusterUsageReport-$(get-date -UFormat '%Y-%m-%d-%H-%M-%S').xlsx"
    
    $xl = new-object -ComObject Excel.Application   
    $workbook = $xl.Workbooks.Add() 
    $i = 1
    $clusters = ('Hcohesity01','Hcohesity05')
    
    foreach ($vip in $clusters){
    ### create excel woorksheet
    $worksheet = $workbook.Worksheets.Item($i) 
    $worksheet.Name = "$vip-Storage Growth"
    $worksheet.activate  | Out-Null
    $i ++
    ### there are more scripts below this line, which create charts in each sheet ####
                          }
$xl.visible = $true
$worksheet.columns.autofit() | Out-Null
$worksheet.usedRange.rows(1).Font.Bold = $True
$workbook.SaveAs($xlsx,51) | Out-Null

Message i am getting

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) At C:\raju\scripts\cluster_usage_in_excel.ps1:25 char:1

  • $worksheet = $workbook.Worksheets.Item($i)
  •   + CategoryInfo          : OperationStopped: (:) [], COMException
      + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
    
    

Message coming from line $worksheet = $workbook.Worksheets.Item($i) , any idea how do i achieve this ? Below is entire script .. can you point where needs update ??

usage: ./graphStorageGrowth.ps1 -vip mycluster -username myuser [ -domain mydomain.net ] [ -days 60 ]

### process commandline arguments
[CmdletBinding()]
param (
    [Parameter(Mandatory = $True)][string]$username,
    [Parameter()][int32]$days = 60
)

### constants
$TB = (1024*1024*1024*1024)
$GB = (1024*1024*1024)

### source the cohesity-api helper code
. ./cohesity-api
### create excel spreadsheet
$xlsx = Join-Path -Path (Get-Location).Path -ChildPath "ClusterUsageReport-$(get-date -UFormat '%Y-%m-%d-%H-%M-%S').xlsx"
$xl = new-object -ComObject Excel.Application   
$workbook = $xl.Workbooks.Add() 
$i = 0
$clusters = ('Hcohesity01','hcohesity03')

foreach ($vip in $clusters){
### create excel woorksheet
while($xlsx.Worksheets.Count -lt $i) { $xlsx.Worksheets.Add() }
#$worksheet = $workbook.Worksheets.Item($i) 
$worksheet.Name = "$vip-Storage Growth"
$worksheet.activate()

### headings for data rows
$row = 1
$worksheet.Cells.Item($row,1) = 'Date'
$worksheet.Cells.Item($row,2) = 'Usage in Tib'
$row++



### authenticate
apiauth -vip $vip -username $username -domain corpads.local

### calculate startTimeMsecs
$startTimeMsecs = $(timeAgo $days days)/1000

### get cluster info
$clusterInfo = api get cluster?fetchStats=true
$clusterId = $clusterInfo.id

### collect $days of write throughput stats
#$stats = api get statistics/timeSeriesStats?schemaName=kBridgeClusterStats`&entityId=$clusterId`&metricName=kSystemUsageBytes`&startTimeMsecs=$startTimeMsecs`&rollupFunction=average`&rollupIntervalSecs=86400
$stats = api get "statistics/timeSeriesStats?endTimeMsecs=1662609600000&entityId=$clusterId&metricName=kMorphedUsageBytes&metricUnitType=0&range=day&rollupFunction=average&rollupIntervalSecs=86400&schemaName=kBridgeClusterStats&startTimeMsecs=$startTimeMsecs"


### populate excel worksheet with the throughput stats 
foreach ($stat in $stats.dataPointVec){
    $day = usecsToDate (($stat.timestampMsecs)*1000)
    $consumed = $stat.data.int64Value/$TB
    $worksheet.Cells.Item($row,1) = "$day".split()[0]
    $worksheet.Cells.Item($row,2) =  "{0:N2}" -f $consumed
    $row++
}

### create excel chart
$chartData = $worksheet.Range("A1").CurrentRegion
$chart = $worksheet.Shapes.AddChart().Chart
$chart.chartType = 4
$chart.SetSourceData($chartData)
$chart.HasTitle = $true
$chart.ChartTitle.Text = "Storage Consumption Last $days Days"
$chart.Parent.Top = 50
$chart.Parent.Left = 150
$chart.Parent.Width = 600
$i ++
           }
$xl.visible = $true
#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
$worksheet.columns.autofit() | Out-Null
$worksheet.usedRange.rows(1).Font.Bold = $True
$workbook.SaveAs($xlsx,51) | Out-Null
0 Answers
Related