Powershell: Rota Generator

I was observing the other day someone copying a rotor with visual basic scripting in excel, immediately my first thought was “That’s a horrible way to create a rota” and I know I thought to myself one day that visual basic code will stop working and it will be a nightmare to fix.

Excel and visual basic code - yuck

This assumption was closer to the truth than I thought, visual basic code had broken and it wasn’t generating the new rota correctly.

Powershell to the rescue

This is a perfect opportunity for Powershell to steal the show and get it right from the start that’s consistently always going to work, unless somebody plays with the code, but then you won’t get a rota whatsoever.

Creating Excel documents without Excel installed?

The schedule was pretty simple so I thought it sounded like a challenge to get this working without using excel using the Excel plug-in enable you to create Excel spreadsheets without being in the application, this was a requirement for me because I don’t actually have office installed on my laptop.

Get the requirements

If you are looking to embark on something, you first need to know what the rules are to be able to script it successfully and for it to work, First, we need the shift requirements for the logic:

shifts = @("7AM-3PM", "8AM-4PM", "9AM-5PM", "10AM-6PM")
$daysToGenerate = 20  # Total workdays (4 weeks of 5 workdays excluding weekends)

That seems to cover all the full-time employees, with 4 shifts, then we have that over 20 days (which is four weeks, excluding the weekends which we don’t need to worry about)

The only other requirements are the part-time people, Which by no means should be forgotten that I find it simpler to do the logic for those with a different variable:

# Specific shift patterns for part-time employees
$adamShifts = @("7AM-12PM", "7AM-12PM", "7AM-12PM", "7AM-12PM", "7AM-12PM")
$markShifts = @("7AM-11AM", "7AM-11AM", $null, $null, "7AM-11AM")

This will cover the part-time employees called Adam and Mark in this instance, in this particular example Adam will work 7am to 12pm On a Monday through Friday basis, and Mark will work 7am to 11am on a Monday, Tuesday and Friday, the flag of “null” essentially is not assigning a shift.

Finally, do you want to make sure all the shifts are evenly distributed, I did start with random shifts, but it was far too quick to start bunching up lots of shifts together, which is not ideal so either meant the logic to evenly distribute the shifts, but across all the employees.

Note : The logic for stuff like this will always change as humans are behind the logic, I had to go through a couple of iterations before I got the formula correct, Just remember what humans tell you is not what they always want!

This is where that got me:

This is the Powershell script to create the image above, obviously you will need to change the names and some of the variables to get it with different names and shifts:

Script : Generate-Rota.ps1

# Install the required module if it's not installed
if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    Install-Module -Name ImportExcel -Force -Scope CurrentUser
}

# Import the required module
Import-Module ImportExcel

# Define variables
$fullTimeEmployees = @("Windy", "Snowdrop", "Pluto", "Cyanide", "Portabella", "Marijuana","Everest")
$partTimeEmployees = @("Adam", "Mark")
$shifts = @("7AM-3PM", "8AM-4PM", "9AM-5PM", "10AM-6PM")
$daysToGenerate = 20  # Total workdays (4 weeks of 5 workdays excluding weekends)
$startDate = Get-Date

# Set start date to next Monday if today is not Monday
if ($startDate.DayOfWeek -ne 'Monday') {
    $startDate = $startDate.AddDays((8 - [int]$startDate.DayOfWeek) % 7)
}

# Generate workdays excluding weekends
$workdays = @()
$currentDate = $startDate
while ($workdays.Count -lt $daysToGenerate) {
    if ($currentDate.DayOfWeek -ne 'Saturday' -and $currentDate.DayOfWeek -ne 'Sunday') {
        $workdays += $currentDate
    }
    $currentDate = $currentDate.AddDays(1)
}

# Create header row with dates
$header = @("Employee")
foreach ($day in $workdays) {
    $dateString = $day.ToString("ddd, dd MMM")
    $header += $dateString
}

