Using Excel object model from within PowerShell is fairly easy and straightforward. However the objects you use and the way they work are somewhat different from PowerShell's built-in objects (e.g. Services, processes, file store), or other .NET and to some extent WMI objects. Since Excel is exposed as COM objects, some aspects self discovery are missing. Nevertheless the Excel Object model is open to PowerShell, but the description of these services is from a VB or VBA perspective, not an admin/PowerShell perspective.

Using the object model in this section of the MSDN library is one way of two main ways you can access data in an Excel spreadsheet from within PowerShell. The second way is to use the OLEDB provider and treat the spreadsheet as a database.

Here is a simple script that creates a new Excel 2008 workbook with one worksheet. It then does some formatting of the cells in the worksheet then saves the workbook away.

# new-ExcelWorkbook.ps1
# Creates a new workbook (with just one sheet), in Excel 2007
# Then we create a sample worksheet for other PowerShell based
# Excel samples in MSDN
# Thomas Lee - tfl@psp.co.uk

# Create Excel object
$excel = new-object -comobject Excel.Application

# make Excel visible
$excel.visible = $true

# create a new workbook
$workbook = $excel.workbooks.add()

# default workbook has three sheets, remove 2
# nb: these need to be confirmed in Excel. :-(
$S2 = $workbook.sheets | where {$_.name -eq "Sheet2"}
$s3 = $workbook.sheets | where {$_.name -eq "Sheet3"}
$s3.delete()
$s2.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"

# 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"
$s1.range("D1:D1").cells=2
$s1.range("D2:D2").cells=2
$s1.range("D3:D3").cells.formula = "=sum(d1,d2)"

# Give Cell range a nice name
$range=$s1.range("D1:d3")
$range.name="Dimsum"   

# and save it away!
# if file exists, you need to confirm the save, yada, yada
$s1.saveas("d:\foo\xlsx2.xlsx")