Exporting the output to a Excel File

[aravinds3107]

I am using the attached script to perform an inventory export then as excel file. I have used LucDtechnique from this post to store the information to a temp CSV file and later save them as a XLS file. I need to perform a some formatting in the report, like changing font size of the header, adding some background colours.

As I am exporting them to a CSV file it doesn’t take the formatting. Is there any other way to achieve the formatting?

[LucD · Accepted solution]

You might want to have a look at my Export-Xlsx, the sequel, and ordered data post.

[aravinds3107]

I did check the post but I am not quite sure how to use them with the existing script which I am using.

Also I want to have only header color ,autofit of the columns nd in the first sheet add a title in the first row . If you could let me know how to run the script, I will take a look further..

[LucD]

The function should be quite easy to use, there are some examples in the post.

What exactly is giving you problems ?

[aravinds3107]

This is how i am trying to run the script

1) Imported the Export-XLSx to the current powerCLI session using Import-Module

2) Ran the .\Excel-Report.ps1 | .\Export-XLSx.PS1 -Path "C:\Aravind\Scripts\report.xlsx", it prompts me with my vCenter credentials and look like its running.

But it doesn't product any output file and throws with the below error

Exception calling "SaveAs" with "1" argument(s): "Microsoft Excel cannot access the file 'C:\Scripts\DB1B9200'. There are several possible reasons:

The file name or path does not exist.

The file is being used by another program.

The workbook you are trying to save has the same name as a currently open workbook."

At C:\Aravind\Scripts\Excel-Report.ps1:284 char:62

+ $workbook.SaveAs("C:\Scripts\$($vCenter)-vAssessmentReport-$($Date).xls")

+                                                              ~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : ComMethodTargetInvocation

There is a saveAS in my existing script , should be this removed ?

[aravinds3107]

I have used the attached script to create worksheet which call the function when saving the file but getting the below error

Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]. Make sure that the assembly that contains this type is loaded.

At C:\Aravind\Scripts\report.PS1:169 char:5

+     $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::xlWorkbookNo ...

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException

    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]. Make sure that the assembly that contains this type is loaded.

At C:\Aravind\Scripts\report.PS1:175 char:9

+         $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::xlWorkbo ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException

    + FullyQualifiedErrorId : TypeNotFound

Exception calling "SaveAs" with "2" argument(s): "SaveAs method of Workbook class failed"

At C:\Aravind\Scripts\report.PS1:252 char:5

+     $wb.SaveAs($Path,$xlFixedFormat)

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : ComMethodTargetInvocation

[LucD]

It looks as if the Office Primary Interop Assemblies are not present on the client where you ran the script.

Note, you do not need Excel to be installed on the station, just the Interop assemblies.

See here for the redistributables

[aravinds3107]

Earlier I was running the script from the script where I have excel 2013 installed . Not I have installed  Interop assemblies on a new system and ran the script it throws me a different error. Have a Powershell v2.0 on the system, Does the function requires PS 3.0?

New-Object : Cannot load COM type Excel.Application.

At C:\Scripts\report.PS1:167 char:21

+     $xl = New-Object <<<<  -ComObject Excel.Application

    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException

    + FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Commands.NewObjectCommand

Property 'DisplayAlerts' cannot be found on this object; make sure it exists and is settable.

At C:\Scripts\report.PS1:168 char:9

+     $xl. <<<< DisplayAlerts = $False

    + CategoryInfo          : InvalidOperation: (DisplayAlerts:String) [], RuntimeException

    + FullyQualifiedErrorId : PropertyNotFound

Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat]: make sure that the assembly containing this type is loaded.

At C:\Scripts\report.PS1:169 char:67

+     $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat] <<<< ::xlWorkbookNormal

    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:String) [], RuntimeException

    + FullyQualifiedErrorId : TypeNotFound

You cannot call a method on a null-valued expression.

At C:\Scripts\report.PS1:195 char:30

+       $wb = $xl.Workbooks.Add <<<< ()

    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\Scripts\report.PS1:221 char:29

