VSTO Excel realizzare un componente per generare un file da due file

Si riporta un articolo scritto per il sito https://www.iprogrammatori.it/articoli/programmazione/art_vsto-excel-realizzare-un-componente-per-_1584.aspx



In questa seconda ed ultima parte, viene illustrata la tecnica o meglio il codice che permetterà di leggere i dati presenti in più file, e generare un file con tutti i dati rilevati.
Riprendendo il progetto creato nell’articolo precedente qui https://www.iprogrammatori.it/articoli/programmazione/art_vsto-excel-realizzare-un-componente-per-_1583.aspx, continuiamo la stesura del codice, in particolare nell’evento click del pulsante per la generazione del file Excel, quello situato nella parte in basso.

Stesura del codice
Facciamo doppio click sul pulsante per la generazione del file quello con la decitura “Genera” ed in visualizzazione codice, nell’evento click del pulsante, dovremmo scrivere il codice per la generazione del file.
Il codice che andremo ad implementare dovrà leggere i valori presente nei due file.
Si crea una funzione, che va a leggere nel secondo file, rilevando nel foglio di Excel le informazioni della colonna denominate rispettivamente città e telefono, tramite la condizione della colonna chiave, che abbiamo denominata ID per individuare il singolo record.
Tutto questo viene implementato come se fosse un database e come se andiamo a fare una select prendendo il record da una colonna chiave, che abbiamo creato in precedenza con il nome ID.
Qui di seguito la funzione per entrambi i linguaggi delle suddette operazioni.

Microsoft.Office.Interop.Excel.Worksheet, ByVal id As String, ByVal riga As Int64)

        Dim NomePathFileExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileB.Text.Trim() & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""

        Dim connessione As OleDbConnection = New OleDbConnection(NomePathFileExcel)

        Dim sql As String = "Select * from [Foglio1$] where ID=" & id & ";"

        Dim DtaDati As OleDbDataAdapter = New OleDbDataAdapter(sql, connessione)

        Dim DtsDati As DataSet = New DataSet()

        DtaDati.Fill(DtsDati)

        If DtsDati.Tables(0).Rows.Count > 0 Then

            foglioAttivo.Range("D" & riga).Value2 = DtsDati.Tables(0).Rows(0)("Citta").ToString()

            foglioAttivo.Range("E" & riga).Value2 = DtsDati.Tables(0).Rows(0)("Telefono").ToString()

        End If

 

        connessione.Close()

        DtaDati.Dispose()

        DtsDati.Dispose()

        connessione.Dispose()

    End Sub

 

C#

private void ImpostaCittaTelefono(Worksheet foglioAttivo, string id, Int64 riga)

        {

            string NomePathFileExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileB.Text.Trim() + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

            OleDbConnection connessione = new OleDbConnection(NomePathFileExcel);

            string sql = "Select * from [Foglio1$] where ID=" + id + ";";

            OleDbDataAdapter DtaDati = new OleDbDataAdapter(sql, connessione);

            DataSet DtsDati = new DataSet();

            DtaDati.Fill(DtsDati);

            if (DtsDati.Tables[0].Rows.Count > 0)

            {

                foglioAttivo.Range["D" + riga].Value2 = DtsDati.Tables[0].Rows[0]["Citta"].ToString();

                foglioAttivo.Range["E" + riga].Value2 = DtsDati.Tables[0].Rows[0]["Telefono"].ToString();

 

            }

            connessione.Close();

            DtaDati.Dispose();

            DtsDati.Dispose();

            connessione.Dispose();

 

 

 

 

        }

