Press "Enter" to skip to content

Convertire un file XML o JSON in un foglio di Excel

Visto che ho postato come fare il contrario, e visto che la documentazione su EpPlus è davvero scarsa, ho deciso di postare anche l’esempio contrario, ovvero come generare un foglio di Excel elaborando una collection che abbiamo recuperato leggendo un file XML oppure un file JSON. Ovviamente mi limiterò alle basi, ma EpPlus è in grado di aiutarci a produrre dei fogli excel anche molto complessi e contenenti non solo semplici dati tabellari ma grafici, pivot e molto altro.

Ho pubblicato qualche post fa un esempio d’uso degli oggetti di stampa di .Net per windows forms, ma normalmente, gli output dei miei programmi sono più spesso dei fogli di Excel, principalmente perché lavorando su progetti che riguardano l’analisi e la trasformazione di dati grezzi in dati di qualità, quello che io produco solitamente viene ulteriormente elaborato da chi riceve gli output. E’ comunque buona cosa per chiunque faccia programmi per il mondo delle aziende pensare di inserire in modo standard la possibilità di effettuare un output dei dati che elaboriamo su un foglio di Excel.

mainwindow_01

L’interfaccia utente del nostro programma è molto semplice, in questo caso, ho scelto di elaborare un file XML o JSON con un formato specifico, ovvero quello che abbiamo prodotto nei due esempi precedenti in cui abbiamo creato un file JSON o XML a partire dal foglio di Excel. Il motivo per farlo è quello di aggiungere a vari altri post che ho pubblicato in precedenza riguardo la serializzazione XML e JSON, alcuni tips riguardo la configurazione delle classi per la serializzazione e soprattutto la deserializzazione.

Il progetto FromXmlJsonToExcel, è un progetto WPF molto semplice, contenente una sola Window, MainWindow che al suo interno contiene i seguenti componenti:

  • Grid – Contenitore di tutti i componenti, contiene 3 righe.
  • Grid – Contiene 1 riga 3 colonne, e al suo interno i componenti per acquisire il nome del file XML o JSON.
  • TextBlock – etichetta XML/JSON File Name.
  • TextBox – in Binding con la property del ViewModel DataFileName, contiene il path completo del file dati selezionato.
  • Button – Il Button che permette di selezionare il file dati sorgente dal file system.
  • TextBox – in binding con la property del ViewModel  ResultText  ci permetterà di dare informazioni su quanto il programma sta svolgendo all’utente che lo usa.
  • StackPanel – contiene il Button che permette di convertire i dati in un foglio di Excel.

Prima di commentare il codice che svolge l’operazione di generazione del foglio di Excel, vediamo come sono fatte le classi che contengono i dati.

Le classi RadioCity e City

[XmlRoot(ElementName = "RadioCity")]
public class RadioCity
{

private List<City> mCities;


public RadioCity()
{
Cities = new List<City>();
}


[XmlElement(ElementName = "City")]
public List<City> Cities
{
get
{
return mCities;
}
private set
{
mCities = value;
}
}


public static RadioCity ReadJson(string fileName)
{
string data = File.ReadAllText(fileName);

return JsonConvert.DeserializeObject<RadioCity>(data);
}


public static RadioCity ReadXml(string fileName)
{
using (XmlReader xr = XmlReader.Create(fileName))
{
XmlSerializer serializer = null;
serializer = new XmlSerializer(typeof(RadioCity));
return (RadioCity)serializer.Deserialize(xr);
}
}

}

La classe RadioCity, è il contenitore dati primario, al suo interno è ospitata una Property di tipo Generic List di oggetti di tipo City. La classe contiene inoltre i 2 metodi che permettono di generare un istanza della classe a partire da un file XML oppure da un file JSON.

