Spreadsheet Data Presentation

15 May 202024 minutes to read

Data presentation is helpful for proper representation of data in Spreadsheet. You have following features in Data Presentation.

Cell types
Chart
Conditional formatting
Filtering
Picture
Pivot table
Sorting
Table

Cell Type

You can insert the controls like Button, Checkbox, Dropdown list and Date picker. You can use e-allowcelltype property to enable/disable cell type operations.

To insert a Cell Type.

You can insert the cell type to the selected range of cells by using addCellTypes method.

To remove Cell Type

You can delete the cell type in the selected range of cells by using removeCellTypes method.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowcelltype="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
     syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlCellType = this.XLCellType;
    if (!this.isImport) {
        xlCellType.addCellTypes("D1", {"type" : ej.Spreadsheet.CustomCellType.Button, "text" : "Button1", "background-color" : "green" },  1);
        xlCellType.addCellTypes("B1", {"type" : ej.Spreadsheet.CustomCellType.DatePicker, 'value' : '2/12/2016'},  1);
        xlCellType.addCellTypes("A1", {"type" : ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A11'},  1);
        xlCellType.addCellTypes("C1", {"type" : ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A11'},  1);
        xlCellType.addCellTypes("E1", {"type" : ej.Spreadsheet.CustomCellType.CheckBox, "isChecked" : true },  1);
        xlCellType.removeCellTypes("C1");
    }
}

The following output is displayed as a result of the above code example.

Cell Type

Chart

Chart is a graphical representation of data, that organizes and represents a set of numerical or qualitative data. It mostly displays the selected range of data in terms of x axis and y axis. You can use e-allowcharts property to enable/disable chart operations.

Types of Chart

The following types of charts are available in Spreadsheet.

Area Chart
Bar Chart
Column Chart
Line Chart
Pie Chart
Radar Chart
Scatter Chart

You can create the Chart by one of the following ways,

Using “Chart Type” button to Select the type of chart under Charts group of INSERT Tab in ribbon.
Using createChart method to create the chart.

Chart Customization

You can perform the following customizations for chart. These are available in DESIGN Tab which is enabled while clicking the chart element.

Feature	Description
Add Chart Elements	You can add a chart element like chart axes, legends, chart title, axis title, data labels and grid lines.
Switch Row/Column	You can switch the row of the chart to column of the chart and vice versa.
Select Data	You can modify the data source of Chart.
Chart Type	You can change the type of the chart using Chart Type dialog.
Height and Width	You can change the height and width of the chart.
Chart Themes	You can change the theme of the chart. The available themes are saffron, lemon and azure in dark, light themes.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowcharts="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    if (!this.isImport) {
        this.XLChart.createChart("D1:E6", { type: "column", enable3D: false, marker: false, top: 40, left: 260, width: 340, height: 250 });
        this.performSelection("E1");
        this.XLChart.createChart("E1:F6", { type: "stackingcolumn", enable3D: false, marker: { visible: false }, top: 40, left: 620, width: 340, height: 250 });                     
    }
}

The following output is displayed as a result of the above code example.

Chart

Conditional Formatting

Conditional formatting helps you to apply formats to a cell or range with certain color based on the cells values. You can use e-allowconditionalformats property to enable/disable Conditional formats.

Condition Definition

You can define conditions such as greater than, less than, between, equal to, text contains and date occurring for selected cells and defining value for condition. It highlights the specified cell.
You can do this by one of the following ways,

Using “Conditional Formatting” option in Conditional Formatting button of HOME Tab in ribbon to open the conditional formatting dialog.
Using setCFRule method to define the condition.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowconditionalformats="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlCFormat = this.XLCFormat;
    if (!this.isImport) {
        xlCFormat.setCFRule({ "action": "greaterthan", "inputs": ["10"], "color": "redft", "range": "G2:G11" });
        xlCFormat.setCFRule({ "action": "lessthan", "inputs": ["20"], "color": "yellowft", "range": "E1:E11" });
        xlCFormat.setCFRule({ "action": "between", "inputs": ["300", "600"], "color": "greenft", "range": "F2:F11" });
        xlCFormat.setCFRule({ "action": "textcontains", "inputs": ["loafers"], "color": "redt", "range": "A1:A11" });
        xlCFormat.setCFRule({ "action": "dateoccur", "inputs": ["02/04/2014"], "color": "redft", "range": "B1:B11" });
		xlCFormat.setCFRule({ "action": "databar", "color": "redft", "range": "H1:H11" });
		xlCFormat.setCFRule({action: "colorscale", color: "gyr", range: "D2:D11"});
    }
}

