Discussion :

[miniquick]

Bonjour,

Je cherche à générer via DTS autant de fichiers excel qu'il y a de fournisseurs dans ma table Sql server 2000. Ainsi chaque fournisseur aura son fichier propre qui pourra être envoyé par mail.

Une idée?

[miniquick]

Chouette, je fais les questions et les réponses!
J'ai trouvé du code que j'ai adapté. Pas complètement élégant car j'ai mis en dur les colonnes qui m'intéressent mais en tout cas ça marche. la fonction me génère un fichier excel dont le nom est en paramètre par copie d'un fichier modèle (permet de préparer des mises en forme) et m'insère les enregistrements correspondant à une requête passée également en paramètre.

Create proc sp_Export_Excel (@fileName varchar(100),
@NumOfColumns tinyint,
@query varchar(200))
as
begin
declare @dosStmt varchar(200)
declare @tsqlStmt varchar(500)
declare @colList varchar(200)
declare @charInd tinyint

declare @rmtsrvname varchar(100)
declare @useself varchar(100)
declare @locallogin varchar(100)
declare @rmtuser varchar(100)
declare @rmtpassword varchar(100)
declare @NomServ varchar(100)

set nocount on

-- construct the columnList A,B,C ...
-- until Num Of columns is reached.

-- set @charInd=0
-- set @colList = 'A'
-- while @charInd < @NumOfColumns - 1
-- begin
-- set @charInd = @charInd + 1
-- set @colList = @colList + ',' + char(65 + @charInd)
-- end

set @colList = 'article,"Libellé Article",émission,cde,RAL,"à quai",besoin,confirm,R,"4S",">S4",cad,mag,Montt,ctrl,transit,statut,pda'

print @fileName

-- Create an Empty Excel file as the target file name by copying the template Empty excel File
set @dosStmt = ' copy c:\temp\manquants\vide.xls ' + @fileName
exec master..xp_cmdshell @dosStmt

-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@fileName,
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin
@rmtsrvname ='ExcelSource',
@useself = false,
@locallogin =NULL,
@rmtuser ='ADMIN',
@rmtpassword =NULL;
set @NomServ ='ExcelSource';

-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[Feuil1$] ' + ' ( ' + @colList + ' ) '+ @query

print @tsqlStmt

-- execute dynamically the TSQL statement
exec (@tsqlStmt)

-- drop the linked server
EXEC sp_dropserver 'ExcelSource', 'droplogins'
set nocount off
end

GO

[miniquick]

Salut,

Je continue de me répondre à moi-même... La procédure permet de copier un fichier modèle (vide.xls) contenant une macro qui met en forme la feuille excel.

CREATE PROCEDURE [sp_ExportToExcel] (
@SourceServer VARCHAR(30),
@SourceUID VARCHAR(30)=NULL,
@SourcePWD VARCHAR(30)=NULL,
@QueryText VARCHAR(500),
@filename VARCHAR(100),
@WorksheetName VARCHAR(100),
@RangeName VARCHAR(80),
@Macro VARCHAR(80))
AS
DECLARE @objServer INT,
@objQueryResults INT,
@objCurrentResultSet INT,
@objExcel INT,
@objWorkBooks INT,
@objWorkBook INT,
@objWorkSheet INT,
@objRange INT,
@hr INT,
@Columns INT,
@Rows INT,
@Output INT,
@currentColumn INT,
@currentRow INT,
@ResultSetRow INT,
@off_Column INT,
@off_Row INT,
@command VARCHAR(500),
@ColumnName VARCHAR(500),
@value VARCHAR(255),
@strErrorMessage VARCHAR(500),
@objErrorObject INT,
@Alphabet VARCHAR(27),
@dosStmt varchar(200)

SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'

IF @QueryText IS NULL
BEGIN
RAISERROR ('A query string is required for spDMOExportToExcel',16,1)
RETURN 1
END

-- Sets the server to the local server
IF @SourceServer IS NULL SELECT @SourceServer = @@servername

-- SET NOCOUNT ON

set @dosStmt = ' copy c:\temp\manquants\vide.xls ' + @filename
exec master..xp_cmdshell @dosStmt

SELECT @strErrorMessage = 'instantiating the DMO',
@objErrorObject=@objServer
EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

