UGISS Community

Salve a tutti ragazzi,

ho scoperto per caso questo forum cercando in rete una soluzione relativa al mio problema, e già leggendo qualche vecchio post ho avuto delle idee, ma non sono riuscito comunque a risolvere, per questo mi sono deciso a scrivere.

Ho un problema che forse considererete banale, e che in parte è già stato risolto da conversazioni precedenti, ossia, l'esportazione dei dati di una query su un file excel. Utilizzo il costrutto OPENROWSET che in alcuni casi ho visto funzionare, ma nel mio caso ottengo uno strano problema.

Prima di descriverlo, vi do alcune informazioni sulla struttura che ho sotto, e cosa voglio fare: Utilizzo un SQLServer 2005 e Office 2000 (o anche 2003, nel senso che dal cliente su cui questa applicazione dovrà girare hanno 2003 mentre nello sviluppo ho Office 2000) e Visual Basic 6.0 per l'applicazione.

Di certo la prima cosa che vi verrà da chiedere è perché non uso direttamente VisualBasic per scrivere il file Excel; il motivo è semplice: utilizzare il componente di Excel all'interno di visual basic e spostarci dentro qualcosa come 100.000 record (più o meno la mole di dati è quella) crea un enorme collo di bottiglia, tale per cui, l'utente per almeno 10 minuti non può toccare in nessun modo l'applicazione.

Ho notato che utilizzando OPENROWSET la situazione migliora (SQLServer è evidentemente più abituato a trattare grandi moli di dati) e quindi, vorrei lanciare questo comando da Visual Basic, e far trasferire ad SQL i dati su Excel.

A questo punto, mi credo dentro il file Excel di destinazione lo schema delle tabelle che dovrò trasferire, e lancio questo comado:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\TestProva.xls;','SELECT DataOra,Valore FROM [Riferimento$]') SELECT DATARIC As DataOra,Valore From R1TEMPRIF

Ok. Il comando viene eseguito correttamente (se lo lancio da SQL Server mi dice pure il numero di righe interessate, circa sulle 6000) ma.... quando chiudo VisualBasic e cerco di aprire il foglio excel che ho creato... ERRORE! Mi compare la finestrella del Debugger JIT di VisualStudio che mi evidenzia un'eccezione non gestita! Ci sono problemi di compatibilità fra le varie versioni di SQL e di Office? Quali sarebbero le "accoppiate" migliori?

Se non dipende da questo, A cosa può essere dovuto questo errore? Ah, naturalmente, il foglio Riferimento dentro Excel esiste ed ha, nella prima righa, due colonne: DataOra e Valore.

Ho esaurito le idee. Spero che possiate darmi qualche consiglio utile, altrimenti dovrò tornare alla soluzione che passa per VB creando ritardi mostruosi.

Grazie a tutti per l'attenzione. Rimango in attesa di qualche idea.

Michele

Ciao Michele,

prova a dare un'occhiata a questo thread... la stored procedure CS_SP_DMOExportToExcel potrebbe fare al caso tuo. Non spaventarti se sono molte righe di codice sono tutti controlli.

Ciao!

Sergio

Perchè non posti lo script qui:

http://www.ugiss.org/Content/Resource/Scripts.aspx

?

:-)

Ciao Davide,

perdona la domanda (...da lunedì ), come faccio ad inserire lo script nella pagina http://www.ugiss.org/Content/Resource/Scripts.aspx ?

Sergio

Ciao Sergio,

era proprio quello lo script che avevo visto, ma lo avevo accuratamente scansato perché mi ero fatto spaventare da quel codice enorme.

Comunque, armato di coraggio e di santa pazienza, mi sono messo a dargli un'occhiata, solo che ho ottenuto solamente questo errore:

Per me è abbastanza arabo, mi sapresti spiegare qual'è il problema? Ah già, ti posto anche la chiamata (fatta tramite query direttamente su SQL Server)

CS_SP_DMOExportToExcel @SourceServer=null,@QueryText='Dataric,Valore From R1TEMPRIF', @filename='C:\TestProva.xls', @WorksheetName='Riferimento$', @WorksheetIndex=3

Utilizzo l'autenticazione integrata con Windows (quindi il parametro SourceServer non dovrei metterlo, ma me lo richiede così l'ho messo a null).

Grazie mille per l'aiuto

 Michele

Michele Ciao,

cia.michele:

Utilizzo l'autenticazione integrata con Windows (quindi il parametro SourceServer non dovrei metterlo, ma me lo richiede così l'ho messo a null).

Se utilizzi l'autenticazione integrata di Windows puoi omettere utente e password ma il server su cui desideri connetterti dovresti metterlo. Nel tuo esempio non da errore perchè viene utilizzata la connessione che hai eseguito per lanciare la stored procedure.

cia.michele:

Messaggio 50000, livello 16, stato 1, procedura CS_SP_DMOExportToExcel, riga 309

Error whilst Esecuzione query "Dataric,Valore From R1TEMPRIF", su ROSSETTI, Utilizzo di sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, parametri IN, OUT o BOTH aggiuntivi]]

Non hai specificato la SELECT... prova così:

-- Utilizzo con integrated security
CS_SP_DMOExportToExcel @SourceServer='<Nome_Server>',
                       @QueryText = 'use <Nome_DataBase> SELECT Dataric,Valore From R1TEMPRIF',
                       @filename = 'C:\TestProva.xls', @WorksheetName='Riferimento$', @WorksheetIndex=3

