[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

Commentaires   

0 #1 Tina 24-02-2018 23:59
Excellent goods from you, man. I have understand your stuff previous to and you're just too magnificent.
I really like what you've acquired here, really like what
you are stating and the way in which you say it. You make
it entertaining and you still take care of to keep it wise.
I cant wait to read much more from you. This is actually a terrific site.


Here is my web page :: money lender: https://cashmart.com.sg/
Citer

Ajouter un Commentaire


Code de sécurité
Rafraîchir