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" |
