Clear Cell Contents or Formats in React Spreadsheet

29 May 202617 minutes to read

The Clear feature enables you to remove cell contents (formulas and data) and formats (including number formats, conditional formats, and borders) from a spreadsheet. When you use Clear All, both the contents and formats are removed simultaneously.

Applying the Clear Feature

You can clear cell contents or formats using the following approaches:

  • Ribbon Toolbar: Select the clear icon under the Home tab to access clear options.
  • Programmatically: Use the clear method.

The available clear options are:

Option Description
Clear All Clears all contents, formats, and hyperlinks.
Clear Formats Clears only the formats (including number formats, conditional formats, and borders) in a cell.
Clear Contents Clears only the contents (formulas and data) in a cell.
Clear Hyperlinks Clears only the hyperlinks in a cell.

The following code example demonstrates how to clear cell contents and formats in a button click event.

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import { DropDownButtonComponent } from '@syncfusion/ej2-react-splitbuttons';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } 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 items = [
        { text: "Clear All" },
        { text: "Clear Formats" },
        { text: "Clear Contents" },
        { text: "Clear Hyperlinks" }
    ];
    const handleItemSelect = (args) => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            if (args.item.text === 'Clear All')
                spreadsheet.clear({ type: 'Clear All', range: 'D1:D10' }); // Clear the content, formats and hyperlinks applied in the provided range.
            if (args.item.text === 'Clear Formats')
                spreadsheet.clear({ type: 'Clear Formats', range: 'B1:B10' }); // Clear the formats applied in the provided range
            if (args.item.text === 'Clear Contents')
                spreadsheet.clear({ type: 'Clear Contents', range: 'A1:A10' }); // Clear the content in the provided range
            if (args.item.text === 'Clear Hyperlinks')
                spreadsheet.clear({ type: 'Clear Hyperlinks', range: 'F2:F6' }); // Clear the hyperlinks applied in the provided range
        }
    };
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt' }, 'A1:F1');
            spreadsheet.cellFormat({ color: '#10c469' }, 'B1:B10');
        }
    }, []);

    return (
        <div>
            <DropDownButtonComponent items={items} select={handleItemSelect}> Clear </DropDownButtonComponent>
            <SpreadsheetComponent ref={spreadsheetRef}>
                <SheetsDirective>
                    <SheetDirective>
                        <RangesDirective>
                            <RangeDirective dataSource={data} />
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={100} />
                            <ColumnDirective width={110} />
                            <ColumnDirective width={100} />
                            <ColumnDirective width={180} />
                            <ColumnDirective width={130} />
                            <ColumnDirective width={130} />
                        </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 { DropDownButtonComponent, ItemModel } from '@syncfusion/ej2-react-splitbuttons';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, MenuSelectEventArgs } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { data } from './datasource';

function App() {
    const spreadsheetRef = React.useRef<SpreadsheetComponent>(null);
    const items: ItemModel[] = [
        { text: "Clear All" },
        { text: "Clear Formats" },
        { text: "Clear Contents" },
        { text: "Clear Hyperlinks" }
    ];
    const handleItemSelect = (args: MenuSelectEventArgs): void => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            if (args.item.text === 'Clear All')
                spreadsheet.clear({ type: 'Clear All', range: 'D1:D10' }); // Clear the content, formats and hyperlinks applied in the provided range.
            if (args.item.text === 'Clear Formats')
                spreadsheet.clear({ type: 'Clear Formats', range: 'B1:B10' }); // Clear the formats applied in the provided range
            if (args.item.text === 'Clear Contents')
                spreadsheet.clear({ type: 'Clear Contents', range: 'A1:A10' }); // Clear the content in the provided range
            if (args.item.text === 'Clear Hyperlinks')
                spreadsheet.clear({ type: 'Clear Hyperlinks', range: 'F2:F6' }); // Clear the hyperlinks applied in the provided range
        }
    };
    React.useEffect(() => {
        let spreadsheet = spreadsheetRef.current;
        if (spreadsheet) {
            spreadsheet.cellFormat({ fontWeight: 'bold', fontSize: '12pt' }, 'A1:F1');
            spreadsheet.cellFormat({ color: '#10c469' }, 'B1:B10');
        }
    }, []);

    return (
        <div>
            <DropDownButtonComponent items={items} select={handleItemSelect}> Clear </DropDownButtonComponent>
            <SpreadsheetComponent ref={spreadsheetRef}>
                <SheetsDirective>
                    <SheetDirective>
                        <RangesDirective>
                            <RangeDirective dataSource={data} />
                        </RangesDirective>
                        <ColumnsDirective>
                            <ColumnDirective width={100} />
                            <ColumnDirective width={110} />
                            <ColumnDirective width={100} />
                            <ColumnDirective width={180} />
                            <ColumnDirective width={130} />
                            <ColumnDirective width={130} />
                        </ColumnsDirective>
                    </SheetDirective>
                </SheetsDirective>
            </SpreadsheetComponent>
        </div>
    );
};
export default App;

const root = createRoot(document.getElementById('root')!);
root.render(<App />);
/**
 * Default data source
 */
export let data = [{
        OrderID: 10248,
        CustomerID: 'VINET',
        EmployeeID: 5,
        ShipName: 'Vins et alcools Chevalier',
        ShipCity: 'Reims',
        Website: 'https://www.amazon.com/'
    },
    {
        OrderID: 10249,
        CustomerID: 'TOMSP',
        EmployeeID: 6,
        ShipName: 'Toms Spezialitäten',
        ShipCity: 'Münster',
        Website: 'https://www.overstock.com/'
    },
    {
        OrderID: 10250,
        CustomerID: 'HANAR',
        EmployeeID: 4,
        ShipName: 'Hanari Carnes',
        ShipCity: 'Rio de Janeiro',
        Website: 'https://www.aliexpress.com/'
    },
    {
        OrderID: 10251,
        CustomerID: 'VICTE',
        EmployeeID: 3,
        ShipName: 'Victuailles en stock',
        ShipCity: 'Lyon',
        Website: 'http://www.alibaba.com/'
    },
    {
        OrderID: 10252,
        CustomerID: 'SUPRD',
        EmployeeID: 4,
        ShipName: 'Suprêmes délices',
        ShipCity: 'Charleroi',
        Website: 'https://taobao.com/'
    }];
/**
 * Default data source
 */
export let data: Object[]  = [{
  OrderID: 10248,
  CustomerID: 'VINET',
  EmployeeID: 5,
  ShipName: 'Vins et alcools Chevalier',
  ShipCity: 'Reims',
  Website: 'https://www.amazon.com/'
},
{
  OrderID: 10249,
  CustomerID: 'TOMSP',
  EmployeeID: 6,
  ShipName: 'Toms Spezialitäten',
  ShipCity: 'Münster',
  Website: 'https://www.overstock.com/'
},
{
  OrderID: 10250,
  CustomerID: 'HANAR',
  EmployeeID: 4,
  ShipName: 'Hanari Carnes',
  ShipCity: 'Rio de Janeiro',
  Website: 'https://www.aliexpress.com/'
},
{
  OrderID: 10251,
  CustomerID: 'VICTE',
  EmployeeID: 3,
  ShipName: 'Victuailles en stock',
  ShipCity: 'Lyon',
  Website: 'http://www.alibaba.com/'
},
{
  OrderID: 10252,
  CustomerID: 'SUPRD',
  EmployeeID: 4,
  ShipName: 'Suprêmes délices',
  ShipCity: 'Charleroi',
  Website: 'https://taobao.com/'
}];