Datafly.pro

Que nous propose SQL Server pour exporter des données vers Excel ? Voici quelques techniques, avec leurs limites :
 

1. Connecter Excel à SQL Server

Cela reste la méthode la plus simple, mais il s'agit plus de liaison plutôt que d'exportation.
Depuis Excel, accessible via le menu Données/Autres Sources/Provenance SQL Server. Il ne vous reste plus qu'à indiquer le nom de l'instance, la base, la table.
Et vous avez le choix entre la table toute simple ou  le tableau croisé dynamique, que vous pourrez rafraichir à volonté.



Pour les utilisateurs de SharePoint et Excel Services, notez qu'une table simple connectée à une base de données n'est pas prise en charge par Excel Services, vous ne pourrez donc pas rafraîchir vos données.
 

2. Copier-coller depuis SSMS

On ne peut pas non plus parler d'une exportation, et cela reste manuel et non automatisable...


 

3. Integration Services

SSIS est bien entendu le moyen privilégié d'importer et d'exporter des données. La littérature ne manque pas à ce sujet.
Je vous conseille l'article de Dominique Verrière.
Notez que SSIS ne permettra pas d'utiliser le gestionnaire de connexion Excel en 64 bits. Vous devrez exécuter vos packages en 32 bits et l'utilitaire pour les lancer sera le DTEXEC qui est localisé dans les binaires 32 bits (Program Files (x86))...
 

Et en TSQL ?

C'est parfois utile et souvent demandé : je vois bien trop souvent de mauvaises fonctions d'exportation au format csv, effectuées par bcp, xpcmdshell, etc...
C'est possible de faire mieux, mais il n'existe malheureusement pas une vraie fonction d'export Excel dans SQL Server comme dans certains autres produits. Il faut donc jongler avec les différentes solutions disponibles, attendre que cette fonctionnalité soit proposée dans une future version ou la développer en CLR, avis aux amateurs !
Voici ce que nous avons sous la main...
Les exemples ci-après fonctionnent sur une plate-forme SQL Server 2008 R2 x64.
Attention pour les plate-formes 64 bits : pour vous connecter avec OpenRowset, OpenDatasource, serveurs liés ou ADODB, vous devrez installer les composants "Access 2010 Database Engine Redistributable x64" téléchargeable ici. (Notez que la version x64 ne supporte pas qu'une application Office 32 bits soit installée sur le même système).

4. Openrowset et Opendatasource

Openrowset et opendatasource nous permettent de lire ou d'écrire dans un fichier Excel existant (le fichier doit exister et doit déjà contenir une table). Nous ne pouvons pas créer un nouveau fichier avec ce moyen.

Pour que ces commandes soit acceptées, vous devez activer la propriété AdHocRemoteQueriesEnabled dans la configuration de la surface d'exposition de l'instance (dans SSMS, clic droit sur l'instance, facets, Surface Area Configuration)

Lire un fichier Excel (avec les composants x64)  :

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\XLS\Monfichierxls.xls', [Sheet1$])

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\XLS\Monfichierxls.xls', 'Select * FROM [Sheet1$]') 

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\XLS\monfichierxls.xls;Extended Properties=Excel 12.0')...[Sheet1$] 

Insérer :

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\XLS\monfichierxls.xls',
'Select * from [Sheet1$]')
(Prénom,Nom,Téléphone)
SELECT 'Gérard','Manvussa','012345' 

INSERT INTO OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\XLS\monfichierxls.xls";
extended Properties=Excel 12.0')...[Sheet1$]
(Prénom,Nom,Téléphone)
SELECT 'Gérard','Manvussa','012345' 

Mettre à jour :

UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\XLS\monfichierxls.xls',
'Select * from [Sheet1$]')
SET Prénom='Jean' WHERE Nom = 'Dupont' 

5. ADODB

Je reprendrai ici un exemple proposé par Robin Page et Phil Factor sur le site simple-talk.com. La procédure stockée suivante permet de créer un fichier Excel et son onglet et d'effectuer les commandes DDL create et drop table, insert, update (mais pas delete)... Elle est remise au goût du jour pour fonctionner avec notre pilote x64.

