Thursday, March 10, 2011

Write Excel Spreadsheets Fast in PowerShell

If you try to populate an Excel spreadsheet cell by cell using PowerShell expect it to take a long time. As I showed in a previous post, "Speed Up Reading Excel Files in PowerShell", when dealing with Excel work with comma separated value text files. The population of the cells appears to be the huge bottleneck in Excel permformace but if you let Excel load a comma separated values file, you can quickly format and manipulate the prepopulated cells.

One example from my own work involves a spreadsheet with ~3,500 rows. If I populate the rows directly through Excel it can take upwards of five minutes to complete the process. If I dump the data to a comma separated values text file then import it into Excel, it takes about 2 seconds -- a huge time savings. When you are running ad hoc reports, it pays off. If you want an excuse to head down to Starbucks from your office, it doesn't.

To demonstrate this technique, I will take a previous post, "BlackBerry User Report", and change the report from a comma separated values text file to Excel spreadsheet. First to make the final result look nice, the NoteProperty names are cleaned up so they make attractive headers in the spreadsheet. Swap out the following lines to achieve this.
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Domain" -value$domain
Add-Member -inputObject $blackberryUser -type NoteProperty -name "User ID" -value$sAMAccountName
Add-Member -inputObject $blackberryUser -type NoteProperty -name "First Name" -value$firstName
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Last Name" -value$firstName
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Display Name" -value$displayName
Add-Member -inputObject $blackberryUser -type NoteProperty -name "E-Mail Address" -value$proxyAddress
Add-Member -inputObject $blackberryUser -type NoteProperty -name "PIN" -value$pin
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Cell Phone Number" -value$phoneNumber
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Desk Phone Number" -value$telephoneNumber
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Street Address" -value$streetAddress
Add-Member -inputObject $blackberryUser -type NoteProperty -name "City" -value$city
Add-Member -inputObject $blackberryUser -type NoteProperty -name "State" -value$state
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Zip Code" -value$zipCode
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Country" -value$country
Add-Member -inputObject $blackberryUser -type NoteProperty -name "BlackBerry Model" -value$modelName
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Carrier" -value$homeNetwork
Add-Member -inputObject $blackberryUser -type NoteProperty -name "IMEI" -value$imei
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Password Enabled" -value$passwordEnabled
Add-Member -inputObject $blackberryUser -type NoteProperty -name "Exchange Server" -value$exchangeServer
Add-Member -inputObject $blackberryUser -type NoteProperty -name "BlackBerry Server" -value$blackberryServer

$blackberryUsers =$blackberryUsers | Sort-Object "BlackBerry Server", "Last Name", "First Name"

$excelFile = ("\\fileserver.ad.mydomain.local\it_reports\blackberry\" + (Get-Date -format yyyyMMdd) + "-BlackBerry User Report.xlsx")$temporaryCsvFile = ($env:temp + "\" + ([System.Guid]::NewGuid()).ToString() + ".csv")$blackberryUsers | Export-Csv -path $temporaryCsvFile -noTypeInformation if(Test-Path -path$excelFile) { Remove-Item -path $excelFile }$excelObject = New-Object -comObject Excel.Application
$excelObject.Visible =$false

$workbookObject =$excelObject.Workbooks.Open($temporaryCsvFile)$workbookObject.Title = ("BlackBerry User List for " + (Get-Date -Format D))
$workbookObject.Author = "Robert M. Toups, Jr."$worksheetObject = $workbookObject.Worksheets.Item(1)$worksheetObject.UsedRange.Columns.Autofit() | Out-Null
$worksheetObject.Name = "BlackBerry Users"$listObject = $worksheetObject.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange,$worksheetObject.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "User Table"$listObject.TableStyle = "TableStyleMedium4" # Style Cheat Sheet in French/English: http://msdn.microsoft.com/fr-fr/library/documentformat.openxml.spreadsheet.tablestyle.aspx

$workbookObject.SaveAs($excelFile,51) # http://msdn.microsoft.com/en-us/library/bb241279.aspx
$workbookObject.Saved =$true
$workbookObject.Close() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) | Out-Null

$excelObject.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

if(Test-Path -path $temporaryCsvFile) { Remove-Item -path$temporaryCsvFile }


1 comment:

1. Thanks for the post. This is a huge help to me.