1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
| Import-Module activedirectory
ADD-content -path "$pwd\debug.log" -value "$Date Date = $(Get-Date -UFormat "%Y%m%d")"
Function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
Function ConvertCSV-ToExcel
{
[CmdletBinding()]
Param
(
[parameter(Mandatory=$False,Position=1)][string]$inputfile,
[parameter(Mandatory=$False,Position=1)][string]$output,
[parameter(Mandatory=$False,Position=1)][string]$sheet
)
#Create Excel Com Object
$excel = new-object -com excel.application
#Show Excel application
$excel.Visible = $False
#Add workbook
if ($sheet -eq "1"){
$outfilename = "E:\Script\Script_annuaire\dlf.xlsx"
}
else {
$outfilename = "E:\Script\Script_annuaire\annuaire dlf(temp).xlsx"
}
$workbook = $excel.workbooks.open("$outfilename")
#Use the first worksheet in the workbook
if ($sheet -eq "1"){ $worksheet1 = $workbook.worksheets.Item(1)}
else {$worksheet1 = $workbook.worksheets.Item(2)}
#Remove other worksheets that are not needed
#$workbook.worksheets.Item(2).delete()
#$workbook.worksheets.Item(2).delete()
#Start row and column
$r = 2
$c = 1
#Begin working through the CSV
$file = (GC $inputfile)
ForEach ($f in $file) {
$arr = ($f).split(';')
ForEach ($a in $arr) {
$worksheet1.Cells.Item($r,$c) = "$(($a).replace('"',''))"
$c++
}
$c = 1
$r++
}
#Select all used cells
$range = $worksheet1.UsedRange
#Autofit the columns
$range.EntireColumn.Autofit() | out-null
#Save spreadsheet
$excel.displayalerts = $false
if ($sheet -eq "1"){
$workbook.saveas("E:\Script\Script_annuaire\annuaire dlf(temp).xlsx")
$excel.displayalerts = $true
#ADD-content -path "$pwd\debug.log" -value "Enregistrement du fichier annuaire dlf(temp).xlsx"
}
else {
$workbook.saveas("E:\Script\Script_annuaire\annuaire dlf.xlsx")
$excel.displayalerts = $true
#ADD-content -path "$pwd\debug.log" -value "en registrement du fichier annuaire DLF.xlsx"
}
#Close Excel
$excel.quit()
#Release processes for Excel
$a = Release-Ref($range)
$a = Release-Ref($worksheet1)
$a = Release-Ref($workbook)
}
Get-AdUser -Filter {OfficePhone -like "*" -AND Company -like "DLF"} -Properties givenName,sn,OfficePhone,HomePhone,description,Department,Company,Mobilephone |
select-object @{name="Service";expression={$_.Department}}, @{name="Nom";expression={$_.sn+" "+$_.givenName}}, @{name="N° interne";expression={$_.OfficePhone}},@{name="Ligne SDA";expression={$_.HomePhone}}, @{name="N° Mobile";expression={$_.MobilePhone}} |
Sort-Object Service, Nom |
Export-Csv -path "E:\Script\Script_annuaire\annuaire dlf.csv" -Delimiter ";" -encoding UTF8 -notypeinformation
ConvertCSV-ToExcel -inputfile "E:\Script\Script_annuaire\annuaire dlf.csv" -output "E:\Script\Script_annuaire\dlf.xlsx" -sheet "1"
Remove-Item "E:\Script\Script_annuaire\annuaire dlf.csv"
Get-AdUser -Filter {OfficePhone -like "*" -AND Company -like "DLF"} -Properties givenName,sn,OfficePhone,HomePhone,description,Department,Company,Mobilephone |
select-object @{name="Service";expression={$_.Department}}, @{name="Nom";expression={$_.sn+" "+$_.givenName}}, @{name="N° interne";expression={$_.OfficePhone}},@{name="Ligne SDA";expression={$_.HomePhone}}, @{name="N° Mobile";expression={$_.MobilePhone}} |
Sort-Object Nom |
Export-Csv -path "E:\Script\Script_annuaire\annuaire dlf.csv" -Delimiter ";" -encoding UTF8 -notypeinformation
ConvertCSV-ToExcel -inputfile "E:\Script\Script_annuaire\annuaire dlf.csv" -output "E:\Script\Script_annuaire\annuaire dlf(temp).xlsx" -sheet "2"
Remove-Item "E:\Script\Script_annuaire\annuaire dlf.csv"
Remove-Item "E:\Script\Script_annuaire\annuaire dlf(temp).xlsx" |
Partager