AD HOC Queries with Excel Output (Multiple Instances)

cls

 #import-module sqlps -DisableNameChecking

$xlsObj = New-Object -ComObject Excel.Application;
 
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

[Int] $intRow = 1;

$content = Get-Content C:\DOCS\instancias.txt 

foreach ($SQLServer in $content)
{

     $xlsSh.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $xlsSh.Cells.Item($intRow,2) = $SQLServer
     $xlsSh.Cells.Item($intRow,1).Font.Bold = $True
     $xlsSh.Cells.Item($intRow,2).Font.Bold = $True
 $xlsSh.Cells.Item($intRow,1).Interior.ColorIndex = 23
 $xlsSh.Cells.Item($intRow,2).Interior.ColorIndex = 23

     $intRow++
    
$Database = 'master';
$SqlQuery = @'
select name as 'nombre', create_date as 'Fecha de Creacion', state_desc as 'Estado', compatibility_level as 'Compatibilidad', collation_name as 'Colación'  from sys.databases 
where name not in (N'master', N'tempdb', N'model', N'msdb', N'gestdb')
'@
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True";

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection; 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;

$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSetTable = $DataSet.Tables["Table"];
 
## - Cabecera:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
 
## - Columnas de Cabecera:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item($intRow, $RowHeader).font.bold = $true;
$xlsSh.Cells.item($intRow, $RowHeader).Interior.ColorIndex = 15
$xlsSh.Cells.item($intRow, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};

$intRow++
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## - Convierte celda a texto
$xlsSh.Cells.NumberFormat = "@";
 
## - despliega columnas:
$xlsSh.Cells.Item($intRow, $colData) = $rec.$($Coln.ColumnName).ToString();

$ColData++;
};
$intRow++; $ColData = 1;