Press "Enter" to skip to content

Convertire un foglio di Excel in JSON

Published 17/06/2016 by Sabrina Cosolo

Nel precedente post, abbiamo visto come convertire un foglio di Excel in XML, ma considerato che il formato JSON sta diventando lo standard per lo scambio di dati, soprattutto quando si parla di servizi Web, e di applicazioni di ogni genere e tipo, credo sia una cosa buona vedere come svolgere lo stesso tipo di conversione creando un file JSON. Per farlo, oltre ad utilizzare la libreria open source EPPlus, la trovate all’inidirizzo:  epplus.codeplex.com per leggere i dati dal foglio di Excel, utilizzeremo JSON.Net ovvero la libreria Newtonsoft Json che è divenuta ormai lo standard utilizzato da tutte le applicazioni .net per creare il file in formato JSON.

Anche in questo caso, l’esempio vuole illustrare come convertire un file in formato generico, senza dover per forza creare una classe .Net in cui memorizzare i dati della tabella Excel per poi convertirla. Serve per le conversioni Una-Tantum o quando vi venissero inviati dati generici che magari devono divenire una Tabella di dati di configurazione per una applicazione che non avrà bisogno di essere riconvertita in seguito.

Vediamo anche in questo caso l’applicazione WPF, che con eccessiva fantasia stavolta ho chiamato FromExcelToJson.

mainwindow_01

Anche in questo caso, il rettangolino nero appare solo in Debug, è una nuova funzione di Visual Studio 2015 per fare il debug dello XAML.

In Questa unica finestra della nostra applicazione, 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 2 righe e 3 colonne e ci permetterà di inserire i controlli necessari ai parametri da indicare per produrre il file Json e per il Button che legge il foglio di Excel
  • TextBlock – l’etichetta Collection Element
  • TextBox – la casella per indicare il nome dell’elemento Collection del file JSON
  • 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 il Json

Essendo un applicazione WPF abbiamo utilizzato MVVM, versione base, per effettuare il Binding dei controlli Textbox ad altrettante property del Model, cosa vuol dire versione base, che data la sua semplicità,  in questo caso il Model della window è la Window stessa.

Come per il precedente esempio, assumo abbiate qualche esperienza in XAML quindi non commenterò in dettaglio il codice, mi aspetto che le descrizioni precedenti siano sufficienti a mostrarvi come è fatto guardando il file di esempio. Perciò passiamo invece subito alla spiegazione del codice.

Il Codice applicativo

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

Come indicato, per poter usare il binding delle property ai controlli, indichiamo alla Window che lei stessa è il proprio ViewModel (DataContext).

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

La dependency  property CollectionElement che supporta la Textbox per la richiesta del nome dell’elemento Json che rappresenta la collezione delle righe del foglio di Excel.

pudependency blic 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 ExcelFileName che supporta la textbox del nome del file di Excel.

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 FirstRowHasFileNames che supporta la checkbox che indica se i nomi dei campi della tabella sono nella prima riga del foglio di Excel.

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 ResultText che supporta la textbox ove mostriamo all’utente le attività ed il risultato di quanto abbiamo generato.

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 generare il file JSON

private void GenerateJson_Click(object sender, RoutedEventArgs e)
    {
        Cursor c = this.Cursor;
        try
        {
            this.Cursor = Cursors.Wait;
            FileInfo infile = new FileInfo(ExcelFileName);
            string outputFile = ExcelFileName.Replace(infile.Extension, ".json");
            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);
        }
        finally
        {
            this.Cursor = c;
        }
    }

Lo inserisco intero per permettervi un miglior copia e incolla, ma lo commentiamo pezzo per pezzo.

Cursor c = this.Cursor;
try
{
    this.Cursor = Cursors.Wait;
    DoEvents();

Una operazione preliminare, impostiamo il cursore in formato clessidra, lo resetteremo al termine dell’esecuzione:

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
    this.Cursor = c;
}

