# encoding: ascii
# api: powershell
# title:
# description: The script leverages Powershell’s .NET, COM and ADODB functionality to automate the creation of Excel workbooks pre-populated with data retrieved from a SQL2008R2 database using stored procedures.
# version: 0.1
# type: class
# author: Kent Finkle
# license: CC0
# function: Release-Ref
# x-poshcode-id: 3127
# x-archived: 2012-01-14T07:05:09
#
# The script is #-heavy, sacrificing brevity for clarity because this is my first effort at a POSH script that goes much beyond the “Hello World” stage. The script has been tested on Win7 × 64 using SQL2008R2 Express and Excel 2010.
# The stimuli to create the script were my inability to:
# 1. Devise a quick way to pump SQL data into Excel worksheets using .NET data objects.
# 2. Locate a Powershell implementation of Excel’s fast CopyToRecordset function.
#
<#=====================================================
SYNOPSIS
Create an Excel workbook for a teacher.
The workbook contains a worksheet for each class.
=====================================================#>
<#=====================================================
RETRIEVE CURRENT USER'S LOGON
Prefer API to $env:username.
http://www.leeholmes.com/blog/2009/...lkthrough/
http://www.pinvoke.net/
LOCAL:
$signature
$type
IN:
OUT:
$logon
=====================================================#>
$signature = @'
[DllImport("mpr.dll", CharSet=CharSet.Auto, SetLastError=true)]
public static extern int WNetGetUser(
[MarshalAs(UnmanagedType.LPTStr)] string lpName,
[MarshalAs(UnmanagedType.LPTStr)] StringBuilder lpUserName,
ref int lpnLength);
'@
$type = Add-Type -MemberDefinition $signature -Name GetLogonString -Namespace WNetGetUser -Using System.Text -PassThru
$logon = New-Object System.Text.StringBuilder 32 # Current user's logon is returned as a null-terminated string in this buffer.
$type::WNetGetUser("", $logon, [ref]$logon.Capacity)
<#=====================================================
RETRIEVE CURRENT USER'S SFKEY
The SFKey is the database UID - four chars.
Test current user's logon first.
If OK, open a .NET connection and get the SFKey that matches the logon.
Allows identity impersonation.
LOCAL:
$cmdSFKey
IN:
$logon
OUT:
$cnnNET
$sfkey
=====================================================#>
Switch($logon.Length) {
0 {throw "Error retrieving Windows logon."}
default {
$cnnNET = New-Object System.Data.SqlClient.SqlConnection
$cnnNET.ConnectionString = "Server=HOMEPC\SQLEXPRESS;Database=Keeper;Integrated Security=SSPI"
$cnnNET.Open()
$cmdSFKey = New-Object System.Data.SqlClient.SqlCommand
$cmdSFKey.Connection = $cnnNET
$cmdSFKey.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmdSFKey.CommandText = "SELMainSFKey"
$cmdSFKey.Parameters.Add("@LogonName",[System.Data.SqlDbType]"varchar",30)
$cmdSFKey.Parameters["@LogonName"].Value = $logon.ToString()
$cmdSFKey.Parameters.Add("@SFKey",[System.Data.SqlDbType]"varchar",4)
$cmdSFKey.Parameters["@SFKey"].Direction = [System.Data.ParameterDirection]"Output"
$cmdSFKey.ExecuteNonQuery()
[string] $sfkey = $cmdSFKey.Parameters["@SFKey"].Value
<#=====================================================
RETRIEVE TIMETABLE PERIOD AND WORKBOOK SAVE LOCATION
Test current user's SFKey first.
If OK, retrieve two values from single-row lookup table:
1. $ttperiod is reqd to look up teacher's classes.
2. $xlpath is required to save the Excel workbook.
Nested switch statements end in this block.
LOCAL:
$cmdAdmin
$adpAdmin
$tblAdmin
$rowAdmin
IN:
$sfkey
$cnnNET
OUT:
$ttperiod
$xlpath
=====================================================#>
Switch($sfkey.Length) {
0 {throw "Error retrieving teacher key."}
default {
$cmdAdmin = New-Object System.Data.SqlClient.SqlCommand
$cmdAdmin.Connection = $cnnNET
$cmdAdmin.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmdAdmin.CommandText = "SELMainAdmin"
$adpAdmin = New-Object System.Data.SqlClient.SqlDataAdapter
$adpAdmin.SelectCommand = $cmdAdmin
$tblAdmin = New-Object System.Data.DataTable
$adpAdmin.Fill($tblAdmin)
$rowAdmin = $tblAdmin.Rows
foreach ($rowAdmin in $tblAdmin) {
[string] $ttperiod = $rowAdmin.Item("CurrentTTPeriod")
[string] $xlpath = $rowAdmin.Item("KeeperPath")
} # End foreach
} # End Switch $sfkey default
} # End Switch $sfkey.length
} # End Switch $logon default
} # End Switch $logon.length
<#=====================================================
RETRIEVE CURRENT USER'S CLASSES
Test Admin values first.
If OK, retrieve teacher's classes into a DataTable.
A "class" has two pieces:
1. subject key (e.g., 08ENG1).
2. class number (e.g., 6).
LOCAL:
$test
$cmdClasses
$adpClasses
IN:
$ttperiod
$xlpath
$cnnNET
$sfkey
OUT:
$rowClasses
$tblClasses
=====================================================#>
[int] $test = 0
$test = $ttperiod.Length
if ($test = 0) {throw "Error retrieving timetable period."}
$test = $xlpath.Length
if ($test = 0) {throw "Error retrieving XL pathname."}
$cmdClasses = New-Object System.Data.SqlClient.SqlCommand
$cmdClasses.Connection = $cnnNET
$cmdClasses.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmdClasses.CommandText = "SELKeeperClassesForTeacher"
$cmdClasses.Parameters.Add("@ttperiod",[System.Data.SqlDbType]"varchar",6)
$cmdClasses.Parameters["@ttperiod"].Value = $ttperiod
$cmdClasses.Parameters.Add("@SFKey",[System.Data.SqlDbType]"varchar",4)
$cmdClasses.Parameters["@SFKey"].Value = $sfkey
$adpClasses = New-Object System.Data.SqlClient.SqlDataAdapter
$adpClasses.SelectCommand = $cmdClasses
$tblClasses = New-Object System.Data.DataTable
$adpClasses.Fill($tblClasses)
$rowClasses = $tblClasses.Rows
<#=====================================================
CREATE EXCEL OBJECTS
Application and workbook.
Create workbook with a worksheet for each class.
LOCAL:
$xl
IN:
$tblClasses
OUT:
$wb
=====================================================#>
$xl = New-Object -ComObject Excel.Application
$xl.SheetsInNewWorkbook = $tblClasses.Rows.Count
$wb = $xl.Workbooks.Add()
$xl.Visible = $True
<#=====================================================
CREATE ADODB OBJECTS
An ADODB recordset is reqd by Excel's CopyFromRecordset.
LOCAL:
$ad [int] constants
$cnnADO
$prmTTPeriod
IN:
$ttperiod
OUT:
$prmSUKey
$prmClass
$cmdStudents
$rstStudents
=====================================================#>
[int] $adCmdStoredProc = 4
[int] $adVarChar = 200
[int] $adSmallInt = 2
[int] $adParamInput = 1
[int] $adOpenForwardOnly = 0 # Default.
[int] $adOpenStatic = 3
[int] $adLockReadOnly = 1 # Default.
[int] $adLockOptimistic = 3
[int] $adUseServer = 2 # Default.
[int] $adUseClient = 3
$cnnADO = New-Object -ComObject ADODB.Connection
$cnnADO.Open("Provider=SQLOLEDB; Data Source=HOMEPC\SQLEXPRESS; Initial Catalog=Keeper; Integrated Security=SSPI")
$cmdStudents = New-Object -ComObject ADODB.Command
$cmdStudents.ActiveConnection = $cnnADO
$cmdStudents.CommandType = $adCmdStoredProc
$cmdStudents.CommandText = "SELKeeperStudentsForClass"
$prmTTPeriod = New-Object -ComObject ADODB.Parameter
$prmTTPeriod = $cmdStudents.CreateParameter("@ttperiod",$adVarChar,$adParamInput,6,$ttperiod) # This parameter doesn't change; assign value here.
$cmdStudents.Parameters.Append($prmTTPeriod)
$prmSUKey = New-Object -ComObject ADODB.Parameter
$prmSUKey = $cmdStudents.CreateParameter("@SUKey",$adVarChar,$adParamInput,7) # This parameter changes; assign value in ForEach.
$cmdStudents.Parameters.Append($prmSUKey)
$prmClass = New-Object -ComObject ADODB.Parameter
$prmClass = $cmdStudents.CreateParameter("@class",$adSmallInt,$adParamInput) # This parameter changes; assign value in ForEach.
$cmdStudents.Parameters.Append($prmClass)
$rstStudents = New-Object -ComObject ADODB.Recordset
$rstStudents.CursorLocation = $adUseServer
$rstStudents.CursorType = $adOpenForwardOnly
$rstStudents.LockType = $adLockReadOnly
<#=====================================================
RETRIEVE STUDENTS
Place students in separate worksheets, one for each class, in a fabricated Excel workbook.
Use an ADODB recordset to take advantage of Excel's speedy CopyFromRecordset function.
LOCAL:
$ix: indexes worksheets.
$subj: student's subject code.
$class: student's class number.
$ws: current worksheet.
$subcode: current worksheet's new name (subj + class).
$rangeData: Excel range for column sizing.
IN:
$rowClasses, $tblClasses: data source for teacher's classes iterated by foreach loop.
$prmSUKey, $prmClass: refreshed with new values during each iteration.
$cmdStudents, $rstStudents: retrieve new class during each iteration.
$wb: current workbook, one per teacher.
OUT:
=====================================================#>
# Seed index for worksheets.
[int] $ix = 1
# Iterate each class.
foreach ($rowClasses in $tblClasses) {
# Assign subject and class values to typed variables.
[string] $subj = $rowClasses.Item("subj")
[int] $class = $rowClasses.Item("class")
# Rename worksheet.
[string] $subcode = $subj + $class
$ws = $wb.Sheets.item($ix)
$ws.Name = $subcode
# Assign typed variables to ADODB parameters.
$prmSUKey.Value = $subj
$prmClass.Value = $class
# Assign result set to ADODB recordset.
$rstStudents = $cmdStudents.Execute()
# Copy result set to current worksheet. Adjust entry cell as reqd.
$wb.Sheets.item($subcode).Cells.item(2,1).CopyFromRecordset($rstStudents)
# Size columns.
$rangeData = $wb.Sheets.item($subcode).UsedRange
[void] $rangeData.EntireColumn.Autofit()
# Increment index for worksheets.
$ix++
} # End foreach
<#=====================================================
SAVE WORKBOOK
LOCAL:
$pathname
IN:
$xlpath
$sfkey
$ttperiod
$wb
OUT:
===================================================== #>
[string] $pathname = $xlpath + $sfkey + $ttperiod + ".xlsx"
$wb.SaveAs($pathname)
<#=====================================================
CLEANUP
=====================================================#>
function Release-Ref ($ref) {
# =====================================================
# Author: Kent Finkle
# http://gallery.technet.microsoft.co...8a41a680a7
# =====================================================
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# Close connections.
$cnnNET.Close
$cnnADO.Close
# Get rid of COM objects.
$k = Release-Ref($ws)
$k = Release-Ref($wb)
$k = Release-Ref($xl)
$k = Release-Ref($rstStudents)
$k = Release-Ref($cmdStudents)
$k = Release-Ref($prmTTPeriod)
$k = Release-Ref($prmSUKey)
$k = Release-Ref($prmClass)
$k = Release-Ref($cnnADO)
# Get rid of .NET objects.
$cmdSFKey.Dispose
Remove-Variable cmdSFKey
$cmdAdmin.Dispose
Remove-Variable cmdAdmin
$adpAdmin.Dispose
Remove-Variable adpAdmin
$tblAdmin.Dispose
Remove-Variable tblAdmin
$rowAdmin.Dispose
Remove-Variable rowAdmin
$cmdClasses.Dispose
Remove-Variable cmdClasses
$adpClasses.Dispose
Remove-Variable adpClasses
$tblClasses.Dispose
Remove-Variable tblClasses
$rowClasses.Dispose
Remove-Variable rowClasses
$cnnNET.Dispose
Remove-Variable cnnNET
# Get rid of Excel process.
# Idea from blub, http://www.powershellcommunity.org/...fault.aspx
$ExcelProcess=get-process excel
$ExcelProcess | foreach {stop-process ($_.id)}
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | out-null