• Home
  • /
  • 2023
  • /
  • Create Line chart with PowerShell

Create Line chart with PowerShell

My ProductOwner gave me an assignment: “Twice a day I receive an email with an attachment. Each attachement contains an excel file with open sessions of Citrix; at 10:00 and at 14:00. Managed devices are on the first tab; the Unmanaged devices on the second. Can you gerenate an line-chart for me so I can see the trend in days?”

I said to him “Let me do my magic”. So first I had to import all files in my PowerShell script.

$locationfiles = "C:\Temp\Files"
$allFiles = Get-ChildItems -Path $locationfiles | Sort Name

The date of the measurement is in the title of the file. The title look like “Session_Citrix_Daily-2023-07-17-10.xlsx”. First I had to cast the title to a datetime object.

# TimeStamp
 [string]$timeStamp = $file.Name -replace "Sessions_Citrix_Daily-","" -replace ".xlsx",""

# TimeStampTime
[string]$timeStampTime = $timeStamp.Split("-")[-1]
[string]$timeStampTimeNotation = $timeStampTime + ':00'

# TimeStampDate
[datetime]$timeStampDate = Get-Date -Date ($timeStamp.Substring(0,$timeStamp.LastIndexOf('-')))

After that load the first Excel file:

# Managed Devices Tab
[object]$managedDevices = Import-Excel $file.FullName -WorksheetName 'Managed'

# Unmanaged Devices Tab
[object]$unmanagedDevices = Import-Excel $file.FullName -WorksheetName 'Unmanaged'

And than I had to think hard, because 1 day is in two files; one for 10:00 and one for 14:00. How to put those in one object. After trail and error, I came with the following solution: Create a PowerShell CustomObject with only the date in it. Check the file’s if information is in it and than add this to the PSCustomObject:

# Add to Custom Object
if ($sessions.Datum -notcontains $timeStampDate) {
   $sessions += [PSCustomObject] @{ `
      "Datum" = $timeStampDate; `
   }
}

# Add a new property to the object
if ($timeStampTimeNotation -eq '10:00') {
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 10:00' -Type NoteProperty -value $managedDevices.count
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 10:00' -Type NoteProperty -value $unmanagedDevices.count
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 10:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
} else {
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 14:00' -Type NoteProperty -value $managedDevices.count
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 14:00' -Type NoteProperty -value $unmanagedDevices.count
   $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 14:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
}

When you loop through all files, you get one PSCustomObject with all information in it. This is the complet loop:

# Loop through each file 
Write-Host "`tLoop through all files"
$sessions = @()
foreach($file in $allFiles) {
    Write-Host "`t`tRead file [$($file.Name)]"

    # TimeStamp
    [string]$timeStamp = $file.Name -replace "Citrix_Sessions_Daily-","" -replace ".xlsx",""

    # TimeStampTime
    [string]$timeStampTime = $timeStamp.Split("-")[-1]
    [string]$timeStampTimeNotation = $timeStampTime + ':00'

    # TimeStampDate
    [datetime]$timeStampDate = Get-Date -Date ($timeStamp.Substring(0,$timeStamp.LastIndexOf('-')))
    
    # Managed Divices Tab
    [object]$managedDevices = Import-Excel $file.FullName -WorksheetName 'Managed'

    # Unmanaged Divices Tab
    [object]$unmanagedDevices = Import-Excel $file.FullName -WorksheetName 'Unmanaged'

    # Add to Custom Object
    if ($sessions.Datum -notcontains $timeStampDate) {
        $sessions += [PSCustomObject] @{ `
            "Datum" = $timeStampDate; 
        }
    }

    # Add a new property to the object
    if ($timeStampTimeNotation -eq '10:00') {
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 10:00' -Type NoteProperty -value $managedDevices.count
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 10:00' -Type NoteProperty -value $unmanagedDevices.count
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 10:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
    } else {
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Managed Devices 14:00' -Type NoteProperty -value $managedDevices.count
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Aantal Unmanaged Devices 14:00' -Type NoteProperty -value $unmanagedDevices.count
        $sessions | Where-Object { $_.Datum -eq $timeStampDate } | Add-Member -Name 'Totaal aantal sessies 14:00' -Type NoteProperty -value ($managedDevices.count + $unmanagedDevices.count)
    }
}

Write-Host "`t...done"

And now I had to create a line chart of this information. I found this blog that helpt me “https://stackoverflow.com/questions/25331407/powershell-to-create-line-chart-from-excel“.

#region ----------------------------------------- Create Line Chart ------------------------------

   $scvstatus = Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel' -PassThru
   $xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
   $xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
   $xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
   $xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
   $xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

   $xl = new-object -ComObject Excel.Application
   $wb = $xl.workbooks.add()
   $ws = $wb.activesheet
   $xl.Visible = $true

   # Populate data onto worksheet
   $sessions |ConvertTo-CSV -NoTypeInformation -Delimiter "`t"| c:\windows\system32\clip.exe
   $ws.Range("A1").Select | Out-Null
   $ws.paste()
   $ws.UsedRange.Columns.item(1).numberformat = "dddd, mmm dd, yyyy"
   $ws.UsedRange.Columns.AutoFit() |Out-Null

   Create Chart
   $chart=$ws.Shapes.AddChart().Chart
   $chart.chartType=$xlChart::xlLine
   $ws.shapes.item("Chart 1").top=40
    
#endregion -------------------------------------- Create Line Chart ------------------------------
0
0

Leave a Reply