+       $usedRange = $ws.Range <<<< ("a1","$($A1Cols)$($Rows)")

    + CategoryInfo          : InvalidOperation: (Range:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\Scripts\report.PS1:223 char:18

+         $ws.Range <<<< ("a1","$($A1Cols)1").Interior.ColorIndex = 48

    + CategoryInfo          : InvalidOperation: (Range:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\Scripts\report.PS1:224 char:18

+         $ws.Range <<<< ("a1","$($A1Cols)1").Font.Bold = $True

    + CategoryInfo          : InvalidOperation: (Range:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

Property 'Value2' cannot be found on this object; make sure it exists and is settable.

At C:\Scripts\report.PS1:228 char:16

+     $usedRange. <<<< Value2 = $Array

    + CategoryInfo          : InvalidOperation: (Value2:String) [], RuntimeException

    + FullyQualifiedErrorId : PropertyNotFound

[LucD]

Sorry, my bad.

This version requires Excel to be installed I'm afraid.

[aravinds3107]

Ok.. So we required both Excel and Interop assemblies to be installed??

Will the script support excel version 2007 and above??

Is it ok to run with Windows PS 2.0??

[LucD]

No, if Excel is installed, the Interop Assemblies should be there as well.

Should work with PS v2

[aravinds3107]

How do I append multiple worksheet in the same excel file, Now when I try to generate 2 different report 2 excel files are getting created.  I have checked and made sure -AppendWorksheet:$false is NOT set and have added - SheetPosition "end", but i get only two file.

[LucD]

The default is $false, so you should add -AppendWorksheet:$true to the call.

Did you already try that ?

[aravinds3107]

That works for me...

Probably my last question

1) How to add a title to the worksheet(1st worksheet) and then the script output gets placed, Add title in row 1 and the script output starts from row 2 or  so...

2) I run the script in following order to collect the information

report1

report2

report3

..

....

reportN

But when the report are exported to excel the last reports is present in first worksheet like

reportN

...

...

report3

report2

report1

[aravinds3107]

I got the answer for 2nd question by adding -SheetPosition "end",

If you can help with 1st query  it would be fine..

[LucD]

Use the WorksheetName parameter.

See the 3th example in the post

[bugblatterbeast]

Hi LucD‌

I have been using the Export-Xlsx function for reporting various things in vCenter for a good long while now and it has worked great on Windows 7 with Office 2013.  I recently moved to Windows 10 and Office 2016 and i am now getting the following errors:-

Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat].

At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:169 char:5

+     $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat]::x ...

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException

    + FullyQualifiedErrorId : TypeNotFound

Unable to find type [Microsoft.Office.Interop.Excel.XLFileFormat].

At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:175 char:9

+         $xlFixedFormat = [Microsoft.Office.Interop.Excel.XLFileFormat ...

+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XLFileFormat:TypeName) [], RuntimeException

    + FullyQualifiedErrorId : TypeNotFound

Unable to get the SaveAs property of the Workbook class

At C:\Users\blah\OneDrive\MyDocs\Stash\vmware\Export-Xlsx.ps1:252 char:5

+     $wb.SaveAs($Path,$xlFixedFormat)

+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OperationStopped: (:) [], COMException

    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

My script is trying to run this:-

get-vm | get-snapshot | select VM,Name,Description | Export-Xlsx -AppendWorksheet -Path $snapdiskReport -WorksheetName $datetime -SheetPosition end | out-null

My PowerCLi version is:-

VMware vSphere PowerCLI 6.0 Release 1 build 2548067

I tried to look for relevant versions of the Interop Assemblies for Office 2016 but couldn't locate them.  In desperation I tried to install the Office 2010 ones you linked but unsurprisingly it didn't work.  Any ideas?

Thanks in advance

Danny

p.s. I know this is an old thread but it was top of the list in google so thought it might be useful for others to raise this here

[bugblatterbeast]

The simplest answer for this issue was simply to install Excel 2013 alongside the 2016 install.....everything works again!

[LucD]

I'll in any case have a look to try and get it to work with 2016