Search results for 'app:weblogs' matching tags 'Windows PowerShell', 'Microsoft Excel', and 'guest blogger'

Use PowerShell to Work with Pictures in Microsoft Excel

Anonymous — Fri, 12 Aug 2011 05:00:00 GMT

Summary: Guest Blogger Robert Robelo shows how to use Windows PowerShell to work with images in Microsoft Excel.

Microsoft Scripting Guy Ed Wilson here. Our guest blogger today is Robert Robelo. First, I want to give Robert a chance to tell us a little bit about himself.

I live in Nicaragua. I have been an incomplete quadriplegic (paralyzed from the chest down, but have limited mobility in upper limbs) for more than twenty years. Because of that I have not had a job since then. Before I got shot, I was an officer in the Nicaraguan Resistance Army, better known as Contras. One of my multiple responsibilities was to train, support, and maintain the IT structure (guess that would be a chief information officer), which was no more than twenty individual machines. My other responsibilities included intelligence, counterintelligence, and friendly forces liaison officer; obviously, before becoming an officer, I was an infantry and artillery soldier. I later became a sniper.

Robert is going to talk to us about working with images in Microsoft Excel via Windows PowerShell. Take it away, Robert!

Inserting an image in an Excel spreadsheet with Windows PowerShell is not an everyday chore for IT pros, but there very well may come a time where it would be useful to know how to do such a task.

An Excel spreadsheet has a Shapes property; it holds a collection of objects in the drawing layer, such as AutoShapes, freeforms, OLE objects, and images. To insert an image in the spreadsheet, use the Shapes collection's AddPicture method. This method takes seven required arguments, which are shown in the following figure.

The AddPicture method returns a Shape object, which should be assigned to a variable. The Shape can then be relocated, resized, rotated, transformed, cropped, formatted, duplicated, and so on.

Let's fire up an Excel instance, get a new workbook, and grab the ever-present Sheet1. Before we do that, let's set up a few Constant variables. First, the MsoTriState constants that apply to the AddPicture method and then a couple Constant variables of our own. These will hold the default width and height of a cell; they can be very useful to place and size the image we are going to insert. However, they are not necessary if you prefer to place and size the image with greater accuracy. In this sample, we will place the image's upper left corner in cell C3's upper left corner—that is two cells from the left and two cells from the top of the sheet, and we will make the image two cells wide and four cells tall. (Note The image path is set to C:\Ensign.jpg; you can substitute this with your own image or download the .zip file that contains that image as part of a test kit. Read more below.)

# Excel Constants

# MsoTriState

Set-Variable msoFalse 0 -Option Constant -ErrorAction SilentlyContinue

Set-Variable msoTrue 1 -Option Constant -ErrorAction SilentlyContinue

# own Constants

# cell width and height in points

Set-Variable cellWidth 48 -Option Constant -ErrorAction SilentlyContinue

Set-Variable cellHeight 15 -Option Constant -ErrorAction SilentlyContinue

$xl = New-Object -ComObject Excel.Application -Property @{

Visible = $true

DisplayAlerts = $false

}

$wb = $xl.WorkBooks.Add()

$sh = $wb.Sheets.Item('Sheet1')

# arguments to insert the image through the Shapes.AddPicture Method

$imgPath = 'C:\Ensign.jpg'

$LinkToFile = $msoFalse

$SaveWithDocument = $msoTrue

$Left = $cellWidth * 2

$Top = $cellHeight * 2

$Width = $cellWidth * 2

$Height = $cellHeight * 4

# add image to the Sheet

$img = $sh.Shapes.AddPicture($imgPath, $LinkToFile, $SaveWithDocument,

$Left, $Top, $Width, $Height)

$xl.Speech.Speak('Add an image to the Sheet through the Add Picture Method.')

Pretty easy, isn't it? You can examine the Shape object with the Get-Member cmdlet to see the different methods and properties it has. There are too many to explore in one blog post anyway.

Now, let's close the workbook without saving it, quit Excel, and clean up its insubordinate instance with the Remove-ComObject function. Basically, the Remove-ComObject function collects all variables that were assigned System.__ComObject objects within the caller scope and are not Constant or Read-Only variables:

# close without saving the workbook

$wb.Close($false)

$xl.Quit()

Remove-ComObject

