Manipulate Excel with Powershell

Powershell can out put in a lot of different files and formats. The ability to easily customize its output is certainly one of its strengths. However, one day you might find yourself wanting to read information from an excel worksheet or have the ability manipulate and existing on. With powershell you can easily Open exisitng files, create new ones, point it to a particular cell, search through it for certain results, input data, add worksheets, rename them etc.

This article is only meant to be an introduction to working with powershell and Excel.

Lets start with how we will call up Excel to start working with it. You will need to create a variable that is calling up the COM Object.

$Excel = New-Object -COMObject Excel.Application

# Now we will want to make it so we can see what it is doing (for debugging purposes)
$Excel.Visible - $True

# Now we need to create a new workbook in excel
$Workbook = $Excel.Workbooks.add()

# If we want to remove the extra sheets we can run something like this
$Worksheet2 = $Workbook.Sheet | Where {$_.Name -eq "Sheet2"}
$Worksheet3 = $Workbook.Sheet | Where {$_.Name -eq "Sheet3"}
$Worksheet2.Delete()
$Worksheet3.Delete()

# You might also want to change the name of the workbook
$Worksheet1 = $Workbook.Sheets | Where {$_.Name -eq "Sheet1"}
$Worksheet.Name = "My Workbook"

#You can also change the properties of the workbook
$Workbook.author = "Powershell Master"
$Workbook.Title = "Example"
$Worekbook.Subject = "The Power of Powershell"

# Enter Data into the spreadsheet
$Worksheet.Cells.Item(1,1) = "This is My Title

# You can also make the cells a variables
$Row = 1

# This can let you sequentially go through excel and enter data in like this
$WorkSheet.Cells.Items($Row,1) = "First Name"
$WorkSheet.Cells.Items($Row,2) = "Last Name"
$Row++
$WorkSheet.Cells.Items($Row,$1) = "John"
$WorkSheet.Cells.Items($Row,$2) = "Doe"

# Now we might want to sort and auto fit a column
$Range1 = $WorkSheet.range('A2:D2500')
$Range2 = $Worksheet.Range('B2')
$Range1.Sort($Range2, 1)
$Range.entirecolumn.autofit()

## We can't forget to save the file
$Excel.Quit()

#If you take a look at your processes you will find that excel is still running. To completely close out with #additional commands.
[System.RunTime.IntropServices.Marshall]::ReleaseComObject ($Excel)
#Then remove variable since the comobject has been release there is nothing we can call from $excel
Remove-Variable $Excel



#I have tested most of this code and found it to work. Some of it I have not. Other things I have done in the past.
1. Open an existing excel file
2. Find last used row and start entering data on the unused row.
3. Read from certain cells and use that data as a variable.
4. Highlight Rows and change the color of fonts and even bold or Underline.

Please take a look at the sources for more information and examples.
Sources 
http://theolddogscriptingblog.wordpress.com/
http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/33dbc1c4-40ee-4ef8-b25b-f29440ab3194/
http://technet.microsoft.com/en-us/library/ff730962.aspx