Vous devrez activer la propriété OleAutomationEnabled dans la configuration de la surface d'exposition de l'instance.

CREATE PROCEDURE [dbo].[spExecute_ADODB_SQL]
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
='Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=%DataSource;
Extended Properties=Excel 12.0'
AS
DECLARE
    @objExcel INT,
    @hr INT,
    @command VARCHAR(255),
    @strErrorMessage VARCHAR(255),
    @objErrorObject INT,
    @objConnection INT,
    @bucket INT
SELECT @ConnectionString
    =REPLACE (@ConnectionString, '%DataSource', @DataSource)
IF @Worksheet IS NOT NULL
    SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet)
SELECT @strErrorMessage='Making ADODB connection ',
            @objErrorObject=NULL
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT
IF @hr=0
    SELECT @strErrorMessage='Assigning ConnectionString property "'
            + @ConnectionString + '"',
            @objErrorObject=@objconnection
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection,
            'ConnectionString', @ConnectionString
IF @hr=0 SELECT @strErrorMessage
        ='Opening Connection to XLS, for file Create or Append'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'
IF @hr=0 SELECT @strErrorMessage
        ='Executing DDL "'+@DDL+'"'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',
        @Bucket out , @DDL
IF @hr<>0
    BEGIN
    DECLARE
        @Source VARCHAR(255),
        @Description VARCHAR(255),
        @Helpfile VARCHAR(255),
        @HelpID INT
    EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,
        @Description output,@Helpfile output,@HelpID output
    SELECT @strErrorMessage='Error whilst '
        +COALESCE(@strErrorMessage,'doing something')+', '
        +COALESCE(@Description,'')
    RAISERROR (@strErrorMessage,16,1)
    END
EXEC @hr=sp_OADestroy @objconnection
GO

Exemple d'utilisation :

-- Créer la table (et crée un onglet s'il n'existe pas)
spExecute_ADODB_SQL @DDL='Create table Contacts
(Prénom Text, Nom Text, Téléphone Text)',
@DataSource ='C:\XLS\NewContacts.xls' 

-- insérer des données
spExecute_ADODB_SQL @DDL='insert into Contacts
(Prénom,Nom,Téléphone)
values (''Jean'',
''Dupont'',
''0123456789'')',
@DataSource ='C:\XLS\NewContacts.xls' 

