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 $blackberryServerNow that you have nice and pretty column headers in your NoteProperty, its time to write out that Excel spreadsheet. This code assumes that you have at least Excel 2007 installed on your scripting workstation/server. First the code writes out to the temp directory using a GUID (just to be fancy) for the comma separated values file's name. We create an Excel object and load that temporary file. At that point, we can save out a fairly boring Excel spreadsheet. That won't impress anyone. If you want to look "pro" when you distribute reports, having a good layout is key. You want to save out a nicely formatted spreadsheet. So you autofit all the columns to longest cell in each, populate the worksheet's name, update some of the metadata in the spreadsheet and then apply a table style. Table styles are a quick way to add color to your spreadsheet with minimal effort by creating a list object. I have found that if you want to apply your own custom style (a future blog post) there is no speed penalty as long as the data is prepopulated. And finally we perform a Save As, remind Excel that we are saved, quit Excel and do some garbage cleanup. If you just quit Excel programatically, you will find that Excel will still be in memory.
$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 }
Thanks for the post. This is a huge help to me.
ReplyDelete