Generate Excel content with PowerShell

Prerequisites

• Excel must be installed
• If the Excel file shall be generated and saved in a scheduled task some folders must be created: C:\Windows\System32\config\systemprofile\Desktop (32 bit and 64 bit) and C:\Windows\SysWOW64\config\systemprofile\Desktop (64 bit)

Open an existing Excel file

Using-Culture en-us {

 # open existing file
 $excelFilePath = "path" + "fileName.xls"
 $Excel = New-Object -COM Excel.Application
 $Excel.visible = $False
 $Excel.DisplayAlerts = $False
 $Excel.Workbooks.Open($excelFilePath)
 
 # create new sheet and activate it
 $sheet = $Excel.sheets.Add()                                                   
 $sheet.Name = "sheetName"
 $sheet.activate()
 
 # clean up
 Remove-ComObject -Verbose
}

Help functions

function Using-Culture ([System.Globalization.CultureInfo]$culture, [ScriptBlock]$script)
{
 $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
 trap
 {
  [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
 }
 [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
 $ExecutionContext.InvokeCommand.InvokeScript($script)
 [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}

function Remove-ComObject {
 [CmdletBinding()]
 param()
 end {
  Start-Sleep -Milliseconds 500
  [Management.Automation.ScopedItemOptions]$scopedOpt = 'ReadOnly, Constant'
  Get-Variable -Scope 1 | Where-Object {
   $_.Value.pstypenames -contains 'System.__ComObject' -and -not ($scopedOpt -band $_.Options)
  } | Remove-Variable -Scope 1 -Verbose:([Bool]$PSBoundParameters['Verbose'].IsPresent)
    [gc]::Collect()
  }
}

Data

# Retrieve cell (1,1) and set the value Name
$sheet.Cells.Item(1,1) = "Name"

# Clear data from a given range
$range = $sheet.Range("a1","e1000")
$range.clear()

Formatting

# Indent cell (1,1) 4 times
$sheet.Cells.Item(1,1).AddIndent = $true
$sheet.Cells.Item(1,1).IndentLevel = 4

# set Bold text on a row
$sheet.Rows.Item(1).Font.Bold = $true

# set Font size on a given range
$range = $sheet.Range("a1","e1000")
$range.Font.Size = 12     

# set Font style on row 1
$sheet.Rows.Item(1).Style = "Heading 2"

# set column width in cell (1,1)
$sheet.Cells.Item(1,1).columnwidth=40