Automating Excel Spreadsheets with Powershell

As a system administrator I find myself using Windows Powershell to automate just about everything that I do. After all that is the purpose of Information Techonlogy; to make our jobs, lives, etc. more efficient. That is what we do at Donet, we strive to make the services we provide more efficient for you as a customer.

If you operate in the Microsoft realm, just about everything you do can be scripted out with Windows Powershell. In this article we will take a look at automating excel spreadsheet creation with Windows Powershell. We assume that you have powershell installed on your local machine.

This post is for you if you find yourself filling out the same Excel spreadsheet month after month with the same data source.

Creating the spreadsheet
The first thing we do is create a new Excel COM object within the operating system and 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 want to see the Excel application you can issue the following command:

$excel.Visible = $True

At this point the spreadsheet is blank, exactly as if you just double clicked the excel application and opened it up.

Adding information
Here is where things start to get interesting. We can script out grabbing information from a datasource (Databases, CSVs, text files, etc) and populating the spreadsheet with that information. Feel free to google importing data into powershell and learning about the various techniques to gather and import information into Powershell.

To add information to the cell, perform the following:

$worksheet.Cells.Item(3,3) = “Title of Excel Document”
$worksheet.Cells.Item(4,3) = “This is really cool”

This places “Title of Excel Document” into C,3. The first number is the vertical identifier, with the second number being the column identifier.

Formatting data
This works great if we just have a bunch of text to put in different fields. But what if I wanted bold text, different font size, or I want to adjust my column width?

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

That formats the cell to use 22 font size and bold font. As you can tell formatting individual cells in powershell could be extremely time consuming to type out all that code. However we can format a range of cells by performing 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

Feel free to perform a ‘$range | get-member’ to see the parameters you can toggle.

Saving the Spreadsheet
After you are finished with adding items to the excel spreadsheet you will need to save it to a location. This is done rather simply by:

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

Conclusion
Once you save it to the location you can utilize powershell to email the excel spreadsheet as an attachment or notify your boss the spreadsheet is completed. Once the powershell script is finished you can set it up as a scheduled task from your local machine or server, completely automating the entire process.

Posted in donet.com

Advertisements
This entry was posted in PowerShell With Excel. Bookmark the permalink.