Powershell: Working with Excel

A few days ago at work I needed to create a quick report on some info from one of our VMware clusters.  I proceeded to dump some data to a CSV file and then converted it to an XLS file.

I then modified/cleaned up the data and my report was done.

I started to clean up the code for reuse and decided to skip the CSV file altogether and just save the data directly to an Excel file.

Listed here are some samples to do just that:

NOTE: This assumes that you have powershell and Excel installed on your local machine.

Create the Spreadsheet

For starters, we use the Excel COM object to create a workbook with a worksheet.

$excel = new-object -comobject Excel.Application
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
$worksheet = $worksheets.Item(1)
$worksheet.Name = “Name of Worksheet”

If you have data in a spreadsheet that you just want to query, its usually best to just keep the Excel window hidden by setting the following value to $False.  However if you want to see the Excel window you just set the value to $True:

$excel.Visible = $True

If you were run the script at this point, and you enabled the $Excel.Visible value, you would have a blank Excel spreadsheet open and ready to receive data, but not very useful at this point.

Adding information

Now we can start doing some useful things. We can actually modify specific cells within the spreadsheet. The following examples will add some text to a few cells.

$worksheet.Cells.Item(1,1) = “This is CELL A1”
$worksheet.Cells.Item(3,4) = “This is CELL D3”

This puts “This is CELL A1” into A,1 and “This is CELL D3” into D,3. The first number is the row identifier and the second number is the column identifier.

Formatting data

You can even customize the cells by changing the fonts used, the font size and even BOLD the text if needed. The following sample shows how to accomplish this:

$worksheet.Cells.Item(1,1).Font.Bold = $True
$worksheet.Cells.Item(1,1).Font.Size = 22
$worksheet.Cells.Item(4,3).Font.Italic = $True
$worksheet.Cells.Item(4,3).Font.Size = 14

What if you have over 100 cells that need format changes? If would be very time consuming to add more than 200 lines of code needed to accomplish that by using the methods above. But there are alternatives available. We can format a range of cells by using the following:

$range = $excel.Range(“C3″,”C4″)
$range.ColumnWidth = 50
$range.Borders.Color = 0
$range.Borders.Weight = 2
$range.Interior.ColorIndex = 37
$range.Font.Bold = $True
$range.HorizontalAlignment = 3

If you are curious about all of the values that can be modified, type the following command:

$range | get-member

Saving the Spreadsheet

Once your done modifying the spreadsheet, you should be sure to save your data.
This can be done by using:

$workbooks.SaveAs(“c:\automate.xlsx”)
$excel.quit()

Conclusion

Once the spreadsheet is saved, you could even use Powershell to send it via email or copy it to a network share. You can even schedule the powershell script to run as a scheduled task and automate the whole process.

— PFM

Advertisement