Datafiltrering
Contents
[
Hide
]
Microsoft Excel erbjuder bra funktioner för autofilter av kalkylbladets data. Aspose.Cells stöder fullt ut Microsoft Excels autofilterfunktioner. Denna artikel förklarar hur man använder funktionerna i Microsoft Excel och hur man kodar dem med Aspose.Cells for JavaScript via C++.
Autofilterdata
Autofiltrering är det snabbaste sättet att välja endast de poster från arbetsbladet som du vill visa i en lista. Autofilterfunktionen gör det möjligt för användare att filtrera poster i en lista enligt ett angivet kriterium. Filtrera baserat på text, nummer eller datum.
Autofilter i Microsoft Excel
För att aktivera autofilterfunktionen i Microsoft Excel:
- Klicka på den rubrikraden i en arbetsbok.
- Från menyn Data, välj Filter och sedan AutoFilter.
När du tillämpar ett autofilter på en arbetsbok visas filteromkopplare (svarta pilar) till höger om kolumnrubrikerna.
- Klicka på en filterpil för att se en lista över filteralternativ.
Några av autofilteralternativen är:
| Alternativ | Beskrivning |
|---|---|
| All | Visa alla poster i listan en gång. |
| Custom | Anpassa filterkriterier som innehåller/inte innehåller |
| Filter by Color | Filter baserat på fyllningsfärg |
| Date Filters | Filtrera rader baserat på olika kriterier på datum |
| Number Filters | Olika typer av filter på nummer som jämförelse, medeltal och Topp 10 etc. |
| Text Filters | Olika filter som börjar med, slutar med, innehåller osv. |
| Blanks/Non Blanks | Dessa filter kan implementeras genom textfilter Tom |
Användare filtrerar manuellt sina arbetsboksdata i Microsoft Excel med dessa alternativ.
Autofilter med Aspose.Cells for JavaScript via C++
Aspose.Cells tillhandahåller en klass, Workbook, som representerar en Excel-fil. Workbook-klassen innehåller en Worksheets-samling som ger åtkomst till varje arbetsbok i Excel-filen.
En arbetsbok representeras av klassen Worksheet. Worksheet-klassen tillhandahåller ett brett utbud av egenskaper och metoder för att hantera arbetsböcker. För att skapa ett autofilter, använd AutoFilter-egenskapen i Worksheet-klassen. AutoFilter-egenskapen är en instans av klassen AutoFilter, som ger Range-egenskapen för att ange den rad med celler som utgör en rubrikrad. Ett autofilter appliceras på cellområdet som är rubrikraden.
I varje arbetsbok kan du endast ange ett filterområde. Detta är begränsat av Microsoft Excel. För anpassad datafiltrering, använd AutoFilter.Custom-metoden.
I exemplet nedan har vi skapat samma AutoFilter med Aspose.Cells for JavaScript via C++ som vi gjorde med Microsoft Excel i ovanstående avsnitt.
<!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>
Olika typer av filter
Aspose.Cells tillhandahåller flera alternativ för att använda olika typer av filter som färgfilter, datumfilter, nummerfilter, textfilter, blanka filter och icke-blanka filter.
Fyllfärg
Aspose.Cells tillhandahåller en funktion AddFillColorFilter för att filtrera data baserat på fyllfärgsegenskapen hos cellerna. I det angivna exemplet nedan används en mallfil med olika fyllfärger i den första kolumnen i arket för att testa färgfiltreringsfunktionen. Exempelfiler kan laddas ner från följande länkar.
<!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
Olika typer av datumfilter kan implementeras, till exempel filtrering av alla rader med datum i januari 2018. Följande kodexempel demonstrerar detta filter med funktionen AddDateFilter. Exempel på filer finns nedan.
<!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>
Dynamiskt datum
Ibland krävs dynamiska filter baserat på datum som alla celler med datum i januari oavsett år. I detta fall används DynamicFilter-funktionen enligt det följande exempelkodet. Exempelfiler ges nedan.
<!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
Anpassade filter kan appliceras med Aspose.Cells som att välja celler med nummer mellan ett givet intervall. Följande exempel demonstrerar användningen av Custom-funktionen för att filtrera nummer. Exempelfiler ges nedan.
<!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
Om en kolumn innehåller text och cellerna ska väljas baserat på ett visst textord kan funktionen Filter() användas. I följande exempel innehåller mallfilen en lista av länder och raden ska väljas som innehåller ett visst land. Följande kod demonstrerar filtrering av text. Exempel på filer finns nedan.
<!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>
Tomma
Om en kolumn innehåller text så att några celler är tomma och det krävs ett filter för att välja de rader där tomma celler finns, kan MatchBlanks-funktionen användas enligt nedan demonstrerat. Exempelfiler ges nedan.
<!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>
Ej tomma
När celler med text ska filtreras, använd MatchNonBlanks filterfunktion enligt nedan. Exempelfiler ges nedan.
<!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>
Anpassat filter med Innehåller
Excel erbjuder anpassade filter som filtrerar rader som innehåller en specifik sträng. Denna funktion är tillgänglig i Aspose.Cells och demonstreras nedan genom att filtrera namnen i provfilen. Exempelfiler ges nedan.
<!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>
Anpassat filter med EjInnehåller
Excel erbjuder anpassade filter, som filterrader som inte innehåller ett visst sträng. Denna funktion är tillgänglig i Aspose.Cells och demonstreras nedan genom att filtrera namnen i sample-filen som finns nedan.
<!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>
Anpassat filter med BörjaMed
Excel erbjuder anpassade filter, som filterrader som börjar med en viss sträng. Denna funktion är tillgänglig i Aspose.Cells och demonstreras nedan genom att filtrera namnen i sample-filen som finns nedan.
<!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>
Anpassat filter med SlutaMed
Excel tillhandahåller anpassade filter för att filtrera rader som slutar med en specifik sträng. Denna funktion finns i Aspose.Cells och visas nedan genom att filtrera namnen i exempelfilen nedan.
<!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>