Automated Excel phonebook form AD data

PowerShell Help
1

Hi, im currently working on making list of users and their phone numbers in excel, data is taken from Active Directory so there is no problem, but i approached some problems with generating description for my document, i need to put logo, document name, and description on top of the document.

Import-Module ImportExcel

#Variable with route of file saved.
$ExportFolder = "C:\phonebook.xlsx"

#Counts ammount of records
$recordsCount = (Get-ADUser -Filter {(Company -like '*Company*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={$_.telephoneNumber + "`t"}} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -gt 6 }).Count

#Uses ammount of records to cut it in half to create 2 tables later it gives back only intigers.
$columnsper2 = [Math]::Ceiling($recordsCount/2)

#If file exist already it deletes, it helps with errors that append while overwriting file.
if (Test-Path $ExportFolder) {
    Remove-Item $ExportFolder
}

#Gets users for first column
Get-ADUser -Filter {(Company -like '*Company*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={$_.telephoneNumber + "`t"}} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -gt 6 } |
    Sort-Object Name |
    Select-Object -First $columnsper2|
    Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 1 -AutoSize -TableStyle Medium6

#Gets users for second column
Get-ADUser -Filter {(Company -like '*Company*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={$_.telephoneNumber + "`t"}} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -gt 6 } |
    Sort-Object Name |
    Select-Object -Skip $columnsper2|
    Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 5 -AutoSize -TableStyle Medium6

This is code that i made so far, my goal is to put Company name on first row, some informations and logo.

I would really appreciate also if someone tell me if im doing something wrong with current code, its my first touch with powershell, as well my first IT job so im trying my best but i probably could do some silly mistakes :slight_smile:

2

Marcin,
Welcome to the forum. :wave:t4:

I actually don’t know if it’s possible to add an image but to add a title is pretty straight forward. You simply add the parameter -Title. :man_shrugging:t4:

$OutputFile = 'C:\phonebook.xlsx'

$AllADUsersList = 
Get-ADUser -Filter { (Company -like '*Company*') -and (Enabled -eq $true) } -Properties Description, telephoneNumber |
Where-Object { $null -ne $_.telephoneNumber -and $_.telephoneNumber.Length -gt 6 } |
Select-Object -Property Name, Description, @{Name = 'telephoneNumber'; Expression = { $_.telephoneNumber + "`t" } } |
Sort-Object -Property Name

$recordsCount = $AllADUsersList.Count
$columnsper2 = [Math]::Ceiling($recordsCount / 2)

if (Test-Path $ExportFolder) {
    Remove-Item $ExportFolder
}

$AllADUsersList |
Select-Object -First $columnsper2 |
Export-Excel -Path $OutputFile -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 1 -AutoSize -TableStyle Medium6 -Title 'CompanyName and some Information'


$AllADUsersList |
Select-Object -Skip $columnsper2 |
Export-Excel -Path $OutputFile -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 5 -AutoSize -TableStyle Medium6 -Title 'CompanyName and some Information'

Your code’s been a little repetitive. You queried your AD 3 times for the same information. So I’ve tweaked it a little.

I am curious - may I ask why you want to have the table in such an unusual way?

Live long and prosper! :vulcan_salute:t3:

3

Hi!, I actually spend my week looking for solution and i think i pushed it even further!

About tables, I just want data of users to cover whole screen when you open the file, (that’s one of requirements for my work) and I’m not really sure I’m doing it correct way, those two tables don’t work well together to be honest and I’m still looking for solution :frowning:

However thank you for pointing out I have been doing it repetitive, Im going to fix it with that idea You gave me ^^, I even managed to add company logo by COM Excel Object ^^

This code is a little bit messy and im going to clearly have to use your solution but its like this for now :slight_smile:

PS: I had to do some crazy things with filtering of numbers, there was no strict telephoneNumber fill policy, so i had some work with it to display it fancy :smiley: And its only used for first column yet.

Import-Module ImportExcel

#Variable with route of file saved.
$ExportFolder = "C:\Title1.xlsx"
$imgPath = "C:\Logo\smaller.png"

#Counts ammount of records
$recordsCount = (Get-ADUser -Filter {(Company -like '*COMPANY*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={$_.telephoneNumber + "`t"}} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -gt 6 }).Count

#Uses ammount of records to cut it in half to create 2 tables later it gives back only intigers.
$columnsper2 = [Math]::Ceiling($recordsCount/2)

#If file exist already it deletes, it helps with errors that append while overwriting file.
if (Test-Path $ExportFolder) {
    Remove-Item $ExportFolder
}

#Gets users for first column
Get-ADUser -Filter {(Company -like '*COMPANY*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={
        if ($_.telephoneNumber.StartsWith('+48')) {
            $_.telephoneNumber = $_.telephoneNumber.Substring(3).Replace(' ','')
        }
        if ($_.telephoneNumber -match '^(\d{3})(\d{3})(\d{3})$') {
            $_.telephoneNumber = $matches[1] + ' ' + $matches[2] + ' ' + $matches[3]
        }
        if ($_.telephoneNumber -match '^(\d{9})$') {
            $_.telephoneNumber = $_.telephoneNumber.Insert(3, ' ').Insert(7, ' ')
        }
        $_.telephoneNumber
    }} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -eq 11 } |
    Sort-Object Name |
    Select-Object -First $columnsper2 |
    Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 1 -AutoSize -TableStyle Medium6 -NumberFormat '### ### ###'


#Gets users for second column
Get-ADUser -Filter {(Company -like '*COMPANY*') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object Name, Description, @{Name='telephoneNumber';Expression={$_.telephoneNumber + "`t"}} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -gt 6 } |
    Sort-Object Name |
    Select-Object -Skip $columnsper2|
    Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 5 -AutoSize -TableStyle Medium6



#Using COM Excel
$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$excel.Workbooks.Open($ExportFolder)

#Accesing particular cells
#Left - Title
$leftTitle = $excel.Range("A1","B1")
$leftTitle.Merge()
$excel.Cells.Item(1,1) = "Lista telefoniczna - COMPANY"
$excel.Rows.Item(1).RowHeight = 35
$leftTitle.HorizontalAlignment = -4108 #center align
$leftTitle.VerticalAlignment = -4108 #center align
$leftTitle.Font.Size = 16
$leftTitle.Font.Bold = $true
$leftTitle.Font.Color = 0xDD0000

#Right - Title
$rightTitle = $excel.Range("F1","G1")
$rightTitle.Merge()

#Adding image
$shapes = $excel.ActiveSheet.Shapes
$shapes.AddPicture($imgPath, $false, $true, 920, 6, -1, -1)

#Workbook Save
$excel.Save()
4

Great to hear that. :+1:t4:

May I ask why? What purpose is that supposed to serve?

Could you give an example? What exactly do you want to achieve?

Another idea I had was to use a kind of template. You could prepare an Excel sheet with the desired layout including the title and logo and everything you want and add the data you get qith your query to that template.

1 Like
5

Hi, i wasn’t able to work on it since last week but i go back to it yesterday and made a little changes :slight_smile:

May I ask why? What purpose is that supposed to serve?

well, I want 90% screen covered with usefull informations, if I make only one table, there would be informations on left side but empty space on right side so i decided to make two tables, half records on first and another half on second one.

Could you give an example? What exactly do you want to achieve?

I want to create an Excel sheet with the names, groups, and telephone numbers of workers that will be refreshed every day, so there is no need to manually add each person to the list. The data will be pulled from ADUsers, so all the necessary information can be easily obtained.

This is my actuall progress with the thing :slight_smile:

Import-Module ImportExcel

#Variable with route of file saved.
$ExportFolder = "C:\Sheet.xlsx"
$imgPath = "C:\LOGO-smaller.png"

#Gets users
$AllADUsersList = Get-ADUser -Filter {(Company -like '*NAME') -and (Enabled -eq $true)} -Properties Description, telephoneNumber |
    Select-Object -Property Name, Description, @{Name='telephoneNumber';Expression={
        if ($_.telephoneNumber.StartsWith('+48')) {
            $_.telephoneNumber = $_.telephoneNumber.Substring(3).Replace(' ','')
        }
        if ($_.telephoneNumber -match '^(\d{3})(\d{3})(\d{3})$') {
            $_.telephoneNumber = $matches[1] + ' ' + $matches[2] + ' ' + $matches[3]
        }
        if ($_.telephoneNumber -match '^(\d{9})$') {
            $_.telephoneNumber = $_.telephoneNumber.Insert(3, ' ').Insert(7, ' ')
        }
        $_.telephoneNumber
    }} |
    Where-Object { $_.telephoneNumber -ne $null -and $_.telephoneNumber.Length -eq 11 } |
    Sort-Object -Property Name

#Uses ammount of records to cut it in half to create 2 tables later it gives back only intigers.
$recordsCount = $AllADUsersList.Count
$columnsper2 = [Math]::Ceiling($recordsCount/2)

#If file exist already it deletes, it helps with errors that append while overwriting file.
if (Test-Path $ExportFolder) {
    Remove-Item $ExportFolder
}

#Write first column
$AllADUsersList |
Select-Object -First $columnsper2 |
Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 1 -AutoSize -TableStyle Medium6 -NumberFormat '### ### ###'

#Write second column
$AllADUsersList |
Select-Object -Skip $columnsper2 |
Export-Excel -Path "$ExportFolder" -FreezeTopRow -BoldTopRow -StartRow 2 -StartColumn 5 -AutoSize -TableStyle Medium6 -NumberFormat '### ### ###'

#Using COM Excel
$excel = New-Object -ComObject Excel.Application

$excel.Visible = $false

$workbook = $excel.Workbooks.Open($ExportFolder)

#Accesing particular cells
#Left - Title
$leftTitle = $excel.Range("A1","B1")
$leftTitle.Merge()
$excel.Cells.Item(1,1) = "Lista telefoniczna - COMPANY NAME"
$excel.Rows.Item(1).RowHeight = 35
$leftTitle.HorizontalAlignment = -4108 #center align
$leftTitle.VerticalAlignment = -4108 #center align
$leftTitle.Font.Size = 16
$leftTitle.Font.Bold = $true
$leftTitle.Font.Color = 0xDD0000

#Right - Title
#$rightTitle = $excel.Range("F1","G1")
#$rightTitle.Merge()

#Adding image
$shapes = $excel.ActiveSheet.Shapes
$shapes.AddPicture($imgPath, $false, $true, 920, 6, -1, -1)

##############################################################################
#Here i was planning to add some search bar on top, but it wasn't working
###############################################################################
# Save workbook without prompt
$excel.DisplayAlerts = $false
$workbook.SaveAs($ExportFolder)
$excel.DisplayAlerts = $true

$excel.Quit()
6

Hmmm … I guess I didn’t make clear what I meant. Is it just for display purposses? Or do you want to process these data with further steps.

If it’s just for display purposses you may consider other approaches. Something like the PowerShell equivalent for Format-Wide. Or an HTML report … :man_shrugging:t4:

OK. And what do you use this Excel sheet for? That’s what I meant. :wink:

Maybe you’re using the wrong tool for the job. :wink: Is that Excel sheet meant to be used interactively or just statically for display purposses? You may ask this question in an Excel related forum as well. There might be some Excel magic out there making your challenge a piece of cake. :slightly_smiling_face:

7

Hmmm … I guess I didn’t make clear what I meant. Is it just for display purposses? Or do you want to process these data with further steps.

If it’s just for display purposses you may consider other approaches. Something like the PowerShell equivalent for Format-Wide. Or an HTML report … :man_shrugging:t4:

Current list is created using Excel and displayed as a downloadable file on the company website. However, someone is currently manually editing it. The task I have been given is to create an automated list, and I have been suggested to use PowerShell to accomplish this. Therefore, I have been exploring the possibilities of PowerShell. :slight_smile:

I’m not sure how good the solution I chose is, but it has to be an easy-to-use file for most people in the office so they can easily access all the information. I picked the way of doing it as they suggested to me and just tried to make it useful. :blush:

8

I did not mean that PowerShell is the wrong tool. :wink:

That’s what I meant. What do they do with it? Do they only look at it? Will they try to search in it?

Why not creating an easily usable website with the needed information? You could still “feed” the desired information using PowerShell.