There are many techniques that attempt to release these defiant System.__ComObject objects. Some do get the job done eventually, but they look chaotic or involve repeated calls to Runtime.Interopservices.Marshal's ReleaseComObject method. Using the Remove-Variable cmdlet the traditional way to get rid of these vestigial System.__ComObject objects will not suffice either, but using it as I do in the Remove-ComObject function, this capricious cmdlet does its job very well with a little help from its friends.

First, the Remove-ComObject function briefly pauses for half a second to let Windows PowerShell mark the System.__ComObject object as Disposed and ready to be collected by the Garbage Collector. Next, a variable is set with the Management.Automation.ScopedItemOptions ReadOnly and Constant enumerations. This variable will be the second half of the filter that will be applied in the following step. Then, the Get-Variable cmdlet fetches all variables in the caller scope (-Scope 1) and pipes them to the Where-Object cmdlet, where a filter is applied to exclude Constant or Read-Only variables, but whose value are of type System.__ComObject. This first half of the filter is done by comparing the value's PSTypeNames collection against the string System.__ComObject with the Contains operator. The unfortunate and rebellious variables that go through are then piped to the Remove-Variable cmdlet and are slyly shredded to oblivion. Finally, the Garbage Collector gets a call to sanitize the area and collect the scraps.

The Remove-ComObject function is an advanced function because it has common parameters, which permits the user to use its Verbose flag to divulge which variables were removed. I do want to point out a very important fact: the Remove-ComObject function removes all variables declared in the function's caller scope. It is safer to use it within scripts, functions, or modules, but you can also call it from the command line. Just beware that you may wipe out other System.__ComObject objects. This is the reason the function excludes Constant or Read-Only variables. It is up to you to declare those precious variables as such.

I have set up a test that demonstrates the efficacy of my technique to successfully release System.__ComObject objects before they become ghosts in your machine. You can download the test kit here; the results are displayed in the console. The test will show RAM usage and Excel processes running before and after the execution of two scripts, one that cleans up with a Remove-ComObject call and another that does not. These scripts also demonstrate other image manipulation that you might find interesting. Unzip the contents to your favorite test directory, run TestExcelDemo.ps1, watch and listen. It is less than five minutes. Excel 2010 or Excel 2007 is required.

Robert, I want to thank you for writing a really cool script, and for sharing your Windows PowerShell goodness with us.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Use a PowerShell Module to Easily Export Excel Data to CSV

Anonymous — Thu, 21 Jul 2011 05:00:00 GMT

Summary: Learn how to use a Windows PowerShell module to easily import and export Excel data.

Microsoft Scripting Guy Ed Wilson here. Jeremy Engel has joined us today to talk about using a Windows PowerShell module to easily import and export Excel data.

Take it away, Jeremy!

I started coding at the age of 10, writing games in BASIC for my lightning-fast Commodore 64. From there I ventured into the high-tech world of text-based online gaming, affectionately known as MUDs—specifically EverDark, where I learned to write C++ code. Later, I made the jump to systems engineering, and it was there that I discovered scripting. I loathe repetitive tasks and have a great love for efficiency (I’m lazy). Therefore, I can find ways to incorporate Windows PowerShell into almost every project and task I do as a systems engineer. I am also known to write the occasional application or service in Visual C#, which is mutually reinforcing with Windows PowerShell because they are essentially different expressions of the same language. Now for today’s blog post. Hope you like it.

Excel to Windows PowerShell and Back Again

Ever wish you could import from and export to Excel just like you can using CSV files? So did I, and so did a lot of other people, as I discovered.

Back during Tech∙Ed this year, it seemed like I spent an entire day griping about this blatant disparity between reality and my imagination. Not that I kept bringing it up (I swear I did not!), but that it kept coming up in conversation. You know, like one of those weird days where the same esoteric word finds its way into a multitude of conversations, only to then never be heard of again until the next cosmic kink in the space-time continuum.

Anyway, during dinner that night, it came up again. I casually blurt out that it is ridiculous that there are not any such functions, and how I could code them in a single night! Then it struck me—I actually think I could code them in a single night. Before I realized it, I was announcing my plans to the group of fellow Windows PowerShell scripters. So realizing I was losing hours, I dashed home by 8:30 P.M. and set to work. By 1:00 A.M., I had two fairly well fleshed-out cmdlets, but more importantly, they worked!

The complete Excel module can be downloaded from the Scripting Guys Script Repository.

