**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

*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](<https://samples.epplussoftware.com/JsonExport>).*

## 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.

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

### Datetime

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

### TimeSpan/Time of day

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

### Cell with a comment

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

### Cell with a hyperlink

```Json
{
    "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](<https://samples.epplussoftware.com/JsonExport>)

```csharp
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.
});
```

```vbnet
Dim json = worksheet.Cells("A1:B3").ToJson(Sub(x)
                                               x.Minify = False
                                               x.AddDataTypesOn = eDataTypeOn.OnColumn 'The data type will be identified by the first value in the column range.
                                           End Sub)
```

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.

```Json
{
    "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.

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

```vbnet
Dim json = table.ToJson(Sub(x)
                            x.Minify = False
                        End Sub)
```

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

```Json
{
    "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"
                    }
                ]
            }
        ]
    }
}
```

## See also

- For more details have a look at 2.6 in the sample project [Sample 2.6-C\#](<https://github.com/EPPlusSoftware/EPPlus.Samples.CSharp/tree/master/02-Import and export/06-Export to Json>) or [Sample 2.6-VB](<https://github.com/EPPlusSoftware/EPPlus.Samples.VB/tree/master/02-Import and export/06-Export to Json>).
- Also see our [sample web site](<https://samples.epplussoftware.com/JsonExport>) with EPPlus.
- [Export data](<https://epplussoftware.com/docs/import-export-data/export/index.md>)