Come si vede dal precedente codice, abbiamo utilizzato Ado.Net in particolare le classi per la gestione del database (oledbconection, oledbDataAdapter, e dataset) in modo che trattiamo i file Excel come un database. A questo punto, dopo aver eseguito la query, impostiamo nelle colonne del file Excel i valori che abbiamo rilevato.
A questo punto ritorniamo nell’evento click del pulsante per la generazione del file e scriviamo il codice che ci permette di impostare le colonne con i valori presenti nelle colonne denominate in nome e cognome.
Anche in questo caso la tecnica che  utilizzeremo è Ado.Net.
Al termine del codice, salveremo il file in base al percorso e nome del file impostato nella casella di testo riguardante la generazione del file, posta nella form.
Qui di seguito le suddette operazioni per entrambi i linguaggi.

VB.Net

 

Private Sub btnGenera_Click(sender As Object, e As EventArgs) Handles btnGenera.Click

        Try

            If txtNuovoFile.Text.Trim() = "" OrElse txtFileA.Text.Trim() = "" OrElse txtFileB.Text.Trim() = "" Then

                MessaggioInformazione("Imposatre in ogni casella il percorso e nome del file Excel")

                Return

            End If

           

            Dim foglioAttivo As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet

 

            Dim NomePathFileExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileA.Text.Trim() & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""

            Dim connessione As OleDbConnection = New OleDbConnection(NomePathFileExcel)

            Dim sql As String = "Select * from [Foglio1$];"

            Dim DtaDati As OleDbDataAdapter = New OleDbDataAdapter(sql, connessione)

            Dim DtsDati As DataSet = New DataSet()

            DtaDati.Fill(DtsDati)

            If DtsDati.Tables(0).Rows.Count > 0 Then

                foglioAttivo.Range("A1").Value = "ID"

                foglioAttivo.Range("B1").Value = "Nome"

                foglioAttivo.Range("C1").Value = "Cognome"

                foglioAttivo.Range("D1").Value = "Città"

                foglioAttivo.Range("E1").Value = "Telefono"

                Dim contatore As Long = 2

                For Each riga As DataRow In DtsDati.Tables(0).Rows

                    foglioAttivo.Range("A" & contatore).Value2 = riga("ID").ToString()

                    foglioAttivo.Range("B" & contatore).Value2 = riga("Nome").ToString()

                    foglioAttivo.Range("C" & contatore).Value2 = riga("Cognome").ToString()

                    ImpostaCittaTelefono(foglioAttivo, riga("ID").ToString(), contatore)

                    contatore = contatore + 1

 

                Next

 

            End If

 

 

            connessione.Close()

            DtaDati.Dispose()

            DtsDati.Dispose()

            connessione.Dispose()

 

 

            Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs(txtNuovoFile.Text)

            MessaggioInformazione("File creato correttamente.")

 

 

        Catch ex As Exception

            MessaggioErrore(ex.Message)

        End Try

    End Sub

 

 

C#

private void btnGenera_Click(object sender, EventArgs e)

        {

            try

            {

                if (txtNuovoFile.Text.Trim() == "" || txtFileA.Text.Trim() == "" || txtFileB.Text.Trim() == "")

                {

                    MessaggioInformazione("Imposatre in ogni casella il percorso e nome del file Excel");

                    return;

                }

 

                Worksheet foglioAttivo = ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet);

 

 

 

                string NomePathFileExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileA.Text.Trim() + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

                OleDbConnection connessione = new OleDbConnection(NomePathFileExcel);

                string sql = "Select * from [Foglio1$];";

                OleDbDataAdapter DtaDati = new OleDbDataAdapter(sql, connessione);

                DataSet DtsDati = new DataSet();

                DtaDati.Fill(DtsDati);

 

                if (DtsDati.Tables[0].Rows.Count > 0)

                {

 

                    foglioAttivo.Range["A1"].Value = "ID";

                    foglioAttivo.Range["B1"].Value = "Nome";

                    foglioAttivo.Range["C1"].Value = "Cognome";

                    foglioAttivo.Range["D1"].Value = "Città";

                    foglioAttivo.Range["E1"].Value = "Telefono";

 

                    Int64 contatore = 2;

                    foreach (DataRow riga in DtsDati.Tables[0].Rows)

                    {

 

                        foglioAttivo.Range["A" + contatore].Value2 = riga["ID"].ToString();

                        foglioAttivo.Range["B" + contatore].Value2 = riga["Nome"].ToString();

                        foglioAttivo.Range["C" + contatore].Value2 = riga["Cognome"].ToString();

                        ImpostaCittaTelefono(foglioAttivo, riga["ID"].ToString(), contatore);

                        contatore += 1;

                    }

 

                }

 

                connessione.Close();

                DtaDati.Dispose();

                DtsDati.Dispose();

                connessione.Dispose();

            

 

                Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs(txtNuovoFile.Text);

                MessaggioInformazione("File creato correttamente.");

 

 

            }

            catch (Exception ex)

            {

 

                MessaggioErrore(ex.Message);

            }

        }

