Create an object structure in ASP.NET Core Spreadsheet Control
28 Jul 202624 minutes to read
This topic explains how to construct a JSON object that can be passed to the openFromJson method to render the Spreadsheet. The JSON object contains a Workbook key whose value defines the Spreadsheet properties.
{
Workbook: {}
}The following table lists the root-level properties of the Workbook object.
| Property | Type | Description |
|---|---|---|
| activeSheetIndex | number | Specifies the active sheet index in the workbook. |
| sheets | Sheet[] |
Contains a list of sheet properties. |
| definedNames | DefineName[] |
Specifies the name for a range and uses it in the formula for calculation. |
The following table defines each property of the Sheet.
| Property | Type | Description |
|---|---|---|
| name | string | Specifies the name of the sheet. |
| selectedRange | string | Specifies selected range in the sheet. |
| activeCell | string | Specifies active cell within selectedRange in the sheet. |
| topLeftCell | string | Specifies the cell positioned at the upper-left corner of the sheet. |
| showHeaders | boolean | Specifies to show or hide column and row headers in the sheet. |
| showGridLines | boolean | Specifies to show or hide gridlines in the sheet. |
| isProtected | boolean | Specifies to protect the cells in the sheet. |
| state | SheetState |
Specifies the sheet visibility state. There must be at least one visible sheet in Spreadsheet. |
| columns | Column[] |
Contains a list of column properties. |
| rows | Row[] |
Contains a list of row properties. |
| protectSettings | ProtectSettings |
Configures protect and its options. |
| conditionalFormats | ConditionalFormat[] |
Specifies the conditional formatting for the sheet. |
The following table defines each property of the Column.
| Property | Type | Description |
|---|---|---|
| width | number | Specifies the width of the column. |
| customWidth | boolean | Specifies custom width of the column. |
| hidden | boolean | To hide or show the column in the sheet. |
The following table defines each property of the Row.
| Property | Type | Description |
|---|---|---|
| height | number | Specifies the height of the row. |
| customHeight | boolean | Specifies the custom height of the row. |
| hidden | boolean | To hide or show the row in the sheet. |
| cells | Cell[] |
Contains a list of cell properties |
The following table defines each property of the Cell.
| Property | Type | Description |
|---|---|---|
| value | string | Defines the value of the cell which can be text or number. |
| formula | string | Defines the formula or expression of the cell. |
| format | string | Specifies the number format code to display the value in specified number format. |
| hyperlink | string | Specifies the hyperlink of the cell. |
| wrap | boolean | Wraps the cell text to the next line when its width exceeds the column width. |
| isLocked | boolean | Specifies whether the cell is locked when worksheet protection is enabled. |
| colSpan | number | Specifies the column-wise cell merge count. |
| rowSpan | number | Specifies the row-wise cell merge count. |
| style | CellStyle |
Specifies the cell style options. |
| validation | Validation |
Specifies the validation of the cell. |
| image | Image[] |
Specifies the images associated with the cell. |
The following table defines each property of the CellStyle.
| Property | Type | Description |
|---|---|---|
| fontFamily | FontFamily |
Specifies font family of the cell. |
| verticalAlign | VerticalAlign |
Specifies vertical align of the cell. |
| textAlign | TextAlign |
Specifies text align style of the cell. |
| textIndent | string | Specifies text indent style of the cell. |
| color | string | Specifies font color of the cell. |
| backgroundColor | string | Specifies the background color of the cell. |
| fontWeight | FontWeight |
Specifies font weight of the cell. |
| fontStyle | FontStyle |
Specifies font style of the cell. |
| fontSize | string | Specifies font size of the cell. |
| textDecoration | TextDecoration |
Specifies text decoration of the cell. |
| border | string | Specifies border of the cell. |
| borderTop | string | Specifies top border of the cell. |
| borderBottom | string | Specifies bottom border of the cell. |
| borderLeft | string | Specifies left border of the cell. |
| borderRight | string | Specifies right border of the cell. |
Cell style type definitions
type FontFamily = 'Arial' | 'Arial Black' | 'Axettac Demo' | 'Batang' | 'Book Antiqua' | 'Calibri' | 'Courier' | 'Courier New' | 'Din Condensed' | 'Georgia' | 'Helvetica' | 'Helvetica New' | 'Roboto' | 'Tahoma' | 'Times New Roman' | 'Verdana';
type VerticalAlign = 'bottom' | 'middle' | 'top';
type TextAlign = 'left' | 'center' | 'right';
type FontWeight = 'bold' | 'normal';
type FontStyle = 'italic' | 'normal';
type TextDecoration = 'underline' | 'line-through' | 'underline line-through' | 'none';The following table defines each property of the Validation.
| Property | Type | Description |
|---|---|---|
| type | ValidationType |
Specifies the validation type. |
| operator | ValidationOperator |
Specifies the validation operator. |
| value1 | string | Specifies the minimum validation value. |
| value2 | string | Specifies the maximum validation value. |
| ignoreBlank | boolean | Specifies whether blank cells are ignored. |
| inCellDropDown | boolean | Specifies whether the in-cell drop-down is displayed. |
| isHighlighted | boolean | Specifies whether invalid data is highlighted. |
Validation type definitions
type ValidationType = 'WholeNumber' | 'Decimal' | 'Date' | 'TextLength' | 'List' | 'Time';
type ValidationOperator = 'Between' | 'NotBetween' | 'EqualTo' | 'NotEqualTo' | 'LessThan' | 'GreaterThan' | 'GreaterThanOrEqualTo' | 'LessThanOrEqualTo';The following table defines each property of the Image.
| Property | Type | Description |
|---|---|---|
| src | string | Specifies the image source. |
| id | string | Specifies image element id. |
| height | number | Specifies the height of the image. |
| width | number | Specifies the width of the image. |
| top | number | Specifies the top position of the image. |
| left | number | Specifies the left position of the image. |
The following table defines each property of the ConditionalFormat.
| Property | Type | Description |
|---|---|---|
| type |
HighlightCell or TopBottom or DataBar or ColorScale or IconSet
|
Specifies the conditional-formatting type. |
| cFColor | CFColor |
Specifies a predefined color for highlighting cells that meet the condition. |
| format | Format |
Specifies the custom formatting to apply when preferred formatting is required instead of the predefined cFColor options. |
| value | string | Specifies the conditional-formatting value. |
| range | string | Specifies the range to which conditional formatting is applied. |
The HighlightCell, TopBottom, DataBar, ColorScale, and IconSet types are defined in the following type definitions.
Conditional-formatting type definitions
type HighlightCell = 'GreaterThan' | 'LessThan' | 'Between' | 'EqualTo' | 'ContainsText' | 'DateOccur' | 'Duplicate' | 'Unique';
type TopBottom = 'Top10Items' | 'Bottom10Items' | 'Top10Percentage' | 'Bottom10Percentage' | 'BelowAverage' | 'AboveAverage';
type DataBar = 'BlueDataBar' | 'GreenDataBar' | 'RedDataBar' | 'OrangeDataBar' | 'LightBlueDataBar' | 'PurpleDataBar';
type ColorScale = 'GYRColorScale' | 'RYGColorScale' | 'GWRColorScale' | 'RWGColorScale' | 'BWRColorScale' | 'RWBColorScale' | 'WRColorScale' | 'RWColorScale' | 'GWColorScale' | 'WGColorScale' | 'GYColorScale' | 'YGColorScale';
type IconSet = 'ThreeArrows' | 'ThreeArrowsGray' | 'FourArrowsGray' | 'FourArrows' | 'FiveArrowsGray' | 'FiveArrows' | 'ThreeTrafficLights1' | 'ThreeTrafficLights2' | 'ThreeSigns' | 'FourTrafficLights' | 'FourRedToBlack' | 'ThreeSymbols' | 'ThreeSymbols2' | 'ThreeFlags' | 'FourRating' | 'FiveQuarters' | 'FiveRating' | 'ThreeTriangles' | 'ThreeStars' | 'FiveBoxes';
type CFColor = 'RedFT' | 'YellowFT' | 'GreenFT' | 'RedF' | 'RedT';The following table defines each property of the Format.
| Property | Type | Description |
|---|---|---|
| format | string | Specifies the number format code to display the value in specified number format. |
| style | CellStyle |
Specifies the cell style options. |
The following table defines each property of the DefinedName.
| Property | Type | Description |
|---|---|---|
| name | string | Specifies a name for the defined name, which can be used in the formula. |
| scope | string | Specifies scope for the defined name. |
| comment | string | Specifies comment for the defined name. |
| refersTo | string | Specifies reference for the defined name. |
Bind the created event when rendering the Spreadsheet. In the created event handler, obtain the rendered Spreadsheet instance and call the openFromJson() method with the constructed Workbook JSON object to render the Spreadsheet.
The following example passes the constructed JSON object to the openFromJson() method in the created event handler to render the Spreadsheet.
<div class="control-section" >
<ejs-spreadsheet id="spreadsheet" created="created">
</ejs-spreadsheet>
</div>
</div>
<script>
var jsonData = {
"Workbook": {
"definedNames": [],
"sheets": [
{
"columns": [
{
"width": 100
},
{
"width": 158
},
{
"width": 72
},
{
"width": 113
},
{
"width": 113
},
{
"width": 77
},
{
"width": 97
},
{
"width": 73
}
],
"conditionalFormats": [
{
"type": "GYRColorScale",
"range": "C3:C10"
},
{
"type": "LessThan",
"cFColor": "RedF",
"range": "G3:G10",
"value": "43707,"
},
{
"type": "BlueDataBar",
"range": "D3:D10"
},
{
"type": "GreenDataBar",
"range": "E3:E10"
},
{
"type": "Top10Items",
"format": {
"style": {
"backgroundColor": "#009999",
"fontWeight": "Bold"
}
},
"range": "F3:F10",
"value": "1,"
},
{
"type": "Bottom10Items",
"format": {
"style": {
"backgroundColor": "#C68D53",
"fontWeight": "Bold"
}
},
"range": "F3:F10",
"value": "1,"
},
{
"type": "ThreeStars",
"range": "H3:H10"
}
],
"name": "Inventory List",
"rows": [
{
"cells": [
{
"colSpan": 8,
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
},
"value": "Inventory List"
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
},
{
"style": {
"fontSize": "13pt",
"fontWeight": "Bold",
"textAlign": "Center",
"verticalAlign": "Middle"
}
}
],
"height": 30
},
{
"cells": [
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Item Code"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Item Name"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Quantity"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Purchase Price"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Selling Price"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Profit"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Last Updated"
},
{
"style": {
"fontWeight": "Bold",
"textAlign": "Center"
},
"value": "Rating"
}
]
},
{
"cells": [
{
"value": "AG940Z"
},
{
"value": "Laser Printer"
},
{
"value": "144"
},
{
"format": "$#,##0.00",
"value": "169.5"
},
{
"format": "$#,##0.00",
"value": "172"
},
{
"format": "$#,##0.00",
"formula": "=E3-D3"
},
{
"format": "mm-dd-yyyy",
"value": "43610"
},
{
"value": "4.5"
}
]
},
{
"cells": [
{
"value": "BJ120K"
},
{
"value": "Scientific Calculator"
},
{
"value": "116"
},
{
"format": "$#,##0.00",
"value": "21.8"
},
{
"format": "$#,##0.00",
"value": "23"
},
{
"format": "$#,##0.00",
"formula": "=E4-D4"
},
{
"format": "mm-dd-yyyy",
"value": "43674"
},
{
"value": "4"
}
]
},
{
"cells": [
{
"value": "BC120M"
},
{
"value": "Wired Keyboard"
},
{
"value": "438"
},
{
"format": "$#,##0.00",
"value": "26.8"
},
{
"format": "$#,##0.00",
"value": "29"
},
{
"format": "$#,##0.00",
"formula": "=E5-D5"
},
{
"format": "mm-dd-yyyy",
"value": "43920"
},
{
"value": "4.25"
}
]
},
{
"cells": [
{
"value": "BS121L"
},
{
"value": "Memory Card"
},
{
"value": "486"
},
{
"format": "$#,##0.00",
"value": "23.5"
},
{
"format": "$#,##0.00",
"value": "25"
},
{
"format": "$#,##0.00",
"formula": "=E6-D6"
},
{
"format": "mm-dd-yyyy",
"value": "43697"
},
{
"value": "3.5"
}
]
},
{
"cells": [
{
"value": "BU121K"
},
{
"value": "Coffee Maker"
},
{
"value": "176"
},
{
"format": "$#,##0.00",
"value": "56.5"
},
{
"format": "$#,##0.00",
"value": "59"
},
{
"format": "$#,##0.00",
"formula": "=E7-D7"
},
{
"format": "mm-dd-yyyy",
"value": "43863"
},
{
"value": "4.5"
}
]
},
{
"cells": [
{
"value": "BD121M"
},
{
"value": "Table Lamp"
},
{
"value": "0"
},
{
"format": "$#,##0.00",
"value": "22.5"
},
{
"format": "$#,##0.00",
"value": "25"
},
{
"format": "$#,##0.00",
"formula": "=E8-D8"
},
{
"format": "mm-dd-yyyy",
"value": "43780"
},
{
"value": "5"
}
]
},
{
"cells": [
{
"value": "AT992X"
},
{
"value": "Document Scanner"
},
{
"value": "116"
},
{
"format": "$#,##0.00",
"value": "175"
},
{
"format": "$#,##0.00",
"value": "177"
},
{
"format": "$#,##0.00",
"formula": "=E9-D9"
},
{
"format": "mm-dd-yyyy",
"value": "43568"
},
{
"value": "4.75"
}
]
},
{
"cells": [
{
"value": "AP992Z"
},
{
"value": "Gaming Headset"
},
{
"value": "58"
},
{
"format": "$#,##0.00",
"value": "32"
},
{
"format": "$#,##0.00",
"value": "35"
},
{
"format": "$#,##0.00",
"formula": "=E10-D10"
},
{
"format": "mm-dd-yyyy",
"value": "43875"
},
{
"value": "4.4"
}
]
}
],
"selectedRange": "A1:H1"
}
]
}
};
// Triggers after the spreadsheet is created.
function created() {
var spreadsheet = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheet.openFromJson({ file: jsonData });
}
</script>public IActionResult Open(IFormCollection openRequest)
{
OpenRequest open = new OpenRequest();
open.File = openRequest.Files[0];
return Content(Workbook.Open(open));
}
public IActionResult Save(SaveSettings saveSettings)
{
return Workbook.Save(saveSettings);
}