Interfacciare Excel da VB. - pagina vista: volte

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.

Articolo pubblicato per gentile concessione del sito Visual Basic Tips & Trick

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.
 

Ringrazio Maurizio Brasca ed il sito www.visual-basic.it (che invito caldamente a visitare) per il materiale gentilmente concesso.

 

prelevato sul sito www.ennius.altervista.org