Questo ci permette anche di intercettare eventuali errori e visualizzarli in modo umano.

FileInfo infile = new FileInfo(ExcelFileName);
string outputFile = ExcelFileName.Replace(infile.Extension, ".json");
using (ExcelPackage exp = new ExcelPackage(infile))
{

Creiamo un oggetto FileInfo dal nome del file di Excel perché EpPlus lo richiede per poter aprire il foglio di Excel, creiamo anche il nome per il file json generato che sarà posto nella stessa cartella del foglio di Excel e ne avrà lo stesso nome, cambiando solo l’estensione.

Creiamo un oggetto ExcelPackage, che è la rappresentazione fornita da EpPlus del nostro file .xlsx.

if (exp.Workbook.Worksheets.Count > 0)
{
    ExcelWorksheet ws = exp.Workbook.Worksheets.First();
    var start = ws.Dimension.Start;
    var end = ws.Dimension.End;

…

}
else
{
    MessageBox.Show("Looks like there are no worksheets!");
}

Controlliamo che la cartella dati Excel contenga almeno un foglio di lavoro, se così non fosse daremo un messaggio e non eseguiremo nulla. Se esiste almeno un foglio di lavoro nella cartella Excel, eseguiamo una query LINQ per recuperare il primo foglio, non usiamo un semplice indice perché questa collection non ha indici, l’unico altro modo per recuperarne un elemento è conoscere il nome del foglio di lavoro.

exp.Workbook.Worksheets[0] darebbe errore, ma potremmo usare exp.Workbook.Worksheets[“NomeFoglio”], siccome a priori non lo sappiamo, abbiamo fatto una funzione generica, leggo solo il primo foglio, se abbiamo un file con più fogli potremmo utilizzare un ciclo ed esportarli tutti.

Oltre a creare una variabile contenente il foglio di lavoro, ne acquisiamo le coordinate della prima e dell’ultima cella.

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));
    }
}

Prima di procedere a elaborare il foglio, dobbiamo creare i nomi dei campi che contengono i dati, tali nomi saranno quelli della prima riga se presenti altrimenti ne generiamo una serie che si chiamerà Column_1… Column_n.

GenerateJsonFile(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);
}

Chiamiamo il metodo di generazione del file Json e se tutto va bene, apriamo il file con il programma di lettura impostato come default sulla macchina.

I metodi di supporto

Nel precedente metodo e nel metodo di generazione, utilizziamo alcuni metodi di supporto, ne vediamo il contenuto prima di continuare:

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();
}

Il metodo GetCellStringValue, restituisce una stringa con il valore (non formula) della cella se presente, altrimenti restituisce un null.

[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;
}

I metodi qui sopra effettuano la simulazione WPF del DoEvents di Windows forms. Non sono ne il più performante ne il più elegante modo per fare si che durante una operazione di elaborazione possiamo dare informazioni all’utente su quello che stiamo facendo, però sono quella più veloce da implementare e con meno problemi di Cross Threading, essendo un esempio base, vorrei evitare di complicare l’applicazione con programmazione asincrona e multi threading, magari in seguito ne parleremo in modo opportuno.

Il metodo che genera il file Json

private int GenerateJsonFile(string outputFile, ExcelWorksheet ws,
    ExcelCellAddress start, ExcelCellAddress end, Dictionary<int, string> fieldNames, int firstRow)
{
    int count = 0;
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    JsonWriter jsonWriter = null;
    jsonWriter = new JsonTextWriter(sw);
 
    //Use indentation for readability.
    jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
    jsonWriter.WriteStartObject();
    jsonWriter.WritePropertyName(CollectionElement);
    jsonWriter.WriteStartArray();
    int countDoEvents = 0;
    for (int row = firstRow; row <= end.Row; row++)
    {
        count++;
        countDoEvents++;
        if (countDoEvents >= 10)
        {
            ResultText = "Reading record " + count;
            countDoEvents = 0;
            DoEvents();
        }
        jsonWriter.WriteStartObject();
        for (int col = start.Column; col <= end.Column; col++)
        {
            jsonWriter.WritePropertyName(fieldNames[col]);
            jsonWriter.WriteValue(GetCellStringValue(ws, row, col));
        }
        jsonWriter.WriteEndObject();
    }
    jsonWriter.WriteEndArray();
    jsonWriter.WriteEndObject();
    jsonWriter.Close();
 
    ResultText = "Ended reading writing file " + outputFile;
    countDoEvents = 0;
 
    sw.Close();
    File.WriteAllText(outputFile, sb.ToString());
    ResultText = File.ReadAllText(outputFile);
    return (count);
}

