Generate Excel spreadsheet (xls) in SQL SERVER

Question

Good afternoon,

Could anyone help?

I need to generate an excel spreadsheet automatically by SQL server.

I have a table with N codes, each code should go to a tab on the file.

Ex: select code, name, value from Table where code = 'AA' - Go to a Tab with name AA

select code, name, value from Table where code = 'BB' - Go to a Tab with name BB

As I can have several codes, it would not be possible for that "save as".

Does anyone know how to generate an XLS this way?

Answer 1

Hi DanielLippi,

SSIS allows to do that.
You would need to check the 'old' SSIS forum for a solution.

SQL Server Integration Services

Answer 2

You can achieve this using SSIS. Please see below similar question below -

how-to-export-data-to-multiple-excel-sheet-from-sql-server-table-using-ssis

=========================================================
Please don't forget to "Accept Answer" and upvote if the response helped -- Vaibhav

Answer 3

Hi Daniel Lippi,

May I know how many groups do you want to divide the table into?

If there are only a few groups, please use Conditional Split Transformation in SSIS package.

Please refer to the following link and pictures:
SSIS Conditional Split Transformation overview.

Best Regards,
Mona

----------

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 4

We found powershell the easiest to use if that helps..then schedule with task scheduler

Write-Host –NoNewLine “Processing.................Do not close"
## - Get SQL Server Table data:
$SQLServer = 'server';
$Database = 'database';
$today = (get-date).ToString("dd-MM-yyyy")
$ExportFile = "\\zoom-nas\Shared_Documents\FC Folder\Despatch\Brexit Files\Landmark\Landmark "+$today+".xlsx"
##$SqlQuery = @'EXEC [zoomfs].[LandMarkGlobalExport]'@ ;

## - Connect to SQL Server using non-SMO class 'System.Data':
$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 = $("EXEC [scyhema].[LandMarkGlobalExport]");
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandTimeout = 0;

## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSetTable = $DataSet.Tables["Table"];


## ---------- Working with Excel ---------- ##

## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);
$xlsSh.columns.item('A').NumberFormat = "@"
$xlsSh.columns.item('P').NumberFormat = "@"
## - Copy entire table to the clipboard as tab delimited CSV
$DataSetTable | ConvertTo-Csv -NoType -Del "`t" | Clip

## - Paste table to Excel
$xlsObj.ActiveCell.PasteSpecial() | Out-Null

## - Set columns to auto-fit width
$xlsObj.ActiveSheet.UsedRange.Columns|%{$_.AutoFit()|Out-Null}

## - Saving Excel file - if the file exist do delete then save
$xlsFile = $ExportFile;

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };