JSON Export - DON-PHAM/EPPlus GitHub Wiki

EPPlus 6 provides a new functionality to export JSON directly from a range or a table. The .NET datatypes from EPPlus will be translated to a more generic format and both the raw value and the formatted value from the cells will be included. JSON export for ranges and table looks slightly different as tables contains a more structured format.

Sample

/Images/ImportExport/JsonExport1.png

Json export can be used for many purposes, one example is to export spreadsheet data to a front end ui library. See our sample site.

Datatypes

See the table below for how .NET datatypes are mapped to the export.

.NET data type	JSON datatype name	Comment
bool	boolean	Exported as 1/0
Byte, Sbyte, UInt16, UInt32, UInt64, Int16, Int32, Int64, Decimal, Double, Single (float)	number	All types mapped to the single number datatype
DateTime	datetime	Exported to milliseconds before/after January 1, 1970 (Unix time).
TimeSpan	timespan	Exported as a numeric value, total milliseconds after midnight.
string (and all other datatypes)	string	Exported as-is

Datatypes are exported in the field dt (see examples below) and is either present on the column or the cell level.

Examples of exported elements

Numeric cell value

Note that in this sample the formatted value (t) uses a comma as decimal separator, however the raw value (v) always uses a dot.

{
   "v": "10.35",
   "t": "10,35"
}

Datetime

{
   "v": "1635811200000",
   "t": "2021-11-02"
}

TimeSpan/Time of day

{
    "v": "3720000",
    "t": "01:02:00"
}

Cell with a comment

{
   "v": "1635724800000",
   "t": "2021-11-01",
   "comment": "Comment in A2"
}

Cell with a hyperlink

{
    "v": "https://epplussoftware.com",
    "t": "https://epplussoftware.com",
    "uri": "https://epplussoftware.com"
}

Export a range

To export a range you use the ToJson method or the SaveToJson method. The ToJson method returns a JSON string, while the SaveToJson saves to a file or a stream. Exporting to a range will by default set the data type on the cell level. You can alter this by using the AddDataTypesOn property in the settings argument. The json export will export values, data types, texts, comments and hyperlinks. For table it will also export some table properties like the table column data, name, showHeader and showTotals. To see how you can use the JSON export, have a look at our sample web site

var json = worksheet.Cells["A1:B3"].ToJson(x =>
{
   x.Minify = false;
   x.AddDataTypesOn=eDataTypeOn.OnColumn //The data type will be identified by the first value in the column range.
});

The output can look like this the output below. The first row is by default assumed to be the header row. You can alter the number of header rows(if any) in the settings for the method.

{
    "range": {
        "columns": [
            {
                "name": "SEK",
                "dt": "number"
            },
            {
                "name": "EUR",
                "dt": "number"
            },
            {
                "name": "USD",
                "dt": "number"
            }
        ],
        "rows": [
            {
                "cells": [
                    {
                        "v": "1",
                        "t": "1"
                    },
                    {
                        "v": "10.35",
                        "t": "10,35"
                    },
                    {
                        "v": "9.51",
                        "t": "9,51"
                    }
                ]
            },
            {
                "cells": [
                    {
                        "v": "1",
                        "t": "1"
                    },
                    {
                        "v": "10.48",
                        "t": "10,48"
                    },
                    {
                        "v": "9.59",
                        "t": "9,59"
                    }
                ]
            }
        ]
    }

Export a table

A table object also exposes the ToJson and the SaveToJson methods. A table will by default set the data type on the Column level.

var json = table.ToJson(x =>
{
    x.Minify = false;                
});

The output from a table export will look slightly different than a range as it exports the table:

{
    "table": {
        "name": "tblGradient",
        "showHeader": "1",
        "showTotal": "0",
        "columns": [
            {
                "name": "Date",
                "dt": "datetime"
            },
            {
                "name": "NumValue",
                "dt": "number"
            },
            {
                "name": "StrValue",
                "dt": "string"
            },
            {
                "name": "NumFormattedValue",
                "dt": "number"
            },
            {
                "name": "HyperLink",
                "dt": "string"
            },
            {
                "name": "TimeSpan",
                "dt": "timespan"
            }
        ],
        "rows": [
            {
                "cells": [
                    {
                        "v": "1635724800000",
                        "t": "2021-11-01",
                        "comment": "Comment in A2"
                    },
                    {
                        "v": "2",
                        "t": "2"
                    },
                    {
                        "v": "Value 2",
                        "t": "Value 2"
                    },
                    {
                        "v": "66",
                        "t": "66"
                    },
                    {
                        "v": "https://epplussoftware.com",
                        "t": "https://epplussoftware.com",
                        "uri": "https://epplussoftware.com"
                    },
                    {
                        "v": "3660000",
                        "t": "01:01:00"
                    }
                ]
            },
            {
                "cells": [
                    {
                        "v": "1635811200000",
                        "t": "2021-11-02"
                    },
                    {
                        "v": "3",
                        "t": "3"
                    },
                    {
                        "v": "Value 3",
                        "t": "Value 3"
                    },
                    {
                        "v": "99",
                        "t": "99"
                    },
                    {
                        "v": "https://epplussoftware.com",
                        "t": "https://epplussoftware.com",
                        "uri": "https://epplussoftware.com"
                    },
                    {
                        "v": "3720000",
                        "t": "01:02:00"
                    }
                ]
            }
        ]
    }
}