Named Ranges in React Spreadsheet

26 May 202624 minutes to read

You can assign a meaningful name to a cell range and then use that name in formulas for calculation. This makes formulas easier to read, understand, and maintain. Named ranges can be added to the Spreadsheet in several ways:

  • Use the definedNames collection to add multiple named ranges during the initial load.
  • Use the addDefinedName method to add a named range dynamically at runtime.
  • Remove a named range dynamically using the removeDefinedName method.
  • Select a range of cells and enter a name for the selected range directly in the Name Box.

The following code example demonstrates how named ranges can be defined and used in formulas within 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, 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'
    }
];