Learn What IT Pros Need to Know About Windows 11 - August 24th at 1 PM ET! Learn What IT Pros Need to Know About Windows 11 - August 24th at 1 PM ET!
PowerShell

Integrating Microsoft Excel with PowerShell: Create a Rich Excel Doc

And we’re back with our series on integrating Microsoft Excel with PowerShell. Last time we looked at building a basic report with Microsoft Excel and Windows PowerShell. In some ways, what we created last time isn’t that much different than creating a CSV and opening it in Excel. So, if you want to use Excel, let’s really use it! In today’s post, I’ll use the basic demonstration script from last time, but create a richer Excel document. Later, in part three I’ll show you how to read data from an Excel file.

How to Integrate Microsoft Excel with PowerShell

As before, we’ll get disk information via WMI and create an Excel application object.

Win32_LogicalDisk -ComputerName $computer -Filter "DriveType=3"
$xl=New-Object -ComObject "Excel.Application"

Like Microsoft Word, a great deal of Excel automation relies on the use in internal constant values like xlDown. I could track down the value and create a variable, or I can load the appropriate classes that contain the required constants. For what I have in mind, I will need to grab the following:

[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

Later, when I want to use xlDown, I can specify it as $xlDirection::xlDown, which you’ll see in a bit. For now, I’ll write the disk data as I did in Part 1, but now let’s add some style. Another way of referencing parts of a spreadsheet is to use a Range from the worksheet object. You can either specify a range by a single cell reference like A1 or a range like A1:A10. The Range object has a style property so I’m going to set A1 to the Title style and my table header to “Heading 2”.

Sponsored Content

Read the Best Personal and Business Tech without Ads

Staying updated on what is happening in the technology sector is important to your career and your personal life but ads can make reading news, distracting. With Thurrott Premium, you can enjoy the best coverage in tech without the annoying ads.

$range.Style="Title"
#or set it like this
$ws.Range("A3:F3").Style = "Heading 2"

Another common formatting option is to adjust column widths. We can either set columns to a specific width or autosize it.

$ws.columns.item("D:F").columnwidth=10.5
$ws.columns.item("B:B").EntireColumn.AutoFit() | out-null

By the way, I pipe methods like AutoFit() to Out-Null to suppress any output, since I don’t really need it. Now for the really fun stuff. I thought it might be nice to take advantage of Excel’s conditional formatting. Specifically, I want to use a traffic light icon set to reflect how heavily utilized a particular drive might be. As I said before, if we’re going to create an Excel file, let’s take advantage of it. I’m going to show you the code, but don’t panic.

#get the last cell
$Selection=$ws.Range($start,$start.End($xlDirection::xlDown))
#add the icon set
$Selection.FormatConditions.AddIconSetCondition() | Out-Null
$Selection.FormatConditions.item($($Selection.FormatConditions.Count)).SetFirstPriority()
$Selection.FormatConditions.item(1).ReverseOrder = $True
$Selection.FormatConditions.item(1).ShowIconOnly = $False
$Selection.FormatConditions.item(1).IconSet = $xlIconSet::xl3TrafficLights1
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Type=$xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Value=0.8
$Selection.FormatConditions.item(1).IconCriteria.Item(2).Operator=7
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Type=$xlConditionValues::xlConditionValueNumber
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Value=0.9
$Selection.FormatConditions.item(1).IconCriteria.Item(3).Operator=7

I did not wake up one morning knowing how to do this in PowerShell. Instead, I created an Excel macro to apply the formatting, and then translated that code into PowerShell. I wish I could give you a set of translation rules, but it just takes trial and error and experience. Notice the use of the constant values?

Next, I’m going to add a bar chart style graph to the worksheet:

​$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

Again, I ran this process through a macro to discover the methods and correct values. Next, I need to select the data sources for the graph.

​$start=$ws.range("A3")
#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range("F3")
#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

The drive names will be on the Y axis and %Used values will be on the X axis. I then use this collection of ranges to define the chart data.

​$chartdata=$ws.Range("A$($Y.item(1).Row):A$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)")
$chart.SetSourceData($chartdata)

The last thing I want with the graph is to include data labels and a chart title.

​$chart.seriesCollection(1).Select() | Out-Null
$chart.SeriesCollection(1).ApplyDataLabels() | out-Null
$chart.ChartTitle.Text = "Utilization"

More than likely, Excel won’t put the graph where you want it, so you can use code like this to move it:

​$ws.shapes.item("Chart 1").top=40
$ws.shapes.item("Chart 1").left=400

Top and Left are positions from the top and left of the Excel window. This may take some trial and error to get the right values, and be sure to test on different computers. The last step is to rename the worksheet with the computername.

​$xl.worksheets.Item("Sheet1").name=$name

Once you understand how all of this Excel magic works, it’s not that much more work to add a worksheet for each computer you want to query. Figure 1 shows the final result.

Excel PowerShell Figure1

Figure 1: An Enhanced Excel Report

Conclusion

You can download my demonstration script and try it out for yourself. If you really need to write to Excel, I encourage you to go all out. It will take some work to master the basics but it is well worth the effort. Next time we’ll look at reading data from Excel files.

Related Topics:

BECOME A PETRI MEMBER:

Don't have a login but want to join the conversation? Sign up for a Petri Account

Register
Comments (5)

5 responses to “Integrating Microsoft Excel with PowerShell: Create a Rich Excel Doc”

  1. How to Install Windows XP | Install Windows XP

    [...] the prompts that will pop out your screen. Finally, you are able to install windows XP completely.[caption id="attachment_17" align="alignleft" width="300" caption="Install Windows Xp"][/caption] On...lity and quick installation is Windows XP by Microsoft. Even in the latest version of Windows 7, [...]
  2. 电影之家

    [...] of Microsoft Excel | MCQ QuestionsHow to Create Drop Down Lists in Microsoft ExcelExcel Training ...Integrating Microsoft Excel with PowerShell Part 2 - Petri IT ...Microsoft Excel Add-In | BlackbaudKnowHow.comBreak Even Point Microsoft Excel?首页 | 登陆 | [...]
  3. Install Windows XP Via USB | Install Windows XP

    [...] XP CD | I mE mY minEIs this another scam? Thanks a lot!? – Fax BusinessWindows UpdateWrite to Excel file with Powershell - Part 2MCSA Inform Users of Important [...]
  4. ~j5321

    To load the constants in the second code block, I first need to load the Excel assembly, using: Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel" | out-null Found at http://import-powershell.blogspot.com/2012/03/excel-part-1.html
  5. Integrating Microsoft Excel with PowerShell: Basic Reports

    […] a basic report with Microsoft Excel and Windows PowerShell. In part two, I'll go over how to create a richer Excel document. Later, in part three I'll show you how to read data from an Excel […]

Leave a Reply

Register for Advanced Microsoft 365 Day!

GET-IT: Advanced Microsoft 365 1-Day Virtual Conference - Live August 24th!

Join us on Tuesday, August 24th and hear from Microsoft MVPs and industry experts about how to take advantage of Microsoft 365 at a technical level and dive deep into the features and functionality that will make your environment more secure and compliant.

RSVP Now

Sponsored By