Anche in questo caso, lo copio completo in modo da darvi modo di fare un copia e incolla comodo, poi lo spieghiamo per pezzi.

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
JsonWriter jsonWriter = null;
jsonWriter = new JsonTextWriter(sw);

Al contrario dell’XmlWriter che scrive su uno stream, il JsonWriter scrive su una stringa, o meglio su uno StringBuilder, questo probabilmente è dovuto al fatto che essendo nato per il Web, è più facile che Json sia restituito come stringa piuttosto che scritto su un file. Quindi cosa facciamo in questo codice:

Creiamo uno StringBuilder, lo usiamo per produrre uno StringWriter, ed utilizziamo lo StringWriter per creare un JsonWriter.

jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName(CollectionElement);
jsonWriter.WriteStartArray();

Siccome vogliamo poter leggere ad occhio nudo il nostro file Json, usiamo le opzioni di formattazione fornite dal Writer, se dovessimo scrivere Json compatto, possiamo ometterlo.

Per iniziare a creare il nostro Json, utilizziamo il metodo WriteStartObject per creare l’apertura dell’oggetto, e poi creiamo l’apertura della property che rappresenta la collezione delle righe del foglio di Excel, a questa property diamo il nome indicato dall’utente per fare questo utilizziamo WritePropertyName.

Creata la property della collection, apriamo l’oggetto array con WriteStartArray, per iniziare poi a creare gli oggetti che rappresentano gli elementi all’interno dell’array.

int count = 0;
int countDoEvents = 0;
for (int row = firstRow; row <= end.Row; row++)
{
    count++;
    countDoEvents++;
    if (countDoEvents >= 10)
    {
        ResultText = "Reading record " + count;
        countDoEvents = 0;
        DoEvents();
    }
...
 
}

Qui sopra il ciclo che scorre tutte le righe del foglio di Excel, questa prima parte del suo contenuto è quella che serve a informare l’utente di cosa stiamo facendo, utilizzando il nostro DoEvents per dar modo all’interfaccia utente di potersi aggiornare, come ho indicato, è un metodo molto grezzo, e consuma tempo e risorse, pertanto lo lancio ogni 10 elementi per non rallentare troppo l’elaborazione, se il foglio di Excel è molto grande, è opportuno aggiornare il dato ogni 100, 1000 righe come preferite.

jsonWriter.WriteStartObject();
for (int col = start.Column; col <= end.Column; col++)
{
    jsonWriter.WritePropertyName(fieldNames[col]);
    jsonWriter.WriteValue(GetCellStringValue(ws, row, col));
}
jsonWriter.WriteEndObject();

Questo è Il succo della scrittura del Json, che per ogni riga del foglio di Excel, apre un oggetto con WriteStartObject, vi scrive ognuma delle colonne con il proprio nome usando  WritePropertyName e il proprio valore  usando WriteValue e poi chiude l’oggetto, con WriteEndObject,  come potete notare, in Json non dobbiamo dare un nome all’oggetto riga, mentre in XML non abbiamo dato nome ne definito la collection ma abbiamo dato il nome ad ogni oggetto riga.

jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Close();
 
ResultText = "Ended reading writing file " + outputFile;
countDoEvents = 0;
 