# Create an array to hold the data rows
$dataRows = @()

# Assign shifts evenly across full-time employees by week
$shiftCount = $shifts.Count
$employeeCount = $fullTimeEmployees.Count
for ($i = 0; $i -lt $fullTimeEmployees.Count; $i++) {
    $employee = $fullTimeEmployees[$i]
    $row = [ordered]@{ Employee = $employee }
    for ($j = 0; $j -lt $workdays.Count; $j++) {
        $dateString = $workdays[$j].ToString("ddd, dd MMM")

# Use integer division to determine the week
        $weekIndex = [math]::Floor($j / 5)

# Distribute shifts evenly by rotating through shifts for each employee per week
        $row[$dateString] = $shifts[($i + $weekIndex) % $shiftCount]
    }
    $dataRows += New-Object PSObject -Property $row
}

# Specific shift patterns for part-time employees
$adamShifts = @("7AM-12PM", "7AM-12PM", "7AM-12PM", "7AM-12PM", "7AM-12PM")
$markShifts = @("7AM-11AM", "7AM-11AM", $null, $null, "7AM-11AM")

# Skeletor schedule: 3 weeks of 8AM-4PM followed by 1 week of 10AM-6PM (F shift), repeating
$SkeletorShifts = @("8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "8AM-4PM", "10AM-6PM", "10AM-6PM", "10AM-6PM", "10AM-6PM", "10AM-6PM")
$SkeletorShiftCount = $SkeletorShifts.Count

# Add Adam's schedule
$row = [ordered]@{ Employee = "Adam" }
for ($i = 0; $i -lt $workdays.Count; $i++) {
    $dateString = $workdays[$i].ToString("ddd, dd MMM")
    $row[$dateString] = if ($i % 5 -lt $adamShifts.Count) { $adamShifts[$i % 5] } else { $null }
}
$dataRows += New-Object PSObject -Property $row

# Add Mark's schedule
$row = [ordered]@{ Employee = "Mark" }
for ($i = 0; $i -lt $workdays.Count; $i++) {
    $dateString = $workdays[$i].ToString("ddd, dd MMM")
    $row[$dateString] = if ($i % 5 -lt $markShifts.Count) { $markShifts[$i % 5] } else { $null }
}
$dataRows += New-Object PSObject -Property $row

# Add Skeletor's schedule
$row = [ordered]@{ Employee = "Skeletor" }
for ($i = 0; $i -lt $workdays.Count; $i++) {
    $dateString = $workdays[$i].ToString("ddd, dd MMM")
    $row[$dateString] = $SkeletorShifts[$i % $SkeletorShiftCount]
}
$dataRows += New-Object PSObject -Property $row

# Combine header and data into a single collection
$combined = $dataRows | Sort-Object Employee

# Specify the path where the Excel file will be saved
$excelFilePath = "Rota.xlsx"

# Ensure the Excel file is not open before trying to save it
if (Test-Path $excelFilePath) {
    try {
        # Attempt to remove the file if it's open
        Remove-Item $excelFilePath -Force -ErrorAction Stop
    }
    catch {
        Write-Host "The file is currently open or locked. Please close the file and try again." -ForegroundColor Red
        exit
    }
}

# Export the rota to an Excel file

try {
    $combined | Export-Excel -Path $excelFilePath -WorkSheetname "Rota" -AutoSize -BoldTopRow

# Load the Excel file
    $excelPackage = Open-ExcelPackage -Path $excelFilePath
    $worksheet = $excelPackage.Workbook.Worksheets["Rota"]

# Define shift colors
  $shiftColors = @{
        "7AM-3PM"  = "LightGreen"
        "8AM-4PM"  = "LightYellow"
        "9AM-5PM"  = "LightCoral"
        "10AM-6PM" = "LightBlue"
        "7AM-12PM" = "LightCyan"
        "7AM-11AM" = "LightPink"
    }

# Define color for empty cells
    $emptyCellColor = "LightGray"

# Apply color formatting to cells based on shift type
    for ($col = 2; $col -le $worksheet.Dimension.End.Column; $col++) {
        for ($row = 2; $row -le $worksheet.Dimension.End.Row; $row++) {
            $cellValue = $worksheet.Cells[$row, $col].Text
            if ($shiftColors.ContainsKey($cellValue)) {
                $worksheet.Cells[$row, $col].Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
                $worksheet.Cells[$row, $col].Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromName($shiftColors[$cellValue]))
            }
            elseif ($cellValue -eq "" -or $cellValue -eq $null) {
                $worksheet.Cells[$row, $col].Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
                $worksheet.Cells[$row, $col].Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromName($emptyCellColor))
            }
        }
    }

