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.
Eldo issacAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
A .CSV is only a type of text file - it doesn't have sheets.  For that you would need something like an Excel file.  It is possible to create an Excel file via COM object, but the code is much more involved.  However, it's pretty simple to generate multiple .CSVs based on the server name.
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 | Export-CSV "$svr.csv" -notype

}

Open in new window

Eldo issacAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That would then be something like this:
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
  $ws = $wsTemplate.Copy($wsTemplate)
  $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

You see, this in fact adds much more code, as said by footech. It saves the final workbook as "ServerStats.xlsx" in the current folder.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Eldo issacAuthor Commented:
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"Batchelor", Developer and EE Topic AdvisorCommented:
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 issacAuthor Commented:
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"Batchelor", Developer and EE Topic AdvisorCommented:
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

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
BTW, should be better if you replace the backslash with a different character, like "_", instead of only using the server name. The latter leads to duplicates if you have more than one (queried) instance on a machine.
Eldo issacAuthor Commented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You are correct. I adapted the export function from another (more generic) script of mine, and missed that we are not working with user objects but data tables, so line 12 is wrong. Replace with
		$props = $_ | gm -MemberType Property

Open in new window

For the sheet name (line 52), I used
  $ws.Name = $svr.Replace('\', '_')

Open in new window

Eldo issacAuthor Commented:
Hello Qlemo,

Thanks a ton. It worked.
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.