Riportiamo il codice completo di entrambi i linguaggi.

VB.Net

Private Sub MessaggioInformazione(ByVal testo As String)

        MessageBox.Show(testo, "Crea File Excel", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

 

    Private Sub MessaggioErrore(ByVal testo As String)

        MessageBox.Show(testo, "Crea File Excel", MessageBoxButtons.OK, MessageBoxIcon.[Error])

    End Sub

 

    Private Sub btnFileA_Click(sender As Object, e As EventArgs) Handles btnFileA.Click

 

        Try

            Dim selezionaFile As New OpenFileDialog

            selezionaFile.Title = "Seleziona il file A da caricare i dati"

            selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx"

            selezionaFile.DefaultExt = "xls|xlsx"

            If selezionaFile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

                txtFileA.Text = selezionaFile.FileName

            End If

        Catch ex As Exception

            MessaggioErrore(ex.Message)

        End Try

 

    End Sub

 

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

 

    End Sub

 

    Private Sub btnFileB_Click(sender As Object, e As EventArgs) Handles btnFileB.Click

        Try

            Dim selezionaFile As New OpenFileDialog()

            selezionaFile.Title = "Seleziona il file B da caricare i dati"

            selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx"

            selezionaFile.DefaultExt = "xls|xlsx"

            If selezionaFile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

                txtFileB.Text = selezionaFile.FileName

            End If

        Catch ex As Exception

            MessaggioErrore(ex.Message)

        End Try

    End Sub

 

    Private Sub BtnNuovoFile_Click(sender As Object, e As EventArgs) Handles BtnNuovoFile.Click

        Try

            Dim selezionaFile As New SaveFileDialog()

            selezionaFile.Title = "Seleziona il file da creare"

            selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx"

            selezionaFile.DefaultExt = "xls|xlsx"

            If selezionaFile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

                txtNuovoFile.Text = selezionaFile.FileName

            End If

        Catch ex As Exception

            MessaggioErrore(ex.Message)

        End Try

    End Sub

 

    Private Sub btnGenera_Click(sender As Object, e As EventArgs) Handles btnGenera.Click

        Try

            If txtNuovoFile.Text.Trim() = "" OrElse txtFileA.Text.Trim() = "" OrElse txtFileB.Text.Trim() = "" Then

                MessaggioInformazione("Imposatre in ogni casella il percorso e nome del file Excel")

                Return

            End If

           

            Dim foglioAttivo As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet

 

            Dim NomePathFileExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileA.Text.Trim() & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""

            Dim connessione As OleDbConnection = New OleDbConnection(NomePathFileExcel)

            Dim sql As String = "Select * from [Foglio1$];"

            Dim DtaDati As OleDbDataAdapter = New OleDbDataAdapter(sql, connessione)

            Dim DtsDati As DataSet = New DataSet()

            DtaDati.Fill(DtsDati)

            If DtsDati.Tables(0).Rows.Count > 0 Then

                foglioAttivo.Range("A1").Value = "ID"

                foglioAttivo.Range("B1").Value = "Nome"

                foglioAttivo.Range("C1").Value = "Cognome"

                foglioAttivo.Range("D1").Value = "Città"

                foglioAttivo.Range("E1").Value = "Telefono"

                Dim contatore As Long = 2

                For Each riga As DataRow In DtsDati.Tables(0).Rows

                    foglioAttivo.Range("A" & contatore).Value2 = riga("ID").ToString()

                    foglioAttivo.Range("B" & contatore).Value2 = riga("Nome").ToString()

                    foglioAttivo.Range("C" & contatore).Value2 = riga("Cognome").ToString()

                    ImpostaCittaTelefono(foglioAttivo, riga("ID").ToString(), contatore)

                    contatore = contatore + 1

 

                Next

 

            End If

 

 

            connessione.Close()

            DtaDati.Dispose()

            DtsDati.Dispose()

            connessione.Dispose()

 

 

            Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs(txtNuovoFile.Text)

            MessaggioInformazione("File creato correttamente.")

 

 

        Catch ex As Exception

            MessaggioErrore(ex.Message)

        End Try

    End Sub

 

    Private Sub ImpostaCittaTelefono(ByVal foglioAttivo As Microsoft.Office.Interop.Excel.Worksheet, ByVal id As String, ByVal riga As Int64)

        Dim NomePathFileExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFileB.Text.Trim() & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""

        Dim connessione As OleDbConnection = New OleDbConnection(NomePathFileExcel)

        Dim sql As String = "Select * from [Foglio1$] where ID=" & id & ";"

        Dim DtaDati As OleDbDataAdapter = New OleDbDataAdapter(sql, connessione)

        Dim DtsDati As DataSet = New DataSet()

        DtaDati.Fill(DtsDati)

        If DtsDati.Tables(0).Rows.Count > 0 Then

            foglioAttivo.Range("D" & riga).Value2 = DtsDati.Tables(0).Rows(0)("Citta").ToString()

            foglioAttivo.Range("E" & riga).Value2 = DtsDati.Tables(0).Rows(0)("Telefono").ToString()

        End If

 

        connessione.Close()

        DtaDati.Dispose()

        DtsDati.Dispose()

        connessione.Dispose()

    End Sub

 

C#

private void MessaggioInformazione(string testo)

        {

            MessageBox.Show(testo, "Crea File Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

 

 

        private void MessaggioErrore(string testo)

        {

            MessageBox.Show(testo, "Crea File Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);

 

        }

        private void btnFileA_Click(object sender, EventArgs e)

        {

            try

            {

                OpenFileDialog selezionaFile = new OpenFileDialog();

                selezionaFile.Title = "Seleziona il file A da caricare i dati";

                selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx";

                selezionaFile.DefaultExt = "xls|xlsx";

 

                if (selezionaFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)

                {

                    txtFileA.Text = selezionaFile.FileName;

 

                }

 

 

            }

            catch (Exception ex)

            {

 

                MessaggioErrore(ex.Message);

            }

        }

 

        private void btnFileB_Click(object sender, EventArgs e)

        {

            try

            {

                OpenFileDialog selezionaFile = new OpenFileDialog();

                selezionaFile.Title = "Seleziona il file B da caricare i dati";

                selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx";

                selezionaFile.DefaultExt = "xls|xlsx";

 

                if (selezionaFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)

                {

                    txtFileB.Text = selezionaFile.FileName;

 

                }

 

 

            }

            catch (Exception ex)

            {

 

                MessaggioErrore(ex.Message);

            }

        }

 

        private void BtnNuovoFile_Click(object sender, EventArgs e)

        {

            try

            {

                SaveFileDialog selezionaFile = new SaveFileDialog();

                selezionaFile.Title = "Seleziona il file da creare";

                selezionaFile.Filter = "Excel Files (*.xls, *.xlsx)|*.xls;*.xlsx";

                selezionaFile.DefaultExt = "xls|xlsx";

 

                if (selezionaFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)

                {

                    txtNuovoFile.Text = selezionaFile.FileName;

 

                }

 

 

            }

            catch (Exception ex)

            {

 

                MessaggioErrore(ex.Message);

            }

        }

 

        private void btnGenera_Click(object sender, EventArgs e)

        {

            try

            {

                if (txtNuovoFile.Text.Trim() == "" || txtFileA.Text.Trim() == "" || txtFileB.Text.Trim() == "")

                {

                    MessaggioInformazione("Imposatre in ogni casella il percorso e nome del file Excel");

                    return;

                }

 

                Worksheet foglioAttivo = ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet);

 

 

 

                string NomePathFileExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileA.Text.Trim() + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

                OleDbConnection connessione = new OleDbConnection(NomePathFileExcel);

                string sql = "Select * from [Foglio1$];";

                OleDbDataAdapter DtaDati = new OleDbDataAdapter(sql, connessione);

                DataSet DtsDati = new DataSet();

                DtaDati.Fill(DtsDati);

 

                if (DtsDati.Tables[0].Rows.Count > 0)

                {

 

                    foglioAttivo.Range["A1"].Value = "ID";

                    foglioAttivo.Range["B1"].Value = "Nome";

                    foglioAttivo.Range["C1"].Value = "Cognome";

                    foglioAttivo.Range["D1"].Value = "Città";

                    foglioAttivo.Range["E1"].Value = "Telefono";

 

                    Int64 contatore = 2;

                    foreach (DataRow riga in DtsDati.Tables[0].Rows)

                    {

 

                        foglioAttivo.Range["A" + contatore].Value2 = riga["ID"].ToString();

                        foglioAttivo.Range["B" + contatore].Value2 = riga["Nome"].ToString();

                        foglioAttivo.Range["C" + contatore].Value2 = riga["Cognome"].ToString();

                        ImpostaCittaTelefono(foglioAttivo, riga["ID"].ToString(), contatore);

                        contatore += 1;

                    }

 

                }

 

                connessione.Close();

                DtaDati.Dispose();

                DtsDati.Dispose();

                connessione.Dispose();

            

 

                Globals.ThisAddIn.Application.ActiveWorkbook.SaveAs(txtNuovoFile.Text);

                MessaggioInformazione("File creato correttamente.");

 

 

            }

            catch (Exception ex)

            {

 

                MessaggioErrore(ex.Message);

            }

        }

 

        private void ImpostaCittaTelefono(Worksheet foglioAttivo, string id, Int64 riga)

        {

            string NomePathFileExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileB.Text.Trim() + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

            OleDbConnection connessione = new OleDbConnection(NomePathFileExcel);

            string sql = "Select * from [Foglio1$] where ID=" + id + ";";

            OleDbDataAdapter DtaDati = new OleDbDataAdapter(sql, connessione);

            DataSet DtsDati = new DataSet();

            DtaDati.Fill(DtsDati);

            if (DtsDati.Tables[0].Rows.Count > 0)

            {

                foglioAttivo.Range["D" + riga].Value2 = DtsDati.Tables[0].Rows[0]["Citta"].ToString();

                foglioAttivo.Range["E" + riga].Value2 = DtsDati.Tables[0].Rows[0]["Telefono"].ToString();

 

            }

            connessione.Close();

            DtaDati.Dispose();

            DtsDati.Dispose();

            connessione.Dispose();

 

 

 

 

        }

 

Conclusioni


In questo articolo, è stata illustrata una tecnica di utilizzo di Excel in VSTO per entrambi i linguaggi di programmazione .Net più utilizzati per generare un file Excel da due file con i dati che fanno riferimento ad una colonna chiave.
Una tecnica che permette di unire file e generare uno solo file, o meglio fare il merge di più file. L’articolo è di base anche per la realizzazione di un programma ben più complesso che potrebbe ottimizzare il lavoro, nella gestione di più file, unendoli in uno solo.