Per trasformare il contenuto di un file di testo in un oggetto, ci bastano poche righe di codice, quel che possiamo notare è che anche in questo caso il serializzatore JSON lavora su una stringa, mentre quello XML lavora su un file, probabilmente è sempre dovuto ai momenti e ai mondi in cui entrambi sono nati, XML è più vecchio, nato prima dei servizi REST e non solo per il Web. JSON invece è nato per il web e soprattutto per i servizi, pertanto processa e produce stringhe, che provengano o vengano poi memorizzate su file non è un suo problema.

C’è una sola cosa da notare (o annotare) in questa classe che è relativa alla deserializzazione XML:

[XmlElement(ElementName = "City")]
public List<City> Cities

L’attributo inserito sopra alla collection di oggetti City che informa il sistema del fatto che gli elementi della collection sono di tipo City. Questo attributo deve essere inserito nella classe a causa del modo in cui abbiamo creato il file XML.

<?xml version="1.0" encoding="utf-8"?>
<RadioCity>
    <City>
        <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 />
    </City>
    <City>
        <REF_DMI>DMI-L001</REF_DMI>
        <DMI-W001>Abbadia Cerreto</DMI-W001>
        <DMI-L001>Abbadia Cerreto</DMI-L001>
        <DMI-L002>LO</DMI-L002>
        <DMI-F001>Lombardia Occidentale</DMI-F001>
        <DMI-S001>248</DMI-S001>
        <DMI-A001>JN45TH</DMI-A001>
        <DMI-L003 />
        <DMI-F002>A004</DMI-F002>
        <DMI-B001>297</DMI-B001>
        <DMI-T001 />
        <DMI-J001 />
    </City>

Infatti, nel nostro File XML non abbiamo inserito un tag contenitore che informa il deserializzatore che la collezione di oggetti City deve essere inserita in un oggetto chiamato Cities, ma abbiamo semplicemente inserito nel file XML gli oggetti City uno dopo l’altro. L’attributo XmlElement che dichiara che Cities accoglie elementi chiamati City è quanto necessario affinché l’XmlSerializer comprenda come è fatto il file e come generare la classe a partire dai dati.

public class City
{



[XmlElement(ElementName = "DMI-A001")]
[JsonProperty("DMI-A001")]
public string DMIA001
{
get;
set;
}


[XmlElement(ElementName = "DMI-B001")]
[JsonProperty("DMI-B001")]
public string DMIB001
{
get;
set;
}


[XmlElement(ElementName = "DMI-F001")]
[JsonProperty("DMI-F001")]
public string DMIF001
{
get;
set;
}


[XmlElement(ElementName = "DMI-F002")]
[JsonProperty("DMI-F002")]
public string DMIF002
{
get;
set;
}


[XmlElement(ElementName = "DMI-J001")]
[JsonProperty("DMI-J001")]
public string DMIJ001
{
get;
set;
}


[XmlElement(ElementName = "DMI-L001")]
[JsonProperty("DMI-L001")]
public string DMIL001
{
get;
set;
}


[XmlElement(ElementName = "DMI-L002")]
[JsonProperty("DMI-L002")]
public string DMIL002
{
get;
set;
}


[XmlElement(ElementName = "DMI-L003")]
[JsonProperty("DMI-L003")]
public string DMIL003
{
get;
set;
}


[XmlElement(ElementName = "DMI-S001")]
[JsonProperty("DMI-S001")]
public string DMIS001
{
get;
set;
}


[XmlElement(ElementName = "DMI-T001")]
[JsonProperty("DMI-T001")]
public string DMIT001
{
get;
set;
}


[XmlElement(ElementName = "DMI-W001")]
[JsonProperty("DMI-W001")]
public string DMIW001
{
get;
set;
}


public string REF_DMI
{
get;
set;
}


public static List<String> GetColumnNames()
{
List<string> names = new List<string>();
names.Add("REF_DMI");
names.Add("DMI-W001");
names.Add("DMI-L001");
names.Add("DMI-L002");
names.Add("DMI-F001");
names.Add("DMI-S001");
names.Add("DMI-A001");
names.Add("DMI-L003");
names.Add("DMI-F002");
names.Add("DMI-B001");
names.Add("DMI-T001");
names.Add("DMI-J001");
return (names);
}


}

