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.
VB.Net
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 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.