Formulas in React Spreadsheet component
14 Dec 202324 minutes to read
Formulas are used for calculating the data in a worksheet. You can refer the cell reference from same sheet or from different sheets.
Usage
You can set formula for a cell in the following ways,
- Using the
formula
property fromcell
, you can set the formula or expression to each cell at initial load. - Set the formula or expression through data binding.
- You can set formula for a cell by
editing
. - Using the
updateCell
method, you can set or update the cell formula.
Create User Defined Functions / Custom Functions
The Spreadsheet includes a number of built-in formulas. For your convenience, a list of supported formulas can be found here.
You can define and use an unsupported formula, i.e. a user defined/custom formula, in the spreadsheet by using the addCustomFunction function. Meanwhile, remember that you should define a user defined/custom formula whose results should only return a single value. If a user-defined/custom formula returns an array, it will be time-consuming to update adjacent cell values.
The following code example shows an unsupported formula in the spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const styles = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell, secondCell) => {
return Number(firstCell) / Number(secondCell);
};
// Custom function to calculate round down for values.
const roundDownHandler = (value, digit) => {
let multiplier = Math.pow(10, digit);
return Math.floor(value * multiplier) / multiplier;
}
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:F2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Adding custom function for calculating round down for the value.
spreadsheet.addCustomFunction(roundDownHandler, 'ROUNDDOWN');
// Calculate percentage using custom added formula in E12 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
// Calculate round down for average values using custom added formula in F12 cell.
spreadsheet.updateCell({ formula: '=ROUNDDOWN(F11,1)' }, 'F12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const styles: CellStyleModel = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle: CellStyleModel = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle: CellStyleModel = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell: string, secondCell: string): number => {
return Number(firstCell) / Number(secondCell);
};
// Custom function to calculate round down for values.
const roundDownHandler = (value: number, digit: number) => {
let multiplier: number = Math.pow(10, digit);
return Math.floor(value * multiplier) / multiplier;
}
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:F2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Adding custom function for calculating round down for the value.
spreadsheet.addCustomFunction(roundDownHandler, 'ROUNDDOWN');
// Calculate percentage using custom added formula in E12 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C12,D12)' }, 'E12');
// Calculate round down for average values using custom added formula in F12 cell.
spreadsheet.updateCell({ formula: '=ROUNDDOWN(F11,1)' }, 'F12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={140}></ColumnDirective>
<ColumnDirective width={150}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
* Formula data source
*/
export let data = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12',
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3', // You can set the expression or formula as string
'Average Change': '=7.9/E3',
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' }, // You can also set as object with formula field
'Average Change': '=7.9/E4',
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' },
'Average Change': '=7.9/E5',
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6',
'Average Change': '=7.9/E6',
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7',
'Average Change': '=7.9/E7',
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8',
'Average Change': '=7.9/E8',
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9',
'Average Change': '=7.9/E9',
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10',
'Average Change': '=7.9/E10',
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11',
'Average Change': '=7.9/E11',
}
];
/**
* Formula data source
*/
export let data: Object[] = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12', // Setting formula through data binding
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3', // You can set the expression or formula as string
'Average Change': '=7.9/E3',
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' }, // You can also set as object with formula field
'Average Change': '=7.9/E4',
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' },
'Average Change': '=7.9/E5',
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6',
'Average Change': '=7.9/E6',
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7',
'Average Change': '=7.9/E7',
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8',
'Average Change': '=7.9/E8',
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9',
'Average Change': '=7.9/E9',
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10',
'Average Change': '=7.9/E10',
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11',
'Average Change': '=7.9/E11',
},
];
Second, if you want to directly compute any formula or expression, you can use the computeExpression method. This method will work for both built-in and used-defined/custom formula.
The following code example shows how to use computeExpression
method in the spreadsheet.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const styles = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell, secondCell) => {
return Number(firstCell) / Number(secondCell);
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Calculate percentage using custom added formula in E11 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C11,D11)' }, 'E11');
// Calculate expressions using computeExpression in E10 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('C10/D10') }, 'E10');
// Calculate custom formula values using computeExpression in E12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=PERCENTAGE(C12,D12)') }, 'E12');
// Calculate SUM (built-in) formula values using computeExpression in D12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=SUM(D3:D11)') }, 'D12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { CellStyleModel } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const styles: CellStyleModel = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle: CellStyleModel = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle: CellStyleModel = { fontWeight: 'bold', textAlign: 'right' };
// Custom function to calculate percentage between two cell values.
const calculatePercentage = (firstCell: string, secondCell: string): number => {
return Number(firstCell) / Number(secondCell);
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
// Adding custom function for calculating the percentage between two cells.
spreadsheet.addCustomFunction(calculatePercentage, 'PERCENTAGE');
// Calculate percentage using custom added formula in E11 cell.
spreadsheet.updateCell({ formula: '=PERCENTAGE(C11,D11)' }, 'E11');
// Calculate expressions using computeExpression in E10 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('C10/D10') as string }, 'E10');
// Calculate custom formula values using computeExpression in E12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=PERCENTAGE(C12,D12)') as string }, 'E12');
// Calculate SUM (built-in) formula values using computeExpression in D12 cell.
spreadsheet.updateCell({ value: spreadsheet.computeExpression('=SUM(D3:D11)') as string }, 'D12');
}
}, []);
return (<div>
<SpreadsheetComponent ref={spreadsheetRef} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(B3:B11)'} ></CellDirective>
<CellDirective formula={'=SUM(C3:C11)'}></CellDirective>
<CellDirective formula={'=SUM(D3:D11)'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(A3:A11)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(B3:B11)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(B3:B11)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
* Formula data source
*/
export let data = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12',
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3' // You can set the expression or formula as string
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' } // You can also set as object with formula field
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' }
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6'
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7'
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8'
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9'
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10'
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11'
}
];
/**
* Formula data source
*/
export let data: Object[] = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12', // Setting formula through data binding
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3' // You can set the expression or formula as string
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' } // You can also set as object with formula field
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' }
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6'
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7'
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8'
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9'
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10'
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11'
}
];
Formula bar
Formula bar is used to edit or enter cell data in much easier way. By default, the formula bar is enabled in the spreadsheet. Use the showFormulaBar
property to enable or disable the formula bar.
Named Ranges
You can define a meaningful name for a cell range and use it in the formula for calculation. It makes your formula much easier to understand and maintain. You can add named ranges to the Spreadsheet in the following ways,
- Using the
definedNames
collection, you can add multiple named ranges at initial load. - Use the
addDefinedName
method to add a named range dynamically. - You can remove an added named range dynamically using the
removeDefinedName
method. - Select the range of cells, and then enter the name for the selected range in the name box.
The following code example shows the usage of named ranges support.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, DefinedNamesDirective, DefinedNameDirective } from '@syncfusion/ej2-react-spreadsheet';
import { getComponent } from '@syncfusion/ej2-base';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef(null);
const styles = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle = { fontWeight: 'bold', textAlign: 'right' };
const beforeDataBound = () => {
let spreadsheetObj = getComponent(document.getElementById("spreadsheet"), "spreadsheet");
// Adding name dynamically for `last year spending` and `percentage change` ranges.
spreadsheetObj.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
spreadsheetObj.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Removing the unwanted `PercentageChange` named range
spreadsheet.removeDefinedName('PercentageChange', '');
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
}
}, []);
return (
<div>
<SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} beforeDataBound={beforeDataBound} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective name={"Budget Details"}>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(MonthlySpendings)'} ></CellDirective>
<CellDirective formula={'=SUM(AnnualSpendings)'}></CellDirective>
<CellDirective formula={'=SUM(LastYearSpendings)'}></CellDirective>
<CellDirective formula={'=C12/D12'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(Categories)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(MonthlySpendings)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(MonthlySpendings)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(MonthlySpendings)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
<DefinedNamesDirective>
<DefinedNameDirective name={'Categories'} refersTo={"=Budget Details!A3:A11"}> </DefinedNameDirective>
<DefinedNameDirective name={'MonthlySpendings'} refersTo={"=Budget Details!B3:B11"}> </DefinedNameDirective>
<DefinedNameDirective name={'AnnualSpendings'} refersTo={"=Budget Details!C3:C11"}> </DefinedNameDirective>
</DefinedNamesDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root'));
root.render(<App />);
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective, CellStyleModel, DefinedNamesDirective, DefinedNameDirective } from '@syncfusion/ej2-react-spreadsheet';
import { getComponent } from '@syncfusion/ej2-base';
import { data } from './datasource';
function App() {
const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
const styles: CellStyleModel = { textAlign: 'center', fontWeight: 'bold', verticalAlign: 'middle', fontStyle: 'italic', fontSize: '15pt' };
const cellStyle: CellStyleModel = { fontStyle: 'italic', fontWeight: 'bold' };
const fontStyle: CellStyleModel = { fontWeight: 'bold', textAlign: 'right' };
const beforeDataBound = (): void => {
let spreadsheetObj: SpreadsheetComponent = getComponent(document.getElementById("spreadsheet") as HTMLElement, "spreadsheet");
// Adding name dynamically for `last year spending` and `percentage change` ranges.
spreadsheetObj.addDefinedName({ name: 'LastYearSpendings', refersTo: '=D3:D11' });
spreadsheetObj.addDefinedName({ name: 'PercentageChange', refersTo: '=E3:E11' });
};
React.useEffect(() => {
let spreadsheet = spreadsheetRef.current;
if (spreadsheet) {
// Removing the unwanted `PercentageChange` named range
spreadsheet.removeDefinedName('PercentageChange', '');
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:E2');
spreadsheet.numberFormat('$#,##0', 'B3:D12');
spreadsheet.numberFormat('0%', 'E3:E12');
}
}, []);
return (
<div>
<SpreadsheetComponent id='spreadsheet' ref={spreadsheetRef} beforeDataBound={beforeDataBound} showSheetTabs={false} showRibbon={false}>
<SheetsDirective>
<SheetDirective name={"Budget Details"}>
<RowsDirective>
<RowDirective height={40} customHeight={true}>
<CellsDirective>
<CellDirective value={'Monthly Expense'} style={styles} colSpan={5}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective height={30}></RowDirective>
<RowDirective index={11}>
<CellsDirective>
<CellDirective value={'Totals'} style={cellStyle}></CellDirective>
<CellDirective formula={'=SUM(MonthlySpendings)'} ></CellDirective>
<CellDirective formula={'=SUM(AnnualSpendings)'}></CellDirective>
<CellDirective formula={'=SUM(LastYearSpendings)'}></CellDirective>
<CellDirective formula={'=C12/D12'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Number of Categories'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=COUNTA(Categories)'} index={3}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Average Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={'=AVERAGE(MonthlySpendings)'} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Min Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MIN(MonthlySpendings)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
<RowDirective>
<CellsDirective>
<CellDirective index={1} value={'Max Spend'} style={fontStyle} colSpan={2}></CellDirective>
<CellDirective formula={"=MAX(MonthlySpendings)"} index={3} format={'$#,##0'}></CellDirective>
</CellsDirective>
</RowDirective>
</RowsDirective>
<RangesDirective>
<RangeDirective dataSource={data} startCell={"A2"}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={150}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
<ColumnDirective width={120}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
<DefinedNamesDirective>
<DefinedNameDirective name={'Categories'} refersTo={"=Budget Details!A3:A11"}> </DefinedNameDirective>
<DefinedNameDirective name={'MonthlySpendings'} refersTo={"=Budget Details!B3:B11"}> </DefinedNameDirective>
<DefinedNameDirective name={'AnnualSpendings'} refersTo={"=Budget Details!C3:C11"}> </DefinedNameDirective>
</DefinedNamesDirective>
</SpreadsheetComponent>
</div>
);
};
export default App;
const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
* Formula data source
*/
export let data = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12',
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3' // You can set the expression or formula as string
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' } // You can also set as object with formula field
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' }
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6'
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7'
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8'
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9'
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10'
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11'
}
];
/**
* Formula data source
*/
export let data: Object[] = [
{
'Category': 'Household Utilities',
'Monthly Spend': '=C3/12', // Setting formula through data binding
'Annual Spend': 3000,
'Last Year Spend': 3000,
'Percentage Change': '=C3/D3' // You can set the expression or formula as string
},
{
'Category': 'Food',
'Monthly Spend': '=C4/12',
'Annual Spend': 2500,
'Last Year Spend': 2250,
'Percentage Change': { formula: '=C4/D4' } // You can also set as object with formula field
},
{
'Category': 'Gasoline',
'Monthly Spend': '=C5/12',
'Annual Spend': 1500,
'Last Year Spend': 1200,
'Percentage Change': { formula: '=C5/D5' }
},
{
'Category': 'Clothes',
'Monthly Spend': '=C6/12',
'Annual Spend': 1200,
'Last Year Spend': 1000,
'Percentage Change': '=C6/D6'
},
{
'Category': 'Insurance',
'Monthly Spend': '=C7/12',
'Annual Spend': 1500,
'Last Year Spend': 1500,
'Percentage Change': '=C7/D7'
},
{
'Category': 'Taxes',
'Monthly Spend': '=C8/12',
'Annual Spend': 3500,
'Last Year Spend': 3500,
'Percentage Change': '=C8/D8'
},
{
'Category': 'Entertainment',
'Monthly Spend': '=C9/12',
'Annual Spend': 2000,
'Last Year Spend': 2250,
'Percentage Change': '=C9/D9'
},
{
'Category': 'Vacation',
'Monthly Spend': '=C10/12',
'Annual Spend': 1500,
'Last Year Spend': 2000,
'Percentage Change': '=C10/D10'
},
{
'Category': 'Miscellaneous',
'Monthly Spend': '=C11/12',
'Annual Spend': 1250,
'Last Year Spend': 1558,
'Percentage Change': '=C11/D11'
}
];
Supported Formulas
The following are the list of formulas supported in spreadsheet,
Formula | Description |
---|---|
ABS | Returns the value of a number without its sign. |
ADDRESS | Returns a cell reference as text, given specified row and column numbers. |
AND | Returns TRUE if all the arguments are TRUE, otherwise returns FALSE. |
AVERAGE | Calculates average for the series of numbers and/or cells excluding text. |
AVERAGEA | Calculates the average for the cells evaluating TRUE as 1, text and FALSE as 0. |
AVERAGEIF | Clears content of the active cell and enables edit mode. |
AVERAGEIFS | Calculates average for the cells based on specified conditions. |
CEILING | Rounds a number up to the nearest multiple of a given factor. |
CHOOSE | Returns a value from list of values, based on index number. |
CHAR | Returns the character from the specified number. |
CODE | Returns the numeric code for the first character in a given string. |
CONCAT | Concatenates a list or a range of text strings. |
CONCATENATE | Combines two or more strings together. |
COUNT | Counts the cells that contain numeric values in a range. |
COUNTA | Counts the cells that contains values in a range. |
COUNTBLANK | Returns the number of empty cells in a specified range of cells. |
COUNTIF | Counts the cells based on specified condition. |
COUNTIFS | Counts the cells based on specified conditions. |
DATE | Returns the date based on given year, month, and day. |
DATEVALUE | Converts a date string into date value. |
DAY | Returns the day from the given date. |
DAYS | Returns the number of days between two dates. |
DECIMAL | Converts a text representation of a number in a given base into a decimal number. |
DEGREES | Converts radians to degrees. |
DOLLAR | Converts the number to currency formatted text. |
EDATE | Returns a date with given number of months before or after the specified date. |
EOMONTH | Returns the last day of the month that is a specified number of months before or after an initially supplied start date. |
EVEN | Rounds a positive number up and negative number down to the nearest even integer. |
EXACT | Checks whether a two text strings are exactly same and returns TRUE or FALSE. |
EXP | Returns e raised to the power of the given number. |
FACT | Returns the factorial of a number. |
FIND | Returns the position of a string within another string, which is case sensitive. |
FLOOR | Rounds a number down to the nearest multiple of a given factor. |
HLOOKUP | Looks for a value in the top row of the array of values and then returns a value in the same column from a row in the array that you specify. |
HOUR | Returns the number of hours in a specified time string. |
IF | Returns value based on the given expression. |
IFERROR | Returns value if no error found else it will return specified value. |
IFS | Returns value based on the given multiple expressions. |
INDEX | Returns a value of the cell in a given range based on row and column number. |
INT | Rounds a number down to the nearest integer. |
INTERCEPT | Calculates the point of the Y-intercept line via linear regression. |
ISNUMBER | Returns true when the value parses as a numeric value. |
LARGE | Returns the k-th largest value in a given array. |
LEN | Returns a number of characters in a given string. |
LN | Returns the natural logarithm of a number. |
LOG | Returns the logarithm of a number to the base that you specify. |
LOOKUP | Looks for a value in a one-row or one-column range, then returns a value from the same position in a second one-row or one-column range. |
MATCH | Returns the relative position of a specified value in given range. |
MAX | Returns the largest number of the given arguments. |
MEDIAN | Returns the median of the given set of numbers. |
MINUTE | Returns the number of minutes in a specified time string. |
MIN | Returns the smallest number of the given arguments. |
MOD | Returns a remainder after a number is divided by divisor. |
MONTH | Returns the number of months in a specified date string. |
NOT | Returns the inverse of a given logical expression. |
NOW | Returns the current date and time. |
ODD | Rounds a positive number up and negative number down to the nearest odd integer. |
OR | Returns TRUE if any of the arguments are TRUE, otherwise returns FALSE. |
PI | Returns the value of pi. |
POWER | Returns the result of a number raised to power. |
PRODUCT | Multiplies a series of numbers and/or cells. |
RADIANS | Converts degrees into radians. |
RAND | Returns a random number between 0 and 1. |
RANDBETWEEN | Returns a random integer based on specified values. |
ROUND | Rounds a number to the specified number of digits. |
ROUNDDOWN | Rounds a number down, toward zero. |
ROUNDUP | Rounds a number up, away from zero. |
RSQ | Returns the square of the Pearson product moment correlation coefficient based on data points in known_y’s and known_x’s. |
SECOND | Returns the number of seconds in a specified time string. |
SMALL | Returns the k-th smallest value in a given array. |
SLOPE | Returns the slope of the line from linear regression of the data points. |
SORT | Sorts the contents of a column, range, or array in ascending or descending order. |
SQRT | Returns the square root of a positive number. |
SUBTOTAL | Returns subtotal for a range using the given function number. |
SUM | Adds a series of numbers and/or cells. |
SUMIF | Adds the cells based on specified condition. |
SUMIFS | Adds the cells based on specified conditions. |
SUMPRODUCT | Returns the sum of the products of the corresponding array in given arrays. |
T | Checks whether a value is text or not and returns the text. |
TEXT | Converts the supplied value into text by using the user-specified format. |
TIME | Converts hours, minutes, seconds to the time formatted text. |
TODAY | Returns the current date. |
TRUNC | Truncates a supplied number to a specified number of decimal places. |
UNIQUE | Returns a unique values from a range or array. |
VLOOKUP | Looks for a specific value in the first column of a lookup range and returns a corresponding value from a different column within the same row. |
Formula Error Dialog
If you enter an invalid formula in a cell, an error dialog with an error message will appear. For instance, a formula with the incorrect number of arguments, a formula without parenthesis, etc.
Error Message | Reason |
---|---|
We found that you typed a formula with an invalid arguments | Occurs when passing an argument even though it wasn’t needed. |
We found that you typed a formula with an empty expression | Occurs when passing an empty expression in the argument. |
We found that you typed a formula with one or more missing opening or closing parenthesis | Occurs when an open parenthesis or a close parenthesis is missing. |
We found that you typed a formula which is improper | Occurs when passing a single reference but a range was needed. |
We found that you typed a formula with a wrong number of arguments | Occurs when the required arguments were not passed. |
We found that you typed a formula which requires 3 arguments | Occurs when the required 3 arguments were not passed. |
We found that you typed a formula with a mismatched quotes | Occurs when passing an argument with mismatched quotes. |
We found that you typed a formula with a circular reference | Occurs when passing a formula with circular cell reference. |
We found that you typed a formula which is invalid | Except in the cases mentioned above, all other errors will fall into this broad category. |
Note
You can refer to our React Spreadsheet feature tour page for its groundbreaking feature representations. You can also explore our React Spreadsheet example to knows how to present and manipulate data.