Quando ho scritto i due articoli per la serializzazione, ho indicato che i nomi delle colonne del foglio Excel avevano qualcosa che non andava bene a .Net, ma che c’era modo di aggirare il problema, in questa classe molto semplice che contiene una property per ognuno dei valori inseriti nei file XML o JSON, vediamo quale è il problema e come si risolve.

Se avete scritto qualche programma in precedenza, sapete bene che in C# non è permesso creare nomi di variabili che contengono il carattere ‘-‘ trattino, meno o dash in base a come vi piace chiamarlo. Pertanto non possiamo creare delle property che hanno il nome di quelle scritte nei tag XML o JSON, in quanto entrambi i linguaggi non hanno problemi con i trattini quindi sono stati prodotti senza alcun problema.

[XmlElement(ElementName = "DMI-A001")]
[JsonProperty("DMI-A001")]
public string DMIA001
{
get;
set;
}

Qual è la soluzione? Anche in questo caso l’uso di attributi, per entrambi i linguaggi infatti è possibile definire il nome del campo/tag come si trova sul file dati con l’attributo XmlElement oppure JsonProperty al di sopra della property in cui dovrà essere memorizzato, e la property stessa potrà chiamarsi come preferiamo.

[XmlElement(ElementName = "DMI-A001")]
[JsonProperty("DMI-A001")]
public string Pippo
{
get;
set;
}

La property qui sopra funziona esattamente come la precedente, che noi leggiamo o scriviamo XML o JSON da questa classe, sul file dati verranno scritti o letti i tag DMI-A001.

Oltre a questo, nella classe abbiamo inserito un metodo GetColumnNames che restituirà una lista con i nomi delle colonne da inserire sulla prima riga del foglio di Excel generato.

Il Codice dietro a MainWindow

Anche in questo esempio abbiamo usato un MVVM modello base, pertanto la classe MainWindow è anche il Model di se stessa.

public MainWindow()
{
InitializeComponent();
this.DataContext = this;
}

Assegnamo quindi la classe alla property DataContext per permettere il Binding delle due TextBox inserite nell’interfaccia a due Dependency Property definite in questa classe.

public string DataFileName
{
get
{
return (string)this.GetValue(DataFileNameProperty);
}
set
{
this.SetValue(DataFileNameProperty, value);
}
}
public static readonly DependencyProperty DataFileNameProperty = DependencyProperty.Register(
FLD_DataFileName, typeof(string), typeof(MainWindow), new FrameworkPropertyMetadata("", FrameworkPropertyMetadataOptions.BindsTwoWayByDefault));

La property DataFileName che ospiterà il nome del file dati che leggeremo.

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 property ResultText che ospiterà quello che useremo per fornire all’utente informazioni su quello che stiamo facendo.

private void GetDataFile_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Indicate the XML file containing the RadioCity Collection";
ofd.Filter = "XML file (*.xml)|*.xml|JSON File (*.json)|*.json|All files (*.*)|*.*";
ofd.Multiselect = false;
bool? opened = ofd.ShowDialog(this);
if (opened.HasValue && opened.Value)
{
DataFileName = ofd.FileName;
}
}

Il metodo GetDataFile, permette di scegliere il file dati sorgente da cui leggere i dati da trasferire su Excel.