The following output is displayed as a result of the above code example.

Conditional Formatting

Clear Rules

You can clear the defined rules by using one of the following ways,

Using “Clear Rules” option in Conditional Formatting button of HOME Tab in ribbon to clear the rule.
Using clearCF method to clear the defined rules.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowconditionalformats="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlCFormat = this.XLCFormat;
    if (!this.isImport) {
        xlCFormat.setCFRule({ "action": "greaterthan", "inputs": ["10"], "color": "redft", "range": "G2:G11" });
        xlCFormat.setCFRule({ "action": "lessthan", "inputs": ["20"], "color": "yellowft", "range": "E1:E11" });
        xlCFormat.setCFRule({ "action": "between", "inputs": ["300", "600"], "color": "greenft", "range": "F2:F11" });
        xlCFormat.clearCF("G2:G11");
        xlCFormat.clearCF("F2:F11");
    }
}

The following output is displayed as a result of the above code example.

Clear Rules

Filtering

Filtering allows you to view specific rows in Spreadsheet, while hiding the other rows. When a filter is added to the header row, a drop-down menu appears in each cell of the header row. You can use e-allowfiltering property to enable/disable filtering.

You can apply filtering by one of the following ways,

Using “Filter” option in Sort & Filter button under Editing group of HOME Tab in ribbon.
Using “Filter” button under Sort and Filter group of DATA Tab in ribbon.

You have following options in Filtering.

Filter by Value.
Filter by Color.
Clear Filter.

Filter by Value

You can perform filtering by using number, string. The filtered rows are only visible in the Spreadsheet. All the other rows within the filtered range were hidden.

You can do this by one of the following ways,

Using dropdown button in filter header to open the filter dialog.
Using context menu to select “Filter by Selected Cell’s Value” option in Filter.
Using filterByActiveCell method.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowfiltering="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlFilter = this.XLFilter;
    if (!this.isImport) {
        this.performSelection("E2");
        xlFilter.filterByActiveCell();
    }
}

The following output is displayed as a result of the above code example.

Filtering

Filter by Color

You can perform filtering by the selected cell color or font color. The filtered rows are only visible in the Spreadsheet. You can do this by clicking “Filter by Color” option in filter dialog to select filter by cell color or font color.

NOTE

This option is only available if the selected range of cells having any color.

Clear Filter

You can clear the filtering to show all the filtered rows in the spreadsheet within the filtered range.

You can do this by one of the following ways,

Using “Clear Filter” option in the filter dialog.
Using context menu to select “Clear Filter” option in Filter.
Using “Clear Filter” option in “Sort & Filter” button under Editing group of HOME Tab in ribbon.
Using “Clear Filter” option under Sort and Filter group of DATA Tab in ribbon.
Using clearFilter method to perform clear filtering.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowfiltering="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlFilter = this.XLFilter;
    if (!this.isImport) {
        this.performSelection("E2");
        xlFilter.filterByActiveCell();
        xlFilter.clearFilter();
    }
}

The following output is displayed as a result of the above code example.

Clear Filter

Picture

You can insert a picture by selecting the “Pictures” button under Illustrations group of INSERT Tab in ribbon.

Picture Customization

You can perform the following customizations for picture. These are available in DESIGN Tab which is enabled while clicking the picture element.

Feature	Description
Change Picture	You can change the picture with existing picture.
Reset Picture	You can reset the changes done in the picture such as border changes, height and width changes.
Picture Border	You can add border to the picture. You have Border Color, Border Type and Border weight options to draw a border.
Height and Width	You can change the height and width of the picture.

Pivot Table

Pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data to obtain a desired report. You can use e-enablepivottable property to enable/disable pivot table operations.

You can do this by one of the following ways,

