-
Notifications
You must be signed in to change notification settings - Fork 270
JSON Export
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.
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.
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.
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"
}
{
"v": "1635811200000",
"t": "2021-11-02"
}
{
"v": "3720000",
"t": "01:02:00"
}
{
"v": "1635724800000",
"t": "2021-11-01",
"comment": "Comment in A2"
}
{
"v": "https://epplussoftware.com",
"t": "https://epplussoftware.com",
"uri": "https://epplussoftware.com"
}
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"
}
]
}
]
}
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"
}
]
}
]
}
}
- For more details have a look at 2.6 in the sample project Sample 2.6-C# or Sample 2.6-VB.
- Also see our sample web site with EPPlus.
- Export data
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles