A named range is not something you set up once and forget. As the data table is restructured, the original name may no longer fit, and the referred range can go stale when rows are added or removed. Some named ranges exist only as an intermediate helper for a formula and have no business showing up in the Name Manager. And named ranges that are no longer used, if kept forever, turn the name list into something long and hard to search. Modifying, hiding and deleting are therefore just as much a part of working with named ranges as creating them. Spire.XLS for JavaScript provides a complete named range management API and can perform all of the above in the browser through WebAssembly, with no backend service required.
This article covers three key features:
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.
Modify a Named Range
Modifying covers two independent aspects: the name itself and the referred range. The name is reassigned through the Name property and the referred range through the RefersToRange property. The two can be changed separately, or together as in the example below. The steps are:
- Load the workbook and get the first worksheet.
- Take the named range to modify with
workbook.NameRanges.get(0). - Set
Nameto the new name. - Point
RefersToRangeat the new cell range. - Save the workbook.
The following is a complete code example that shows how to modify a named range in React:
function App() {
const modifyNamedRange = 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 = 'AllNamedRanges.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);
// Change the name of the named range
workbook.NameRanges.get(0).Name = "RegionData";
// Change the cell range the named range refers to
workbook.NameRanges.get(0).RefersToRange = sheet.Range.get("B2:C4");
// Save the workbook
const outputFileName = 'ModifyNamedRange.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>Modify Named Range</h1>
<button onClick={modifyNamedRange}>Start</button>
</div>
);
}
export default App;
After running, the effect of modifying a named range:
Hide a Named Range
Set the Visible property to false and the named range is hidden. A hidden named range is still stored in the workbook and formulas that refer to it are unaffected — it simply no longer appears in Excel's Name Manager and name box, which keeps the name list tidy. After hiding it, the example below also writes the formula =SUM(NameRange1) into cell F2: the formula still calculates normally, which is exactly what shows that the named range is only hidden, not deleted. The steps are:
- Load the workbook and get the first worksheet.
- Take the named range to hide.
- Set
Visibletofalse. - Write a formula that refers to the named range into a cell, to confirm it still works.
- Save the workbook.
The following is a complete code example that shows how to hide a named range in React:
function App() {
const hideNamedRange = 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 = 'AllNamedRanges.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);
// Hide the first named range
workbook.NameRanges.get(0).Visible = false;
// Write a formula that refers to the hidden named range, proving it still exists and works
sheet.Range.get("F1").Text = "Sum After Hiding";
sheet.Range.get("F2").Formula = "=SUM(NameRange1)";
// Calculate the formulas so the saved file shows the result as soon as it is opened
workbook.CalculateAllValue();
// Save the workbook
const outputFileName = 'HideNamedRange.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>Hide Named Range</h1>
<button onClick={hideNamedRange}>Start</button>
</div>
);
}
export default App;
After running, the effect of hiding a named range:
Note: The formula in cell F2 refers to the hidden NameRange1, and it still calculates 120. That shows the named range has only been hidden from view, not removed from the workbook.
Delete a Named Range
There are two ways to delete a named range: call Remove() when the name is known, or RemoveAt() when the position is known. Both remove the named range from the workbook entirely. The steps are:
- Load the workbook.
- Call
Remove()to delete a named range by name. - Call
RemoveAt()to delete a named range by index. - Save the workbook.
The following is a complete code example that shows how to delete a named range in React:
function App() {
const deleteNamedRange = 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 = 'AllNamedRanges.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);
// Load the workbook
const workbook = new xlsModule.Workbook();
workbook.LoadFromFile(inputFileName);
// Delete a named range by name
workbook.NameRanges.Remove("NameRange2");
// Delete a named range by index
workbook.NameRanges.RemoveAt(0);
// Save the workbook
const outputFileName = 'DeleteNamedRange.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>Delete Named Range</h1>
<button onClick={deleteNamedRange}>Start</button>
</div>
);
}
export default App;
After running, the effect of deleting a named range:
FAQ
Why is the result of a formula missing when the saved file is opened?
Cause: Setting only the Formula property of a cell does not make Spire calculate it. The saved file then contains the formula itself but no calculated result value, so the cell comes up blank when the file is opened.
Solution: Call workbook.CalculateAllValue() before saving, to evaluate the formulas first:
// Calculate all formulas so the result value is written into the saved file
workbook.CalculateAllValue();
Can I pass a named range object to the delete API?
Cause: Remove() takes a name string. Passing a NameRange object does not match the expected type and throws Assert failed: Value is not a String, and nothing is deleted.
Solution: Pass the name when it is known, or the index when the position is known:
// Delete by name
workbook.NameRanges.Remove("NameRange2");
// Delete by index
workbook.NameRanges.RemoveAt(0);
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.
