Estrarre dati complessi da un file XML prodotto da Excel
a cura di Diego Cattaruzza (requisiti: nessuno)

Premessa
Questo articolo nasce dal desiderio di studiare il modo di risolvere con VB.Net un problema risolto con VB6.
Nei giorni scorsi, in lista è passato un messaggio, in cui veniva posto il problema di trasferire dati da un file XML a un database, via ADO.
Nel thread è stato allegato anche il file XML.

Analisi del problema
Il vero ostacolo, nella soluzione, sta dal fatto che, come potete vedere in figura, ogni foglio della cartella in esame contiene due tipi diversi di dati: quelli (riga 2) che identificano il foglio e tutti gli altri (dalla riga 5 in poi) che costituiscono le rilevazioni.
Inoltre ci sono le righe di intestazione, nonché, nel file XML, informazioni relative al formato e ad altro, che non vanno trasferite nel database, ovviamente.

In buona sostanza, in questo caso, bisogna ottenere l'insieme dei fogli della cartella, di ciascun foglio bisogna estrarre la 'tabella', della quale ci interessano la seconda riga di quelle con tre caselle non vuote, e tutte le righe tranne la prima di quelle con quattro caselle non vuote.

Risulta quindi evidente (almeno a me) che bisogna non solo leggere i dati, ma discernerne quelli da estrarre. E per effettuare tale discriminazione bisogna leggere le righe una a una.
Mi sembra inoltre chiaro che bisogna scrivere i dati in due distinte tabelle, tra loro correlate: una tabella per i fogli, con le tre informazioni e un id, e una per le rilevazioni, con le quattro informazioni opportunamente tipizzate e l'id del foglio di riferimento.

La soluzione in VB6
Sulla base dell'analisi sopra riassunta ho sviluppato un paio di procedure, referenziando a un progetto le librerie Microsoft XML, v6 e ADO 2.8 (penso comunque che funzionerebbero anche librerie con versioni meno recenti).
Sono disponibili entrambe nel messaggio di risposta, ma riporto qui quella che scandisce il file XML, mentre quella che scrive i record con oggetti Command parametrizzati è fuori contesto, in questo articolo.

Private Sub LeggiXML()
  Dim document As New DOMDocument, workbook As IXMLDOMElement
  Dim worksheets As IXMLDOMNodeList, wks As IXMLDOMNode
  Dim table As IXMLDOMNode, rows As IXMLDOMNodeList, row As IXMLDOMNode
  Dim nodo As IXMLDOMNode

  Dim dati(3) As String, i As Integer

  document.Load filexml ' carico il file
  Set workbook = document.documentElement ' localizzo la root
  Set worksheets = workbook.childNodes ' ottengo l'elenco dei fogli

  For Each wks In worksheets ' lo scandisco
    If wks.baseName = "Worksheet" Then ' non sono tutti worksheet
      Set table = wks.firstChild ' ottengo l'unico nodo del foglio
      Set rows = table.childNodes ' ottengo l'insieme delle righe

      For Each row In rows ' per ciascuna di esse
        ' verifico che sia row e che abbia nodi figli
        If row.baseName = "Row" And row.hasChildNodes Then
          ' a seconda del numero dei figli, i dati appartengono
          ' alla prima o alla seconda tabella
          'per la prima tabella ci sono sempre solo due righe
          ' quella dei titoli e quella dei dati
          If row.childNodes.length = 3 And _
              row.childNodes(0).Text = "POD" Then
            ' passo alla riga seguente e leggo i dati
            Set nodo = row.nextSibling
            For i = 0 To 2
              dati(i) = nodo.childNodes(i).Text
            Next
            ' li scrivo nella prima tabella
            Scrivirecord dati, 1
          Else
            ' per la seconda tabella, scarto la riga coi nomi di campo
            If row.childNodes.length = 4 And _
                row.childNodes(0).Text <> "Giorno" Then
              ' leggi i dati del record
              For i = 0 To 3
                dati(i) = nodo.childNodes(i).Text
              Next
              ' li scrivo nella seconda tabella
              Scrivirecord dati, 2
            End If
          End If ' hasChildNodes
        End If ' is row
      Next ' row
    End If ' is worksheet
  Next ' wks

  Scrivirecord dati, 0 ' chiude e annienta gli oggetti statici

  ' anniento tutti gli oggetti istanziati
  Set nodo = Nothing
  Set row = Nothing
  Set rows = Nothing
  Set table = Nothing
  Set wks = Nothing
  Set worksheets = Nothing
  Set workbook = Nothing
  Set document = Nothing
End Sub

