Un collega mi ha chiesto un suggerimento su come fare un programma per leggere dati da un foglio di Excel e convertirli in un file XML, considerato che è qualcosa che potrebbe accadere anche spesso, visto che soprattutto i non informatici hanno una propensione per utilizzare i fogli di Excel come database. O come formato standard di scambio dati, ho deciso di fare un esempio che mostra come utilizzare la libreria Open Source EPPlus, la trovate all’inidirizzo: epplus.codeplex.com per leggere un file di Excel e scrivere un documento XML senza utilizzare per questo una specifica classe.
Perché questo esempio? perché è semplice da utilizzare per ricevere dati di tipo One Shot ovvero ci arriveranno una sola volta, quindi non vale la pena di costruire una classe entity e una collection per leggere una sola volta un foglio di Excel.
Vediamo come è fatta l’applicazione, si tratta di una applicazione WPF che ho chiamato con molta fantasia FromExcelToXml che contiene una sola Window, MainWindow:
Se vi chiedete cos’è il rettangolino nero è una nuova feature di Visual Studio 2015 per il debug dello XAML, non compare quando non stiamo usando la finestra da Visual Studio come ho fatto io.
In Questa finestra, abbiamo inserito i seguenti controlli:
- Grid – con 3 righe contiene tutti gli altri controlli e da forma alla window
- Grid – Contiene una riga e 3 colonne e contiene i controlli della prima riga e ci permette di definire come vogliamo siano costruiti.
- TextBlock – l’etichetta per la selezione del file di excel da leggere
- TextBox – una casella di testo per il nome del file di excel da leggere
- Button – Un bottone per andare a selezionare il file di excel da leggere sul disco
- Textbox – una grande textbox che ci servirà per visualizzare lo stato delle operazioni.
- Grid – contiene 3 righe e 3 colonne e ci permetterà di inserire i controlli necessari ai parametri da indicare per produrre il file Xml e per il button che legge il foglio di excel
- TextBlock – l’etichetta Root Element
- TextBlock – l’etichetta RowElement
- TextBox – la casella per indicare il nome dell’elemento Root del file XML
- TextBox – la casella per indicare il nome dell’elemento che contiene i dati di una riga del foglio di Excel.
- CheckBox – per indicare se la prima riga del foglio di excel contiene i nomi dei campi
- Button – Fornisce l’azione necessaria a leggere il file excel generando l’XML
Essendo un applicazione WPF abbiamo utilizzato MVVM versione base per effettuare il Binding dei controlli Textbox ad altrettante property del Model, siccome si tratta di una versione base, in questo caso il Model è la Window stessa.
Vediamo il code behind che è ovviamente la parte più succosa, essendo un articolo non riservato ai principianti, assumerò che quanto descritto vi basti per capire il contenuto della parte XAML dell’applicazione.
Il codice applicativo
public MainWindow()
{
InitializeComponent();
this.DataContext = this;
}
Come indicato, apriamo con il costruttore in cui agganceremo la property DataContext alla Window stessa, trasformandola nel Model di se stessa.
public string ExcelFileName
{
get
{
return (string)this.GetValue(ExcelFileNameProperty);
}
set
{
this.SetValue(ExcelFileNameProperty, value);
}
}
public static readonly DependencyProperty ExcelFileNameProperty = DependencyProperty.Register(
FLD_ExcelFileName, typeof(string), typeof(MainWindow), new FrameworkPropertyMetadata("", FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));
La dependency property che rappresenta il nome del file di Excel che elaboreremo.
public bool FirstRowHasFieldNames
{
get
{
return (bool)this.GetValue(FirstRowHasFieldNamesProperty);
}
set
{
this.SetValue(FirstRowHasFieldNamesProperty, value);
}
}
public static readonly DependencyProperty FirstRowHasFieldNamesProperty = DependencyProperty.Register(
FLD_FirstRowHasFieldNames, typeof(bool), typeof(MainWindow), new FrameworkPropertyMetadata(true, FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));
La dependency property che rappresenta il boolean che è collegato alla checkbox che indica se la prima riga del foglio di Excel contiene i nomi dei campi.
public string ResultText
{
get
{
return (string)this.GetValue(ResultTextProperty);
}
set
{
this.SetValue(ResultTextProperty, value);
}
}
public static readonly DependencyProperty ResultTextProperty = DependencyProperty.Register(
FLD_ResultText, typeof(string), typeof(MainWindow), new FrameworkPropertyMetadata("", FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));
La dependency property della textbox dove indicheremo lo stato delle operazioni.
public string RootElement
{
get
{
return (string)this.GetValue(RootElementProperty);
}
set
{
this.SetValue(RootElementProperty, value);
}
}
public static readonly DependencyProperty RootElementProperty = DependencyProperty.Register(
FLD_RootElement, typeof(string), typeof(MainWindow), new FrameworkPropertyMetadata("Root", FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));
La dependency property collegata alla textbox che ci dirà qual è il nome da assegnare al tag Root del file XML che produrremo.
public string RowElement
{
get
{
return (string)this.GetValue(RowElementProperty);
}
set
{
this.SetValue(RowElementProperty, value);
}
}
public static readonly DependencyProperty RowElementProperty = DependencyProperty.Register(
FLD_RowElement, typeof(string), typeof(MainWindow), new FrameworkPropertyMetadata("Row", FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));
La dependency property collegata alla textbox che ci dirà qual è il nome da assegnare al tag che rappresenta una riga del foglio di Excel nel nostro file XML.
Il metodo per acquisire il nome del foglio di excel
private void GetExcelFile_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Indicate the excel file containing the data";
ofd.Filter = "Excel file (*.xlsx)|*.xlsx|All files (*.*)|*.*";
ofd.Multiselect = false;
bool? opened = ofd.ShowDialog(this);
if (opened.HasValue && opened.Value)
{
ExcelFileName = ofd.FileName;
}
}
Utilizziamo la OpenFileDialog standard per andare a selezionare il foglio di Excel che ci serve e lo memorizziamo nella variabile collegata alla textbox apposita.
Il metodo per elaborare il foglio di Excel
private void GenerateXml_Click(object sender, RoutedEventArgs e)
{
try
{
FileInfo infile = new FileInfo(ExcelFileName);
string outputFile = ExcelFileName.Replace(infile.Extension, ".xml");
using (ExcelPackage exp = new ExcelPackage(infile))
{
if (exp.Workbook.Worksheets.Count > 0)
{
ExcelWorksheet ws = exp.Workbook.Worksheets.First();
var start = ws.Dimension.Start;
var end = ws.Dimension.End;
Dictionary<int, string> fieldNames = new Dictionary<int, string>();
int firstRow = start.Row;
if (FirstRowHasFieldNames)
{
for (int x = start.Column; x <= end.Column; x++)
{
fieldNames.Add(x, GetCellStringValue(ws, x, start.Row));
}
firstRow++;
}
else
{
for (int x = start.Column; x <= end.Column; x++)
{
fieldNames.Add(x, string.Format("Column_{0}", x));
}
}
GenerateXmlFile(outputFile, ws, start, end, fieldNames, firstRow);
if (MessageBox.Show("Do You want to open output file?", "Open the file", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
Process.Start(outputFile);
}
}
else
{
MessageBox.Show("Looks like there are no worksheets!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
Lo commentiamo per pezzi in modo da essere precisi, anche se preferisco listarlo completamente per il vostro copia e incolla.
FileInfo infile = new FileInfo(ExcelFileName);
string outputFile = ExcelFileName.Replace(infile.Extension, ".xml");
using (ExcelPackage exp = new ExcelPackage(infile))
{
Per prima cosa, creiamo un oggetto FileInfo relativo al file Excel, questo perché EPPlus richiede che gli sia passato per poter generare l’oggetto ExcelPackage che ci permette di accedere al foglio di Excel.
Generiamo inoltre il file di output, con lo stesso nome del foglio di Excel, cambiandone solo l’estensione.
Apriamo il foglio di Excel, utilizzando ExcelPackage, l’oggetto di EpPlus che gestisce un file xlsx.
if (exp.Workbook.Worksheets.Count > 0)
{
Verifichiamo che la cartella Excel contenga almeno un foglio.
}
else
{
MessageBox.Show("Looks like there are no worksheets!");
}
Se non vi fossero fogli (un po’ strano ma meglio controllare che dare errore) diamo messaggio e usciamo.
ExcelWorksheet ws = exp.Workbook.Worksheets.First();
var start = ws.Dimension.Start;
var end = ws.Dimension.End;
Dictionary<int, string> fieldNames = new Dictionary<int, string>();
int firstRow = start.Row;
Se invece il foglio esiste, andiamo a prendere il primo foglio, utilizziamo una query LINQ per farlo, perché stranamente, la collection dei Workbook non ha un indexer numerico e quindi non è possibile chiedere
exp.Workbook.Worksheets[0] perché da errore, al contrario è invece possibile chiedere un foglio per nome, quindi se state preparando un sistema di acquisizione dati in cui ricevete dei fogli di Excel, se vi accordate con chi fornisce i dati per dare un nome specifico al foglio potete usare exp.Workbook.Worksheets[“NomeFoglio”] per leggere quello che vi interessa.
Una volta ottenuto l’oggetto ExcelWorksheet, per prima cosa memorizziamoci gli indici della prima e ultima cella utilizzata del foglio.
Creiamo poi un Dictionary dove inserire i nomi dei campi la cui chiave è l’indice del campo.
Creiamo inoltre una variabile ove memorizzare la riga da cui iniziare a leggere il foglio dati.
if (FirstRowHasFieldNames)
{
for (int x = start.Column; x <= end.Column; x++)
{
fieldNames.Add(x, GetCellStringValue(ws, x, start.Row));
}
firstRow++;
}
Se ci è stato indicato che la prima riga contiene i nomi dei campi, andiamo a leggerli e aggiungiamoli al dizionario.
NOTA!!! Non facciamo controlli sulla forma dei nomi di campo, assumendo che siano dei nomi validi per l’XML, in una applicazione del mondo reale, sarebbe opportuno verificare il contenuto dei campi ed eliminare tutti i caratteri non utilizzabili per i nomi di campo come: punteggiatura, parentesi, spazi, tabulazioni, e altri caratteri speciali. Il trattino possiamo conservarlo e nel nostro esempio lo useremo perché poi, faremo l’articolo contrario (da xml a Excel) e vedremo come gestirlo.
else
{
for (int x = start.Column; x <= end.Column; x++)
{
fieldNames.Add(x, string.Format("Column_{0}", x));
}
}
Se non ci sono nomi dei campi nella prima riga, creiamo dei nomi di campo fittizi corrispondenti ad ogni colonna contenente dati del foglio di Excel.
GenerateXmlFile(outputFile, ws, start, end, fieldNames, firstRow);
if (MessageBox.Show("Do You want to open output file?", "Open the file", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
Process.Start(outputFile);
}
A questo punto, procediamo a generare il file Xml e quando terminato, se l’utente lo desidera apriamo il file XML con l’applicazione di default per la lettura dei file XML installata sul computer.
I metodi di supporto
Ci sono 3 metodi di supporto al metodo che crea il file XML, uno che abbiamo già usato permette di leggere il contenuto di una cella:
private static string GetCellStringValue(ExcelWorksheet wks, int row, int col)
{
object cVal = wks.Cells[row, col].Value;
if (cVal == null)
return null;
else
return cVal.ToString();
}
Questo metodo, controlla e restituisce una stringa rappresentante il contenuto della cella (il suo valore, non la formula).
[SecurityPermissionAttribute(SecurityAction.Demand, Flags = SecurityPermissionFlag.UnmanagedCode)]
public void DoEvents()
{
DispatcherFrame frame = new DispatcherFrame();
Dispatcher.CurrentDispatcher.BeginInvoke(DispatcherPriority.Background,
new DispatcherOperationCallback(DoExitFrame), frame);
Dispatcher.PushFrame(frame);
}
public object DoExitFrame(object f)
{
((DispatcherFrame)f).Continue = false;
return null;
}
Questi due metodi, simulano in WPF l’effetto del DoEvents di Windows Forms, non sono il metodo più sofisticato e neppure il più bello che si possa usare in WPF, però li trovo pratici quando devo fare un operazione lunga e voglio mostrare all’utente, che comunque deve attendere che io termini l’operazione, che cosa sto facendo, invece che semplicemente una clessidra. Non uso i metodi asincroni perché comunicare con la UI da un thread è eccessivamente complicato, quindi magari vi dedicheremo un paio di post in seguito.
Il metodo che legge il foglio di excel e genera l’XML
private int GenerateXmlFile(string outputFile, ExcelWorksheet ws,
ExcelCellAddress start, ExcelCellAddress end, Dictionary<int, string> fieldNames, int firstRow)
{
int count = 0;
FileStream fs = new FileStream(outputFile, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
XmlTextWriter xmlWriter = null;
xmlWriter = new XmlTextWriter(sw);
//Use indentation for readability.
xmlWriter.Formatting = Formatting.Indented;
xmlWriter.Indentation = 4;
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement(RootElement);
int countDoEvents = 0;
for (int row = firstRow; row <= end.Row; row++)
{
count++;
countDoEvents++;
if (countDoEvents >= 10)
{
ResultText = "Reading record " + count;
countDoEvents = 0;
DoEvents();
}
xmlWriter.WriteStartElement(RowElement);
for (int col = start.Column; col <= end.Column; col++)
{
xmlWriter.WriteStartElement(fieldNames[col]);
xmlWriter.WriteString(GetCellStringValue(ws, row, col));
xmlWriter.WriteEndElement();
}
xmlWriter.WriteEndElement();
}
xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();
xmlWriter.Close();
ResultText = "Ended reading writing file " + outputFile;
countDoEvents = 0;
sw.Close();
fs.Close();
ResultText = File.ReadAllText(outputFile);
return (count);
}
Anche in questo caso lo commentiamo per pezzettini in modo da capire come funziona, l’importante è ricordare, che questo programma è disegnato per lavorare con fogli di excel tabulari ma generici, di cui non sa a priori la forma.
FileStream fs = new FileStream(outputFile, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
XmlTextWriter xmlWriter = null;
xmlWriter = new XmlTextWriter(sw);
Per prima cosa, abbiamo bisogno di un FileStream per scrivere sul disco, dopo di che, ci serve uno StreamWriter per scrivere il testo ed infine, di un XmlTextWriter che è lo strumento creato per scrivere in XML.
xmlWriter.Formatting = Formatting.Indented;
xmlWriter.Indentation = 4;
Attiviamo le opzioni di base per formattare il file XML in modo che sia leggibile se caricato su un notepad.
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement(RootElement);
int count = 0;
int countDoEvents = 10;
for (int row = firstRow; row <= end.Row; row++)
{
La prima cosa da scrivere sul file xml è l’intestazione del documento che viene creata dal metodo WriteStartDocument.
Subito dopo, dobbiamo scrivere il nostro elemento Root ovvero il tag che contiene tutto il contenuto del foglio di Excel.
Azzeriamo i contatori per visualizzare il progresso all’utente e iniziamo il ciclo su tutte le righe del nostro foglio di Excel a partire dalla seconda se la prima ospitava i nomi di campo.
count++;
countDoEvents++;
if (countDoEvents >= 10)
{
ResultText = "Reading record " + count;
countDoEvents = 0;
DoEvents();
}
xmlWriter.WriteStartElement(RowElement);
for (int col = start.Column; col <= end.Column; col++)
{
xmlWriter.WriteStartElement(fieldNames[col]);
xmlWriter.WriteString(GetCellStringValue(ws, row, col));
xmlWriter.WriteEndElement();
}
xmlWriter.WriteEndElement();
}
Per ogni riga del foglio di Excel, per prima cosa se necessario mostriamo all’utente il progresso, non facciamolo ogni riga (possiamo anche farlo ogni 100 righe per fogli molto grandi, perché il DoEvents così fatto rallenta di molto il funzionamento, pertanto va usato con parsimonia, ecco perché ho detto che ci sono delle soluzioni molto più eleganti e performanti per fare qualcosa di simile.
Dopo il dovuto codice informativo per l’utente, usiamo il metodo WriteStartElement per aprire la nostra riga inserendo l’apertura del tag che abbiamo chiesto all’utente.
Per ogni colonna del foglio di Excel, Apriamo un Tag con il nome del campo sempre usando WriteStartElement e poi usiamo WriteString per scriverne il contenuto, chiudiamo il tag utilizzando WriteEndElement e finito il ciclo sulla riga, usiamo ancora WriteEndElement per chiudere il Tag RowElement.
xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();
xmlWriter.Close();
ResultText = "Ended reading writing file " + outputFile;
countDoEvents = 0;
Terminato di scrivere tutte le righe, chiudiamo il Root Tag utilizzando WriteEndElement e chiudiamo il documento usando WriteEndDocument ed infine chiudiamo l’XmlTextWriter e stampiamo la fine del ciclo.
sw.Close();
fs.Close();
ResultText = File.ReadAllText(outputFile);
return (count);
Chiudiamo lo StreamWriter e il FileStream e per mostrare cosa abbiamo fatto all’utente, leggiamo il file scritto e visualizziamolo sulla Textbox opportunamente creata allo scopo. Restituiamo il numero di righe elaborate al chiamante.
Riepilogo
Per leggere un file di Excel ed elaborarne i risultati si può utilizzare gli oggetti:
- ExcelPackage – che rappresenta un file xlsx
- ExcelWorkbook – che rappresenta il la cartella (workbook) contenuta nell’xlsx
- ExcelWorksheet – che rappresenta un foglio della cartella excel
Per scrivere un file Xml generico possiamo usare:
- XmlTextWriter – che permette di scrivere un documento XML tag per tag (non abbiamo generato attributi, ma possiamo fare anche quello se vogliamo)
- WriteStartDocument – è il metodo per aprire un documento
- WriteEndDocument – è il metodo per chiudere un documento
- WriteStartElement – è il metodo per aprire un Tag nel documento
- WriteEndElement – è il metodo per chiudere un Tag nel documento
- WriteString – è il metodo per scrivere del testo dentro a un Tag aperto
Vediamo gli screenshot
Ecco che cosa abbiamo usato per il test e quello che abbiamo ottenuto:
Questo è un pezzetto del foglio Excel di esempio, è una lista di dati utilizzati dai radioamatori per riconoscere i punti di contatto radio.
Abbiamo selezionato il file Excel e indicato al sistema che Il tag Root deve chiamarsi RadioCity e il tag che racchiude una riga deve chiamarsi City, inoltre che i nomi dei campi sono sulla prima riga.
Cliccando sul bottone Generate Xml, in pochi secondi viene generato il file XML, poi, l’esempio sembra bloccarsi per una trentina di secondi, questo è dovuto al fatto che legge tutto il file dentro alla Textbox, potete non mettere questa funzionalità se lo userete in un programma nel mondo reale e tutto sarà molto più semplice.
<?xml version=”1.0″ encoding=”utf-8″?>
<Root>
<Row>
<REF_DMI>DMI-W001</REF_DMI>
<DMI-W001>Abano Terme</DMI-W001>
<DMI-L001>Abano Terme</DMI-L001>
<DMI-L002>PD</DMI-L002>
<DMI-F001>Veneto</DMI-F001>
<DMI-S001>248</DMI-S001>
<DMI-A001>JN55VI</DMI-A001>
<DMI-L003 />
<DMI-F002>A001</DMI-F002>
<DMI-B001>19349</DMI-B001>
<DMI-T001 />
<DMI-J001 />
</Row>
… molte migliaia di righe…
<Row>
<REF_DMI />
<DMI-W001 />
<DMI-L001 />
<DMI-L002 />
<DMI-F001 />
<DMI-S001 />
<DMI-A001 />
<DMI-L003 />
<DMI-F002 />
<DMI-B001 />
<DMI-T001 />
<DMI-J001 />
</Row>
</Root>
Il file XML che viene prodotto si apre con la dichiarazione di formato dell’XML, poi apre il Tag root RadioCity e poi per ogni riga crea un tag City con un tag per ogni colonna.
Questo file, con qualche accorgimento a causa dei nomi con i trattini, può essere letto in modo diretto da una classe C#. Vedremo come si fa nell’articolo che dimostra il contrario, ovvero come scrivere un foglio di Excel a partire da una collezione di oggetti letti da un file XML.
Potete scaricare il progetto esempio dal link qui indicato:
Per qualsiasi domanda, osservazione, commento, approfondimento, o per segnalare un errore, potete usare il link alla form di contatto in cima alla pagina.