how generate a excel report usuing WFA

[venkate_SAN]

Hi,

I have created a script to collect capacity of all the filers and store it in excell.

while executing workflow i am getiing error.

Exception calling "Add" with "0" argument(s): "Add method of Workbooks class failed"

[mbeattie]

Hi,

I'm assuming you want to port your script code into WFA (which can't output to an excel spreadsheet by default)

Are you using the EDM pack imported on your WFA server?

https://automationstore.netapp.com/pack-detail.shtml?packUuid=EDM-Pack&packVersion=1.0.1

Any reason you need the data in a spreasheet? (IE do you need multiple related worksheets in workbook etc), if not and you just want a simple capacity report from WFA on your aggregates or volumes then it is "possible" to have WFA invoke a SQL query for your volume\aggregate capacity, output it as a .csv file then add a WFA return paramater for the URL to download the .csv file (or have WFA email you the .csv file to you). This can be used as an alternate method instead of creating a custom OCUM report...however "possible" does not necessarily equate to "advisable"

There are multiple ways to get this information you are probably looking for however considering the WFA database is just a cache of the OCUM database have you considered using the OCUM to schedule a report and email it to you in .csv or .pdf format??? I'd advise looking at the default "Aggregate Capacity and Utilization" or "Volume Capacity and Utilization" reports.

You "could" re-create the OCUM reports using WFA or a script...but why re-invent the wheel?

/Matt

[sinhaa]

@venkate_SAN

I believe the primary requirement to debug a code is to HAVE THE CODE.  Can you post your code?

Also you can create .CSV( Comma Separated values) reports which can be opened using MS XL. All you need is to add your report data in a .csv file.

sinhaa

[venkate_SAN]

Please find the code below.

$Excel = New-Object -ComObject Excel.Application

$Excel.Visible=$true

$Excel1 = $Excel.Workbooks.Add()

$sheet = $Excel1.Worksheets.Item(1)

$sheet1 = $Excel1.Worksheets.Item(2)

$sheet.Name = 'Backup_Filer'

$sheet1.Name = 'Backup_Isilon'

$Sheet.Cells.Item(1,1)= 'Location'

$Sheet.Cells.Item(1,1).Font.Bold=$True

$Sheet.Cells.Item(1,2)= 'Purpose'

$Sheet.Cells.Item(1,2).Font.Bold=$True

$Sheet.Cells.Item(1,3)= 'Storage Name'

$Sheet.Cells.Item(1,3).Font.Bold=$True

$Sheet.Cells.Item(1,4)= 'H/W Model'

$Sheet.Cells.Item(1,4).Font.Bold=$True

$Sheet.Cells.Item(1,5)= 'SerialNumber'

$Sheet.Cells.Item(1,5).Font.Bold=$True

$Sheet.Cells.Item(1,6)= 'OS Version'

$Sheet.Cells.Item(1,6).Font.Bold=$True

$Sheet.Cells.Item(1,7)= 'Raw Capacity (TB)'

$Sheet.Cells.Item(1,7).Font.Bold=$True

$Sheet.Cells.Item(1,8)= 'Usable Capacity (TB)'

$Sheet.Cells.Item(1,8).Font.Bold=$True

$Sheet.Cells.Item(1,9)= 'Allocated Capacity (TB)'

$Sheet.Cells.Item(1,9).Font.Bold=$True

$Sheet.Cells.Item(1,10)= 'Unallocated Capacity (TB)'

$Sheet.Cells.Item(1,10).Font.Bold=$True

$Sheet1.Cells.Item(1,1)= 'Storage Name'

$Sheet1.Cells.Item(1,1).Font.Bold=$True

$Sheet1.Cells.Item(1,2)= 'H/W Model'

$Sheet1.Cells.Item(1,2).Font.Bold=$True

$Sheet1.Cells.Item(1,3)= 'Raw Capacity (TB)'

$Sheet1.Cells.Item(1,3).Font.Bold=$True

$Sheet1.Cells.Item(1,4)= 'Allocated Capacity (TB)'

$Sheet1.Cells.Item(1,4).Font.Bold=$True

$row=2

$col=1

$row1=2

$col1=1

#Auto fit everything so it looks better

$usedRange = $Sheet.UsedRange

$usedRange1 = $Sheet1.UsedRange

$usedRange.EntireColumn.AutoFit() | Out-Null

$usedRange1.EntireColumn.AutoFit() | Out-Null

$Excel1.SaveAs($out)

$Excel.Quit() -f

#Release COM Object

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null