La prima soluzione in VB.Net
In VB.Net abbiamo a disposizione, al posto del DOMDocument e compagnia, un intero Namespace, System.Xml, di cui è una branca LINQ, lo studio del quale è il motivo per cui mi sono messo a cercare di risolvere in VB.Net il problema già risolto in VB6.

Aperto un nuovo progetto, non è necessario referenziare alcuna libreria, perché sia XML che Data sono referenziate di default.

Ho dapprima sviluppato il codice sulla falsariga dello stesso ragionamento fatto con VB6, facendo un passo alla volta ed esaminando, riga per riga, i risultati ottenuti nella finestra Variabili Locali, onde rendermi conto di ciò che dovevo leggere o scartare. Infatti non conoscevo ancora gli oggetti del Namespace XML, avendo finora, per lo più, serializzato e deserializzato altri oggetti, senza curarmi di dover decifrare un file XML non prodotto da una mia applicazione.
Ecco quindi la procedura, che commento nel seguito:

  Private Sub ElaboraXMLfile1(ByVal xmlfilepath As String)
    Dim data(3) As String
    Dim doc As XElement = XElement.Load(xmlfilepath)

Poichè i file.xml da leggere (dello stesso tipo, ovviamente) potrebbero essere più di uno, ho munito il metodo di un parametro per il percorso-nome del file.
Poiché i dati sono o tre o quattro, uso un vettore di quattro elementi.
Ho inoltre scelto di leggere il file, anziché in un XDocument, in un XElement, perché tale tipo esponeva la proprietà XElements.
All'inizio, infatti, non m'ero accorto che potevo usare, al posto di XML.XDocument, XML.LINQ.XDocument, e ho tribolato un po' con Nodes e ChildNodes e affini, cercando di isolare le righe che mi servivano.

Come nel codice VB6, scorro il file XML per via gerarchica, passando da un insieme a un sottoinsieme, a un sottoinsieme di questo e così via, fino a isolare i dati che devo trasferire nel database.
I commenti nel codice che segue dovrebbero essere sufficienti a comprendere i passi:

  Private Sub ElaboraXMLfile1(ByVal xmlfilepath As String)
    Dim data(3) As String ' il vettore dei dati da leggere
    Dim doc As XElement = XElement.Load(xmlfilepath)
    ' si scorre l'oggetto doc livello per livello
    ' prima l'insime dei fogli
    Dim worksheets As IEnumerable(Of XElement) = doc.Elements
    ' poi di ciascun foglio
    For Each wks As XElement In worksheets
      ' si verifica che sia <Worksheet>
      If wks.Name.LocalName = "Worksheet" Then
        ' del quale si estrae l'unico elemento, la <table>
        Dim table As XElement = DirectCast(wks.FirstNode, XElement)
        ' della quale si estraggono le righe
        Dim rows As IEnumerable(Of XElement) = table.Elements
        ' di ciascuna riga
        For Each row As XElement In rows
          ' si verifica sia <row>
          If row.Name.LocalName = "Row" Then
            ' e si contano gli elementi che contiene
            ' se ne contiene tre, sono gli identificativi del foglio
            ' quindi si legge solo la seconda riga
            If row.Elements.Count = 3 And Not row.Value.StartsWith("POD") Then
              ' della quale si estraggono gli elementi
              Dim cells As IEnumerable(Of XElement) = row.Elements
              ' con il Value di ciascuno dei quali valorizzo il vettore dei dati
              For i As Integer = 0 To 2
                data(i) = cells(i).Value
              Next
              ' passo i dati al metodo per scrivere nella prima tabella del database.
              Scrivirecord(data, tabella.Tabella1)

              ' se invece gli elementi sono 4, sono le rilevazioni,
              ' e si salta la riga di intestazione
            ElseIf row.Elements.Count = 4 And Not row.Value.StartsWith("Giorno") Then
              ' di ogni riga si estraggono gli elementi
              Dim cells As IEnumerable(Of XElement) = row.Elements
              ' con il Value di ciascuno dei quali valorizzo il vettore dei dati
              For i As Integer = 0 To 3
                data(i) = cells(i).Value
              Next
              ' passo i dati al metodo per scrivere nella prima tabella del database.
              Scrivirecord(data, tabella.Tabella2)
            End If
          End If
        Next
      End If
    Next
    ' passo al metodo il parametro per annientare gli oggetti statici.
    Scrivirecord(data, tabella.Chiusura)
  End Sub

