I need to be able to create an Excel document with multiple tabs for a dataset with multiple results\data tables. I have a stored procedure with 3 selects in them, fairly simple ones pulling data from the sys tables in SQL Server.
Below is my script where I am assuming there will be 5 tables, if there are more then it will be missed and if there are less and then the other tabs will be empty.
What's the best way to loop through the dataset and create the Excel document?
$SQLServer = '.\SQLEXPRESS';
$Database = 'AdventureWorks';
$sqlCommand = "SELECT 'Tables' AS DataSetName, * FROM sys.tables
SELECT 'Columns' AS DataSetName, * FROM sys.columns";
$connectionString = "Server=$SQLServer;Database=$Database;Integrated
Security=true"
$connection = New-Object
System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.Sqlclient.Sqlcommand($sqlCommand,
$connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) > null
$connection.Close()
foreach($table in $dataset.Tables)
{
Write-Host $table.TableName
if ($($table.TableName) -eq 'table'){$Table1 = $table}
if ($($table.TableName) -eq 'table1'){$Table2 = $table}
if ($($table.TableName) -eq 'table2'){$Table3 = $table}
if ($($table.TableName) -eq 'table3'){$Table4 = $table}
if ($($table.TableName) -eq 'table4'){$Table5 = $table}
}
$DataToGather = @{
table = {$Table1}
table1 = {$Table2}
table2 = {$Table3}
table3 = {$Table4}
table4 = {$Table5}
}
Export-MultipleExcelSheets -Show -AutoSize 'C:\Temp\Test.xlsx' $DataToGather
$DataToGather.Clear()