private void GenerateExcel_Click(object sender, RoutedEventArgs e)
{
try
{
string extension = System.IO.Path.GetExtension(DataFileName);
string excelFile = null;
RadioCity radioCity = null;
switch (extension.ToLower())
{
case ".xml":
excelFile = DataFileName.Replace(extension, "_x.xlsx");
radioCity = RadioCity.ReadXml(DataFileName);
break;

case ".json":
excelFile = DataFileName.Replace(extension, "_j.xlsx");
radioCity = RadioCity.ReadJson(DataFileName);
break;
}
FileInfo infile = new FileInfo(excelFile);
if (File.Exists(excelFile))
{
File.Delete(excelFile);
}

if (radioCity.Cities.Count > 0)
{
GenerateExcelFile(excelFile, infile, radioCity);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

Il Metodo GenerateExcel_Click legge il contenuto del file selezionato creando un oggetto di tipo RadioCity e se la lettura va a buon fine e ci sono dei dati nella collezione, genera il foglio di Excel con il contenuto.

private void GenerateExcelFile(string excelFile, FileInfo infile, RadioCity radioCity)
{
using (ExcelPackage exp = new ExcelPackage(infile))
{
ExcelWorksheet ws = exp.Workbook.Worksheets.Add("RadioCity");

int wscol = 1;
int wsrow = 1;

ws.Cells[wsrow, wscol].Value = "The Radiocity Export Data";
wsrow++;

List<string> headers = City.GetColumnNames();
foreach (string colHeader in headers)
{
ws.Cells[wsrow, wscol].Value = colHeader;
wscol++;
}
wsrow++;
foreach (City c in radioCity.Cities)
{
ws.Cells[wsrow, 1].Value = c.REF_DMI;
ws.Cells[wsrow, 2].Value = c.DMIW001;
ws.Cells[wsrow, 3].Value = c.DMIL001;
ws.Cells[wsrow, 4].Value = c.DMIL002;
ws.Cells[wsrow, 5].Value = c.DMIF001;
ws.Cells[wsrow, 6].Value = c.DMIS001;
ws.Cells[wsrow, 7].Value = c.DMIA001;
ws.Cells[wsrow, 8].Value = c.DMIL003;
ws.Cells[wsrow, 9].Value = c.DMIF002;
ws.Cells[wsrow, 10].Value = c.DMIB001;
ws.Cells[wsrow, 11].Value = c.DMIT001;
ws.Cells[wsrow, 12].Value = c.DMIJ001;
wsrow++;
}
exp.Save();
ResultText = "Export terminated";
}
if (File.Exists(excelFile))
{
if (MessageBox.Show("Do You want to open the Excel Workbook?", "Open Excel", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
Process.Start(excelFile);
}
}
}

Il Metodo GenerateExcelFile si occupa della generazione vera e propria del foglio di Excel, vediamo quali sono le operazioni effettuate:

  • Generiamo un nuovo oggetto ExcelPackage con il nome del file di input e l’estensione xlsx (un suffisso x o j prima dell’estensione ci dice se abbiamo generato da XML o da JSON).
  • Generiamo un foglio di lavoro utilizzando l’oggetto ExcelWorkBook per produrre un oggetto ExcelWorksheet
  • Ci posizioniamo sulla prima riga e prima colonna e creiamo un titolo
  • Scendiamo di una riga e creiamo le intestazioni di colonna
  • Scendiamo di una riga e iniziamo a generare una riga sul foglio Excel per ogni riga della nostra collection.
  • Salviamo l’ExcelPackage
  • Chiediamo all’utente se vuole aprire il foglio di Excel e nel caso decida di farlo, usiamo il programma di default del sistema per aprire il file generato.

excel_01

Abbiamo generato un foglio di lavoro di Excel molto semplice e non formattato.

Ma siccome vogliamo fare un po’ meglio e formattare il foglio generato, ecco alcune delle cose che possiamo fare:

var end = ws.Dimension.End;

int startRow = 3;
int endRow = end.Row;

Prima di tutto calcoliamo quante righe dobbiamo formattare, visto quello che abbiamo creato, la prima e la seconda riga sono le intestazioni, dalla terza riga in poi sono dati, l’oggetto ws.Dimension contiene i dati relativi alle celle iniziali e finali del foglio di Excel, in questo caso, sappiamo che la colonna iniziale è 1 e quella finale è 12 pertanto recuperiamo solo il valore dell’ultima riga.

ws.Cells[1, 1, endRow, 12].AutoFitColumns();

L’oggetto Cells, che in verità è semplicemente un Range contenente tutto il foglio di lavoro, ha un indexer molto ben costruito con 4 parametri:

riga inizio, colonna inizio, riga fine, colonna fine, che ci permettono di contrassegnare aree rettangolari dentro al foglio di Excel per poi effettuare operazioni su tutte le celle dell’area. Nel caso della riga appena riportata, abbiamo selezionato tutta la tabella e usiamo il metodo AutoFitColumns per fare in modo che vengano ridimensionate le colonne del foglio di excel prodotto in modo da adattarsi al contenuto.

ws.Cells[1, 1, 1, 12].Merge = true;
ws.Cells[1, 1, 1, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[1, 1, 1, 12].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[1, 1, 1, 12].Style.Font.Size = 16;
ws.Cells[1, 1, 1, 12].Style.Font.Bold = true;
ws.Cells[1, 1, 1, 12].Style.Font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#274f04"));
ws.Cells[1, 1, 1, 12].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[1, 1, 1, 12].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells[1, 1, 1, 12].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[1, 1, 1, 12].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[1, 1, 1, 12].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[1, 1, 1, 12].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#d1fbac"));

Queste righe formattano il titolo che abbiamo messo sulla prima riga, facciamo prima di tutto un merge delle celle, allineiamo il testo al centro, cambiamo la font e mettiamo il grassetto, cambiamo il colore del testo, assegnamo il bordo al rettangolo e lo riempiamo con un colore di sfondo specifico. Il Pattern Solid serve perché altrimenti sarebbe trasparente.

ws.Cells[2, 1, 2, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[2, 1, 2, 12].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[2, 1, 2, 12].Style.Font.Bold = true;
ws.Cells[2, 1, 2, 12].Style.Font.Color.SetColor(System.Drawing.ColorTranslator.FromHtml("#372600"));
ws.Cells[2, 1, 2, 12].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[2, 1, 2, 12].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells[2, 1, 2, 12].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[2, 1, 2, 12].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
ws.Cells[2, 1, 2, 12].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[2, 1, 2, 12].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ffbd24"));

Queste righe formattano i titoli delle colonne, allineandoli al centro, mettendoli in grassetto e cambiando il colore del testo e dello sfondo, nonché il bordo delle celle.

ws.Cells[startRow, 1, endRow, 12].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
ws.Cells[startRow, 1, endRow, 12].Style.Border.Top.Style = ExcelBorderStyle.Thin;
ws.Cells[startRow, 1, endRow, 12].Style.Border.Left.Style = ExcelBorderStyle.Thin;
ws.Cells[startRow, 1, endRow, 12].Style.Border.Right.Style = ExcelBorderStyle.Thin;
ws.Cells[startRow, 1, endRow, 12].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

Per finire, mettiamo il bordo attorno a tutte le celle contenenti i dati e allineiamo verticalmente al centro delle celle.

Tutto questo prima di eseguire il metodo Save dell’ExcelPackage.

excel_02

Con poco sforzo, abbiamo dato un aspetto più professionale al nostro foglio di Excel.

Per vostra informazione, EpPlus fornisce anche la possibilità di utilizzare gli indici con i nomi di colonna e di riga, pertanto

ws.Cells[1, 1, 1, 12]
ws.Cells["A1:L1"]

Le due righe qui sopra sono equivalenti e rappresentano l’area della prima riga del nostro foglio di Excel.

Riepilogo

In questo post abbiamo visto le seguenti cose:

  • Come creare una classe in grado di deserializzare dati da Xml e Json anche se il loro formato non è banale e contiene alcuni formati non direttamente compatibili con .Net e C#.
  • Come scrivere i dati di una collection come tabella su un foglio di lavoro Excel usando EpPlus.
  • Come formattare i dati scritti sul foglio di excel utilizzando le funzioni apposite di EpPlus

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.