数据筛选
Contents
[
Hide
]
微软Excel提供了一些自动筛选工作表数据的优秀功能。Aspose.Cells完全支持微软Excel的自动筛选功能。本文介绍如何在微软Excel中使用这些功能，以及如何通过Aspose.Cells for JavaScript用C++编写代码实现它们。
自动筛选数据
自动筛选是选择仅显示列表中您想要显示的项的最快方法。自动筛选功能允许用户根据一组标准过滤列表中的项目。根据文本、数字或日期进行筛选。
Microsoft Excel 中的自动筛选
要在 Microsoft Excel 中启用自动筛选功能：
- 单击工作表中的标题行。
- 从数据菜单中选择筛选，然后选择自动筛选。
在工作表上应用自动筛选时，过滤开关 (黑色箭头) 会出现在列标题右侧。
- 单击筛选箭头，以查看筛选选项列表。
一些自动筛选选项包括：
| 选项 | 描述 |
|---|---|
| All | 在列表中显示所有项目一次。 |
| Custom | 自定义包含/不包含等筛选条件 |
| Filter by Color | 基于填充颜色的筛选 |
| Date Filters | 基于不同日期标准的行筛选 |
| Number Filters | 在数字上应用不同类型的筛选，例如比较，平均值和前10名等。 |
| Text Filters | 不同的筛选，如以…开始、以…结束、包含等。 |
| Blanks/Non Blanks | 这些筛选可以通过文本筛选空白值实现。 |
用户可以使用这些选项手动筛选其 Microsoft Excel 工作表中的数据。
使用Aspose.Cells for JavaScript通过C++实现自动筛选
Aspose.Cells提供了一个表示Excel文件的类Workbook。Workbook类包含一个Worksheets集合，允许访问Excel文件中的每个工作表。
工作表由Worksheet类表示。Worksheet类提供了广泛的属性和方法来管理工作表。要创建自动筛选，请使用Worksheet类的AutoFilter属性。AutoFilter属性是AutoFilter类的对象，该类提供了Range属性，用于指定构成标题行的单元格范围。自动筛选应用于构成标题行的单元格范围。
在每个工作表中，您只能指定一个筛选范围。这是由Microsoft Excel限制的。要进行自定义数据过滤，请使用AutoFilter.Custom方法。
在下面的示例中，我们用Aspose.Cells for JavaScript通过C++创建了与上节微软Excel中相同的自动筛选。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells AutoFilter Example</title>
</head>
<body>
<h1>AutoFilter 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');
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 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);
// Creating AutoFilter by giving the cells range of the heading row
worksheet.autoFilter.range = "A1:B1";
// 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';
resultDiv.innerHTML = '<p style="color: green;">AutoFilter created successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
不同类型的筛选
Aspose.Cells 提供了多种选项，应用不同类型的筛选，如颜色筛选、日期筛选、数字筛选、文本筛选、空白筛选和非空白筛选。
填充色
Aspose.Cells 提供了 AddFillColorFilter 函数，用于根据单元格的填充颜色属性筛选数据。在下面的示例中，使用具有工作表第一列中不同填充颜色的模板文件来测试颜色筛选功能。示例文件可从以下链接下载。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Filter Coloured 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');
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 by opening the uploaded Excel file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Instantiating a CellsColor object for foreground color
const clrForeground = workbook.createCellsColor();
clrForeground.color = AsposeCells.Color.fromArgb(255, 0, 0); // Red color
// Instantiating a CellsColor object for background color
const clrBackground = workbook.createCellsColor();
clrBackground.color = AsposeCells.Color.fromArgb(255, 255, 255); // White color
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call AddFillColorFilter function to apply the filter
worksheet.autoFilter.addFillColorFilter(0, AsposeCells.BackgroundType.Solid, clrForeground, clrBackground);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredColouredCells.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
resultDiv.innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
日期
可以实现不同类型的日期筛选，例如筛选所有2018年1月的日期。以下示例代码演示了如何使用AddDateFilter函数完成此筛选。提供的样例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Date Filter</title>
</head>
<body>
<h1>Date Filter Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx" />
<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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call AddDateFilter function to apply the filter
worksheet.autoFilter.addDateFilter(0, AsposeCells.DateTimeGroupingType.Month, 2018, 1, 0, 0, 0, 0);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredDate.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
动态日期
有时需要基于日期进行动态过滤，例如筛选所有在一月份的日期，而不考虑年份。在这种情况下，可以使用 DynamicFilter 函数，如以下示例代码所示。示例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Filter Dynamic Date 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call DynamicFilter function to apply the filter
worksheet.autoFilter.dynamic_Filter(0, AsposeCells.DynamicFilterType.January);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredDynamicDate.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
数字
可以使用 Aspose.Cells 应用自定义筛选，例如选择在给定范围内的数字的单元格。以下示例演示了使用 Custom() 函数筛选数字的用法。示例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - Filter Numbers</title>
</head>
<body>
<h1>Filter Numbers 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call Custom function to apply the filter
worksheet.autoFilter.custom(0, AsposeCells.FilterOperatorType.GreaterOrEqual, 5, true, AsposeCells.FilterOperatorType.LessOrEqual, 10);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredNumber.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
文本
如果某列包含文本，且欲筛选包含特定文本的单元格，可以使用Filter()函数。以下示例中，模板文件包含国家名单，要筛选包含某个国家名的行。以下代码演示文本筛选。样例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Filter AutoFilter 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call Filter function to apply the filter
const autoFilter = worksheet.autoFilter;
autoFilter.filter(0, "Angola");
// Call refresh function to update the worksheet
autoFilter.refresh();
// Saving the modified 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 = 'FilteredText.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
空白
如果某一列包含文本，有一些单元格为空白，并且需要筛选出只包含空白单元格的行，则可以使用 MatchBlanks() 函数，如下所示。示例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Filter Blank Rows Example</title>
</head>
<body>
<h1>Filter Blank Rows 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Call MatchBlanks function to apply the filter
worksheet.autoFilter.matchBlanks(0);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredBlank.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filtering completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
非空白
当需要过滤带有任何文本的单元格时，可以使用 MatchNonBlanks 过滤函数，如下所示。示例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Filter Non-Blank 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');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
// Read the selected file
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);
// Call MatchNonBlanks function to apply the filter
worksheet.autoFilter.matchNonBlanks(0);
// Call refresh function to update the worksheet
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'FilteredNonBlank.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
resultDiv.innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
包含自定义筛选
Excel 提供了自定义筛选功能，例如筛选包含特定字符串的行。Aspose.Cells 中也提供了此功能，并且通过下面的示例演示了对样本文件中的名称进行筛选。示例文件如下。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells AutoFilter Example</title>
</head>
<body>
<h1>AutoFilter 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, FilterOperatorType } = 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));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Creating AutoFilter by giving the cells range
worksheet.autoFilter.range = "A1:A18";
// Initialize filter for rows containing string "Ba"
worksheet.autoFilter.custom(0, FilterOperatorType.Contains, "Ba");
// Refresh the filter to show/hide filtered rows
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'outSourseSampleCountryNames.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
不包含特定字符串的自定义筛选
Excel提供自定义筛选，例如筛选不包含某些特定字符串的行。此功能在Aspose.Cells中可用，以下示例通过筛选样本中的姓名演示。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells AutoFilter Example</title>
</head>
<body>
<h1>AutoFilter 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, FilterOperatorType } = 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 uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Creating AutoFilter by giving the cells range
worksheet.autoFilter.range = "A1:A18";
// Initialize filter for rows not containing string "Be"
worksheet.autoFilter.custom(0, FilterOperatorType.NotContains, "Be");
// Refresh the filter to show/hide filtered rows
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'outSourseSampleCountryNames.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
以指定字符串开头的自定义筛选
Excel还提供以某些字符串开头的自定义筛选。此功能在Aspose.Cells中可用，以下示例通过筛选样本中的姓名进行演示。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Filter Countries Starting With "Ba"</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, FilterOperatorType } = 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 uploaded Excel file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Creating AutoFilter by giving the cells range
worksheet.autoFilter.range = "A1:A18";
// Initialize filter for rows starting with string "Ba"
worksheet.autoFilter.custom(0, FilterOperatorType.BeginsWith, "Ba");
// Refresh the filter to show/hide filtered rows
worksheet.autoFilter.refresh();
// Saving the modified 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 = 'outSourseSampleCountryNames.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Filtered Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
以指定字符串结尾的自定义筛选
Excel提供了自定义筛选功能，例如筛选以特定字符串结尾的行。这一功能在Aspose.Cells中可用，并通过以下演示在示例文件中筛选名称。
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Apply AutoFilter 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');
const resultDiv = document.getElementById('result');
const downloadLink = document.getElementById('downloadLink');
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
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Creating AutoFilter by giving the cells range
const autoFilter = worksheet.autoFilter;
autoFilter.range = "A1:A18";
// Initialize filter for rows end with string "ia"
autoFilter.custom(0, AsposeCells.FilterOperatorType.BeginsWith, "ia");
// Refresh the filter to show/hide filtered rows
autoFilter.refresh();
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'outSourseSampleCountryNames.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Filter applied successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>