Gianni Giaccaglini

Tricks & mini applics on WPF
posts - 46, comments - 0, trackbacks - 0

Prime prove con Excel Automation

 

Prime prove con Excel Automation

Dopo il sottostante articolo dedicato all’utilizzo di (OLE) Automation per gestire messaggi Outlook ho pensato di rivisitare tale tecnologia con Excel. Dico rivisitare perché l’avevo già fatto temporibus illis in ambiente VBA (le macro di Office). Ed ecco qui una serie di test elementari e affetti da rozzezze anche ignobili ma, al di là di queste renzarboresche esternazioni, abbastanza significativi.

Anticipo infatti che, tra l’altro:

·         Si possono creare ex-novo cartelle di lavoro (Workbook) o aprirne di preesistenti sfruttando la potenza del foglio di calcolo nonché – cosa che non ho fatto ma che lascio come non arduo esercizio agl’interessati – lanciare la grafica di Excel magari partendo da dati WPF (o Form);

·         Si può riutilizzare codice VBA, anche se con qualche adattamento;

·         Anche i patiti del C#, udite udite!, possono farlo a parte le graffe così care alla gens cancellettofila per il semplice motivo che la sintassi delle proprietà e metodi esposti dalla libreria Excel è identica;

·         Per certi versi si emulano i VSTO (Visual Studio Tools per Office) evitando, se non altro, le complicanze di tali pur potenti arnesi (tra il macchinoso deployment) e qualche limite come le funzioni personalizzate che restano affidate… al VBA (nonché, o forse sbaglio?, il supporto parziale a finestre WPF…).

Osservo infine all’ultimo SMAU ho notato una software house produttrice di un noto pacchetto gestionale che supporta fogli Excel. Da quel che ho capito, hanno proprio sfruttato Automation e non VSTO.

Le prove pratiche

Una premessa è doverosa. La proposta che segue, lungi dal costituire un insieme organico, è solo un’accozzaglia di tre semplici esempi tipici e non mi sono minimamente preoccupato di prevenire errori. Pertanto raccomando come Figaro di utilizzare i tre pulsanti uno alla volta per carità e nell’ordine.

Si crei una griglia Grid di due righe, la prima, più ampia, comprende una grossa TextBox1 caratterizzata dalla proprietà AllowDrop=”True” che la rende multiline. Nella seconda riga si hanno tre pulsanti Button1, Button2 e Button3, il cui Content è rispettivamente “Prima prova”,“Seconda prova e “Terza prova”.

Mi fermo qui, riportando qui di seguito senza superflui commenti la

Struttura XAML

<Window x:Class="MainWindow"

  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

  Title="MainWindow" Height="350" Width="525">

  <Grid>

  <Grid.RowDefinitions>

    <RowDefinition  Height="250" />

    <RowDefinition />    

  </Grid.RowDefinitions>

  <TextBox Height="129" HorizontalAlignment="Left" Margin="12,121,0,0" Name="TextBox1"

     VerticalAlignment="Top" Width="487" AllowDrop="True" />

  <Button Content="Prima prova" . . . Grid.Row="1"  />

  <Button Content="Seconda prova" . . . Grid.Row="1" />

   <Button Content="Terza prova" . . . Grid.Row="1" />

  </Grid>

</Window>

Nella quale le proprietà relative al posizionamento dei pulsanti sono state sostituite da puntini per brevità.

Prima prova su un Workbook una tantum

Comincio con le righe iniziali del modulo MainWindow.xaml.vb:

Imports Microsoft.Office.Interop.Excel

Class MainWindow

  Private mioExcel As Microsoft.Office.Interop.Excel.Application

  Private Property Numcelle As Long

 

La direttiva Imports funziona, non guasta rammentarlo a distratti & immemori, se e solo se nel progetto si è fissato il riferimento alla libreria Microsoft Excel 14.0 (o 12.0 se il nostro PC ospita l’edizione precedente). Quindi la classe MainWindow esordisce con variabili comunitarie, la più importante essendo mioExcel che ci evita nel seguito di ricorrere alla chilometrica Microsoft.Office.... eccetera eccetera.

Nota. Nel seguito comunque non può essere evitata la noia della continua anteposizione di mioExcel, che nel mondo VBA viene sottinteso.