# Add a key below the data

    $startRowForKey = $worksheet.Dimension.End.Row + 2
    $worksheet.Cells["A$startRowForKey"].Value = "Shift Key:"
    $worksheet.Cells["A$startRowForKey"].Style.Font.Bold = $true
    $row = $startRowForKey + 1
    foreach ($shift in $shiftColors.Keys) {
        $worksheet.Cells["A$row"].Value = $shift
        $worksheet.Cells["B$row"].Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid  $worksheet.Cells["B$row"].Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromName($shiftColors[$shift]))
        $worksheet.Cells["B$row"].Value = $shiftColors[$shift]
        $row++
    }

# Add a color key for empty cells
    $worksheet.Cells["A$row"].Value = "No Shift"
    $worksheet.Cells["B$row"].Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid
$worksheet.Cells["B$row"].Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::FromName($emptyCellColor))
    $worksheet.Cells["B$row"].Value = $emptyCellColor

# Save the changes to the Excel file
    $excelPackage.Save()
    Write-Host "Rota has been created, excluding weekends, color-coded with a key, and saved to $excelFilePath" -ForegroundColor Green
}
catch {
    Write-Host "Failed to save the Excel file. Please make sure it is not open and you have write permissions to the directory." -ForegroundColor Red
}

Then you wish to then e-mail this out to people the you can use this script to send the email which I have used before on other blog posts:

Script : Rota-Mailer.ps1

Obviously update the variables as required and the you need the "email.html" which will be the main content of the email:

# Define Variables

$fromaddress = "rota@croucher.cloud"

$toaddresses = @(

    "lee@croucher.cloud") 

$Subject = "Rota : Servie Desk (Weekly)"

$body = Get-Content "email.html"

$attachments = @("Rota.xlsx")

$smtpserver = "smtp.bear.local"

# Function to check if file is 0 bytes

function Is-FileZeroBytes($filePath) {

    $fileInfo = Get-Item $filePath

    return $fileInfo.Length -eq 0

}

# Check if any attachments are 0 bytes

$sendEmail = $true

foreach ($attachment in $attachments) {

    if (Is-FileZeroBytes $attachment) {

        $sendEmail = $false

        break

    }

}

# Send email only if no attachments are 0 bytes

if ($sendEmail) {

    # Create the MailMessage object

    $message = New-Object System.Net.Mail.MailMessage

    $message.From = $fromaddress

    $message.IsBodyHtml = $True

    $message.Subject = $Subject

    $message.Body = $body

    # Add each attachment in the attachments array

    foreach ($attachment in $attachments) {

        $attach = New-Object Net.Mail.Attachment($attachment)

        $message.Attachments.Add($attach)

    }

    # Add each address in the toaddresses array to the To field

    foreach ($address in $toaddresses) {

        $message.To.Add($address)

    }

    # Send the email

    $smtp = New-Object Net.Mail.SmtpClient($smtpserver)

    $smtp.Send($message)

}

The mail Delivered to your Inbox

This is then how the email will look when it lands in your inbox depending on what you choose, I went for a wavy hand then some simple text.