[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