Per dirigere il comportamento del metodo ScriviRecord, faccio uso di un tipo enumerato, rendendo il codice più leggibile. Inoltre, esso è Private, dato che serve solo in questa classe. (Così, ho sperimentato che anche i tipi enumerati possono essere Private e ugualmente utili - argomento di un altro thread di messaggi passati in lista di recente).

  Private Enum tabella
    Chiusura = 0
    Tabella1 = 1
    Tabella2 = 2
  End Enum

  Private Sub Scrivirecord(ByVal data As String(), ByVal tb As tabella)
    ' oggetti statici per non doverli ricreare ogni volta
    Static cn As OleDbConnection, id1 As Integer, cmd1 As OleDbCommand, cmd2 As OleDbCommand
    ' stringa di connessione al 'mio' database Access
    Const connstring As String = _
          "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\...\dbProveVarie.mdb;Persist Security Info=False"

    'controllo esistenza e conseguente istanza degli oggetti statici
    If IsNothing(cn) Then
      cn = New OleDbConnection(connstring)
    End If
    If IsNothing(cmd1) Then
      cmd1 = New OleDbCommand("INSERT INTO tabella1 VALUES(@id,@pod,@ruolo,@umi)", cn)
      With cmd1
        .Parameters.Add(New OleDbParameter("@id", OleDbType.Integer))
        .Parameters.Add(New OleDbParameter("@pod", OleDbType.VarChar))
        .Parameters.Add(New OleDbParameter("@ruolo", OleDbType.VarChar))
        .Parameters.Add(New OleDbParameter("@umi", OleDbType.VarChar))
      End With
      cmd2 = New OleDbCommand("INSERT INTO tabella2 VALUES(@id,@giorno,@daora,@aora,@valore)", cn)
      With cmd2
        .Parameters.Add(New OleDbParameter("@id", OleDbType.Integer))
        .Parameters.Add(New OleDbParameter("@giorno", OleDbType.Date))
        .Parameters.Add(New OleDbParameter("@daora", OleDbType.VarChar))
        .Parameters.Add(New OleDbParameter("@aora", OleDbType.VarChar))
        .Parameters.Add(New OleDbParameter("@valore", OleDbType.Double))
      End With
    End If

    ' a seconda del parametro di comportamento
    Select Case tb
      Case tabella.Chiusura
        cn.Close()
        id1 = 0
        cmd1 = Nothing
        cmd2 = Nothing

      Case tabella.Tabella1
        If id1 = 0 Then ' trovo l'ultimo id scritto (non è un campo ad autoincremento)
          Dim cmd As New OleDbCommand("SELECT MAX(id) FROM tabella1", cn)
          id1 = cmd.ExecuteScalar
          cmd = Nothing
        End If
        id1 += 1
        With cmd1
          .Parameters(0).Value = id1
          For i = 0 To 2
            .Parameters(i + 1).Value = data(i)
          Next
          .ExecuteNonQuery()
        End With

      Case tabella.Tabella2
        With cmd2
          .Parameters(0).Value = id1
          .Parameters(1).Value = CDate(data(0))
          .Parameters(2).Value = data(1)
          .Parameters(3).Value = data(2)
          .Parameters(4).Value = CDbl(data(3))
          .ExecuteNonQuery()
        End With

    End Select
  End Sub

La seconda soluzione in VB.Net, con LINQ
Però, io non volevo tanto tradurre il codice ex-VB6 in codice VB.Net, quanto verificare la potenza di LINQ.
Dopo qualche vano tentativo, ho consultato il mio consulente privato, cioè Alessandro Del Sole, fresco autore di un libro proprio su LINQ, il quale mi ha dato le dritte giuste per capire perché non riuscivo a combinare niente.

