Create Named Ranges in React with JavaScript

In Excel, formulas usually have to hard-code a specific cell range, such as =SUM(D2:D10). As such formulas multiply, maintenance costs rise: when the data range changes, every related formula must be updated one by one, and a single missed edit produces a wrong result. A named range is designed to solve exactly this problem — give a cell range a meaningful name and refer to that name in the formula. The range and the formula are thereby separated: changing the range takes a single edit, every formula that refers to it updates automatically, and the result is both less error-prone and easier to read. Spire.XLS for JavaScript ships a complete named range API and can create both global (workbook-level) and local (worksheet-level) named ranges in the browser through WebAssembly, with no backend service required.

This article covers two key features:

Global Named Range
Local Named Range

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is already installed and the WebAssembly module has been initialized.

Global Named Range

A global named range is stored in the workbook's name collection Workbook.NameRanges, its name is unique across the whole workbook, and any worksheet can refer to it directly. Create it with Workbook.NameRanges.Add() and point it at a cell range through the RefersToRange property. The steps are:

Load the Excel file that contains the data and get the first worksheet.
Create a global named range with workbook.NameRanges.Add("SalesData").
Set namedRange.RefersToRange to sheet.Range.get("A1:D10"), that is, the range A1:D10.
Read namedRange.Name and namedRange.RefersToRange.RangeAddress and write the name and the referred address back into cells.
Save the workbook with the Workbook.SaveToFile() method.

The following is a complete code example that shows how to create a global named range in React:

function App() {
  const createGlobalNamedRange = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check if the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into VFS
    const inputFileName = 'NamedRanges.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Get the first worksheet
    let sheet = workbook.Worksheets.get(0);

    // Create a workbook-level (global) named range
    let namedRange = workbook.NameRanges.Add("SalesData");

    // Set the cell range the named range refers to
    namedRange.RefersToRange = sheet.Range.get("A1:D10");

    // Read the name and the referred address
    sheet.Range.get("F1").Text = "Named Range Name";
    sheet.Range.get("F2").Text = namedRange.Name;
    sheet.Range.get("G1").Text = "Refers To Address";
    sheet.Range.get("G2").Text = namedRange.RefersToRange.RangeAddress;

    // Auto-fit the columns
    sheet.AllocatedRange.AutoFitColumns();

    // Save the workbook
    const outputFileName = 'GlobalNamedRange.xlsx';
    workbook.SaveToFile(outputFileName);

    // Release resources
    workbook.Dispose();

    // Read the result file from VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Create Global Named Range</h1>
      <button onClick={createGlobalNamedRange}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of creating a global named range:

Create Global Named Range

Local Named Range

A global named range requires its name to be unique across the entire workbook. When different worksheets all want to use the same name while pointing at different data areas, switch to a local named range instead — added through Worksheet.Names.Add(), its name only takes effect inside the owning worksheet, so same-named ranges can live on several worksheets at once without interfering with each other. The steps are:

Load the workbook and get the first worksheet.
Create a local named range on the first worksheet with sheet.Names.Add("SalesData"), pointing at A2:D10.
Add another worksheet with workbook.Worksheets.Add() and create a same-named local named range on it, pointing at a different area.
Read the referred address of both ranges back into cells.
Save the workbook.

The following is a complete code example that shows how to create a local named range in React:

function App() {
  const createLocalNamedRange = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check if the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into VFS
    const inputFileName = 'NamedRanges.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Get the first worksheet
    let sheet = workbook.Worksheets.get(0);

    // Create a local named range on the first worksheet
    let localRange = sheet.Names.Add("SalesData");
    localRange.RefersToRange = sheet.Range.get("A2:D10");

    // Add another worksheet and create a same-named local range on it
    let sheet2 = workbook.Worksheets.Add("Summary");
    let localRange2 = sheet2.Names.Add("SalesData");
    localRange2.RefersToRange = sheet2.Range.get("A1:B5");

    // Read the addresses of the same-named ranges in both worksheets
    sheet.Range.get("F1").Text = "SalesData on Sheet1";
    sheet.Range.get("F2").Text = localRange.RefersToRange.RangeAddress;
    sheet.Range.get("G1").Text = "SalesData on Sheet2";
    sheet.Range.get("G2").Text = localRange2.RefersToRange.RangeAddress;

    // Auto-fit the columns
    sheet.AllocatedRange.AutoFitColumns();

    // Save the workbook
    const outputFileName = 'LocalNamedRange.xlsx';
    workbook.SaveToFile(outputFileName);

    // Release resources
    workbook.Dispose();

    // Read the result file from VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Create Local Named Range</h1>
      <button onClick={createLocalNamedRange}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of creating a local named range:

Create Local Named Range

FAQ

How do I use a named range in a formula?

Cause: The real value of a named range is being referenced from formulas. Once the amount column is defined as a named range, the formula no longer needs a literal address, and the summed range expands automatically when rows are inserted later.

Solution: Simply write the name of the named range into the formula:

let namedRange = workbook.NameRanges.Add("SalesAmount");
namedRange.RefersToRange = sheet.Range.get("D2:D10");

// Refer to the named range in a formula
sheet.Range.get("F2").Formula = "=SUM(SalesAmount)";

How do I read the named ranges that already exist in a workbook?

Cause: A named range is saved together with the workbook, so it has to be read back before you can tell which names currently exist and which area each one points to.

Solution: Walk the NameRanges collection: take the count first, then read the name and the refers-to address of each entry by index:

// Total number of named ranges
let count = workbook.NameRanges.Count;

// Read the name and the refers-to address of each one
for (let i = 0; i < count; i++) {
  let namedRange = workbook.NameRanges.get(i);
  sheet.Range.get(`F${i + 2}`).Text = namedRange.Name;
  sheet.Range.get(`G${i + 2}`).Text = namedRange.RefersToRange.RangeAddress;
}

This walks workbook-level named ranges; worksheet-level ones are read through sheet.Names, in exactly the same way.

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.