Il codice VB del primo pulsante è riportato qui sotto.

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Button1.Click

    Static i As Integer

    If mioExcel Is Nothing Then

      mioExcel = New Microsoft.Office.Interop.Excel.Application

      mioExcel.Visible = True

      mioExcel.Workbooks.Add()

      mioExcel.Range("A1:C4").Select()

      mioExcel.ActiveCell.Value = "Buongiorno a tutti!"

    Else

      Dim Dimmi = InputBox("Come ti chiami?")

      ' MessageBox.Show(Dimmi) ' Servito per debug

      TextBox1.Text = += Dimmi & vbLf ' =Dimmi (variante)

      i += 1

      mioExcel.Range("A1").Offset(i).Value = Dimmi ' TextBox1.Text (variante)

    End If

    If i < 5 Then Exit Sub

    If MessageBox.Show("Fine lavoro?", "", _

       MessageBoxButton.YesNo) = MessageBoxResult.Yes Then

      mioExcel.DisplayAlerts = False

      mioExcel.ActiveWorkbook.Close()

      mioExcel.Quit() ' Abbandona Excel

      mioExcel = Nothing

    End If

  End Sub

Va subito detto che l’istruzione
MioExcel = New Microsoft.Office.Interop.Excel.Application

crea una nuova istanza di Excel che, a differenza di quanto accade nell’uso normale, è invisibile e priva della cartella default. Il clic sul Button1 si preoccupa di visualizzare Excel e di aggiungervi un Workbook, col metodo Add applicato all’insieme Workbooks (inizialmente vuoto).

Delle altre istruzioni cito solo
mioExcel.Range("A1").Offset(i).Value = Dimmi

che sfrutta celle sfalsata mano a mano di un valore i (.Offset(i)) rispetto alla cella iniziale A1 per inserirvi i successivi valori immessi dall’utente con la vetusta InputBox (chiedo venia ai puristi) mentre in parallelo questi sono infilati in linee successive della casella di testo.

Varianti di questo banalissimo esercizio, come il trasferimento di una serie di dati presenti a vario titolo nella Window WPF, sono lasciate al lettore. Mi limito a sottolineare l’importanza delle ultime istruzioni che dovrebbero chiudere ogni sessione Automation, atte a chiudere Excel e a liberare l’impegno della variabile specifica mioExcel. Si noti infine che la proprietà DisplayAlerts = False evita il solito invito a salvare le modifiche prima di chiudere questo file di sciocchezzuole estemporaneo (dal nome default Cartel1) che non andrà su disco.

Seconda prova su un Workbook una tantum con formule al volo

Si tratta di una variante più complessa del caso precedente. Anche qui le azioni si esercitano su un foglio nuovo, vuoto, che la routine non solo riempie di dati prelevati da semplici Vettori – Intest, Codici, Prodotti e Prezzi – ma anche di formule semplici ma inserite programmaticamente.

Inoltre l’istruzione
mioExcel.Visible = True

viene lanciata alla fine, in guisa tale che soltanto allora l’utente vede non di nascosto il risultato del lavoro in background (con qualche vantaggio velocifero).

Riporto senza indugi qui di seguito il codice V B del secondo pulsante.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles Button2.Click

    Dim Intest = {"COD", "Prodotto", "Quantità", "Prezzo", "Importo"}

    Dim Codici = {"AA", "BB", "CC", "DD", "EE", "FF"}

    Dim Prodotti = {"Mele", "Pere", "Susine", "Pesche", "Arance", "Banane"}

    Dim Prezzi = {3.5, 5, 4, 4.5, 3.2, 3}

    If mioExcel Is Nothing Then

      mioExcel = New Microsoft.Office.Interop.Excel.Application

      ' mioExcel.Visible = True => Le operazioni si svolgono nell'ombra!

      mioExcel.Workbooks.Add()

    End If

    For i = 0 To Intest.Length - 1

      mioExcel.Range("A1")(1, i + 1).Value = Intest(i)

    Next

    For i = 0 To Codici.Length - 1

      With mioExcel.Range("A2")(i + 1)

        .Value = Codici(i)

        .offset(0, 1).Value = Prodotti(i)

        .Offset(0, 3).Value = Prezzi(i)

      End With

    Next

    For i = 0 To Codici.Length – 1

      ' Chiedi all’utente ripetutamente i prezzi dei vari frutti

      Dim Quant = CDbl(InputBox("Quantità di " & Prodotti(i) & "?"))

      mioExcel.Range("C2")(i + 1).value = Quant

    Next

    Numcelle = 0

    With mioExcel.Range("A2")

      Numcelle = mioExcel.Range(.Cells(1), .End(XlDirection.xlDown)).Count

    End With

    With mioExcel.Range("E2")

      Dim ZonaFormule = mioExcel.Range(.Cells(1), .Cells(Numcelle))

      ZonaFormule.FormulaR1C1 = "=RC[-2]*RC[-1]"

    End With

    mioExcel.Visible = True

    mioExcel = Nothing

  End Sub

 