-- mettre à jour
spExecute_ADODB_SQL @DDL='update Contacts 
set Prénom = ''James''
where Nom = ''Dupont''',
@DataSource ='C:\XLS\NewContacts.xls' 

-- supprime la table (mais pas l'onglet)
spExecute_ADODB_SQL @DDL='Drop table Contacts',
@DataSource ='C:\XLS\NewContacts.xls' 

6. CLR

Evidemment, l'intégration du CLR dans SQL Server peut ouvrir de larges possibilités. Mais la DLL parfaite qui permet d'exporter vers Excel au bon format (xls ou xlsx) reste à écrire.
Je vous propose de tester une DLL développée par Anders Pedersen (source http://www.sqlservercentral.com/articles/SQLCLR/68842/).
Vous pouvez en télécharger une nouvelle version avec ses sources ici (j'ai corrigé une petite erreur dans les formats de date). 
Cette DLL vous permettra d'exporter le résultat d'une procédure stockée vers un fichier au format XML d'Excel. Ce n'est pas un vrai format XLS ou XLSX, de ce fait Excel 2010 vous demandera une confirmation à l'ouverture du fichier (ce warning est éventuellement désactivable avec une clé de registre), mais surtout vous ne pourrez pas relire ce fichier avec un OPENROWSET !
Pour désactiver le warning à l'ouverture de fichier par Excel, créez à HKCU\Software\Microsoft\Office\14.0\Excel\Security une valeur REG_DWORD nommée ExtensionHardening, valeur 0

Pour utiliser la procédure d'export, vous devez :
- activer la propriété OleAutomationEnabled dans la configuration de la surface d'exposition de l'instance
- copier la DLL dans un endroit accessible par le service SQL
- autoriser la base ou vous voulez l'utiliser : ALTER DATABASE mabase SET TRUSTWORTHY ON
- créer l'assembly :
- Créer Assembly

CREATE ASSEMBLY ExportToExcel 
FROM 'D:\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

- Créer la procédure stockée sp_CLR_Export_Excel

CREATE PROCEDURE [dbo].[sp_CLR_Export_Excel]
  @proc [nvarchar](100)
, @path [nvarchar](200)
, @filename [nvarchar](100)
, @params XML
AS EXTERNAL NAME 
  [ExportToExcel].[StoredProcedures].[ExportToExcel]
GO

L'utilisation se fera de la manière suivante :

DECLARE @path varchar(4000), @file varchar(4000), @params XML
SET @path = 'C:\XLS\'
SET @file = 'MonfichierExcel'
-- spécifier les paramètres de la procédure appelée
SET @params='<params><param name="FirstName" value="Anders" /></params>'
-- pour une procédure sans paramètres : SET @params = '<params></params>'
EXEC sp_CLR_Export_Excel 'maprocedure', @path, @file,@params

Pour une utilisation à partir d'une table, et avec quelques aménagements (conversion vers le format XLS avec OLE Automation, entre autres), je vous propose la procédure sp_Export_Excel à la fin de cet article.

7. OLE-Automation

Quoi de mieux que de demander à Excel de produire de l'Excel ? OLE-Automation va nous permettre de piloter l'application Excel installée sur le serveur. Du coup, toutes les possibilités sont ouvertes : créer un onglet, écrire dans une cellule, formattage (gras, police, etc...).
Cela reste néanmoins une méthode très discutable : Excel est installé et exécuté sur le serveur, une erreur non gérée peut laisser un ou plusieurs processus Excel actifs, attention aux ressources consommées et à la montée en charge...!

Pour faire fonctionner l'exemple suivant, vous devez :
- Installer Excel 2010 sur le serveur
- Via l'utilitaire DCOMCNFG, attribuer la permission DCOM exécution et activation au compte du service SQL Server, sur "Microsoft Excel Application"
- Créer un répertoire pour le profil système : C:\Windows\System32\config\systemprofile\Desktop
- Donner les permissions contrôle total au compte du service SQL Server sur le répertoire Appdata du profil système (C:\Windows\System32\config\systemprofile\AppData)

Créer un nouveau classeur et écrire 'Hello World' en gras dans une cellule :

-- Déclarations
DECLARE @xl INTEGER
DECLARE @rs INTEGER
DECLARE @Value VARCHAR(30)
DECLARE @ObjProp VARCHAR(200)
-- Le texte à écrire
SET @Value = 'Hello World'
-- Ouvrir Excel
EXECUTE @rs = master.dbo.sp_OACreate 'Excel.Application', @xl OUTPUT
-- Empêcher les alertes et les mises à jour d'écran
EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'
-- Obtenir le pointeur du classeur
DECLARE @xlWBs INTEGER
EXECUTE @rs = master.dbo.sp_OAMethod @xl, 'Workbooks', @xlWBs OUTPUT
-- Ajouter un nouveau classeur
DECLARE @xlWB INTEGER
EXECUTE @rs = master.dbo.sp_OAMethod @xlWBs, 'Add', @xlWB OUTPUT, -4167
-- Ajouter une nouvelle feuille
DECLARE @xlWS INTEGER
EXECUTE @rs = master.dbo.sp_OAMethod @xlWB, 'Sheets(Sheet1)', @xlWS OUTPUT
-- Obtenir le pointeur de la cellule
DECLARE @xlCell INTEGER
EXECUTE master.dbo.sp_OAGetProperty @xlWS, 'Cells', @xlCell OUTPUT, 4, 2
-- Ecrire dans la cellule
EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, 'Value', @Value
-- Formatter en gras
SET @ObjProp = 'Font.Bold'
EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, @ObjProp, 'True'
-- Fermer le pointeur 
EXECUTE @rs = master.dbo.sp_OADestroy @xlCell
-- Sauvegarder le fichier
DECLARE @FileName VARCHAR(100)
SET @FileName = 'C:\XLS\NewFile_' + CONVERT(CHAR(10), GETDATE(), 110) + '.xls'
EXECUTE @rs = master.dbo.sp_OAMethod @xlWB, 'SaveAs', NULL, @FileName
-- Fermer et quitter
EXECUTE @rs = master.dbo.sp_OAMethod @xlWB, 'Close'
EXECUTE @rs = master.dbo.sp_OAMethod @xl, 'Quit'
EXECUTE @rs = master.dbo.sp_OADestroy @xlWS
EXECUTE @rs = master.dbo.sp_OADestroy @xlWBs
EXECUTE @rs = master.dbo.sp_OADestroy @xlWB
EXECUTE @rs = master.dbo.sp_OADestroy @xl

8. sp_export_excel : CLR + OLE-Automation

Personnellement, j'aime assez la méthode CLR, mais je lui trouve plusieurs inconvénients : la source est une procédure stockée, le fichier est produit en xml et non en xls. Pour améliorer cela, je vous propose 2 procédures :

- sp_Export_Excel : permet d'exporter plusieurs tables vers des onglets d'un fichier Excel. Une des options est de convertir le fichier produit en bon XLS avec OLE automation, ce qui utilisera la procédure décrite juste après. Exemple d'utilisation (exporter trois table système vers un classeur Excel) :

EXEC sp_Export_Excel 
'sysobjects,syscolumns,sysprotects', 
'd:\xlstest\systemtables2.xls'
,NULL,1

- sp_Convert_ExcelXMStoXLS : Ouvre le fichier (Excel XML) est le resauvegarde (XLS), utilise OLE Automation. Appelée par la précédente.
Utilisation :

EXEC sp_Convert_ExcelXMLtoXLS 'c:\XLS\Monfichier.XLS'

/*-------------------------------------------------------------------
[SCRIPT] sp_Export_Excel  
[DATABASE] Base utilisateur
[DESCRIPTION] Procédure d'export vers Excel, multi-onglets
[PARAMETRES]
@TableList : liste de tables à exporter, séparée par des virgules
@PathFile : chemin complet du fichier
@TabRemoveTemp (facultatif) : 0 ou 1. Enlève le # devant le nom de l'onglet si nécessaire. Valeur 1 par défaut.
@ConvertToXLS (facultatif) : 0 ou 1. Convertit le Excel 2003 XML vers du XLS. Valeur 1 par défaut.
[SYNTAXE] : EXEC sp_Export_Excel '#mytable,#mytable2', 'c:\xls\monfichier.xls'
[DEPENDANCES] : Procédure sp_CLR_Export_Excel, procédure sp_Convert_ExcelXMLToXLS
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 25/08/2012 
-------------------------------------------------------------------*/
CREATE PROCEDURE sp_Export_Excel
  @TableList VARCHAR(8000)
, @PathFile VARCHAR(8000)
, @TabRemoveTemp BIT = 1
, @ConvertToXLS BIT = 1
AS 
  DECLARE @File VARCHAR(8000)
  , @Path VARCHAR(8000)
  , @Table VARCHAR(100)
  , @Pos INT
  , @sql VARCHAR(1000)
  , @cmd VARCHAR(8000)= ''
  , @TabName VARCHAR(100)
  SET @PathFile = LTRIM(RTRIM(@PathFile))
  IF RIGHT(@PathFile, 4) = '.xls' 
    SET @PathFile = LEFT(@PathFile, LEN(@PathFile) - 4)
  SET @File = REVERSE(LEFT(REVERSE(@PathFile),
                           CHARINDEX('\', REVERSE(@PathFile), 1) - 1)) 
  SET @Path = LEFT(@PathFile,
                   LEN(@PathFile) - CHARINDEX('\', REVERSE(@PathFile), 1) + 1)
  SET @TableList = LTRIM(RTRIM(@TableList)) + ','
  SET @Pos = CHARINDEX(',', @TableList, 1)
  IF REPLACE(@TableList, ',', '') <> '' 
    BEGIN
      WHILE @Pos > 0
        BEGIN
          SET @Table = LTRIM(RTRIM(LEFT(@TableList, @Pos - 1)))
          IF @Table <> '' 
            BEGIN
              IF LEFT(@Table, 1) = '#' AND
                @TabRemoveTemp = 1 
                SET @TabName = RIGHT(@Table, LEN(@Table) - 1)
              ELSE 
                SET @TabName = @Table
              SET @sql = 'SELECT ''' + @TabName + ''', * FROM ' + @Table +
                '; '
              SET @cmd = @cmd + @sql
            END
          SET @TableList = RIGHT(@TableList, LEN(@TableList) - @Pos)
          SET @Pos = CHARINDEX(',', @TableList, 1)
        END
    END
  IF OBJECT_ID('tempdb..#sp_ExportExcelDataset') <> 0 
    DROP PROCEDURE #sp_ExportExcelDataset
  SET @cmd = 'CREATE PROCEDURE #sp_ExportExcelDataset AS ' + @cmd
-- print @cmd
  EXEC ( @cmd
      )
  DECLARE @params XML
  SET @params = '<params></params>'
  EXEC sp_CLR_Export_Excel '#sp_ExportExcelDataset', @path, @file,@params
  IF @ConvertToXLS = 1 EXEC sp_Convert_ExcelXMLtoXLS @PathFile
  RETURN
GO

/*-------------------------------------------------------------------
[SCRIPT] sp_Convert_ExcelXMLtoXLS 
[DATABASE] Base utilisateur
[DESCRIPTION] Procédure de conversion d'un fichier Excel de 2003 XML vers XLS - OLE Automation
[PARAMETRES]
@PathFile : Chemin complet du fichier Excel
[SYNTAXE] : EXEC sp_Convert_ExcelXMSToXLS 'c:\xls\monfichier.xls'
[DEPENDANCES] : OLE Automation - Excel installé sur le serveur !
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 25/08/2012 
-------------------------------------------------------------------*/
CREATE PROCEDURE sp_Convert_ExcelXMLtoXLS
  @PathFile VARCHAR(8000)
, @NewPathFile VARCHAR(8000)=NULL
AS 
  DECLARE @objExcel INT
  , @objWorkBook INT
  , @objWorkSheet INT
  , @HR INT
  , @cmd VARCHAR(1000) 
  DECLARE @Source VARCHAR(255)
  , @Description VARCHAR(400)
  , @Helpfile VARCHAR(255)
  , @HelpID INT
SET @PathFile = LTRIM(RTRIM(@PathFile))
--New file not provided, save with the same name
IF @NewPathFile IS NULL SET @NewPathFile = @PathFile
--Create Excel application 
  EXEC @HR = sp_OACreate 'Excel.Application', @objExcel OUT 
  EXEC @hr = sp_OASetProperty @objExcel, 'ScreenUpdating', 'False'
  EXEC @hr = sp_OASetProperty @objExcel, 'DisplayAlerts', 'FALSE'
--Open Workbook
  EXEC @hr= sp_OAMethod @objExcel, 'Workbooks.open', @objWorkbook OUT,
    @filename = @PathFile 
--Save spreadsheet to file
  SET @cmd = 'SaveAs ("' + @NewPathFile + '",56)'
  EXEC @HR = master.dbo.sp_oaMethod @objWorkbook, @cmd 
-- Termine
  EXEC sp_OAdestroy @objworkbook 
  EXEC sp_OAdestroy @objWorkSheet
  EXEC @hr = sp_OAMethod @objExcel, 'quit'
  EXEC sp_OAdestroy @objExcel