Create Access and Copy Named Ranges with JavaScript via C++
Introduction
Normally, column and row labels are used to refer to individual cells. It is possible to create descriptive names to represent cells, ranges of cells, formulas, or constant values. The word name may refer to a string of characters that represents a cell, range of cells, formula, or constant value. Assigning a name to a range means that range of cells can be referred to by its name. Use easy-to-understand names, such as Products, to refer to hard-to-understand ranges, such as Sales!C20:C30. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, you may use a name. Named ranges are among the most powerful features of Microsoft Excel, especially when used as the source range for list controls, pivot tables, charts, and so on.
Working with Named Range Using Microsoft Excel
Create Named Ranges
The following steps describe how to name a cell or range of cells using MS Excel. This method applies to Microsoft Office Excel 2003, Microsoft Excel 97, 2000, and 2002.
- Select the cell or range of cells that you want to name.
- Click the Name Box at the left end of the formula bar.
- Type the name for the cells.
- Press ENTER.
Working with Named Range Using Aspose.Cells
Here, we use the Aspose.Cells API to do the task.
Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection.
Create Named Range
It is possible to create a named range by calling the overloaded createRange(string, string) method of the cells collection. A typical version of createRange(string) method takes the following parameters:
- Name of the upper left cell, the name of the top left cell in the range.
- Name of the lower right cell, the name of the bottom right cell in the range.
When the createRange(string) method is called, it returns the newly created range as an instance of the Range class. Use this Range object to configure the named range. For example, set the name of the range using the name property. The following example shows how to create a named range of cells that extends over B4:G14.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Create Named Range 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();
// Instantiating a Workbook object by opening the Excel file from the file input
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Creating a named range
const range = worksheet.cells.createRange("B4", "G14");
// Setting the name of the named range
range.name = "TestRange";
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Named range created successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Input Data into the Cells in the Named Range
You can insert data into the individual cells of a range following the pattern
- JavaScript: Range[row,column]
Say you have a named range of cells that spans A1:C4. The matrix makes 4 * 3 = 12 cells. The individual range cells are arranged sequentially: Range[0,0], Range[0,1], Range[0,2], Range[1,0], Range[1,1], Range[1,2], Range[2,0], Range[2,1], Range[2,2], Range[3,0], Range[3,1], Range[3,2].
Use the following properties to identify the cells in the range:
- firstRow returns the index of the first row in the named range.
- firstColumn returns the index of the first column in the named range.
- rowCount returns the total number of rows in the named range.
- columnCount returns the total number of columns in the named range.
The following example shows how to input some values into the cells of a specified range.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Create Named Range 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');
// Instantiate or load workbook
let workbook;
if (fileInput.files && fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Get the first worksheet in the workbook.
const worksheet1 = workbook.worksheets.get(0);
// Create a range of cells based on H1:J4.
const range = worksheet1.cells.createRange("H1", "J4");
// Name the range.
range.name = "MyRange";
// Input some data into cells in the range.
range.get(0, 0).value = "USA";
range.get(0, 1).value = "SA";
range.get(0, 2).value = "Israel";
range.get(1, 0).value = "UK";
range.get(1, 1).value = "AUS";
range.get(1, 2).value = "Canada";
range.get(2, 0).value = "France";
range.get(2, 1).value = "India";
range.get(2, 2).value = "Egypt";
range.get(3, 0).value = "China";
range.get(3, 1).value = "Philipine";
range.get(3, 2).value = "Brazil";
// Save the excel file.
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'rangecells.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created and range populated successfully! Click the download link to get the file.</p>';
});
</script>
</html>
Identify Cells in the Named Range
You can insert data into the individual cells of a range following the pattern:
- JavaScript: Range[row,column]
If you have a named range that spans A1:C4. The matrix makes 4 * 3 = 12 cells. The individual range cells are arranged sequentially: Range[0,0], Range[0,1], Range[0,2], Range[1,0] ,Range[1,1], Range[1,2], Range[2,0], Range[2,1], Range[2,2], Range[3,0], Range[3,1], Range[3,2].
Use the following properties to identify the cells in the range:
- firstRow returns the index of the first row in the named range.
- firstColumn returns the index of the first column in the named range.
- rowCount returns the total number of rows in the named range.
- columnCount returns the total number of columns in the named range.
The following example shows how to input some values into the cells of a specified range.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Get Named Range</title>
</head>
<body>
<h1>Get Named Range 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, Worksheet, Cell, 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();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Getting the specified named range
const range = workbook.worksheets.rangeByName("TestRange");
if (!range) {
document.getElementById('result').innerHTML = '<p style="color: red;">Named range "TestRange" not found.</p>';
return;
}
// Identify range cells and display properties
const firstRow = range.firstRow;
const firstColumn = range.firstColumn;
const rowCount = range.rowCount;
const columnCount = range.columnCount;
const html = [
`<p>First Row : ${firstRow}</p>`,
`<p>First Column : ${firstColumn}</p>`,
`<p>Row Count : ${rowCount}</p>`,
`<p>Column Count : ${columnCount}</p>`
].join('');
document.getElementById('result').innerHTML = html;
});
</script>
</html>
Access Named Ranges
Access a Specific Named Range
Call the worksheets collection’s rangeByName(string) method to get a range by the specified name. A typical rangeByName(string) method takes the name of the named range and returns the specified named range as an instance of the Range class. The following example shows how to access a specified range by its name.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Named Range Example</title>
</head>
<body>
<h1>Get Named Range 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();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Getting the specified named range
const worksheets = workbook.worksheets;
const range = worksheets.rangeByName("TestRange");
if (range !== null) {
document.getElementById('result').innerHTML = `<p>Named Range : ${range.refersTo}</p>`;
} else {
document.getElementById('result').innerHTML = '<p style="color: red;">Named range "TestRange" not found.</p>';
}
});
</script>
</html>
Access All the Named Ranges in a Spreadsheet
Call the worksheet collection’s namedRanges() method to get all named ranges in a spreadsheet. The namedRanges() method returns an array of all named ranges in the worksheets collection.
The following example shows how to access all the named ranges in a workbook.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Named Ranges</title>
</head>
<body>
<h1>Get 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();
// Instantiating a Workbook object by opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Getting all named ranges
const ranges = workbook.worksheets.namedRanges;
if (ranges) {
// Some collections expose 'count', others may expose 'length'
const total = (typeof ranges.count !== 'undefined') ? ranges.count : ranges.length;
document.getElementById('result').innerHTML = `<p style="color: green;">Total Number of Named Ranges: ${total}</p>`;
} else {
document.getElementById('result').innerHTML = '<p style="color: orange;">No named ranges found.</p>';
}
});
</script>
</html>
Copy Named Ranges
Aspose.Cells provides range.copy(Range, PasteOptions) method to copy a range of cells with formatting into another range.
The following example shows how to copy a source range of cells to another named range.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Copy Ranges</title>
</head>
<body>
<h1>Copy Ranges Example</h1>
<p>Select an Excel file to modify, or leave empty to create a new workbook.</p>
<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, BorderType, CellBorderType, Color } = 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');
// Instantiate a new Workbook or load from selected file
let workbook;
if (fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
workbook = new Workbook(new Uint8Array(arrayBuffer));
} else {
workbook = new Workbook();
}
// Get all the worksheets in the book.
const worksheets = workbook.worksheets;
// Get the first worksheet in the worksheets collection.
const worksheet = workbook.worksheets.get(0);
// Create a range of cells.
const range1 = worksheet.cells.createRange("E12", "I12");
// Name the range.
range1.name = "MyRange";
// Set the outline border to the range.
range1.outlineBorder = { borderType: BorderType.TopBorder, style: CellBorderType.Medium, color: new Color(0, 0, 128) };
range1.outlineBorder = { borderType: BorderType.BottomBorder, style: CellBorderType.Medium, color: new Color(0, 0, 128) };
range1.outlineBorder = { borderType: BorderType.LeftBorder, style: CellBorderType.Medium, color: new Color(0, 0, 128) };
range1.outlineBorder = { borderType: BorderType.RightBorder, style: CellBorderType.Medium, color: new Color(0, 0, 128) };
// Input some data with some formattings into a few cells in the range.
range1.get(0, 0).putValue("Test");
range1.get(0, 4).putValue("123");
// Create another range of cells.
const range2 = worksheet.cells.createRange("B3", "F3");
// Name the range.
range2.name = "testrange";
// Copy the first range into second range.
range2.copy(range1);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'copyranges.out.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>