IF @SourcePWD IS NULL OR @SourceUID IS NULL
BEGIN
--use a trusted connection
IF @hr=0 SELECT @strErrorMessage=
'Setting login to windows authentication on '
+@SourceServer, @objErrorObject=@objServer
IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1
IF @hr=0 SELECT @strErrorMessage=
'logging in to the requested server using windows authentication on '
+@SourceServer
IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,
'Connect', NULL, @SourceServer
IF @SourceUID IS NOT NULL AND @hr=0
EXEC @hr=sp_OAMethod
@objServer, 'Connect', NULL, @SourceServer ,@SourceUID
END
ELSE
BEGIN
IF @hr=0
SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
''' with user ID '''+@SourceUID+'''',
@objErrorObject=@objServer
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL,
@SourceServer, @SourceUID, @SourcePWD
END

--now we execute the query
IF @hr=0 SELECT @strErrorMessage='executing the query "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objServer,
@command = 'ExecuteWithResults("' + @QueryText + '")'
IF @hr=0
EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT

IF @hr=0
SELECT @strErrorMessage='getting the first result set for "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults
IF @hr=0 EXEC @hr=sp_OAMethod
@objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT
IF @hr=0
SELECT @strErrorMessage='getting the rows and columns "'
+@querytext+'", on '+@SourceServer
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT

--so now we have the queryresults. We start up Excel
IF @hr=0
SELECT @strErrorMessage='la création de l''application Excel '
+@SourceServer, @objErrorObject=@objExcel

IF @hr=0
EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT

IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object '
--IF @hr=0
-- EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks', @objWorkBooks OUT

-- open the file to be processed
IF @hr=0
SELECT @strErrorMessage='opening the workbook "'
+@querytext+'", on '+@SourceServer,
@objErrorObject=@objQueryResults

IF @hr=0
EXEC @hr = sp_OAMethod @objExcel, 'WorkBooks.Open', @objWorkBook OUT,@filename

SELECT @WorksheetName='WorkSheets("' + @WorkSheetName + '")'
EXEC @hr = sp_oaGetProperty @objWorkBook,@WorkSheetName, @objWorkSheet out

--format the headings in Bold nicely
--IF @hr=0
-- SELECT @strErrorMessage='formatting the column headings in bold ',
-- @objErrorObject=@objWorkSheet,
-- @command='Range("A1:Z1").font.bold'
--IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1
--now we write out the data

SELECT @currentRow = 2
WHILE (@currentRow <= @Rows+1 AND @hr=0)
BEGIN
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr=0)
BEGIN
IF @hr=0
SELECT
@strErrorMessage=
'getting the value from the query string'
+ LTRIM(STR(@currentRow)) +','
+ LTRIM(STR(@currentRow))+')',
@objErrorObject=@objQueryResults,
@ResultSetRow=@CurrentRow-1
IF @hr=0
EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString',
@value OUT, @ResultSetRow, @currentColumn
IF @hr=0
SELECT @strErrorMessage=
'assigning the value from the query string'
+ LTRIM(STR(@CurrentRow-1)) +', '
+ LTRIM(STR(@currentcolumn))+')' ,
@objErrorObject=@objExcel,
@command='Cells('+STR(@currentRow) +', '
+ STR(@CurrentColumn)+').value'
IF @hr=0
EXEC @hr=sp_OASetProperty @objExcel, @command, @value
SELECT @currentColumn = @currentColumn + 1
END
SELECT @currentRow = @currentRow + 1
END

IF @Macro IS NOT NULL
BEGIN
SELECT @command = 'Run '+ @Macro
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
END


IF @hr=0
SELECT @strErrorMessage='Saving the workbook"'+@filename+'"',
@objErrorObject=@objRange,
@command = 'Save'
IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command
IF @hr=0 SELECT @strErrorMessage='closing Excel ',
@objErrorObject=@objExcel
EXEC @hr=sp_OAMethod @objWorkBook, 'Close'
EXEC sp_OAMethod @objExcel, 'Close'

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 @hr, @source, @Description,@Helpfile,@HelpID output
SELECT @strErrorMessage='Erreur pendant '
+COALESCE(@strErrorMessage,'doing something')
+', '+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkBooks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
RETURN @hr
GO


On fait l'appel de la façon suivante:

declare @four varchar(6),
@QueryTexttmp varchar(500)
set @four='000200'
set @QueryTexttmp = 'select nom, prénom from essaifm'
EXEC sp_ExportToExcel @SourceServer=NULL,
@SourceUID= 'ADMIN',
@SourcePWD = NULL,
@QueryText = @QueryTexttmp,
@filename = 'C:\temp\manquants\000300.xls',
@WorksheetName='A_Manquant',
@RangeName =NULL,
@Macro='"MAJ"'

Le seul hic, c'est que la macro ne s'exécute pas, ou plutôt je ne récupère jamais la main dans l'analyseur de requête. La macro fait :

Sub MAJ()
'
' MAJ Macro
' Macro enregistrée le 29/06/2007 par Franck MOLMY
'

'
Rows("A1:Q1").Select
Selection.Font.Bold = True

End Sub


Quelqu'un a une idée?