asked on

Excel and Powershell Open Workbook with Multiple Sheets

Hello Guys,

I have written the following code for getting database status from all the servers:

cls
foreach ($svr in get-content "demo.txt")

{

  $con = "server=$svr;database=master;Integrated Security=sspi" 

  $cmd = "select state_desc as DB_STATUS,COUNT(name) as [TOTAL DB'S] from sys.databases group by state_desc"

  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) |out-null

  $svr

  $dt | Format-Table -autosize

}

Open in new window

Is it somehow possible to export the output into a csv file in such a way that, for each server a new sheet is created with the server name as of the sheet name and the output ($dt) .
Kindly help on this as this is urgent and i'm not able to figure out.

SOLUTION

footech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eldo issac

ASKER

Hello Footech,

Thanks for your comments. It is somehow possible to club all the csv's generated in the above code into a single excel workbook with all the csv's as the sheets in the excel workbook???

Also, if you can just give a way to create an excel with multiple sheets for each server, then I can try myself.

Thanks again

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eldo issac

ASKER

Hello Qlemo,

Thanks for your comment. I'm trying your code but getting the following errors:

Property 'Name' cannot be found on this object; make sure it exists and is settable.
At line:57 char:7
+ $ws. <<<< Name = $drive1
+ CategoryInfo : InvalidOperation: (Name:String) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At line:14 char:18
+ $ws.Cells.Item <<<< ($row, $col).Value2 = $_.Name
+ CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:15 char:18
+ $ws.Cells.Item <<<< ($row, $col++).Font.Bold = $true
+ CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:21 char:17
+ $ws.Cells.Item <<<< ($row, $col++).Value2 = $_.($prop.Name)
+ CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:26 char:36
+ $ws.usedRange.EntireColumn.AutoFit <<<< () | Out-Null
+ CategoryInfo : InvalidOperation: (AutoFit:String) [], RuntimeExceptio
n
+ FullyQualifiedErrorId : InvokeMethodOnNull

One more thing here is, I'm trying the following code to just generate simple work-sheets using the following code and its working perfectly fine:

cls
$USN = gc demo.txt

# create an instance of Excel
$xl = new-object -c excel.application -vb:0
$xl.visible = $true

[regex]$invChars = &{
$ofs = '|'
"$('?','*',':','/','\','[',']' | % {[regex]::escape($_)})"
}

# set the number of sheets in _next new_ workbook
$xl.sheetsInNewWorkbook = $USN.count
$wb1 = $xl.workbooks.add()
$USN | % {$i = 1} {
$sh = $wb1.sheets.item($i++)
# you got the sheet, rename it and do rest of processing in the loop
$sh.name = $_ -replace $invChars
$sh.range('a1') | % {
$_.value2 = "The name of this sheet is $($sh.name)"
[void]$_.columns.autofit()
}
}

Open in new window

Can you please look into this and give your comments. Thanks again for your comments.

Qlemo

The first error message shows a command I did not use exactly that way. I suppose you have changed too much, so $ws is no WorkSheet anymore.

Eldo issac

ASKER

Hello Qlemo,

Actually, I just made the following edit :

$drive1 = $svr.replace("\", "_")
$ws.Name = $drive1

Open in new window

The change I made is because the input format in the txt file is:

SERVERNAME\SQLINSTANCENAME

Can't create excel sheet name with\ in the name.
Also, maybe the error is because I'm using Powershell V2.0. Kindly help on this

Qlemo

Only a minor bug, thought the Worksheet.Copy returns the new sheet object, but it does not.

cls

function export-xlsSheet (<# [Excel.WorkSheet] #> $ws)
{
begin {
	$props = $null
	$row = 1
}
process {
	if (!$props) {
	  $col = 1
		$props = $_ | gm -MemberType NoteProperty
		$props | % {
			$ws.Cells.Item($row, $col).Value2 = $_.Name
			$ws.Cells.Item($row, $col++).Font.Bold = $true
		}
		$row++
	}
	$col = 1
	foreach ($prop in $props) {
		$ws.Cells.Item($row, $col++).Value2 = $_.($prop.Name)
	}
	$row++
}
end {
	$ws.usedRange.EntireColumn.AutoFit() | Out-Null
}
}


<# Excel initialization stuff #>
$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

#   Delete all but one work sheet
$excel.DisplayAlerts = $false
for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
$excel.DisplayAlerts = $true
$wsTemplate = $wb.Worksheets.Item(1)

<# real stuff #>
foreach ($svr in get-content "demo.txt")
{
  $con = "server=$svr;database=master;Integrated Security=sspi" 
  $cmd = "select state_desc as DB_STATUS,COUNT(name) as [TOTAL DB'S] from sys.databases group by state_desc"
  $da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
  $dt = new-object System.Data.DataTable
  $da.fill($dt) |out-null
  $wsTemplate.Copy($wb.Worksheets.Item(1))   # new sheet as very first one
  $ws = $wb.Worksheets.Item(1)
  $ws.Name = $svr
  $dt | Export-xlsSheet($ws)
}


<# remove last sheet = empty Template, save and close Excel #>
$wsTemplate.Delete()

$wb.SaveAs('ServerStats.xlsx')
$excel.Quit()

Open in new window

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eldo issac

ASKER

Hello Qlemo,

Thanks for your comment. The thing is that I got the bug from your script and corrected the same. But now, the problem is nothing is getting populated in the excel sheet. The excel file is getting generated with the sheet name as the server name, but it's just complete blank.
No Output

Kindly look into this and give your suggestions

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eldo issac

ASKER

Hello Qlemo,

Thanks a ton. It worked.
Thank you very much