MSSQL Express naar Excel

Als je wat meer aan de kant van het organiseren terecht komt van het IT werk moet je een hoop documenten en rapporten op hoesten en je moet het nog op tijd opsturen ook. Gelukkig valt dat ook te scripten. Alle benodigde data bewaar ik een een MS SQL Express database. In een eerdere posting liet ik je al weten hoe ik het via Powershell up-to-date hield. Nu de andere kant op, van database naar Excel en vervolgens via Outlook mailen.

Ik ben natuurlijk niet gek. Ik maak het me niet moeilijker dan nodig. Ik heb een excel sheet met wat ‘conditional formatting’ en dat ga ik niet allemaal met scripts maken. Ik heb een excelsheet gemaakt zoals ik het helemaal wil en deze opgeslagen als template. Het script neemt dat die template en vult het kopie met data.

Hieronder vind je de scripts.

# set the variables
$date    = get-Date -format yyyyMd
$folder  = "C:toolsservicedesk-lijst"
$folderA = $folder + "archive"
$xlTemplate = "Servicedesk-template.xlsx"
$xlFile  = "Servicedesk-" + $date + ".xlsx"
$mail    = "mailbody.txt"
$rcpt    = "adres1@mail.com;adres2@mail.com"
$rcptCC  = "mijzelf@mail.com"

$rowStart = 2 # start-row for the SQL data fill
$colStart = 1 # start-column for the SQL data fill
$sqlServer = "SERVERNAAM"

# set fullpath in variable
$xlTemplate = $folder + $xlTemplate
$xlFile = $folder + $xlFile
$mailMgr = $folder + $mailMgr
$mailSup = $folder + $mailSup

# create the file from template which has all necessairy formatting
Copy-Item -Path $xlTemplate -Destination $xlFile -Force

# start excel instance with file
$xl = New-Object -ComObject "Excel.Application"
$wb = $xl.Workbooks.Open($xlFile)
$ws = $wb.Sheets.Item(1)
#$xl.Visible = $True # makes excel visible in development-status

# activate SQL mode and query
Import-Module "sqlps" -DisableNameChecking

$sqlQuery = "SELECT [Kolom1],[Kolom2],[Kolom3],[Kolom4] FROM [inventarisaties].[dbo].[view] ORDER BY [Kolom1] ASC"
$data = Invoke-SQLcmd $sqlQuery -ServerInstance $sqlServer

# walk through the results and fill the excel file
$row = $rowStart
foreach ($line in $data) {
    $col = $colStart
    $cells = $ws.Cells
    $cells.item($row,$col) = $line.Kolom1
    $col++
    $cells.item($row,$col) = $line.Kolom2
    $col++
    $cells.item($row,$col) = $line.Kolom3
    $col++
    $cells.item($row,$col) = $line.Kolom4
    $row++
}

# close excel instances
$wb.Save()
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

# Start Outlook
Start-Process Outlook
$o = New-Object -com Outlook.Application
$mail = $o.CreateItem(0)

# Send an e-mail
$mail.subject = “Hierbij de laatste lijst“
$mail.body = (Get-Content $mail | out-string)
$mail.To = $rcpt
$mail.Bcc = $rcptCC
$mail.Attachments.Add($xlFile)
$mail.Send()

# Cleanup
Start-Sleep -s 16 # give time to send mails, before quitting Outlook
Move-Item ($folder + "Servicedesk-2*.xlsx") $folderA -force
$o.Quit() # quits everything concerning Outlook
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($o)
Start-Process Outlook # only on live-workstation