Excel ile ilgili bazı işlemleri içeren bir script yazmıştım bir kısmını kapatacağım isimler burada kullanmaya uygun olmadığından.

##################################

# um mit diesem Skript zu arbeiten, sollten zunächst alle CSV-Dateien in den gleichen Ordner mit diesem Skript kopiert werden.
# auch die Reporting-Excel-Datei (HEX-Mailboxes_final.xlsx) vom Vormonat sollte ebenfalls hier sein.
# Hinweis: muss überprüfen, ob keine der CVS-Dateien 1kb Daten enthält (als wäre es eine falsche heruntergeladene Datei).
# Nachdem das Skript ausgeführt wurde, können CSV-Dateien gelöscht werden.
##################################
#
#19.07.2019
#
#################################
### Erst Excel Aktualisierung
#################################
$pwdd = (Get-Location).Path
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$pathh = $pwdd+"\HEX-Mailboxes_final.xlsx"
$Workbook = $excel.Workbooks.open($pathh)
$Worksheet = $Workbook.WorkSheets.item(“Tabelle1”)
$worksheet.activate() 
$range = $WorkSheet.Range(“B2:D5”)
$range.copy()
$Range2 = $Worksheet.Range(“I2:K5”)
$Worksheet.Paste($range2) 
########################################
### Counts in Variables
#######################################
$CSVs = Get-ChildItem -Recurse |where {$_.Extension -eq ".csv"} | % { $_.FullName }
$Dog2010basic = $CSVs|Select-String "dohex2010-results-ex2010-basic"
$Dog2010Standard = $CSVs|Select-String "dohex2010-results-ex2010-standard"
$dog2013basic = $CSVs|Select-String "basic_sal"
$dog2013standard = $CSVs | Select-String "standard_sal_"
$dog2013ent = $CSVs|Select-String "enterprise_sal"
$Simple2010Stand = $CSVs | Select-String "Simple-results-ex2010-standard-sal"
$Simple2010Basic = $CSVs | Select-String "Simple-results-ex2010-basic-sal"
$simple2013all = $CSVs | Select-String "results-ex2013"
###############
$Dog2010basicCsv = Import-Csv $Dog2010basic -Delimiter ";"
$Dog2010basicCount = ($Dog2010basicCsv| where { $_.whencreated -ne "" } |Measure-Object).count
###############
$Dog2010StandardCsv = Import-Csv $Dog2010Standard -Delimiter ";"
$Dog2010StandardCount = ($Dog2010StandardCsv| where { $_.whencreated -ne "" } |Measure-Object).count
###############
$dog2013basicCSV = Import-Csv $dog2013basic -Delimiter ","
$dog2013basicCount = ($dog2013basicCSV | where { $_.whencreated -ne "" } |Measure-Object).count
###############
$dog2013standardCsv = Import-csv $dog2013standard -Delimiter ","
$dog2013standardCount = ($dog2013standardCsv | where { $_.whencreated -ne "" } |Measure-Object).count
###############
$dog2013entCsv = Import-csv $dog2013ent -Delimiter ","
$dog2013entCount = ($dog2013entCsv  | where { $_.whencreated -ne "" } |Measure-Object).count
###############
$Simple2010StandCsv = Import-Csv $Simple2010Stand -Delimiter ";"
$Simple2010StandCount = ($Simple2010StandCsv | where { $_.whencreated -ne "" } |Measure-Object).count
###############
$Simple2010BasicCsv = Import-Csv $Simple2010Basic -Delimiter ";"
$Simple2010BasicCount = ($Simple2010BasicCsv | where { $_.whencreated -ne "" } |Measure-Object).count
###############
$simple2013CSv = Import-Csv $simple2013all -Delimiter ";"
$simple2013BasicCounter  = ($simple2013CSv | where { $_.whencreated -ne "" -and $_.MapiEnabled -eq "False" -and $_.IsResource -eq "False" } |Measure-Object).count
$simple2013StandCounter  = ($simple2013CSv | where { $_.whencreated -ne "" -and $_.MapiEnabled -eq "True"} |Measure-Object).count
####################################
### Variables in Excel-Cells
### item(Numbers,Letters (but used as number)
####################################
$worksheet.Cells.item(2,2)= $Simple2010BasicCount
$worksheet.Cells.item(2,3)= $Simple2010StandCount
$worksheet.Cells.item(2,4)= "0"
$worksheet.Cells.item(3,2)= $simple2013BasicCounter
$worksheet.Cells.item(3,3)= $simple2013StandCounter
$worksheet.Cells.item(3,4)= "0"
$worksheet.Cells.item(4,2)= $Dog2010basicCount
$worksheet.Cells.item(4,3)= $Dog2010StandardCount
$worksheet.Cells.item(4,4)= "0"
$worksheet.Cells.item(5,2)= $dog2013basicCount
$worksheet.Cells.item(5,3)= $dog2013standardCount
$worksheet.Cells.item(5,4)= $dog2013entCount
####################################
#### Excel save
####################################
$workbook.save()
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Şimdi öncelikle amacı ve ne olduğunu açıklayayım.

Exchange Server üzerinde her bir müşteri tipinin detaylarını bir skript ile CSV dosyalarına atıyoruz. Takiben yine aynı skript bunları maıl atıyor. Bizde birden çok platform ve birden çok müşteri hizmeti var. Her platformda bunlar koşuyor ve mail olarak bizde toplanıyor. Gelen tüm maillerde ki CSV eklentileri bir folderda topluyoruz. ve Bu scripti orada koşuyoruz.

Şimdi blokları inceleyelim

Excelde Copy Paste

$worksheet.activate() 
$range = $WorkSheet.Range(“B2:D5”)
$range.copy()
$Range2 = $Worksheet.Range(“I2:K5”)
$Worksheet.Paste($range2) 

Burada ki en önemli kısım excelde sol üst sağ alt köşe tanımlanarak copy paste ediliyor.

CVS ye get-content

Get-conten ama biraz farklı ; her bir tip elemanı bir object attribute olarak almasını sağlıyoruz. Örneğin whencreated diye bir değer var her satırda.

$dog2013entCsv = Import-csv $dog2013ent -Delimiter ","
$dog2013entCount = ($dog2013entCsv  | where { $_.whencreated -ne "" } |Measure-Object).count

$dog2013entCsv|fl yaptığımızde detayları ve seçilebilir attribleri görüyoruz. whencreted boş olmayanları sayıyoruz.

 ($dog2013entCsv  | where { $_.whencreated -ne "" } |Measure-Object).count

Bu sayıyı bir değişkene atadıktan sonra excelde bir hücreye atıyoruz. Burada önemli nokta ise normalde excelde bir hücre B3 gibidir oysa bu komutta (Nedense !!!) biraz ters işlemişler önce 3B sonrada B yi de rakam olarak yazmışlar yani 2 kısaca 3,2

item(Numbers,Letters (but used as number)

$worksheet.Cells.item(2,3)= $Simple2010StandCount