sw.Close();
File.WriteAllText(outputFile, sb.ToString());
ResultText = File.ReadAllText(outputFile);
return (count);

Una volta terminato il ciclo su tutte le righe, chiudiamo l’oggetto Array con WriteEndArray, chiudiamo l’oggetto contenitore del tutto con WriteEndObject e chiudiamo il JsonWriter. Aggiornimao l’interfaccia, chiudiamo lo StringWriter, scriviamo il contenuto dello StringBuilder sul file di output e sulla variabile collegata alla Textbox Result.

E questo è tutto.

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 Jsom generico possiamo usare:

  • JsonWriter – Che permette di creare un documento Json elemento per elemento.
    • WriteStartObject – definisce l’apertura di un oggetto sia esso una property o una classe contenente property e collezioni
    • WritePropertyName – scrive il nome di una property ove necessario specificarlo per poter rileggere correttamente i dati in una classe.
    • WriteStartArray – scrive l’apertura di un oggetto collection o array che contiene multiple istanze di altri oggetti.
    • WriteValue – scrive il valore di una property
    • WriteEndArray – chiude un oggetto collection dopo che vi abbiamo scritto tutti gli elementi
    • WriteEndObject – chiude un oggetto (nel nostro caso sia la riga che il contenitore di tutti i dati.

Vediamo gli screenshot

excel_01

Qui sopra il foglio di excel che abbiamo usato come esempio, è lo stesso del post precedente.

mainwindow_02

Sull’interfaccia, selezioniamo il file di Excel che leggeremo e indichiamo il nome da dare alla collection delle righe, in questo caso usiamo Cities e premiamo il tasto di generazione.

mainwindow_03

Al termine dell’elaborazione, l’interfacccia rimarrà bloccata con la clessidra per una trentina di secondi, perché deve leggere e porre nella Textbox l’intero file che ha oltre 112000 righe, è ovvio che in un applicazione del mondo reale questo tipo di operazione è sconsigliabile con file molto grandi. Al termine, premendo Yes, nel mio caso il file viene aperto in Visual Studio.

{
  "Cities": [
    {
      "REF_DMI": "DMI-W001",
      "DMI-W001": "Abano Terme",
      "DMI-L001": "Abano Terme",
      "DMI-L002": "PD",
      "DMI-F001": "Veneto",
      "DMI-S001": "248",
      "DMI-A001": "JN55VI",
      "DMI-L003": null,
      "DMI-F002": "A001",
      "DMI-B001": "19349",
      "DMI-T001": null,
      "DMI-J001": null
    },

…

    {
      "REF_DMI": null,
      "DMI-W001": null,
      "DMI-L001": null,
      "DMI-L002": null,
      "DMI-F001": null,
      "DMI-S001": null,
      "DMI-A001": null,
      "DMI-L003": null,
      "DMI-F002": null,
      "DMI-B001": null,
      "DMI-T001": null,
      "DMI-J001": null
    }
  ]
}

Vi risparmio le 112000 righe ma vi mostro il risultato, WriteStartObject crea le parentesi graffe, WritePropertyName crea i nomi dell’array e delle property, WriteStartArray scrive la Quadra aperta, WriteValue scrive i valori dentro alle property, WriteEndObject chiude le graffe, WriteEndArray chiude le quadre.

I nomi delle property con i trattini, non danno fastidio a Json ma lo daranno a C#, vedremo nel prossimo post come fare a ovviare al problema quando importeremo i file XML e JSON in una classe C#.

Potete scaricare il progetto esempio dal link qui indicato:

Icon

Companion code, Convertire un foglio di Excel in JSON

1 file(s) 937.72 KB
Download codice esempio

Per qualsiasi domanda, osservazione, commento, approfondimento, o per segnalare un errore, potete usare il link alla form di contatto in cima alla pagina.

Published in C#, Tutorial, Uncategorized and WPF Windows Presentation Foundation