Formatting options make your data easier to view and understand. The different types of formatting options in the Spreadsheet are,
Number formatting provides a type for your data in the Spreadsheet. Use the allowNumberFormatting
property to enable or disable the number formatting option in the Spreadsheet. The different types of number formatting supported in Spreadsheet are,
Types | Format |
---|---|
General(default) | NA |
Number | 0.00 |
Currency | $#,##0.00 |
Accounting | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) |
ShortDate | mm-dd-yyyy |
LongDate | dddd, mmmm dd, yyyy |
Time | h:mm:ss AM/PM |
Percentage | 0.00% |
Fraction | # ?/? |
Scientific | 0.00E+00 |
Text | @ |
Number formatting can be applied in following ways,
format
property in cell
, you can set the desired format to each cell at initial load.numberFormat
method, you can set the number format to a cell or range of cells.The following code example shows the number formatting in cell data.
<template>
<ejs-spreadsheet ref="spreadsheet" :created="created" :showFormulaBar="false"
:showRibbon="false" :showSheetTabs="false" :allowInsert="false" :allowDelete="false">
<e-sheets>
<e-sheet :showGridLines="false">
<e-ranges>
<e-range :dataSource="dataSource" startCell="A2"></e-range>
</e-ranges>
<e-columns>
<e-column :width=140></e-column>
<e-column :width=140></e-column>
<e-column :width=160></e-column>
<e-column :width=160></e-column>
<e-column :width=160></e-column>
<e-column :width=120></e-column>
</e-columns>
<e-rows>
<e-row :height=35 customHeight="true">
<e-cells>
<e-cell value="Sales Team Summary" colSpan=6></e-cell>
</e-cells>
</e-row>
<e-row :index=10>
<e-cells>
<e-cell index=1 value="Total:"></e-cell>
<e-cell formula="=SUM(C3:C10)" format="$#,##0.00"></e-cell>
<e-cell formula="=SUM(D3:D10)" format='_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'></e-cell>
<e-cell formula="=SUM(E3:E10)" format='_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'></e-cell>
</e-cells>
</e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import Vue from "vue";
import { SpreadsheetPlugin } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
Vue.use(SpreadsheetPlugin);
export default {
data: () => {
return {
dataSource: data,
}
},
methods: {
created: function () {
var spreadsheet = this.$refs.spreadsheet;
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', textAlign: 'center',
color: '#ffffff', borderBottom: '1px solid #e0e0e0' }, 'A2:F2');
spreadsheet.cellFormat({ borderTop: '1px solid #e0e0e0', backgroundColor: '#EEEEEE' }, 'A11:F11');
spreadsheet.cellFormat({ fontWeight: 'bold', fontStyle: 'italic' }, 'B11');
spreadsheet.cellFormat({ verticalAlign: 'middle', textAlign: 'center', fontSize: '16pt', fontWeight: 'bold', border: '1px solid #e0e0e0', backgroundColor: '#EEEEEE', color: '#279377' }, 'A1');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A2:F11', 'Outer');
// Applied Accounting format to the cells from C3 to E10 range.
spreadsheet.numberFormat('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)', 'C3:E10');
// Applied Percentage format to the cells from C3 to E11 range.
spreadsheet.numberFormat('0%', 'F3:F10');
}
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
</style>
define(["require", "exports"], function (require, exports) {
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.data = [
{
'Salesperson': 'Jeffrey Burke',
'Region Covered': 'Oklahoma',
'February 2019 Sales': '28000',
'Cost of Sales': '2460',
'January 2019 Sales': '21238',
'Percent Change': '.32'
},
{
'Salesperson': 'Amy Fernandez',
'Region Covered': 'North Carolina',
'February 2019 Sales': '23138',
'Cost of Sales': '1521',
'January 2019 Sales': '23212',
'Percent Change': '0'
},
{
'Salesperson': 'Mark Hayes',
'Region Covered': 'Massachusetts',
'February 2019 Sales': '25092',
'Cost of Sales': '1530',
'January 2019 Sales': '20454',
'Percent Change': '.23'
},
{
'Salesperson': 'Judith Ray',
'Region Covered': 'California',
'February 2019 Sales': '21839',
'Cost of Sales': '1923',
'January 2019 Sales': '24619',
'Percent Change': '-.11'
},
{
'Salesperson': 'Rany Graham',
'Region Covered': 'South Carolina',
'February 2019 Sales': '23342',
'Cost of Sales': '2397',
'January 2019 Sales': '20045',
'Percent Change': '.16'
},
{
'Salesperson': 'Christina Foster',
'Region Covered': 'Delaware',
'February 2019 Sales': '23368',
'Cost of Sales': '1500',
'January 2019 Sales': '17537',
'Percent Change': '.33'
},
{
'Salesperson': 'Judy Green',
'Region Covered': 'Texas',
'February 2019 Sales': '21510',
'Cost of Sales': '1657',
'January 2019 Sales': '24951',
'Percent Change': '-.14' },
{
'Salesperson': 'Paula Hall',
'Region Covered': 'Virginia',
'February 2019 Sales': '21314',
'Cost of Sales': '2418',
'January 2019 Sales': '18082',
'Percent Change': '.18'
}
];
});
Text and cell formatting enhances the look and feel of your cell. It helps to highlight a particular cell or range of cells from a whole workbook. You can apply formats like font size, font family, font color, text alignment, border etc. to a cell or range of cells. Use the allowCellFormatting
property to enable or disable the text and cell formatting option in Spreadsheet. You can set the formats in following ways,
style
property, you can set formats to each cell at initial load.cellFormat
method, you can set formats to a cell or range of cells.Various font formats supported in the spreadsheet are font-family, font-size, bold, italic, strike-through, underline and font color.
You can align text in a cell either vertically or horizontally using the textAlign
and verticalAlign
property.
To enhance the appearance of text in a cell, you can change the indentation of a cell content using textIndent
property.
To highlight cell or range of cells from whole workbook you can apply background color for a cell using backgroundColor
property.
You can add borders around a cell or range of cells to define a section of worksheet or a table. The different types of border options available in the spreadsheet are,
Types | Actions |
---|---|
Top Border | Specifies the top border of a cell or range of cells. |
Left Border | Specifies the left border of a cell or range of cells. |
Right Border | Specifies the right border of a cell or range of cells. |
Bottom Border | Specifies the bottom border of a cell or range of cells. |
No Border | Used to clear the border from a cell or range of cells. |
All Border | Specifies all border of a cell or range of cells. |
Horizontal Border | Specifies the top and bottom border of a cell or range of cells. |
Vertical Border | Specifies the left and right border of a cell or range of cells. |
Outside Border | Specifies the outside border of a range of cells. |
Inside Border | Specifies the inside border of a range of cells. |
You can also change the color, size, and style of the border. The size and style supported in the spreadsheet are,
Types | Actions |
---|---|
Thin | Specifies the 1px border size (default). |
Medium | Specifies the 2px border size. |
Thick | Specifies the 3px border size. |
Solid | Used to create the solid border (default). |
Dashed | Used to create the dashed border. |
Dotted | Used to create the dotted border. |
Double | Used to create the double border. |
Borders can be applied in the following ways,
border
, borderLeft
, borderRight
, borderBottom
properties, you can set the desired border to each cell at initial load.setBorder
method, you can set various border options to a cell or range of cells.The following code example shows the style formatting in text and cells of the spreadsheet.
<template>
<ejs-spreadsheet ref= "spreadsheet" :created="created" :showFormulaBar="false" :showRibbon="false"
:showSheetTabs="false" :allowInsert="false" :allowDelete="false" :allowEditing="false">
<e-sheets>
<e-sheet :showGridLines="false">
<e-ranges>
<e-range :dataSource="dataSource" startCell="A2"></e-range>
</e-ranges>
<e-columns>
<e-column :width=100></e-column>
<e-column :width=200></e-column>
<e-column :width=110></e-column>
<e-column :width=140></e-column>
<e-column :width=90></e-column>
</e-columns>
<e-rows>
<e-row :height=35 customHeight="true">
<e-cells>
<e-cell value="Order Summary" colSpan=5></e-cell>
</e-cells>
</e-row>
<e-row :height=30>
<e-cells>
<e-cell index=2></e-cell>
</e-cells>
</e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
<e-row :height=30></e-row>
</e-rows>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import Vue from "vue";
import { SpreadsheetPlugin } from "@syncfusion/ej2-vue-spreadsheet";
import { data } from './data.js';
Vue.use(SpreadsheetPlugin);
export default {
data: () => {
return {
dataSource: data,
}
},
methods: {
created: function () {
var spreadsheet = this.$refs.spreadsheet;
// Setting common styles to table header cells
spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', color: '#ffffff' }, 'A2:E2');
// Setting common styles to whole table cells
spreadsheet.cellFormat({ verticalAlign: 'middle', fontFamily: 'Axettac Demo' }, 'A2:E12');
spreadsheet.cellFormat({textAlign: 'right'}, 'B2');
spreadsheet.cellFormat({ fontFamily: 'Axettac Demo', verticalAlign: 'middle', textAlign: 'center', fontSize: '18pt', fontWeight: 'bold', color: '#279377', border: '1px solid #e0e0e0' }, 'A1');
// Column wise styles setting
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A12');
// Setting text-indent to 2 and 4 column
var style = { textAlign: 'left', textIndent: '8pt' };
spreadsheet.cellFormat(style, 'B2:B12');
spreadsheet.cellFormat(style, 'D2:D12');
spreadsheet.cellFormat({ fontStyle: 'italic', textAlign: 'right' }, 'C3:C12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'E2:E12');
// Applied border to range of cells using 'setBorder' method
spreadsheet.setBorder({ borderLeft: '1px solid #e0e0e0', borderRight: '1px solid #e0e0e0' }, 'A2:E2');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A4:E11', 'Horizontal');
spreadsheet.setBorder({ border: '1px solid #e0e0e0' }, 'A3:E12', 'Outer');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E3:E4');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E9');
spreadsheet.cellFormat({ color: '#10c469', textDecoration: 'line-through' }, 'E12');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E5');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E8');
spreadsheet.cellFormat({ color: '#FFC107', textDecoration: 'underline' }, 'E11');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E6');
spreadsheet.cellFormat({ color: '#62c9e8' }, 'E10');
spreadsheet.cellFormat({ color: '#ff5b5b' }, 'E7');
}
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
</style>
define(["require", "exports"], function (require, exports) {
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.data = [
{
'Order Id': 'SF1001',
'Product': 'Laptop Backpack (Blue)',
'Ordered Date': '02/14/2014',
'Ordered By': 'Rahul Sharma',
'Shipment': 'Delivered'
},
{
'Order Id': 'SF1002',
'Product': 'Oppo F1 S mobile back cover',
'Ordered Date': '06/11/2014',
'Ordered By': 'Adi Pathak',
'Shipment': 'Delivered'
},
{
'Order Id': 'SF1003',
'Product': 'Tupperware 4 bottle set',
'Ordered Date': '07/27/2014',
'Ordered By': 'Himani Arora',
'Shipment': 'Pending'
},
{
'Order Id': 'SF1004',
'Product': 'Tupperware Lunch box',
'Ordered Date': '11/21/2014',
'Ordered By': 'Samuel Samson',
'Shipment': 'Shipped'
},
{
'Order Id': 'SF1005',
'Product': 'Panosonic Hair Dryer',
'Ordered Date': '06/23/2014',
'Ordered By': 'Neha',
'Shipment': 'Cancelled'
},
{
'Order Id': 'SF1006',
'Product': 'Philips LED 2 bulb set',
'Ordered Date': '07/22/2014',
'Ordered By': 'Christine J',
'Shipment': 'Pending'
},
{
'Order Id': 'SF1007',
'Product': 'Moto G4 plus headphone',
'Ordered Date': '02/04/2014',
'Ordered By': 'Shiv Nagar',
'Shipment': 'Delivered'
},
{
'Order Id': 'SF1008',
'Product': 'Lakme Eyeliner Pencil',
'Ordered Date': '11/30/2014',
'Ordered By': 'Cherry',
'Shipment': 'Shipped'
},
{
'Order Id': 'SF1009',
'Product': 'Listerine mouthwash',
'Ordered Date': '07/09/2014',
'Ordered By': 'Siddartha Mishra',
'Shipment': 'Pending'
},
{
'Order Id': 'SF1010',
'Product': 'Protinex original',
'Ordered Date': '10/31/2014',
'Ordered By': 'Ravi Chugh',
'Shipment': 'Delivered'
},
];
});
The following features are not supported in Formatting:
Conditional formatting helps you to format a cell or range of cells based on the conditions applied. You can enable or disable conditional formats by using the allowConditionalFormat
property.
- The default value for the
allowConditionalFormat
property istrue
.
You can apply conditional formatting by using one of the following ways,
conditionalFormat()
method to define the condition.conditionalFormats
in sheets model.Conditional formatting has the following types in the spreadsheet,
Highlight cells rules option in the conditional formatting enables you to highlight cells with a preset color depending on the cell’s value.
The following options can be given for the highlight cells rules as type,
- ‘GreaterThan’, ‘LessThan’, ‘Between’, ‘EqualTo’, ‘ContainsText’, ‘DateOccur’, ‘Duplicate’, ‘Unique’.
The following preset colors can be used for formatting styles,
"RedFT"
- Light Red Fill with Dark Red Text,"YellowFT"
- Yellow Fill with Dark Yellow Text,"GreenFT"
- Green Fill with Dark Green Text,"RedF"
- Red Fill,"RedT"
- Red Text.
Top bottom rules option in the conditional formatting allows you to apply formatting to the cells that satisfy a statistical condition with other cells in the range.
The following options can be given for the top bottom rules as type,
- ‘Top10Items’, ‘Bottom10Items’, ‘Top10Percentage’, ‘Bottom10Percentage’, ‘BelowAverage’, ‘AboveAverage’.
You can apply data bars to represent the data graphically inside a cell. The longest bar represents the highest value and the shorter bars represent the smaller values.
The following options can be given for the data bars as type,
- ‘BlueDataBar’, ‘GreenDataBar’, ‘RedDataBar’, ‘OrangeDataBar’, ‘LightBlueDataBar’, ‘PurpleDataBar’.
Using color scales, you can format your cells with two or three colors, where different color shades represent the different cell values. In the Green-Yellow-Red(GYR) Color Scale, the cell that holds the minimum value is colored as red. The cell that holds the median is colored as yellow, and the cell that holds the maximum value is colored as green. All other cells are colored proportionally.
The following options can be given for the color scales as type,
- ‘GYRColorScale’, ‘RYGColorScale’, ‘GWRColorScale’, ‘RWGColorScale’, ‘BWRColorScale’, ‘RWBColorScale’, ‘WRColorScale’, ‘RWColorScale’, ‘GWColorScale’, ‘WGColorScale’, ‘GYColorScale’, ‘YGColorScale’.
Icon sets will help you to visually represent your data with icons. Every icon represents a range of values. In the Three Arrows(colored) icon, the green arrow icon represents the values greater than 67%, the yellow arrow icon represents the values between 33% to 67%, and the red arrow icon represents the values less than 33%.
The following options can be given for the icon sets as type,
- ‘ThreeArrows’, ‘ThreeArrowsGray’, ‘FourArrowsGray’, ‘FourArrows’, ‘FiveArrowsGray’, ‘FiveArrows’, ‘ThreeTrafficLights1’, ‘ThreeTrafficLights2’, ‘ThreeSigns’, ‘FourTrafficLights’, ‘FourRedToBlack’, ‘ThreeSymbols’, ‘ThreeSymbols2’, ‘ThreeFlags’, ‘FourRating’, ‘FiveQuarters’, ‘FiveRating’, ‘ThreeTriangles’, ‘ThreeStars’, ‘FiveBoxes’.
Using the custom format for conditional formatting you can set cell styles like color, background color, font style, font weight, and underline.
In the MAY and JUN columns, we have applied conditional formatting custom format.
- In the Conditional format, custom format supported for Highlight cell rules and Top bottom rules.
You can clear the defined rules by using one of the following ways,
clearConditionalFormat()
method to clear the defined rules.<template>
<ejs-spreadsheet ref="spreadsheet" :created="created" :showFormulaBar="false">
<e-sheets>
<e-sheet name="Car Sales Record">
<e-ranges>
<e-range :dataSource="dataSource"></e-range>
</e-ranges>
<e-columns>
<e-column :width=120></e-column>
</e-columns>
<e-conditionalformats>
<e-conditionalformat type="GreaterThan", cFColor="RedFT", value="700", range="B2:B9" ></e-conditionalformat>
<e-conditionalformat type="Bottom10Items", cFColor="YellowFT", value="4", range="C2:C9"></e-conditionalformat>
<e-conditionalformat type="BlueDataBar", range="D2:D9"></e-conditionalformat>
</e-conditionalformats>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>
</template>
<script>
import Vue from "vue";
import { SpreadsheetPlugin } from "@syncfusion/ej2-vue-spreadsheet";
import { conditionalFormatData } from './data.js';
Vue.use(SpreadsheetPlugin);
export default {
data: () => {
return {
dataSource: conditionalFormatData,
}
},
methods: {
created: function () {
var spreadsheet = this.$refs.spreadsheet;
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:N1');
spreadsheet.conditionalFormat({ type: "RYGColorScale", range: 'E2:E9' });
spreadsheet.conditionalFormat({ type: "ThreeArrows", range: 'H2:H9' });
//Custom format
spreadsheet.conditionalFormat({ type: 'Top10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#009999', fontWeight: 'bold'}}, range: 'F2:F9' });
spreadsheet.conditionalFormat({ type: 'Bottom10Items', value: '1',
format: { style: { color: '#ffffff', backgroundColor: '#c68d53', fontWeight: 'bold'}}, range: 'G2:G9' });
}
}
}
</script>
<style>
@import "../node_modules/@syncfusion/ej2-vue-spreadsheet/styles/material.css";
@import '../node_modules/@syncfusion/ej2-base/styles/material.css';
@import '../node_modules/@syncfusion/ej2-buttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-dropdowns/styles/material.css';
@import '../node_modules/@syncfusion/ej2-inputs/styles/material.css';
@import '../node_modules/@syncfusion/ej2-navigations/styles/material.css';
@import '../node_modules/@syncfusion/ej2-popups/styles/material.css';
@import '../node_modules/@syncfusion/ej2-splitbuttons/styles/material.css';
@import '../node_modules/@syncfusion/ej2-grids/styles/material.css';
@import "../node_modules/@syncfusion/ej2-spreadsheet/styles/material.css";
</style>
define(["require", "exports"], function (require, exports) {
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.conditionalFormatData = [
{ 'EV Model': 'BMW I3', 'JAN': 1224, 'FEB': 423, 'MAR': 585, 'APR': 367, 'MAY': 729, 'JUN': 733, 'TOTAL': '=SUM(B2:G2)' },
{ 'EV Model': 'Tesla Model S', 'JAN': 975, 'FEB': 763, 'MAR': 723, 'APR': 483, 'MAY': 983, 'JUN': 589, 'TOTAL': '=SUM(B3:G3)' },
{ 'EV Model': 'Chevrolet Volt', 'JAN': 113, 'FEB': 289, 'MAR': 675, 'APR': 458, 'MAY': 391, 'JUN': 198, 'TOTAL': '=SUM(B4:G4)' },
{ 'EV Model': 'Jaguar I-PACE', 'JAN': 78, 'FEB': 177, 'MAR': 244, 'APR': 99, 'MAY': 312, 'JUN': 129, 'TOTAL': '=SUM(B5:G5)' },
{ 'EV Model': 'Tesla Model X', 'JAN': 978, 'FEB': 1108, 'MAR': 1604, 'APR': 879, 'MAY': 1070, 'JUN': 1001, 'TOTAL': '=SUM(B6:G6)' },
{ 'EV Model': 'Nissan LEAF', 'JAN': 229, 'FEB': 978, 'MAR': 1202, 'APR': 822, 'MAY': 135, 'JUN': 878, 'TOTAL': '=SUM(B7:G7)' },
{ 'EV Model': 'Honda Clarity EV', 'JAN': 671, 'FEB': 1302, 'MAR': 466, 'APR': 989, 'MAY':679, 'JUN': 891, 'TOTAL': '=SUM(B8:G8)' },
{ 'EV Model': 'Toyota Prius Prime', 'JAN': 978, 'FEB': 1362, 'MAR': 1872, 'APR': 678, 'MAY': 900, 'JUN': 867, 'TOTAL': '=SUM(B9:G9)' }
]
});
The following features have some limitations in Conditional Formatting:
You can refer to our Vue Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our Vue Spreadsheet example to knows how to present and manipulate data.