Powershell and Excel

some times it will be nice to export data to Excel.

to open Excel from power shell and set a Heading.

Try the following

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Name = “MySheet”
$Sheet.Cells.Item(1,1) = “MyTestheading”
#$Sheet.Cells.Item(1,2) = “You”
#$Sheet.Cells.Item(1,3) = “Get”
#$Sheet.Cells.Item(1,4) = “The”
#$Sheet.Cells.Item(1,5) = “Code”
$WorkBook = $Sheet.UsedRange
#Background color in cell
$WorkBook.Interior.ColorIndex = 37
#Text color in cell
$WorkBook.Font.ColorIndex = 0
$WorkBook.Font.Bold = $True

The code will open Excel create a sheet call MySheet and set a heading call MyTestheading.

Lets fill some data in

#data
$datain = (“Microsoft”,”Apple”,”IBM”,”Redhat”,”Novell”)
# Row Counter / we use row 1 for our Heading.
$intRow = 2
foreach ($line in $datain) {

$Sheet.Cells.Item($intRow,1) = ($line)

$intRow = $intRow + 1

}

# Autofitter
$WorkBook.EntireColumn.AutoFit()

 

The code will open Excel create a sheet call MySheet and set a heading call MyTestheading and put our data in.

 

to be continued