L’effetto che fa, omettendo le intestazioni di colonna (A, B, C, ecc.) e di riga (1, 2, 3, ecc.) è del tipo seguente:

COD

Prodotto

Quantità

Prezzo

Importo

AA

Mele

3

3,5

10,5

BB

Pere

5

5

25

CC

Susine

6

4

24

DD

Pesche

7

4,5

31,5

EE

Arance

2

3,2

6,4

FF

Banane

3

3

9

 Qui parlo a chi ha un minimo di familiarità con Excel e, anche per mia innata indolenza, mi limito a dire che le istruzioni più pregiate sono in grassetto e marcate in giallo. La prima individua il numero NumCell di celle contigue da A1 in giù (*) mentre la seconda inserisce al volo, secondo il formato RC, le formule nell’intervallo di destra da E2 in giù, ossia di fatto (e anteponendo con qualche arbitrio i riferimenti all'"=" delleformule):
E2=C2*D2
E3=C3*D3
E4=C4*D4
……

Nota. I curiosi e spericolati saranno tentati di sperimentare il cut & paste da celle Excel a un contenitore WPF come con una semplice TextBox. Constateranno che anche a mano è possibile, ma solo sulla prima cella dell’intervallo preselezionato. Chi trova soluzioni più potenti si faccia vivo…

Terza prova con file preesistente

Stavolta si agisce su un file Excel presente già sul disco e di tipo un po’ particolare.

Codice VB del terzo pulsante

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles Button3.Click

    If mioExcel Is Nothing Then mioExcel = New Microsoft.Office.Interop.Excel.Application

    mioExcel.Workbooks.Open(Filename:="C:\FormuleAlVolo.xlsx")

    mioExcel.Visible = True

    ' MessageBox.Show("Adesso copio la riga formule in basso")

    Dim Nr As Integer

    With mioExcel.Range("A2")

      Nr = mioExcel.Range(.Cells(1), .End(XlDirection.xlDown)).Count

    End With

    With mioExcel.Range("RigaFormule")

      .Copy(mioExcel.Range(.Cells(2, 1), .Cells(Nr, 1)))

    End With

    mioExcel = Nothing

  End Sub

 

In questo caso il metodo Open dell’insieme WorkBooks apre un file FormuleAlVolo.xlsx e si osservi subito che l’estensione xlsx lo caratterizza come archivio privo di macro. Esso all’apertura si presenta, al netto di intestazioni di righe e colonne, come segue:

COD

Prodotto

Quantità

Prezzo

Sconto

Importo

Sconto

Netto

AA

Mele

     10,00

   3,50

5%

    35,00

    1,75

  33,00

BB

Pere

     12,00

   5,00

10%

 

 

 

CC

Susine

       5,00

   4,00

2%

 

 

 

DD

Pesche

       4,00

   4,50

5%

 

 

 

EE

Arance

       3,00

   3,20

10%

 

 

 

FF

Banane

       2,00

   3,00

5%

 

 

 

 

Le celle della zona F2:H2 cui è stato dato il nome “RigaFormule” contengono le formule seguenti (con l'anteposizione arbitraria vista sopra):
F2=C2*D2
G2=F2*E2
H2=ARROTONDA(F2-G2;0)

Palesemente esse danno, nell’ordine, l’importo, lo sconto e il netto, con arrotondamento della loro differenza. Le istruzioni sopra evidenziate sono varianti della copia di formule ma di un certo pregio, con particolare riguardo per il metodo Copy con argomento Destination che esegue l’operazione anche “a distanza”, cioè qualunque sia la cella attiva corrente e senza dover preselezionare l’origine come nelle operazioni manuali.

Nota. Chi ha pratica di VBA noterà poi che la vecchia sintassi <Origine>.Copy  <Destinazione> in ambito .NET diventa <Origine>.Copy(<Destinazione>) con tanto di parentesi (comunque inserite d’ufficio, as usual).

Un’ultima considerazione è sul fatto che, come testé anticipato, il codice .NET agisce tranquillamente pure con archivi che Microsoft ha decretato come insensibili alle macro in contrapposizione ai tipi .xlm (distinzione che odio nata dal terrorismo dei virus-macro).

Ovviamente dopo queste routine la classe MainWindow termina con

End Class

 

Ultima prova: un possibile binding artigianale

Quest’ultima chicca un po’ avanzata si propone di pescare dati da celle di un foglio Excel il cui layout è noto. Per sperimentarla si crei un quarto pulsante etichettato per eccesso di fantasia “Quarta prova”. Lo specifico evento Click dovrà agire sul file della terza prova trasferendo in due vettori i vari prodotti e i relativi importi netti. Il codice rispecchia in gran parte quello della prova precedente , con evidenziazione delle varianti del caso:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles Button4.Click

    If mioExcel Is Nothing Then mioExcel = New Microsoft.Office.Interop.Excel.Application

    mioExcel.Workbooks.Open(Filename:="C:\FormuleAlVolo.xlsx")

    ' mioExcel.Visible = True ' Eliminato di proposito

    Dim Nr As Integer

    With mioExcel.Range("A2")

      Nr = mioExcel.Range(.Cells(1), .End(XlDirection.xlDown)).Count

    End With

    With mioExcel.Range("RigaFormule")

      .Copy(mioExcel.Range(.Cells(2, 1), .Cells(Nr, 1)))

    End With

    ' Binding artigiano dati che interessano --> due vettori

    Dim VettFrutti() As String

    Dim VettNetti() As Double

    ReDim VettFrutti(Nr) : ReDim VettNetti(Nr)

    For i = 0 To Nr - 1

      VettFrutti(i) = mioExcel.Range("B2")(i + 1).value

      VettNetti(i) = mioExcel.Range("H2")(i + 1).value

    Next

    With mioExcel

      .DisplayAlerts = False

      .ActiveWorkbook.Close()

      .Quit()

    End With

    mioExcel = Nothing

  End Sub

 

Stavolta si rinuncia alla visibilità del file e, al termine, si chiudono senza complimenti il workbook ed Excel. Quanto a VettFrutti e VettNetti essi conterranno le varie mele, pere, susine ecc. e gl’importi netti relativi di colonna H. Va da sé che tali dati in un’applicazione seria serviranno per ulteriori elaborazioni.

Non aggiungo altro, sicuro che i buoni intenditori troveranno alternative di loro gusto, come il ricorso a liste e simili o l’inserimento dei dati “bindati” così alla buona direttamente in controlli tipo ListBox e quant’altro.

Piuttosto farei due considerazioni finali derivanti dal fatto che questi interventi potrebbero scandalizzare i più esperti e amanti delle moderne tecniche binding ben supportate da WPF.

- Il binding con Excel è una sfida al limite dell'impossibile, trattandosi di fogli multipli e sovente assai poco strutturati (formule, celle sparpagliate, a volte tabelle sì ma collocate su un medesimo foglio di lavoro. In passato ho dato un contributo piuttosto advanced sul sito VB Tips&Tricks ottenendo un Visore di fogli Excel  anche "sparpagliati" con un Form che li attinge sfruttando il formato Open XML. A parte il fatto che quest'ultimo NON esiste coi tradizionali e tuttora molto comuni archivi .xls anche questo metodo fallisce in presenza di macro che generano dati e formule "al volo" come nell'esempietto mostrato sopra.

- Piuttosto sarebbe interessante il know how necessario per il binding da un eventuale foglio Excel strutturato, ovvero formato esclusivamente da una tabella o da un archivio di formato tradizionale come il Comma Separated Values (.csv) o l'analogo che sfrutta delle tabulazioni come separatore di campi. E l'adozione del controllo DataGrid sarebbe benvenuta...


Nota. Al sottoscritto piacerebbe farlo per la gestione di circolari Outlook (v. post precedente), pertendo da archivi relativi ai contatti, che Microsoft Outlook  esporta, guardacaso, solo nei formati appena detti...


La letteratura abbonda di esempi,  complessi ma comunque ben descritti, ma ahimè tutti relativi a database Access o Sql Server. Sulla carta con archivi più elementari, come il dannato csv, il binding dovrebbe essere più semplice. Chi sa parli, ovvero scriva un tutorial ad hoc per i comuni mortali.

giannigiac@tin.it

Print | posted on giovedì 23 febbraio 2012 20:51 |

Feedback

No comments posted yet.

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 1 and 5 and type the answer here:

Powered by:
Powered By Subtext Powered By ASP.NET