Search results for 'app:weblogs' matching tag 'Excel'

Building Excel Reports with PowerShell

Anonymous — Wed, 30 Jan 2013 06:00:00 GMT

Last year I wrote a series of articles for the Petri IT KnowledgeBase on using Microsoft Excel with PowerShell. Today I received an email from a reader who had a question about article that showed how to build a drive usage report in Excel. In the article I suggest it wouldn’t be too difficult to [...]

Quick and Dirty Excel from PowerShell

Anonymous — Thu, 10 May 2012 05:00:00 GMT

I continue to tinker with Office applications and Windows PowerShell. I was looking at an Excel issue related to opening a new workbook. To verify the problem wasn’t PowerShell related I offered a suggestion to try creating an Excel workbook … Continue reading →

Displaying Data in Excel (and why it won't work)

Tobias — Tue, 24 Apr 2012 05:00:00 GMT

It's pretty easy to send PowerShell results to Excel and display them nicely as a spreadsheet. Actually, here's a simple function called Out-ExcelReport. Pipe anything to it, and it shows up as an excel sheet (provided you have indeed installed excel beforehand):

function Out-ExcelReport {
param(
$path = "$env:temp\report$(Get-Date -format yyyyMMddHHmmss).csv",
[switch]$open
)
$Input |
Export-Csv $path -NoTypeInformation -UseCulture -Encoding UTF8
if($open) { Invoke-Item $path }
}

Now creating spreadsheets is as easy as this:

Get-Process | Out-ExcelReport -open

Why Does Excel Display Garbage?

Displaying results in Excel isn't so much a problem once you figure out how to export to CSV and then import that to excel.

The problem becomes evident a bit later, when you start to have fun and then suddenly run into lines such as this one:

Get-WMIObject Win32_NetworkAdapterConfiguration -filter 'IPEnabled=True' |
Select-Object Caption, DefaultIPGateway, MACAddress, IPAddress |
Out-ExcelReport -open

This line will indeed find your active network adapters, and it will open excel and display the network properties your code selected. But... the result probably looks similar to this:

Caption DefaultIPGateway MACAddress IPAddress
[00000007] Bro... System.String[] 68:A8:6D:0B:5F:CC System.String[]

Note how some properties display correctly (such as MACAddress), while others read "System.String[]". Where did the IP address go?

Shape Up Your Results!

The problem occurs whenever the property is NOT just text. Since excel can only deal with text, anything that is not text in the first place needs to be converted to text. "System.String[]" simply is the result of such a conversion. The property you tried to display was an array of strings. That's correct BTW. A network adapter can have multiple IP addresses, so the property IPAddress is by design an array.

To create meaningful reports, you need to convert such properties to text in a more clever way. Here are two things you can do:

Join Arrays: use the -join operator to join array elements and produce descriptive text
Out-String: send the property to Out-String and take advantage of PowerShell's own built-in conversion support

So how exactly would you implement these conversion methods? Well, here's a way:

$results = Get-WMIObject Win32_NetworkAdapterConfiguration -filter 'IPEnabled=True' |
Select-Object Caption, DefaultIPGateway, MACAddress, IPAddress

$results.DefaultIPGateway = $results.DefaultIPGateway -join ','
$results.IPAddress = $results.IPAddress | Out-String

$results | Out-ExcelReport -open

Note how I just took the result produced by Select-Object and saved that in $results. That's all. Since Select-Object always creates new objects with only the properties I want, and since objects produced by Select-Object are always read/write, I now can use whatever method I prefer to beautify those properties. Then, when all is fine, I can continue and send $results on to Out-ExcelReport. The result is a report with all the multivalues displaying fine.

Phew! Done - and you now can convert your PowerShell results to excel reports - and fine-tune any property that isn't showing up the way you want it to. Stay tuned...!

Tobias

Microsoft MVP PowerShell Germany

P.S.
If you live in Germany or other parts of Europe and your company would like to set up a truly great PowerShell training, just contact me! I regularly train mid- to large-size companies. Trainings are always a blast with tons of real-world-examples and solutions. Here's how to get in touch with me: tobias.weltner@scriptinternals.de

Automating Office and Excel in PowerShell

Tobias — Thu, 19 Aug 2010 05:00:00 GMT

Let's assume you'd like to write results directly to an Excel sheet, or you'd like to dynamically update Excel cells, or read Excel content into your script. In older scripting languages like VBScript, this was easy. Office has COM libraries which allow direct access to all Office products. The same libraries are accessible from PowerShell as well. Here is some sample code that opens Excel, adds a workbook and writes and reads directly to Excel cells:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook1 = $excel.Workbooks.Add()

# write into cell B5 (column 2, line 5):
$workbook1.ActiveSheet.Cells.Item(5,2)= "I can write directly to cells!"

# read cell content
$content = $workbook1.ActiveSheet.Cells.Item(5,2).Text
"Cell B5 content: $content"

Accessing Office from PowerShell - not that easy!

If that code works for you, happy you! Chances are in the middle of your script, you get an awkward exception, complaining about old formats or invalid type libraries. There has been a lot of confusion just why this error occurs and when. It seems to randomly occur on some machines while others run the code just fine.

Now, the true reason for this is not PowerShell but rather .NET. If your operating system and your MS Office use different languages, you get the exception. Worse yet, if you use MUIs, you may not even notice that there is a language conflict between your OS and your office.

Working around the Language Problem

Fortunately, after some research, we today found a pretty slick workaround. If you have a language conflict, then simply tell PowerShell to execute your code in a different language - and all is fine!

To execute PowerShell in a different language, you need to temporarily change the thread culture. Inspired by an old article Jeffrey Snover posted for Monad, I created an uptodate PowerShell V2 function called Use-Culture. It accepts a culture ID and a script block and then executes the code using the specified culture. Here is the function:

<#
.SYNOPSIS
Executes PowerShell Code using specified culture
.DESCRIPTION
Temporarily switches culture from your default culture to another culture.
.PARAMETER culture
Culture id for the culture you want to use, i.e. en-US for English culture, or de-DE for German culture
.PARAMETER code
PowerShell code you want to run using the specified culture
.EXAMPLE
Use-Culture en-US {Get-Date}
outputs the current date in US format
.EXAMPLE
Use-Culture de-DE {Get-Date}
outputs the current date in German format
.EXAMPLE
Use-Culture ar-IQ {Get-Date}
outputs the current date in Iraq format
.NOTES
Use this command to get a list of all supported culture IDs:
[system.Globalization.CultureInfo]::GetCultures('AllCultures')
.LINK
http://www.powershell.com
#>

function Use-Culture {
param(
[System.Globalization.CultureInfo]
[Parameter(Mandatory=$true)]
$culture,
[ScriptBlock]
[Parameter(Mandatory=$true)]
$code
)

trap {
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}

$currentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
Invoke-Command $code
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
}

To see some simple examples, take advantage of the comment based help (after you ran the function code):

Get-Help Use-Culture -Examples

And to get a complete list of all culture IDs you can play with, use this command:

[system.Globalization.CultureInfo]::GetCultures('AllCultures')

Accessing Excel from PowerShell

Now back to Excel. If you get the exception and want to run your code, place your code inside braces and store it in some variable. Then, run Use-Culture with the en-US culture and specify your code like this:

$code = {
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook1 = $excel.Workbooks.Add()

# write into cell B5 (column 2, line 5):
$workbook1.ActiveSheet.Cells.Item(5,2)= "I can write directly to cells!"

# read cell content
$content = $workbook1.ActiveSheet.Cells.Item(5,2).Text
"Cell B5 content: $content"
}

Use-Culture en-US $code

It works! Finally.

Likewise, if you normally do not get the weird Excel exception, simply specify a conflicting culture such as ar-IQ and watch the exception pop up.

Enjoy and have fun!

Tobias
Microsoft MVP PowerShell

New-ExcelWorkbook.ps1

Anonymous — Thu, 18 Mar 2010 05:00:00 GMT

<#
.SYNOPSIS
This script creates an Excel workbook using PowerShell
.DESCRIPTION
This script demonstrates manipulating Excell with PowerShell
and the Excel.Application COM object.
.NOTES
File Name : New-ExcelWorkbook.ps1
Author : Thomas Lee - tfl@psp.co.uk
Requires : PowerShell Version 2.0
.LINK
This script posted to:
http://pshscripts.blogspot.com/2010/03/new-excelworkbookps1.html
MSDN Sample posted at:
http://msdn.microsoft.com/en-us/library/bb211359.aspx
.EXAMPLE
Run it and see one!
#>
##
# Start of Script
##
# Then we create and save a sample worksheet
# Create Excel object
$excel = new-object -comobject Excel.Application
# Make Excel visible
$excel.visible = $true
# Create a new workbook
$workbook = $excel.workbooks.add()
# The default workbook has three sheets, remove 2
$S2 = $workbook.sheets | where {$_.name -eq "Sheet2"}
$s3 = $workbook.sheets | where {$_.name -eq "Sheet3"}
$s2.delete()
$s3.delete()
# Get sheet and update sheet name
$s1 = $workbook.sheets | where {$_.name -eq 'Sheet1'}
$s1.name = "PowerShell Sample"
# Update workook properties
$workbook.author = "Thomas Lee - tfl@psp.co.uk"
$workbook.title = "Excel and PowerShell rock!"
$workbook.subject = "Demonstrating the Power of PowerShell with Excel"
# Next update some cells in the worksheet 'PowerShell Sample'
$s1.range("A1:A1").cells="Cell a1"
$s1.range("A2:A2").cells="A2"
$s1.range("b1:b1").cells="Cell B1"
$s1.range("b2:b2").cells="b2"
# now make a sum
$s1.range("E1:E2").cells="Widgets"
$s1.range("E2:E2").cells=2
$s1.range("E3:E3").cells=2
$s1.range("E4:E4").cells=38
$s1.range("D5:D5").cells="Total"
$s1.range("E5:E5").cells.formula = "=sum(e2,E4)"
# And save it away:
$s1.saveas("c:\foo\xlsx3.xlsx")
# end of script

Technorati Tags: Excel,PowerShell,Scripts,Excel.Application