Delete Named Ranges with JavaScript via C++
Contents
[
Hide
]
Introduction
If there are too many defined names or named ranges in the Excel files, we have to clear some for they are not referred again.
Remove Named Range in MS Excel
To remove a named range from Excel, you can follow these steps:
- Open Microsoft Excel and open the workbook that contains the named range.
- Go to the “Formulas” tab in the Excel ribbon.
- Click on the “Name Manager” button in the “Defined Names” group. This will open the Name Manager dialog box.
- In the Name Manager dialog box, select the named range you want to remove.
- Click on the “Delete” button. Confirm the deletion when prompted.
- Click on the “Close” button to close the Name Manager dialog box.
- Save the workbook to retain the changes.
Delete Named Range using Aspose.Cells for JavaScript via C++
With Aspose.Cells for JavaScript via C++, you can remove named ranges or defined names by text or index in the list.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Remove Named Ranges Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate a Workbook from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get all the worksheets in the book.
const worksheets = workbook.worksheets;
// Deleted a named range by text.
worksheets.names.remove("NamedRange");
// Deleted a defined name by index. Ensure to check the count before removal.
if (worksheets.names.count > 0) {
worksheets.names.removeAt(0);
}
// Save the workbook to retain the changes.
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'Book2.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Named ranges removed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Note: if the defined name is referred by formulas, it could not be removed. We only can remove the formula of the defined name.
Removes Some Named Ranges
When we remove a defined name, we have to check whether it is referred by all formulas in the file. In order to improve performance of removing named ranges, we can remove some together.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Remove Named Ranges Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate a new Workbook from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get all the worksheets in the book.
const worksheets = workbook.worksheets;
// Delete some defined names.
worksheets.names.remove(["NamedRange1", "NamedRange2"]);
// Save the workbook to retain the changes.
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'Book2.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Named ranges removed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Remove Duplicate Defined Names
Some Excel files corrupt because some defined names are duplicate. So we can remove these duplicate names to repair the file.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Remove Duplicate Defined Names</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate a new Workbook from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get all the worksheets in the book.
const worksheets = workbook.worksheets;
// Deleted some defined names.
worksheets.names.removeDuplicateNames();
// Save the workbook to retain the changes.
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'Book2.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Duplicate defined names removed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>