Ciao!

Sergio

Grazie mille! Funziona perfettamente.
Ora però vorrei risolvere il mio problema iniziale, che era, oltre ad esportare i dati, formattare la prima colonna come "DataOra" x Excel.

Ho tentato di ottenere questo risultato, inserendo questo codice...

IF @hr=0

SELECT @strErrorMessage='Assegnazione column heading '+ LTRIM(STR(@currentColumn)) + ' dalla query', @objErrorObject=@objExcel, @command='Cells('+LTRIM(STR(@currentRow)) +', ' + LTRIM(STR(@CurrentColumn))+').NumberFormat="dd/mm/yyyy hh:mm.ss"'

IF @hr=0

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

...prima della restituzione dei valori della query... tuttavia, pur non dando errori, la query dura 12 minuti e non accenna a fermarsi.

Considerato il fatto che se ho scelto questa strada per l'esportazione, è per evitare un tempo morto di 6 minuti causato dall'ADO di VisualBasic, direi che c'è qualche problema.

Puoi dirmi qual'è l'errore?

Grazie mille per l'aiuto!!

Michele

Ciao, 

cia.michele:

Ora però vorrei risolvere il mio problema iniziale, che era, oltre ad esportare i dati, formattare la prima colonna come "DataOra" x Excel.

prova ad estrarre i dati già formattati dalla query, ad esempio con SELECT CONVERT(DateTime, <Campo_Data>, 113). L'ultimo parametro è lo stile... dai un'occhiata su SQL Server Books Online cercando "CONVERT"... troverai una tabella con tutti gli stili previsti (il 113 è il default europeo).

cia.michele:

...prima della restituzione dei valori della query... tuttavia, pur non dando errori, la query dura 12 minuti e non accenna a fermarsi.

Verifica che il foglio Excel non sia aperto/in uso.

Ciao!

Sergio

Ciao Sergio,

Innanzitutto grazie per la pazienza e per i preziosi consigli che mi hai dato. Sono ahimè costretto però, a rivolgermi di nuovo a te, per quel problema di formattazione. Dunque: Per formattare la data utilizzo una funzione che ho scritto io, che costruisce la stringa della data (nella mia tabella il campo dataora è un campo di testo e quindi va ricostruito) e non ci sono problemi.

A questo punto però mi sorge un'altro problema, ossia quello di formattare la seconda colonna con un tipo che excel veda come numerico non intero. Devo aggiungere che nella tabella è salvato come intero e che il valore che devo visualizzare sul foglio excel è VALORE/10. Effettivamente sarebbe più semplice se nella tabella ci fosse già un float, ma purtroppo il report in excel è solo la parte finale di un'applicazione più complessa (che lega PLC Siemens, Visualbasic e OPCServer) che mi richiede questo tipo di formattazione.

Utilizzando il comando che ti posto qui sotto, la colonna sul foglio excel invece che come numerico float o reale, viene vista come data!

<< 

CS_SP_DMOExportToExcel @SourceServer=null,@QueryText='use MTSCClim SELECT CAST(CAST(VALORE As float)/10 AS float) AS Valore From R1TEMPRIF', @filename='C:\TestProva.xls', @WorksheetName='Riferimento', @WorksheetIndex=3

>>

Hai qualche idea? Lo stile dei dati per la CONVERT o la CAST mi va bene quello di default, solo che excel non li vede così come li vedo io lanciando la SELECT da SQL.

Grazie ancora!

 Michele

Ciao,

per il momento prova a sostituire, nella stored procedure, 'GetColumnString' con 'GetColumnFloat' nella chiamata a sp_OAMethod:

EXEC @hr = sp_OAMethod @objQueryResults, 'GetColumnString', @value OUT, @ResultSetRow, @currentColumn

Nei prossimi giorni provo di postare qualcosa di più strutturato ...

Ciao!

Sergio

Ciao Sergio

ti ho abilitato su www.ugiss.org come Editor.

Segui le brevi istruzioni presenti un questo video:

http://video.msn.com/video.aspx?vid=8090895c-17bb-4530-979f-ae502a5d9964 

e via! :-)

dmauri:

ti ho abilitato su www.ugiss.org come Editor

Grazie Davide.

Per Michele: prova questa versione della stored procedure, dovrebbe risolvere la tua esigenza.

Ciao!

OTTIMO il salvataggio su file excel :) 
Stavo testano lo script, ma ho sempre questo problema da cosa può dipendere?
 

-2147211483 ODSOLE Extended Procedure                                                                                                                                                                                                                                       Utilizzo di sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, parametri IN, OUT o BOTH aggiuntivi]]                                                                                                                              NULL                                                                                                                                                                                                                                                            0

Messaggio 50000, livello 16, stato 1, procedura CS_SP_DMOExportToExcel, riga 416
Error whilst Creazione dell'applicazione Excel su stefano, Utilizzo di sp_OAMethod: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, parametri IN, OUT o BOTH aggiuntivi]]
Return Value
------------
-2147211483

(Righe interessate: 1)

Ciao Stefano,

prova a verificare se è attivo il supporto OLE Automation in SQL Server 2005 Surface Area Configuration for Features. Se è attivo, prova a postare la chiamata alla stored procedure.

Ciao!

Ciao,

Controlla pure se hai istanze di Excel aperte fra i processi, oppure se hai il file già aperto.

 Michele