Jump to content
Sign in to follow this  
leandro2k3

Write powershell data in excel XLS - not a CSV

Recommended Posts

leandro2k3

I am trying to record given data get on powershell in excel, but I did not discover a way to record the data, somebody I can help me?

####

$excel = new-object -comobject excel.application

$excel.visible = $true

$workbook = $excel.workbooks.add()

$workbook.workSheets.item(3).delete()

$workbook.WorkSheets.item(2).delete()

$workbook.WorkSheets.item(1).Name = "DBUsers"

$sheet = $workbook.WorkSheets.Item("DBUsers")

$x = 2

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]

$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]

$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]

$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

For($b = 1 ; $b -le 5 ; $b++)

{

$sheet.cells.item(1,$ :D .font.bold = $true

$sheet.cells.item(1,$ :D .borders.LineStyle = $lineStyle::xlDashDot

$sheet.cells.item(1,$ ;) .borders.ColorIndex = $colorIndex::xlColorIndexAutomatic

$sheet.cells.item(1,$ :P .borders.weight = $borderWeight::xlMedium

}

$sheet.cells.item(1,1) = "Database"

$sheet.cells.item(1,2) = "DisplayName"

$sheet.cells.item(1,3) = "StorageLimitStatus"

$sheet.cells.item(1,4) = "ItemCount"

$sheet.cells.item(1,5) = "TotalItemSize"

############excel write ??

$Database = @{n="Database";e={ $stats.database }}

$DisplayName = @{n="DisplayName";e={ $stats.Displayname }}

$StorageLimitStatus = @{n="StorageLimitStatus";e={ $stats.StorageLimitStatus }}

$ItemCount = @{n="ItemCount";e={ $stats.ItemCount }}

$TotalItemSize = @{n="TotalItemSize(MB)";e={ $stats.TotalItemSize.Value.ToMB() }}

$Servers=get-content c:\servers.txt

Foreach($server in $servers) {

$stats = get-mailbox -server $server | foreach {

$stats = get-mailboxstatistics $_

$_ | select alias, *SMTP*,$Database,$DisplayName,$StorageLimitStatus,$ItemCount,$TotalItemSize

$sheet.cells.item($x,1) = $database

$sheet.cells.item($x,2) = $Displayname

$sheet.cells.item($x,3) = $StorageLimitStatus

$sheet.cells.item($x,4) = $ItemCount

$sheet.cells.item($x,5) = $TotalItemSize.Value.ToMB

$x++

}

}

###

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.