Il web è veramente
pieno di soluzioni a nostri problemi più o meno quotidiani. Alcuni mi
avevano chiesto chiarimenti circa la possibilità di utilizzare cartelle o
fogli di Excel in applicazioni tipiche VB, e non avendo l'esperienza
necessaria, non avevo potuto fornire suggerimenti. Ecco però un'articolo
scritto da Maurizio Brasca
che risponde al problema, e che riporto integralmente.
Interfacciare Excel da VB
a cura di
Maurizio Brasca
(requisiti:
conoscenza fondamenti OLE)
Premessa in difesa di Excel
Da molti anni Microsoft suscita nella comunità degli sviluppatori
sentimenti contrastanti, dall'amore tenero all'odio viscerale.
E molti considerano Microsoft come Sodoma e Gomorra, in attesa della
distruzione da parte dell'Entità Superiore.
Ma anche Dio, prima di distruggere Sodoma e Gomorra, propose di
risparmiarle se vi si fosse trovato un solo giusto (senza peraltro
ottenerlo).
E se noi volessimo trovare anche un solo motivo per risparmiare la
Microsoft un nome si farebbe avanti prepotente: Excel.
A mio giudizio Excel è un vero piccolo
miracolo dell'informatica: potente, versatile, intuitivo, in una sola
parola "vincente".
Lo prova la totale assenza di concorrenti che possano anche lontanamente
paragonarglisi, sia dentro Win-Sodoma che nei grandi pascoli dell'open
source.
E se per l'utente finale Excel è
diventato nel tempo uno strumento di lavoro insostituibile, per gli
sviluppatori è un'occasione sensazionale per ottenere risultati di tutto
rispetto, sia come ambiente per piccole applicazioni personalizzate per
l'uso di ufficio, sia come piattaforma sofisticata per applicazioni di
reporting.
Non è questo il luogo per enfatizzare tutti i plus di Excel per uno
sviluppatore (magari lo faremo in seguito); basti la considerazione che
per più di un buon motivo uno sviluppatore Visual Basic dovrebbe guardare
ad Excel come ad un partner ideale.
Infatti con l'avvento del VBA e
dell'automazione OLE Excel ha messo tutto se stesso a disposizione di chi,
come noi, ha una sufficiente cultura VB-oriented.
Per utilizzare le risorse messe a disposizione da Excel si possono
utilizzare tecniche diverse, quali:
- scrivere macro (sub e funzioni) in VBA
- pilotare Excel tramite OLE automation da programma VB (magari VB6)
- accedere ad Excel come se fosse un database, magari tramite ADO
La prima soluzione è estremamente
flessibile e "vicina" ad Excel, essendo il codice eseguito da esso stesso,
conservando un'elevata compatibilità con VB6; per contro il codice è
interpretato e pertanto "visibile", nonostante i palliativi di password
facilmente violabili con un minimo di esperienza.
Inoltre l'applicazione VBA parte da Excel e ne condivide l'interfaccia
utente, soluzione che spesso non è gradita al programmatore.
La seconda soluzione invece consente di
produrre un eseguibile compilato e di utilizzare le soluzioni messe a
disposizione da VB6 in termini di interfaccia utente in maniera molto più
efficace.
Purtroppo, però, l'automazione OLE con Excel è sconsolantemente lenta, e
spesso instabile.
E la terza soluzione, secondo me,
svilisce le possibilità di dialogo con Excel, limitandosi a considerarlo
un "luogo di dati" e non un "luogo di metodi".
In questo articoletto voglio proporre una
soluzione di compromesso, che consenta di ottenere la compattezza,
sicurezza e versatilità del VB6 ed una velocità di colloquio con Excel
molto elevata, ancor più di quella ottenibile con il VBA.
Nel corso della lettura emergerà che "il trucco c'è"; credo però che
nonostante tutto la soluzione proposta, pur non potendo risolvere tutta la
casistica proposta dagli sviluppatori, possa essere utile in molti casi.
La via normale
Ipotizzeremo un caso molto semplice, che illustri facilmente i risultati
ottenibili; la soluzione proposta, però, si applica a qualsiasi utilizzo
dell'automazione di Excel, anche le più sofisticate.
Immaginiamo di dover riempire un'area di un foglio, assegnando valori
diversi ad ogni cella al punto di rendere indispensabile un singolo
assegnamento per ogni cella.
Nel nostro esempio vogliamo riempire un rettangolo di 30 colonne per 100
righe con un testo del tipo : "Cella " & Riga & "," & Colonna.
Si tratta di eseguire 3000 assegnamenti, ovvero utilizzare 3000 volte
l'oggetto CELLS.
Un semplice esempio di codice basato
sull'automazione OLE potrebbe essere il seguente:
Dim c As Integer, r As Integer
Dim Xl As Excel.Application
Set Xl = GetObject(, "Excel.Application")
With Xl.Workbooks(1).Worksheets(1)
For r = 1 To 100
For c = 1 To 30
.Cells(r, c) = "Cella " & r & "," & c
Next
Next
End With
Set Xl = Nothing
Come si può notare mi riferisco genericamente al primo foglio del primo
workbook presente.
Inoltre do per scontato, oltre al fatto che la libreria di Excel sia
referenziata al progetto, anche che Excel sia già in esecuzione,
limitandomi ad ottenere tramite GetObject un oggetto Application,
e tramite quest'ultimo un oggetto WorkSheet.
E' abbastanza intuitivo che questa iterazione risulta particolarmente
lenta se eseguita tramite l'automazione OLE.
La via diversa
A questo punto introduciamo l'idea.
Al posto di un progetto "Standard EXE" approntiamo un "Activex DLL".
Definiamo:
Public Xl As Excel.Application
e replichiamo sostanzialmente il codice
di prima per l'assegnamento delle celle (tranne ovviamente le Dim).
Poi definiamo la classe Class1 (bello
come nome, no?) con un solo metodo:
Public Function PassaExcel(X As Object)
Set Xl = X
End Function
Scopo di questo metodo è che 'qualcuno',
che accetti di far girare la nostra DLL nel proprio processo, possa
anche passarle un oggetto Excel.Application, il quale venga
memorizzato in una variabile pubblica.
Ovvio che a questo punto avete capito che questo 'qualcuno' sarà Excel
stesso.
Compiliamo la DLL, che chiameremo
CdProvaXl.dll (Classe di Prova Excel, per i più curiosi che vogliono
conoscere il perché di un tale nome, invece di preoccuparsi di capire il
resto dell'articolo).
Creiamo una cartella di lavoro (WorkBook)
Excel che funga da "cavallo di Troia".
Tramite il menu Strumenti/Riferimenti
dell'IDE del VBA di Excel referenziamo CdProvaXl.dll, riscontrando
poi tramite il Visualizzatore Oggetti la presenza della mitica
classe Class1 con il suo metodo PassaExcel.
Quindi nell'evento Open del
Workbook scriveremo qualcosa di simile:
Private Sub WorkBook_Open()
Dim Cicco As CdProvaXl.Class1
Set Cicco = New CdProvaXl.Class1
Cicco.PassaExcel Application
Set Cicco = Nothing
End Sub
Come? Perché "Cicco"? Ma allora non posso
usare le variabili che mi pare?
Beh, metteteci quella che volete; in ogni caso il cavallo di Troia (il
codice nella Open) serve solo ad istanziare la nostra DLL ed a
passarle l'oggetto Application.
Tutto qui: se si fa in modo che la DLL
esegua il codice che assegna le 3000 celle, si rileveranno tempi
strepitosamente minori rispetto a quelli dello Standard EXE.
Ho allegato due progetti (uno per l'EXE e uno per la DLL), scaricabili
dall'Area Download.
Entrambi espongono una Form con un
CommandButton che avvia l'assegnamento ed una Label per
rappresentare il tempo di esecuzione.
Lascio che ognuno rilevi i tempi per proprio conto; vi preannuncio con
assoluta sicurezza però che la differenza fra le due soluzioni è realmente
notevole.
La Form espone anche una
CheckBox "Screenupdating".
Serve per assegnare la proprietà Screenupdating di Xl (per
chi lavora con VBA è la (famosa?) Application.Screenupdating).
Ne accenno come suggerimento per chi non la conoscesse, in quanto può
riservare piacevolissime sorprese in termini di velocità.
Assegnandole il valore True (spuntando la CheckBox) Excel fa
sì che ogni modifica effettuata ad uno dei suoi oggetti comporti
l'aggiornamento automatico del video (è il valore di default).
Viceversa assegnandole il valore False tutti i cambiamenti non
produrranno alcun effetto a video.
Scegliendo quindi una soluzione del tipo:
Xl.ScreenUpdating = False
'...
' assegnamenti agli oggetti di Excel
'...
Xl.ScreenUpdating = True
Excel non effettuerà alcun aggiornamento
del video durante i 3000 assegnamenti, limitandosi ad un solo
aggiornamento al termine; la differenza di velocità è notevole.
Considerazioni finali
In una Activex DLL non si può fare tutto quello che si può fare in
uno Standard EXE (lascio a chi è esperto l'eventuale descrizione
dei due contesti).
Ciò nonostante, in una DLL si può fare moltissimo (ad esempio Form
a piacere) e spesso, se si sta già pianificando un'integrazione con Excel,
si può trovare un contesto adeguato che consenta l'uso della DLL.
Questa soluzione è veloce in quanto non
fa uso di automazione OLE strettamente detta.
La DLL, infatti, viene eseguita nel processo di Excel ed accede
direttamente all'oggetto Excel.Application che le viene passato
come riferimento tramite il metodo PassaExcel.
La cartella di lavoro che funge da
cavallo di Troia non è un oggetto ingombrante: al termine del suo
semplicissimo utilizzo può benissimo essere scaricata automaticamente da
Excel senza compromettere il funzionamento del trabiccolo.
Può essere eseguita, ad esempio, da un altro Standard EXE tramite
l'API ShellExecute, costituendo così uno schema più articolato a
potente.
Chi non è avvezzo a lavorare con le DLL
non si lasci impressionare: non è faticoso.
Pur senza voler uscire dall'argomento dell'articolo, sottolineo come la
DLL può essere debuggata tramite l'IDE di VB6 mandandola in esecuzione (Ctrl-F5)
e referenziando nel foglio cavallo di Troia il VBP anziché
la DLL (vedrete che è proprio così).
E' probabile che seguano altri
articoletti sulla programmazione di Excel, che spero incoraggino molti ad
ibridare "il più grande prodotto software di tutti i tempi" (la
citazione è mia) con il Visual Basic in tutte le varianti.
Relativamente a questo articolo potete
chiedere chiarimenti o comunicare suggerimenti e/o commenti (non
contumelie) all'autore, Maurizio
Brasca, di cui trovate qualche notizia in calce all'articolo
Realizzazione di un sistema multiprocesso in VB6.