UGISS Community

Ciao a tutti,

avrei la necessità di esportare i dati estratti da una query su un file XLS specificando anche il nome del foglio. Ho pensato di utilizzare la stored procedure xp_cmdshell (il comando viene sempre lanciato da un utente sysadmin) ed effettivamente l'esportazione avviene come mi aspetto.

EXEC MASTER..XP_CMDSHELL 'bcp "SELECT CODICE, DESCR FROM SERVER.DATABASE.DBO.TABELLA ORDER BY CODICE" queryout C:\TABELLA.XLS -c -S SERVER' 

Non riesco però a specificare il foglio del file XLS in cui vorrei esportare i dati, il file creato ha un unico foglio con nome uguale al nome del file XLS.

Avete qualche suggerimento da darmi ?

Sto lavorando con SQL Server 2000.

Grazie

Sergio

Ciao,

se il file excel di arrivo esiste e il foglio in cui vuoi scrivere ha la prima riga col nome delle colonne che estrai nella query e quando esegui la query il file excel e' chiuso puoi usare queste istruzioni:

 [esporta i campi CodFarmacie, DesFarmacia dalla tabella Ana_Farmacia nel foglio Farmacie del file excel c:\temp\Test.xls]

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=c:\temp\Test.xls;',

'SELECT CodFarmacia,DesFarmacia FROM [Farmacie$]') select CodFarmacia,DesFarmacia from dbo.Ana_Farmacia ;

orsocurioso:

se il file excel di arrivo esiste e il foglio in cui vuoi scrivere ha la prima riga col nome delle colonne che estrai nella query e quando esegui la query il file excel e' chiuso

Ciao Franco,

sono nelle condizioni che hai elencato e l'esportazione con OPENROWSET funziona. Ho notato però che le righe vengono sempre accodate, c'è una opzione che permette di sovrascrivere il foglio ?

Grazie

Sergio

Ahime' Sergio, purtroppo non si  riesce a deletare le righe gia' inserite per cui va sempre tutto in Insert "append"

Almeno io non ho mai trovato il modo...

orsocurioso:

Ahime' Sergio, purtroppo non si  riesce a deletare le righe gia' inserite per cui va sempre tutto in Insert "append"

Ciao Franco,

ho provato anche con:

DELETE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', 'Data Source="C:\Scambio\Test.xls"; extended Properties=Excel 8.0')...Foglio2$

WHERE Descr = 'Test'

ma non permette di eliminare righe... poi ho trovato un esempio che definisce una stored procedure di esportazione verso Excel (non è farina del mio sacco , l'ho solo modificato un po').

Lo posto di seguito perchè potrebbe essere utile. Il foglio specificato (Worksheet) viene sovrascritto ad ogni esecuzione. Il file XLS non deve essere in uso al momento dell'esecuzione della stored procedure.

IF (OBJECT_ID('CS_SP_DMOExportToExcel', 'P') IS NOT NULL)

(@SourceServer VARCHAR(30),

@SourcePWD VARCHAR(30)=NULL,

@filename VARCHAR(100),

@RangeName VARCHAR(80)='MyRangeName')

AS BEGIN

@objQueryResults INT,

@objExcel INT,

@objWorkBook INT,

@objRange INT,

@Columns INT,

@Output INT,

@currentRow INT,

@off_Column INT,

@command VARCHAR(255),

@value VARCHAR(255),

@objErrorObject INT,

SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'

BEGIN

RETURN 1

-- Impostazione del server a local server

-- Disabilitazione messaggi

@objErrorObject=@objServer

IF (@SourcePWD IS NULL) OR

-- Utilizzo la trusted connection

SELECT @strErrorMessage= 'Impostazione login con windows authentication su '

@objErrorObject=@objServer

IF @hr=0

+@SourceServer

(@hr=0)

'Connect',

(@hr=0)

'Connect',

END

BEGIN

SELECT @strErrorMessage = 'Connessione a '''+@SourceServer+

@objErrorObject=@objServer

EXEC @hr=sp_OAMethod @objServer,

NULL,

-- Esecuzione della query

SELECT @strErrorMessage='Esecuzione query "'

@objErrorObject=@objServer,

IF @hr=0

IF @hr=0

@objErrorObject=@objQueryResults

EXEC @hr=sp_OAMethod @objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT

SELECT @strErrorMessage='Restituzione di righe e colonne "'

EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT

-- Il risultato della query è disponibile. Start up della connessione a Excel

SELECT @strErrorMessage='Creazione dell''applicazione Excel su '

@objErrorObject=@objExcel

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

SELECT @strErrorMessage='Restituzione dell''oggetto WorkBooks '

-- Creazione workbook

SELECT @strErrorMessage='Aggiunta di un workbook ',

IF @hr=0

-- Creazione worksheet

SELECT @strErrorMessage='Aggiunta di un worksheet ',

IF @hr=0

IF @hr=0

+@WorksheetName+'"',

IF @hr=0

-- Scrittura column headings

WHILE (@currentColumn <= @Columns AND @hr=0)

IF @hr=0

@objErrorObject=@objQueryResults,

IF @hr=0

IF @hr=0

+ LTRIM(STR(@currentColumn))

@objErrorObject=@objExcel,

+ LTRIM(STR(@CurrentColumn))+').value'

EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName

-- Modifica del formato per la prima riga (headings)

SELECT @strErrorMessage='Formattazione colonne con Bold ',

@command='Range("A1:'

+SUBSTRING(@alphabet,@currentColumn % 26,1)

IF @hr=0

-- Scrittura dei dati

WHILE (@currentRow <= @Rows+1) AND

BEGIN

WHILE (@currentColumn <= @Columns) AND

BEGIN

SELECT @strErrorMessage=

+ LTRIM(STR(@currentRow)) +','

@objErrorObject=@objQueryResults,

IF @hr=0

@value OUT, @ResultSetRow, @currentColumn

SELECT @strErrorMessage=

+ LTRIM(STR(@CurrentRow-1)) +', '

@objErrorObject=@objExcel,

+STR(@CurrentColumn)+').value'

EXEC @hr=sp_OASetProperty @objExcel, @command, @value

END

-- Definizione nome range

--Cells(1, 1).Resize(10, 5).Name = "TheData"

SELECT @strErrorMessage='Assegnazione del nome al range '

+ LTRIM(STR(@currentcolumn-1))+')' ,

@command='Cells(1, 1).Resize('+STR(@currentRow-1) +', '

IF @hr=0

-- Auto-fit delle colonne scritte

SELECT @strErrorMessage='Auto-fit delle colonne ',

@command='Columns("A:'

+SUBSTRING(@alphabet,(@Columns % 26),1)+

IF @hr=0 --insert into @bucket(bucket)

IF @hr=0

IF @hr=0

IF @hr=0

@objErrorObject=@objRange,

IF @hr=0

IF @hr=0

@objErrorObject=@objExcel

EXEC sp_OAMethod @objExcel, 'Close'

BEGIN

@Description VARCHAR(255),

@HelpID INT

@Description output,

+COALESCE(@strErrorMessage,'doing something')

RAISERROR (@strErrorMessage,16,1)

-- Libero la memoria allocata

EXEC sp_OADestroy @objQueryResults

EXEC sp_OADestroy @objExcel

EXEC sp_OADestroy @objWorkBook

RETURN @hr

@SourceUID= 'UserID',

@QueryText = 'use MyDataBase Select Codice, Descr from Test',

@WorksheetName='MyTest',

@QueryText = 'use MyDataBase select Codice, Descr from Test',

@RangeName ='MyRangeTest'

Ciao!

Sergio

Urca, non e' una "passeggiata"   Pero' e' piuttosto utile.Grazie dell'Info.

    Franco

Sì effettivamente non è una "passeggiata"... mi serviva a tutti i costi per chiudere un problema su un cliente... sai quelle cose che vengono promesse perchè sembrano facili e poi...

Diciamo che è un esempio di utilizzo dei metodi di un OLE object (tipo sp_OAMethod).

Ciao!

Sergio

Ciao,

vi ricordate l'esportazione del dataset di una query su foglio Excel realizzato con la stored procedure CS_SP_DMOExportToExcel ? Ieri mi hanno chiesto la possibilità di non sovrascrivere il file XLS esistente ma solo il foglio @WorksheetName passato come parametro.

Per chi è interessato, posto di seguito un aggiornamento della stored procedure CS_SP_DMOExportToExcel, questa versione permette di realizzare e mantenere un file Excel con più fogli (worksheet): uno o più fogli "dati" ed uno o più fogli di analisi con formule basate sui fogli "dati".