Search results

Create a JSON structure in JavaScript (ES5) Spreadsheet control

18 Oct 2021 / 8 minutes to read

This topic guides you to construct a JSON structure that can be passed to the openFromJson method to render the spreadsheet. The JSON structure is an object with the key as Workbook and the properties of the spreadsheet as value.

Copied to clipboard
{ Workbook: {} }

The following properties are the root level properties of the Workbook object.

Property Type Description
activeSheetIndex number Specifies 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 Specified cell will be 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, if the text width exceeds the column width.
isLocked boolean Specifies the cell whether it is locked or not, for allowing edit range in the spreadsheet protect option.
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 image of 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.
Copied to clipboard
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 Validation Type.
operator ValidationOperator Specifies Validation Operator.
value1 string Specifies Validation Minimum Value.
value2 string Specifies Validation Maximum Value.
ignoreBlank boolean Specifies IgnoreBlank option in Data Validation.
inCellDropDown boolean Specifies InCellDropDown option in Data Validation.
isHighlighted boolean Specifies to allow Highlight Invalid Data.
Copied to clipboard
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 Conditional formatting Type.
format Format Specifies format.
cFColor CFColor Specifies Conditional formatting Highlight Color.
value string Specifies Conditional formatting value.
range string Specifies Conditional formatting range.
Copied to clipboard
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.

In the following demo, the JSON structure is passed to the openFromJson method to render the spreadsheet in the created event.

Source
Preview
index.js
index.html
Copied to clipboard
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    created: function () {
        spreadsheet.openFromJson({ file: jsonData });
    }
});

spreadsheet.appendTo('#spreadsheet');

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"
      }
    ]
  }
}
Copied to clipboard
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
</head>

<body>
       <!--Element which is going to render-->
       
       <div id="container">
       <div id="spreadsheet"></div>
       </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>