Get All Hidden Rows Indices after Refreshing AutoFilter
Possible Usage Scenarios
When you apply the auto filter on worksheet cells, then some of the rows get hidden automatically. But it might be the case that some of the rows are already hidden manually by Excel end user and they are not hidden by an auto filter. It therefore makes difficult to know which of the rows are hidden by the auto filter and which of them are hidden manually by Excel end user. Aspose.Cells for JavaScript via C++ deals with this problem using the Array AutoFilter.refresh(hideRows). This method returns the row indices of all the rows that are hidden by the auto filter and not manually by the Excel end user.
Get All Hidden Rows Indices after Refreshing AutoFilter
Please see the following sample code that loads the sample Excel file which contains some of the rows hidden manually by Excel end user. The code applies the auto filter and refreshes it using the Array AutoFilter.refresh(hideRows) method that returns the row indices of all the hidden rows by the auto filter. It then prints the indices of the hidden rows on the console along with cells names and values.
Sample Code
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Get Hidden Rows After Refreshing AutoFilter</title>
</head>
<body>
<h1>Get Hidden Rows After Refreshing AutoFilter</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');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.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 file input
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet
const worksheet = workbook.worksheets.get(0);
// Apply autofilter
worksheet.autoFilter.addFilter(0, "Orange");
// True means it will refresh autofilter and return hidden rows.
const rowIndices = worksheet.autoFilter.refresh(true);
console.log("Printing Rows Indices, Cell Names and Values Hidden By AutoFilter.");
console.log("--------------------------");
let output = '<p>Printing Rows Indices, Cell Names and Values Hidden By AutoFilter.</p><pre>';
rowIndices.forEach(r => {
const cell = worksheet.cells.get(r, 0);
console.log(`${r}\t${cell.name}\t${cell.stringValue}`);
output += `${r}\t${cell.name}\t${cell.stringValue}\n`;
});
output += '</pre>';
resultDiv.innerHTML = output;
});
</script>
</html>
Console Output
Printing Rows Indices, Cell Names and Values Hidden By AutoFilter.
\--------------------------
1 A2 Apple
2 A3 Apple
3 A4 Apple
6 A7 Apple
7 A8 Apple
11 A12 Pear
12 A13 Pear