As a basis for understanding how the conversion between Excel and Windows PowerShell works, think of each row in an Excel spreadsheet as an object in Windows PowerShell with the names of each column in Excel being a property name of the object in Windows PowerShell.

So if the value for A1 is Example1 and the value for A2 is test, in PowerShell:

$row.Example1 = “test”

If the value for A3 is ubu, in Windows PowerShell:

$row.Example1 = “ubu”

Each row object is then appended to an array of these objects, eventually representing the entire spreadsheet in Windows PowerShell object form.

Got it? When you understand this, it becomes easy to see how we could move from Excel to Windows PowerShell and back again. All that is left then is to work out the mechanics.

I really wanted this to be Excel-independent, meaning that I wanted to read an Excel document as an XML file and vice versa. I was able to get the export cmdlet to work fine writing to XML, but I was not able to figure out how to get the Excel XML to do an import—it was all just a jumbled mess. So unfortunately, I had to stick with the “wonderful” COM object.

Honestly, COM objects are not that bad. They just look really bad when you’re expecting them to be as flexible, transparent, fast, and disposable as Windows PowerShell objects. That aside, they do have a fair amount of useful methods and properties, which I make use of in these two cmdlets. I will not elucidate them all here, but just show you some of the important ones.

$excel = New-Object -ComObject Excel.Application

$workbook = $excel.Workbooks.Open($Path)

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

As you can see, I am creating an instance of the Excel.Application object and then opening the workbook located at $Path and from there retrieving the first worksheet. What do you mean you have Excel workbooks with more than one worksheet? Okay, I’ll work on an enhancement later. When, you ask? Later.

Here is another quirk about COM objects. If you take those first two lines of Windows PowerShell code shown above and open up Excel workbook, you’ll notice that typing $workbook.Worksheets in Windows PowerShell looks like it shows you an array of worksheet objects. Alas, no, it is actually another COM object. So I couldn’t just pull the object for sheet 1 by doing $workbook.Worksheets[0]; I had to use a COM method called Item(). Fun, huh?

One last bit on COM objects. Apparently, COM objects are like that quasihomeless guy who sleeps on your couch. They are extremely hard to get rid of. I alluded to the problem in the preceding paragraph, which is that COM objects like to return more COM objects, rather than property values. So you think you may be querying a value, when what you’re getting is a sneaky cockroach of a COM object.

Because of that, in each cmdlet I had to go through and try to repeatedly wipe out each object of the COM before finally killing the mothership. And wow, in doing my previous example on the worksheets, I just realized that even though I wasn’t saving the COM object references for workbooks and worksheets to Windows PowerShell variables, they were still lying out there. That is why I was sometimes having trouble killing the Excel process. So I had to adjust my script slightly to release those as well!

$workbooks = $excel.Workbooks

$workbook = $workbooks.Open($Path)

$worksheets = $workbook.Worksheets

$sheet = $worksheets.Item(1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($columns) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($rows) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($range) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheets) } while($o -gt -1)

$workbook.Close($false)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) } while($o -gt -1)

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbooks) } while($o -gt -1)

$excel.Quit()

do { $o = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) } while($o -gt -1)

It isn’t pretty, but it gets the job done.

I tried to make the Import-Excel cmdlet as flexible as possible, knowing how people often have messy spreadsheets. So I needed to account for situations when all of the columns were missing headers (the NoHeaders switch), or if only some of the columns had no headers. Under the latter situation, could also fall stray cells out in the middle of nowhere. I didn’t want to make value-judgments on any of that data; I just wanted to make sure I could logically ingest it into Windows PowerShell. To do that, the Import-Excel script creates generic property names for missing headers.

In the previous screen shots, did you notice how the Excel document had no header for column B? The cmdlet noticed that too and gave it a generic property name, in this case: Column2.

After you have imported the spreadsheet, you can manipulate the objects just like any other in Windows PowerShell. In the following figure, I fill in item 3’s Example1 value.

In the following figure, I add an entirely new item to the object array.

Converse to Import-Excel is Export-Excel, which takes an array of objects and writes them to an Excel file. For a little flare, I added in the ability to make the header row bold, and also gave you three choices of bottom border, if so desired.

And that, my friends, is how a self-imposed dare is won!

Thank you, Jeremy, this is a great post, and I look forward to using your new Excel module.

Ed Wilson, Microsoft Scripting Guy