[SQL Server] Transformer les classeurs Excel en plusieurs fichiers Excel

 

-- =============================================

-- Author:        Zouhaier KHARROUBI

-- Create date:   01/08/2017

-- Description:   Transformer les classeurs Excel en plusieurs fichiers Excel

-- =============================================

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

   -- Insert statements for procedure here

  

   IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Fichiers_1]'))

            BEGIN

                  DROP TABLE #Fichiers_1

            END

     

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Liste_Classeurs_Excel'))

            BEGIN

                  DROP TABLE #Liste_Classeurs_Excel

            END

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Liste_Classeurs_Excel_Temp'))

            BEGIN

                  DROP TABLE #Liste_Classeurs_Excel_Temp

            END

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Liste_Classeurs_Delete'))

            BEGIN

                  DROP TABLE #Liste_Classeurs_Delete

            END

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#InformationDivers'))

            BEGIN

                  DROP TABLE #InformationDivers

            END

                                  

      CREATE TABLE #Fichiers_1

      (

            Chemin_Fichier VARCHAR(350) NULL

      )

     

      CREATE TABLE #Liste_Classeurs_Excel_Temp

      (

      TABLE_CAT         VARCHAR(255) NULL

      ,TABLE_SCHEM      VARCHAR(255) NULL

      ,TABLE_NAME       VARCHAR(255) NULL

      ,TABLE_TYPE       VARCHAR(255) NULL

      ,REMARKS          VARCHAR(255) NULL

      )

      CREATE TABLE #Liste_Classeurs_Excel

      (

      Num_Classeur     INT IDENTITY (1,1)

      ,TABLE_CAT        VARCHAR(255) NULL

      ,TABLE_SCHEM      VARCHAR(255) NULL

      ,TABLE_NAME       VARCHAR(255) NULL

      ,TABLE_TYPE       VARCHAR(255) NULL

      ,REMARKS          VARCHAR(255) NULL

      )

      CREATE TABLE #Liste_Classeurs_Delete

      (

      Num_Classeur     INT IDENTITY (1,1)

      ,TABLE_CAT        VARCHAR(255) NULL

      ,TABLE_SCHEM      VARCHAR(255) NULL

      ,TABLE_NAME       VARCHAR(255) NULL

      ,TABLE_TYPE       VARCHAR(255) NULL

      ,REMARKS          VARCHAR(255) NULL

      )

     

      CREATE TABLE #InformationDivers

      (

      Num_Lig    INT IDENTITY (1,1)

      ,Info_1           NVARCHAR(250) NULL

      ,Info_2           NVARCHAR(250) NULL

      )

     

      DECLARE @Chemin_Fichier_Excel           VARCHAR(4000)

                  ,@Serveur_Lie_Excel                VARCHAR(250)

                  ,@Nom_Classeur_Excel         VARCHAR(50)

                  ,@Num_Classeur                     INT

                  ,@Nbre_Classeur                    INT  

                  ,@Num_Classeur_Delete        INT

                  ,@Nbre_Classeur_Delete       INT  

                  ,@CMD                              NVARCHAR(4000)   

                  ,@Requete                          NVARCHAR(MAX)    

                  ,@Info_1                           NVARCHAR(250)

                  ,@Info_2                           NVARCHAR(250)

                  ,@Nouveau_Nom_Fichier        NVARCHAR(MAX)

                  ,@Chemin_Fichier             VARCHAR(4000)

                  ,@Nom_Fichier                      VARCHAR(4000)

                  ,@FichierBat                       NVARCHAR(4000)

                  ,@ReturnCode                       INT

                 

                 

      SELECT @Serveur_Lie_Excel = 'FichePaie'

      --SELECT @Chemin_Fichier   = '\\audparsage\BACKUP_DataBases\'

      SELECT @Chemin_Fichier   = '\\MonServeur\MesRapportsSSRS\'

     

      SELECT @CMD = N'DIR "' + @Chemin_Fichier + 'Commission Representants.xls" /B /S'

     

      INSERT INTO #Fichiers_1

      EXEC Master..xp_cmdshell@CMD

     

      SELECT @Chemin_Fichier_Excel=Chemin_Fichier FROM #Fichiers_1 WHERE CHARINDEX('\',ISNULL(Chemin_Fichier,''),1)<>0

     

      IF @Chemin_Fichier_Excel IS NOT NULL

            BEGIN

                 

                  IF EXISTS(SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @Serveur_Lie_Excel)

                        BEGIN

                             EXEC master.dbo.sp_dropserver@server=@Serveur_Lie_Excel, @droplogins='droplogins'                            

                        END

                  EXEC sp_addlinkedserver@server = @Serveur_Lie_Excel,@srvproduct = 'Excel',@provider = 'Microsoft.ACE.OLEDB.12.0',@datasrc = @Chemin_Fichier_Excel,@provstr = 'EXCEL 12.0 XML;HDR=YES';                   

                 

                  INSERT INTO #Liste_Classeurs_Excel_Temp

                  EXEC sp_tables_ex@Serveur_Lie_Excel

                 

                  INSERT INTO #Liste_Classeurs_Excel SELECT * FROM #Liste_Classeurs_Excel_Temp WHERE TABLE_NAME NOT LIKE '%filter%' AND TABLE_NAME NOT LIKE '%Print_Titles%'

                                  

                  SELECT @Nbre_Classeur   = COUNT(*) FROM #Liste_Classeurs_Excel

                  SELECT @Num_Classeur    = 1

                 

                 

                  --Traiter tous les classeurs

                  WHILE @Num_Classeur <= @Nbre_Classeur

                        BEGIN

                       

                             SELECT @Nom_Classeur_Excel = '[' + CAST(TABLE_NAME AS VARCHAR(50)) + ']' FROM #Liste_Classeurs_Excel WHERE Num_Classeur=@Num_Classeur

                            

                             SELECT @Requete =N'INSERT INTO #InformationDivers(Info_1,Info_2)'

                             SELECT @Requete = @Requete + 'SELECT TOP 7 F1,ISNULL(F4,F5) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml; HDR=YES; IMEX=1; Database=' + @Chemin_Fichier_Excel + ''',' + @Nom_Classeur_Excel + ')'

                             EXECUTE sp_executesql@Requete

                 

                            

                             --Récupérer la date de la commission et la formater en jj_mm_aaaa_jj_mm_aaaa

                             SELECT @Info_1 = REPLACE(REPLACE(SUBSTRING(Info_1,30,30), ' AU ','_'),'/','_') FROM #InformationDivers WHERE Info_1 LIKE 'COMMISSIONS REPRESENTANTS%' AND ISNULL(Info_1,'')<>''

                            

                             --Récupérer le nom du Salarié

                             SELECT @Info_2 = REPLACE(REPLACE(Info_2,' ','_'),'''','_') FROM #InformationDivers WHERE Info_2 NOT LIKE 'Prénom Salarié%' AND ISNULL(Info_2,'')<>''

                            

                             SELECT @Nom_Fichier = @Serveur_Lie_Excel + '_' + @Info_2 + '_' + @Info_1 + '_' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2))

                             + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR(2))

                             + CAST(DATEPART(SECOND,GETDATE()) AS VARCHAR(2)) + '.xls'

                            

                             ----Copier le classeur courant dans un fichier Excel      

                             SELECT @CMD = 'COPY /Y "' + @Chemin_Fichier_Excel + '" "' + @Chemin_Fichier + @Nom_Fichier + '"'

                             EXEC Master..xp_cmdshell@CMD

                            

                             TRUNCATE TABLE #Liste_Classeurs_Delete

                             INSERT INTO #Liste_Classeurs_Delete(TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS) SELECT TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS FROM #Liste_Classeurs_Excel WHERE Num_Classeur<>@Num_Classeur

                            

                             SELECT @Nbre_Classeur_Delete = COUNT(*) FROM #Liste_Classeurs_Delete

                             SELECT @Num_Classeur_Delete = 1

                            

                             WHILE @Num_Classeur_Delete <= @Nbre_Classeur_Delete

                                   BEGIN

                                         SELECT @Nom_Classeur_Excel = REPLACE(CAST(TABLE_NAME AS VARCHAR(50)),'$','')   FROM #Liste_Classeurs_Delete WHERE Num_Classeur = @Num_Classeur_Delete

                                         SELECT @CMD = 'powershell -ExecutionPolicy unrestricted -Command "C:\Mes_Scripts\FichePaie.ps1 -NomFichierExel "' + @Chemin_Fichier + @Nom_Fichier + '" -NomClasseur "' + @Nom_Classeur_Excel + '""'

                                         SELECT @FichierBat = 'D:\REF_AUDIM\Scripts\' + @Nom_Classeur_Excel + '.bat'

                            

                                         --Créer et lancer le fichier bat qui permet de lancer le script powershell qui supprime les classeurs Excel non concernés par le Salarié

                                         SELECT @CMD ='ECHO ' + @CMD + ' > ' + @FichierBat

                                         EXEC @ReturnCode = xp_cmdshell@CMD

                                        

                                         EXEC @ReturnCode = xp_cmdshell@FichierBat

                                                                                                                    

                                         --Supprimer le fichier bat

                                         SELECT @CMD ='DEL ' + @FichierBat

                                         EXEC @ReturnCode = xp_cmdshell@CMD

                                  

                                         SELECT @Num_Classeur_Delete = @Num_Classeur_Delete + 1

                                   END

                            

                             SELECT @Num_Classeur    = @Num_Classeur + 1

                            

                            

                        END

           

                  --Renommer le fichier principal pour éviter les traitement en boucle

                  SELECT @CMD = 'REN "' + @Chemin_Fichier_Excel + '" "Faiche Paie_' + @Info_1 + '.xls"'

                  SELECT @CMD

                  EXEC Master..xp_cmdshell@CMD

                 

                  --Envoyer la confirmation   

                  SELECT @CMD = N'DIR "' + @Chemin_Fichier + 'FichePaie*.xls" /B /S'

     

                  TRUNCATE TABLE #Fichiers_1

                 

                  INSERT INTO #Fichiers_1

                  EXEC Master..xp_cmdshell@CMD

                 

                  DECLARE @emailSubject   VARCHAR(100)

                             ,@tableHTML       NVARCHAR(MAX)

                             ,@body                  VARCHAR(MAX)

                  SELECT @emailSubject = 'Commissions Representants Génération Rapports'

                  SET @tableHTML = N'<H><font size="4">Bonjour</font><BR></BR></H>' +

                     N'<H><font size="4">Veuillez trouver ci-dessous, la liste des fiches de paie qui ont été générées : </font><BR></BR></H>' +

                     N'<html><head><style>' +

                     N'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

                     N'</style></head><body>' +

                     N'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +

                     N'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +

                     N'<tr bgcolor=#4b6c9e>' +

                     N'<td align=center><font face="calibri" color=White><b>N°</b></font></td>' +  

                     N'<td align=center><font face="calibri" color=White><b>Nom Fichier</b></font></td></tr>'

                  SELECT @body =

                  (

                     SELECT td=ROW_NUMBER() OVER(ORDER BY Chemin_Fichier) ,  

                                td = Chemin_Fichier  

                     FROM #Fichiers_1 WHERE ISNULL(Chemin_Fichier,'')<>''

                     ORDER BY Chemin_Fichier

                     FOR XML RAW('tr'), ELEMENTS

                  )

                  SET @body = REPLACE(@body, '<td>', '<td align=left><font face="calibri">')

                  SET @body = REPLACE(@body, '</td>', '</font></td>')

                  SET @body = REPLACE(@body, '_x0020_', SPACE(1))

                  SET @body = Replace(@body, '_x003D_', '=')

                  SET @tableHTML = @tableHTML + @body + '</table></div></body></html>'

                  SET @tableHTML = @tableHTML + N'<H><BR></BR></H>'

                  SET @tableHTML = @tableHTML + N'<H><font size="4">Cordialement,</font><BR></BR></H>'

                  SET @tableHTML = @tableHTML + N'<H><font size="4">MEKTABA.INFO</font></H>'

                 

                  EXEC msdb.dbo.sp_send_dbmail

               @profile_name = 'MektabaSqlServerProfil',

               @recipients = 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',

               @body = @tableHTML,

               @subject = @emailSubject,

               @body_format = 'HTML'

                 

            END

END

#Le code du script PowerShell FichePaie.ps1

Param (

[string] $NomFichierExel,

[string] $NomClasseur

)

# $NomFichierExel = chemin fichier Excel

# $NomClasseur = Nom du classeur Excel

$excel_objet = new-object -com excel.application -Property @{Visible = $false}

$excel_objet.Visible = $false

$excel_objet.displayAlerts = $false # ne afficher une demande de confirmation à l'utilisateur

$wb2 = $excel_objet.workbooks.open($NomFichierExel) # Ouvrir le fichier Excel

#$wb2.displayAlerts = $false # ne pas afficher une demande de confirmation à l'utilisateur

$sh2_wb2 = $wb2.sheets | where {$_.name -eq $NomClasseur}

#$sh2_wb2.displayAlerts = $false # ne pas afficher une demande de confirmation à l'utilisateur

$sh2_wb2.delete() #S upprimer le classeur Excel concerné

$wb2.close($true) # Fermer et sauvegarder le fichier Excel

$excel_objet.quit()

spps -n excel