JavaScript
EditEdit This Document
Demos
Support
Forum
Upgrade Guide
FREE TRIAL
Search results

Excel Cell Style Customization in JavaScript Grid control

24 May 2022 / 3 minutes to read

Conditional cell formatting

Grid cells in the exported Excel can be customized or formatted using excelQueryCellInfo event. In this event, we can format the grid cells of exported PDF document based on the column cell value.

In the below sample, we have set the background color for Freight column in the exported excel by args.cell and backColor property.

Source
Preview
index.ts
index.html
Copied to clipboard
import { Grid, Toolbar, ExcelExport, Page } from '@syncfusion/ej2-grids';
import { data } from './datasource.ts';
Grid.Inject(Toolbar, ExcelExport, Page);

let grid: Grid = new Grid({
    dataSource: data,
    allowPaging: true,
    allowExcelExport: true,
    toolbar: ['ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', type: 'string' },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120 },
        { field: 'OrderDate', headerText: 'Order Date', width: 140, format: 'yMd', textAlign: 'Right' }
    ],
    height: 230
});
grid.toolbarClick = (args: Object) => {
    if (args['item'].id === 'Grid_excelexport') {
        grid.excelExport();
    }
}
grid.excelQueryCellInfo = (args: Object) => {
        if(args.column.field == 'Freight'){
            if(args.value < 30) {
                args.style = {backColor: '#99ffcc'};
            }
            else if(args.value < 60) {
                args.style = {backColor: '#ffffb3'};
            }
            else {
                args.style = {backColor: '#ff704d'};
            }
        }
    }
grid.queryCellInfo = (args: Object) => {
        if(args.column.field == 'Freight'){
            if(args.data['Freight'] < 30) {
                args.cell.bgColor = '#99ffcc';
            }
            else if(args.data['Freight'] < 60) {
                args.cell.bgColor = '#ffffb3';
            }
            else {
                args.cell.bgColor = '#ff704d';
            }
        }
    }
grid.appendTo('#Grid');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
</head>
<body>
    <div id='loader'>Loading....</div>
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id='container'>
        <div id='Grid'></div>        
    </div>
</body>
</html>

Theme

The excel export provides an option to include theme for exported excel document.

To apply theme in exported Excel, define the theme in exportProperties .

Source
Preview
index.ts
index.html
Copied to clipboard
import { Grid, Toolbar, ExcelExport, ExcelExportProperties, Page } from '@syncfusion/ej2-grids';
import { data } from './datasource.ts';

Grid.Inject(Toolbar, ExcelExport, Page);

let grid: Grid = new Grid({
    dataSource: data,
    allowPaging: true,
    allowExcelExport: true,
    toolbar: ['ExcelExport'],
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 120, type: 'number' },
        { field: 'CustomerID', width: 140, headerText: 'Customer ID', type: 'string' },
        { field: 'Freight', headerText: 'Freight', textAlign: 'Right', width: 120, format: 'C' },
        { field: 'OrderDate', headerText: 'Order Date', width: 140, format: 'yMd', textAlign: 'Right' }
    ],
    height: 230
});
grid.toolbarClick = (args: Object) => {
    if (args['item'].id === 'Grid_excelexport') {
        let excelExportProperties: ExcelExportProperties = {
            theme:
                {
                    header: { fontName: 'Segoe UI', fontColor: '#666666' },
                    record: { fontName: 'Segoe UI', fontColor: '#666666' },
                    caption: { fontName: 'Segoe UI', fontColor: '#666666' }
                }
        };
        grid.excelExport(excelExportProperties);

    }
}
grid.appendTo('#Grid');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
    <style>
        .e-row[aria-selected="true"] .e-customizedExpandcell {
            background-color: #e0e0e0;
        }
        .e-grid.e-gridhover tr[role='row']:hover {
            background-color: #eee;
        }
        .e-expand::before {
        content: '\e5b8';
    }
    </style>
</head>
<body>
    <div id='loader'>Loading....</div>
    <script id="template" type="text/x-template">
        <input id='${OrderID}' value='${Freight}' class='custemp' type='text' style='width: 100%'>
    </script>
    <div id='container'>
        <div id='Grid'></div>        
    </div>
</body>
</html>

By default, material theme is applied to exported excel document.

Previous
Next