Prima di tutto, mi ha segnalato il link giusto per scaricare un ottimo strumento per ottenere lo schema di qualsiasi file XML, valido anche per Visual Basic Express 2008 (che uso io): si tratta del Template 'XML To Schema', un wizard che produce i file di descrizione degli schemi usati da un file XML (o altre fonti XML).
Questo strumento è compreso nel Service Pack 1 per Visual Studio, ma per le versioni Express si scarica a parte (oppure si fa un aggiornamento dell'intero pacchetto).
L'applicazione del Template (clic sul progetto, Add Item, scheda Data) produce due file xsd, come appare nella figura accanto.
La presenza di questi file permette a Intellisense di suggerire al programmatore i nomi dei vari nodi del file XML, il che costituisce un notevole aiuto in un compito a volte noioso e in un non trascurabile miglioramento della produttività in termini di tempo e precisione.

In secondo luogo, Alessandro mi ha indicato un illuminante articolo di Beth Massi, che mi ha suggerito il trucco 'decisivo' per la soluzione:

Imports <xmlns='urn:schemas-microsoft-com:office:spreadsheet'>
Imports <xmlns:o='urn:schemas-microsoft-com:office:office'>
Imports <xmlns:x='urn:schemas-microsoft-com:office:excel'>
Imports <xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>
Imports <xmlns:html='http://www.w3.org/TR/REC-html40'>

Sono proprio i namespace xml indicati nelle prime righe del file.xml, di cui mostro le parti salienti, per evidenziare le due tipologie di dati:

<?xml version='1.0'?>
<?mso-application progid='Excel.Sheet'?>
<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
          xmlns:o='urn:schemas-microsoft-com:office:office'
          xmlns:x='urn:schemas-microsoft-com:office:excel'
          xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
          xmlns:html='http://www.w3.org/TR/REC-html40'>

...

<Worksheet ss:Name='Energia Attiva Prelevata - 0'>
  <Table>
  <Column ss:AutoFitWidth="0" ss:Width="135" ss:Span="3"/>
  <Row ss:StyleID="Head1">
    <Cell><Data ss:Type='String'>POD</Data></Cell>
    <Cell><Data ss:Type='String'>Ruolo</Data></Cell>
    <Cell><Data ss:Type='String'>Unita' di misura</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type='String'>IT221E00645810</Data></Cell>
    <Cell><Data ss:Type='String'>Energia Attiva Prelevata</Data></Cell>
    <Cell><Data ss:Type='String'>kW.h</Data></Cell>
  </Row>
  <Row/>
  <Row ss:StyleID="Head1">
    <Cell><Data ss:Type='String'>Giorno</Data></Cell>
    <Cell><Data ss:Type='String'>Da ora</Data></Cell>
    <Cell><Data ss:Type='String'>A ora</Data></Cell>
    <Cell><Data ss:Type='String'>Valore</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type='String'>2008-08-01</Data></Cell>
    <Cell><Data ss:Type='String'>00:00:00</Data></Cell>
    <Cell><Data ss:Type='String'>00:15:00</Data></Cell>
    <Cell><Data ss:Type='String'>39.17400000000000</Data></Cell>
  </Row>

...

  </Table>
</Worksheet>
</Workbook>

Le direttive permettono a LINQ (e a Intellisense) di capire come interpretare il codice scritto da noi, in riferimento al file XML che stiamo elaborando. Senza di esse, Visual Basic non saprebbe interpretare le query che ci serve fare sul contenuto del file XML (causa principale del fallimento dei miei primi tentativi con LINQ).

Dopo aver dichiarato il vettore di stringhe per i dati e letto il file:

  Private Sub ElaboraXMLfile2(ByVal xmlfilepath As String)
    Dim dati(3) As String
    Dim doc As XElement = XElement.Load(xmlfilepath)

Ottengo un insieme precisamente filtrato sul tag :

    Dim worksheets As IEnumerable(Of XElement) = From foglio In doc...<Worksheet>.<Table> Select foglio

Vi faccio notare (As IEnumerable(Of XElement) che a me piace tipizzare quanto più precisamente posso le variabili che uso, cercando di non lasciar fare agli automatismi forniti dal potente LINQ.
La query seleziona gli elementi 'foglio' identificati dal sottoinsieme <Worksheet>.<Table>.

    For Each worksheet As XElement In worksheets
      Dim righe As IEnumerable(Of XElement) = From row In worksheet...<Row> Skip 1 Take 1

Di ogni foglio, estraggo solo la seconda riga (<Row> Skip 1 Take 1), di cui leggo gli elementi nel vettore di dati da passare al metodo che scrive nel databasae:

      Dim cells As IEnumerable(Of XElement) = righe(0).Elements
      For i As Integer = 0 To 2
        dati(i) = cells(i).Value
      Next
      Scrivirecord(dati, tabella.Tabella1)

Il resto delle righe da scrivere parte dalla quinta riga (Skip 4):

      righe = From row In worksheet...<Row> Skip 4

Di ciascuna di esse si estraggono i dati da passare al solito metodo, che, alla fine della scansione, viene chiamato per la chiusura e l'annientamento dei suoi oggetti statici:

      For Each riga In righe
        cells = riga.Elements
        For i As Integer = 0 To 3
          dati(i) = cells(i).Value
        Next
        Scrivirecord(dati, tabella.Tabella2)
      Next
    Next
    Scrivirecord(dati, tabella.Chiusura)

Conclusione
Così, a causa di uno dei tanti quesiti posti in lista, e per colpa di un individuo che è meglio non definire (Alessandro Del Sole), ho imparato qualcosa di nuovo sul trattamento di XML da parte di VB.Net e di LINQ.
Come al solito, il codice a corredo è scaricabile dall'area download.