Using “Pivot Table” option under Tables group of INSERT Tab in ribbon.
Using createPivotTable method to create pivot table
Using deletePivotTable method to remove the pivot table.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-enablepivottable="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.pivot" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.pivot }],  
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    if (!this.isImport) {
        var settings = {
            rows: [{fieldName: "Country"}, { fieldName: "State"}],
            columns: [{fieldName: "Product"}],
            values: [{fieldName: "Amount"},{fieldName: "Quantity"}],
            filters: [{fieldName: "Date"}]
        };
        this.XLPivot.createPivotTable("Sheet1!$A$1:$F$25", null, null, settings); 
        //this.XLPivot.deletePivotTable("name");
    }
}

The following output is displayed as a result of the above code example.

Pivot Table

Sorting

Sorting helps you to arrange the data to particular order in a selected range of cells. You can use e-allowsorting property to enable/disable sorting.

You have following options in sorting.

Sort by Ascending or Descending.
Sort by Color.

Sort by Ascending and Descending

You can perform number sorting by ascending or descending and string sorting by A to Z or Z to A to arrange the data.

You can do this by one of the following ways,

By choosing “Sort A to Z” or “Sort Z to A” in Sort & Filter button under Editing group of HOME Tab in ribbon.
Using “Sort A to Z” or “Sort Z to A” button in Sort & Filter group of DATA Tab in ribbon.
Using context menu to select “Sort A to Z” or “Sort Z to A” for strings and option in Sort.
Using “Sort A to Z” or “Sort Z to A” for strings an “Sort Smallest to Largest” or “Sort Largest to Smallest” for numbers in filter dialog.
Using sortByRange method.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowsorting="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
                rangeSettings: [{ dataSource: window.defaultData}],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var xlSort = this.XLSort, xlFormat = this.XLFormat;
    if (!this.isImport) {
        xlSort.sortByRange("A2:A10", "A", "ascending");
        xlSort.sortByRange("E2:E10", "E", "descending");     
    }
}

The following output is displayed as a result of the above code example.

Sorting

Sort by Color

You can perform sort by color to arrange the data based on the selected cell’s background color or font color. This option is only available if the selected range of cells having any color.

You can do this by one of the following ways,

Using “Sort By Color” option in filter dialog to perform sorting by cell color or font color.
Using context menu to select “Put Selected Cell Color To The Top” or “Put Selected Font Color To The Top” in Sort option.

Table

A table is a data structure that organizes information into rows and columns. You can use e-allowformatastable property enable/disable table operations.

You can do this by one of the following ways,

Using “Format As Table” under Styles group of HOME Tab in ribbon.
Using Table option under Tables group of INSERT Tab in ribbon.
Using createTable method to insert a table and removeTable to delete a table.

Table Customization

You can perform the following customizations for table. These are available in DESIGN Tab which is enabled while clicking the table.

Feature	Description
Resize Table	You can resize the table only to increase row count.
Convert to Range	You can remove the table using this option.
First Column	You can highlight the first column of the table.
Last Column	You can highlight the last column of the table.
Total Row	You can insert a new row in the bottom of the table to display the total value of the last column. You can toggle this by using checkbox.
Filter Button	You can able to hide or unhide the filter icons in the filter header of a table.

The following code example describes the above behavior.

HTML

<body ng-controller="SpreadsheetCtrl">
     <div id="Spreadsheet" ej-spreadsheet e-loadcomplete="loadComplete" e-sheets="sheetData" e-allowformatastable="true"></div>
</body>

JAVASCRIPT

var syncApp = angular.module("defaultApp", ["ngRoute", "ejangular"]);
// the datasource "window.bill" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js'
    syncApp.controller('SpreadsheetCtrl', function ($scope,$rootScope) {
         $scope.sheetData = [{
               rangeSettings: [{ dataSource: window.bill }],
            }];
            $scope.loadComplete = loadComplete;
     });
function loadComplete() {
    var i, format = [], formatObj = [],xlFormat = this.XLFormat, formatName = ["TableStyleLight8", "TableStyleLight10"];
    if (!this.isImport) {
        for (i = 0; i < formatName.length; i++)
	        formatObj[i] = { "header": true, "name": formatName[i].substr(10), "formatName": formatName[i] };
        xlFormat.createTable(formatObj[0], "A1:B4");
        xlFormat.createTable(formatObj[1], "D1:E4");
    }
}

The following output is displayed as a result of the above code example.

Table