Datenfilterung
Contents
[
Hide
]
Microsoft Excel bietet einige nützliche Funktionen zum Autofiltering von Arbeitsblattdaten. Aspose.Cells unterstützt vollständig die Autofilter-Funktionen von Microsoft Excel. Dieser Artikel erklärt, wie man die Funktionen in Microsoft Excel verwendet und wie man sie mit dem Aspose.Cells for JavaScript über C++ programmiert.
Daten automatisch filtern
Die Autofilter-Funktion ist der schnellste Weg, um nur die Elemente auszuwählen, die in einer Liste angezeigt werden sollen. Die Autofilter-Funktion ermöglicht es Benutzern, Elemente in einer Liste nach einem bestimmten Kriterium zu filtern. Filtern nach Text, Zahlen oder Datum.
Autofilter in Microsoft Excel
Um die Autofilterfunktion in Microsoft Excel zu aktivieren:
- Klicken Sie auf die Überschriftenzeile in einem Arbeitsblatt.
- Wählen Sie im Menü Daten die Option Filter und dann Autofilter aus.
Wenn Sie einen Autofilter auf ein Arbeitsblatt anwenden, werden Filterumschalter (schwarze Pfeile) rechts von den Spaltenüberschriften angezeigt.
- Klicken Sie auf einen Filterpfeil, um eine Liste der Filteroptionen anzuzeigen.
Einige der Autofilteroptionen sind:
| Optionen | Beschreibung |
|---|---|
| All | Alle Elemente in der Liste einmal anzeigen. |
| Custom | Filterkriterien anpassen, wie enthält/nicht enthält. |
| Filter by Color | Filter basierend auf Füllfarbe. |
| Date Filters | Zeilen basierend auf verschiedenen Kriterien zu Datum filtern. |
| Number Filters | Verschiedene Arten des Filterns von Zahlen wie Vergleich, Durchschnitte und Top 10 usw. |
| Text Filters | Verschiedene Filter wie beginnt mit, endet mit, enthält usw. |
| Blanks/Non Blanks | Diese Filter können über Textfilter leer implementiert werden. |
Benutzer filtern ihre Arbeitsblattdaten in Microsoft Excel manuell mithilfe dieser Optionen.
Autofilter mit Aspose.Cells for JavaScript über C++
Aspose.Cells bietet eine Klasse, Workbook, die eine Excel-Datei repräsentiert. Die Workbook-Klasse enthält eine Worksheets-Sammlung, die den Zugriff auf jedes Arbeitsblatt in der Excel-Datei ermöglicht.
Ein Arbeitsblatt wird durch die Worksheet-Klasse dargestellt. Die Worksheet-Klasse bietet eine Vielzahl von Eigenschaften und Methoden zur Verwaltung von Arbeitsblättern. Um einen Autofilter zu erstellen, verwenden Sie die AutoFilter-Eigenschaft der Worksheet-Klasse. Die AutoFilter-Eigenschaft ist ein Objekt der AutoFilter-Klasse, die die Range-Eigenschaft zur Spezifizierung des Zellbereichs bereitstellt, der eine Überschriftenzeile bildet. Ein Autofilter wird auf den Zellbereich angewendet, der die Überschriftenzeile bildet.
In jedem Arbeitsblatt können Sie nur einen Filterbereich angeben. Dies ist von Microsoft Excel begrenzt. Verwenden Sie für benutzerdefiniertes Datenfiltern die AutoFilter.Custom-Methode.
Im unten angegebenen Beispiel haben wir den gleichen AutoFilter mit dem Aspose.Cells for JavaScript über C++ erstellt, wie wir ihn im oben genannten Abschnitt mit Microsoft Excel erstellt haben.
<!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>
Verschiedene Arten von Filter
Aspose.Cells bietet mehrere Optionen, um verschiedene Arten von Filtern anzuwenden, wie Farbfilter, Datumfilter, Zahlenfilter, Textfilter, Blankfilter und Nicht-Blankfilter.
Füllfarbe
Aspose.Cells bietet eine Funktion AddFillColorFilter zum Filtern von Daten basierend auf der Füllfarbeigenschaft der Zellen. Im unten gezeigten Beispiel wird eine Vorlagendatei mit verschiedenen Füllfarben in der ersten Spalte des Blatts verwendet, um die Farbfilterfunktion zu testen. Beispieldateien können über die folgenden Links heruntergeladen werden.
<!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>
Datum
Verschiedene Arten von Datumsfiltern können implementiert werden, z.B. das Filtern aller Zeilen mit Daten im Januar 2018. Das folgende Beispiel zeigt, wie dieser Filter mit der AddDateFilter-Funktion implementiert wird. Beispiel-Dateien sind unten angegeben.
<!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>
Dynamisches Datum
Manchmal sind dynamische Filter erforderlich, basierend auf dem Datum, z.B. alle Zellen mit Datum im Januar unabhängig vom Jahr. In diesem Fall wird die Funktion DynamicFilter gemäß dem folgenden Beispielscode verwendet. Musterdateien sind unten angegeben.
<!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>
Nummer
Benutzerdefinierte Filter können mit Aspose.Cells angewendet werden, z. B. die Auswahl von Zellen mit einer Zahl innerhalb eines bestimmten Bereichs. Das folgende Beispiel zeigt die Verwendung der benutzerdefinierten() Funktion zum Filtern von Zahlen. Beispieldateien finden Sie unten.
<!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>
Text
Wenn eine Spalte Text enthält und Zellen zum Behalten bestimmter Texte ausgewählt werden sollen, kann die Filter() Funktion verwendet werden. Im folgenden Beispiel enthält die Vorlage eine Lister von Ländern, und die Zeile soll ein bestimmtes Land enthalten. Der folgende Code demonstriert das Filtern von Texten. Beispiel-Dateien sind unten angegeben.
<!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>
Leerzeichen
Wenn eine Spalte Text enthält, so dass nur wenige Zellen leer sind und ein Filter benötigt wird, um nur die Zeilen auszuwählen, in denen leere Zellen vorhanden sind, kann die Funktion MatchBlanks() wie im folgenden Beispiel verwendet werden. Beispieldateien sind unten angegeben.
<!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>
Nicht leer
Wenn Zellen mit beliebigem Text gefiltert werden sollen, verwenden Sie die Filterfunktion MatchNonBlanks wie im folgenden Beispiel gezeigt. Beispieldateien sind unten angegeben.
<!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>
Benutzerdefinierter Filter mit enthält
Excel bietet benutzerdefinierte Filter wie das Filtern von Zeilen, die einen bestimmten String enthalten. Diese Funktion ist in Aspose.Cells verfügbar und wird unten durch Filtern der Namen in der Beispieldatei demonstriert. Beispieldateien finden Sie unten.
<!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>
Benutzerdefinierter Filter mit Nicht enthält
Excel bietet benutzerdefinierte Filter wie das Filtern von Zeilen, die keinen bestimmten String enthalten. Diese Funktion ist in Aspose.Cells verfügbar und wird unten durch das Filtern der Namen in der Beispiel-Datei demonstriert.
<!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>
Benutzerdefinierter Filter mit Beginnt mit
Excel bietet benutzerdefinierte Filter wie das Filtern von Zeilen, die mit einem bestimmten String beginnen. Diese Funktion ist in Aspose.Cells verfügbar und wird durch das Filtern der Namen in der unten angegebenen Beispiel-Datei demonstriert.
<!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>
Benutzerdefinierter Filter mit EndsWith
Excel bietet benutzerdefinierte Filter wie das Filtern von Zeilen, die mit einem bestimmten Text enden. Diese Funktion ist in Aspose.Cells verfügbar und wird unten durch das Filtern der Namen in der unten angegebenen Beispieldatei demonstriert.
<!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>