Manage Worksheets of Microsoft Excel files with JavaScript via C++
Aspose.Cells provides a class, Workbook that represents an Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in the Excel file.
A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of properties and methods for managing worksheets.
Adding Worksheets to a New Excel File
To create a new Excel file programmatically:
- Create an object of the Workbook class.
- Call the WorksheetCollection.add(SheetType) method of the WorksheetCollection class. An empty worksheet is added to the Excel file automatically. It can be referenced by passing the sheet index of the new worksheet to the worksheets collection.
- Obtain a worksheet reference.
- Perform work on the worksheets.
- Save the new Excel file with new worksheets by calling the Workbook.save(string, SaveFormat) method of the Workbook class.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Add Worksheet 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 () => {
// Instantiate a new Workbook
const workbook = new Workbook();
// Get current worksheet count (converted from getWorksheets().getCount())
const i = workbook.worksheets.count;
// Add a new worksheet (converted from getWorksheets().add())
workbook.worksheets.add();
// Obtain the newly added worksheet by index (converted from getWorksheets().get(i))
const worksheet = workbook.worksheets.get(i);
// Set the name of the newly added worksheet (converted from setName)
worksheet.name = "My Worksheet";
// Save the workbook to XLS format and prepare download
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 Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created and saved. Click the download link to get the file.</p>';
});
</script>
</html>
Adding Worksheets to a Designer Spreadsheet
The process of adding worksheets to a designer spreadsheet is the same as that of adding a new worksheet, except that the Excel file already exists and should be opened before worksheets are added. A designer spreadsheet can be opened by the Workbook class.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Add Worksheet</title>
</head>
<body>
<h1>Add Worksheet 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 } = 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();
// Opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Adding a new worksheet to the Workbook object
const i = workbook.worksheets.add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
const worksheet = workbook.worksheets.get(i);
// Setting the name of the newly added worksheet
worksheet.name = "My Worksheet";
// Saving the Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Worksheet added successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Accessing Worksheets using Sheet Name
Access any worksheet by specifying its name or index.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example: Read Cell Value</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 } = 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));
// Accessing a worksheet using its sheet name
const worksheet = workbook.worksheets.get("Sheet1");
const cell = worksheet.cells.get("A1");
console.log(cell.value);
document.getElementById('result').innerHTML = `<p>Cell A1 value: ${cell.value}</p>`;
});
</script>
</html>
Removing Worksheets using Sheet Name
To remove worksheets from a file, call the WorksheetCollection.removeAt(string) method of the WorksheetCollection class. Pass the sheet name to the WorksheetCollection.removeAt(string) method to remove a specific worksheet.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells 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();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Removing a worksheet using its sheet name
workbook.worksheets.removeAt("Sheet1");
// Save workbook
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;">Worksheet removed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Removing Worksheets using Sheet Index
Removing worksheets by name works well when the name of the worksheet is known. If you don’t know the worksheet’s name, use an overloaded version of the WorksheetCollection.removeAt(string) method that takes the sheet index of the worksheet instead of its sheet name.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example - Remove First Worksheet</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();
// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Removing a worksheet using its sheet index
workbook.worksheets.removeAt(0);
// 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;">Worksheet removed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Activating Sheets and Making an Active Cell in the Worksheet
Sometimes, you need a specific worksheet to be active and displayed when a user opens a Microsoft Excel file in Excel. Similarly, you might want to activate a specific cell and set the scrollbars to show the active cell. Aspose.Cells is capable of doing all these tasks.
An active sheet is a sheet you’re working on: the active sheet’s name on the tab is bold by default.
An active cell is a selected cell, the cell into which data is entered when you begin typing. Only one cell is active at a time. The active cell is highlighted by a heavy border.
Activating Sheets and Making a Cell Active
Aspose.Cells provides specific API calls for activating a sheet and a cell. For example, the WorksheetCollection.activeSheetIndex property is useful for setting the active sheet in a workbook. Similarly, Worksheet.activeCell property is used to set and get an active cell in the worksheet.
To make sure that the horizontal or vertical scrollbars are at the row and column index position you want to show specific data, use the Worksheet.firstVisibleRow and Worksheet.firstVisibleColumn properties.
The following example shows how to activate a worksheet and make an active cell in it. In the generated output, the scrollbars will be scrolled to make the 2nd row and 2nd column as their first visible row and column.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Create Workbook 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 () => {
// Instantiate a new Workbook.
const workbook = new Workbook();
// Add a worksheet if collection is empty
const worksheets = workbook.worksheets;
if (worksheets.count === 0) {
worksheets.add();
}
// Get the first worksheet in the workbook.
const worksheet1 = worksheets.get(0);
// Get the cells in the worksheet.
const cells = worksheet1.cells;
// Input data into B2 cell.
const cell = cells.get(1, 1);
cell.value = "Hello World!";
// Set the first sheet as an active sheet.
worksheets.activeSheetIndex = 0;
// Set B2 cell as an active cell in the worksheet.
worksheet1.activeCell = "B2";
// Set the B column as the first visible column in the worksheet.
worksheet1.firstVisibleColumn = 1;
// Set the 2nd row as the first visible row in the worksheet.
worksheet1.firstVisibleRow = 1;
// 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 = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Workbook created successfully! Click the download link to get the file.</p>';
});
</script>
</html>
Advance topics
- Copying and Moving Worksheets
- Count number of cells in the Worksheet
- Detecting Empty Worksheets
- Find if the Worksheet is Dialog Sheet
- Get worksheet unique id
- Create, Manipulate or Remove Scenarios from Worksheets
- Managing Page Breaks
- Page Setup Features
- Utilize Sheet.SheetId property of OpenXml using Aspose.Cells
- Worksheet Views