Show and Hide Rows Columns and Scroll Bars with JavaScript via C++
Show and Hide Rows and Columns
Aspose.Cells for JavaScript via C++ provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows developers to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection that represents all cells in the worksheet. The cells collection provides several methods for managing rows or columns in a worksheet. A few of these are discussed below.
Show Rows and Columns
Developers can show any hidden row or column by calling the unhideRow(number, number) and unhideColumn(number, number) methods of the cells collection respectively. Both methods take two parameters:
- Row or column index - the index of a row or column that is used to show the specific row or column.
- Row height or column width - the row height or column width assigned to the row or column after unhiding.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Unhide Row and Column 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();
const workbook = new Workbook(new Uint8Array(arrayBuffer));
const worksheet = workbook.worksheets.get(0);
worksheet.cells.unhideRow(2, 13.5);
worksheet.cells.unhideColumn(1, 8.5);
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 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>
Hide Rows and Columns
Developers can hide a row or column by calling the hideRow(number) and hideColumn(number) methods of the cells collection respectively. Both methods take the row and column index as a parameter to hide the specific row or column.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Hide Row/Column 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));
// Access the first worksheet
const worksheet = workbook.worksheets.get(0);
// Hide the 3rd row (index 2) and 2nd column (index 1)
worksheet.cells.hideRow(2);
worksheet.cells.hideColumn(1);
// Save the modified workbook and provide a download link
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;">Row and column hidden successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Hide Multiple Rows and Columns
Developers can hide multiple rows or columns at once by calling the hideRows(number, number) and hideColumns(number, number) methods of the cells collection respectively. Both methods take the starting row or column index and the number of rows or columns that should be hidden as parameters.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Hide Rows and Columns</title>
</head>
<body>
<h1>Hide Rows and Columns 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Hiding 3, 4 and 5 rows in the worksheet (rows are zero-based index)
worksheet.cells.hideRows(2, 3);
// Hiding 2 and 3 columns in the worksheet (columns are zero-based index)
worksheet.cells.hideColumns(1, 2);
// 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.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>
Show and Hide Scroll Bars
Scroll bars are used to navigate the contents of any file. Normally, there are two kinds of scroll bars:
- Vertical scroll bars
- Horizontal scroll bars
Microsoft Excel also provides horizontal and vertical scroll bars so that users can scroll through worksheet contents. Using Aspose.Cells, developers can control the visibility of both types of scroll bars in Excel files.
Controlling the Visibility of Scroll Bars
Aspose.Cells provides a class, Workbook that represents an Excel file. The Workbook class provides a wide range of properties and methods for managing an Excel file. To control the visibility of scroll bars, use the WorkbookSettings.isVScrollBarVisible() and WorkbookSettings.isHScrollBarVisible() properties. WorkbookSettings.isVScrollBarVisible() and WorkbookSettings.isHScrollBarVisible() are Boolean properties, which means that these properties can only store true or false values.
Making Scroll Bars Visible
Make scroll bars visible by setting the Workbook class' WorkbookSettings.isVScrollBarVisible() or WorkbookSettings.isHScrollBarVisible() property to true.
Hiding Scroll Bars
Hide scroll bars by setting the Workbook class' WorkbookSettings.isVScrollBarVisible() or WorkbookSettings.isHScrollBarVisible() property to false.
Sample Code
Below is a complete code that opens an Excel file, book1.xls, hides both scroll bars and then saves the modified file as output.xls.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells - Hide Scrollbars 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));
// Hiding the vertical scroll bar of the Excel file
workbook.settings.isVScrollBarVisible = false;
// Hiding the horizontal scroll bar of the Excel file
workbook.settings.isHScrollBarVisible = false;
// 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.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Scrollbars hidden successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>