PowerShell works with Excel spreadsheets

Get-ExcelData Script

This script reads Excel spreadsheets.

It takes two parameters

The Excel file name and an ‘sql command’.

The provider used in the connection treats the worksheet like a database.

Example WorkSheet

This sheet is named Sheet1. You access the data with an sql command.

Using Excel data in calculations

The previous example printed the contents of the Excel sheet. Here is how you can programmatically work with the data.

Using the script, the columns StandardHours and Wage were extracted from the spreadsheet. Then, using the PowerShell ForEach, looped through and the Pay calculated.

Wrapping up

This post outlines how easy it is to access Excel spreadsheets with PowerShell.

Upcoming posts will use the Get-ExcelData script to

• Do code generation
• Simulate FIT (Framework for Integrated Test)
• Write data to a spreadsheet
• Create a spreadsheet on the fly
• Access Excel Named Ranges

Downloads

Download the script and test spreadsheet here.

Links

The Get-ExcelData script is a pared down version of Lee Holmes Invoke-SqlCommand.

You can find it in his upcoming book Windows PowerShell: The Definitive Guide.