• RSS
  • Twitter
  • FaceBook

Exchange Server Forums

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

Power shell to Excel xls - not a csv

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [Microsoft Exchange 2007] >> Management >> Power shell to Excel xls - not a csv Page: [1]
Login
Message << Older Topic   Newer Topic >>
Power shell to Excel xls - not a csv - 23.Jun.2009 5:50:46 PM   
leandro2k3

 

Posts: 2
Joined: 23.Jun.2009
Status: offline
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,$b).font.bold = $true
$sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
$sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$sheet.cells.item(1,$b).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++
}
}
###
Post #: 1
RE: Power shell to Excel xls - not a csv - 24.Jun.2009 2:15:57 AM   
choppol

 

Posts: 720
Joined: 9.Feb.2003
From: sydney
Status: offline
Try this.

get-mailbox -server servername -resultsize:unlimited | get-mailboxstatistics | select-object DisplayName, {$_.TotalItemSize.Value.ToMB()},Database,StorageLimitStatus,ItemCount | export-csv "location of the csv"

(in reply to leandro2k3)
Post #: 2
RE: Power shell to Excel xls - not a csv - 24.Jun.2009 3:29:58 PM   
leandro2k3

 

Posts: 2
Joined: 23.Jun.2009
Status: offline
I found some examples in the Internet working with get-mailbox and get-mailboxstatistics, but I do not obtain to way to select data data from get-mailbox and get-mailboxstatistics and after save them in excel XLS COLUNS. I download the PowerGUI to try to debug, but i still continue without a vision to record in excel.

(in reply to choppol)
Post #: 3
RE: Power shell to Excel xls - not a csv - 25.Jun.2009 7:26:16 PM   
choppol

 

Posts: 720
Joined: 9.Feb.2003
From: sydney
Status: offline
Try this by saving it as a .ps1 file. I've been using this command for the past 6 months. Does a very good job.

(in reply to leandro2k3)
Post #: 4

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [Microsoft Exchange 2007] >> Management >> Power shell to Excel xls - not a csv Page: [1]
Jump to:

New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Follow TechGenix on Twitter