Tre metodi per filtrare i dati del grafico con JavaScript tramite C++
Contents
[
Hide
]
1. Filtrare le serie per visualizzare un grafico
Passaggi per filtrare le serie da un grafico in Excel
In Excel, possiamo filtrare specifiche serie da un grafico, facendo sì che quelle serie filtrate non siano visualizzate nel grafico. Il grafico originale è mostrato in Figura 1. Tuttavia, quando filtriamo Testseries2 e Testseries4, il grafico apparirà come mostrato in Figura 2.
In Aspose.Cells for JavaScript tramite C++, possiamo eseguire un’operazione simile. Per un file sample come questo, se vogliamo filtrare Testseries2 e Testseries4, possiamo eseguire il seguente codice. Inoltre, manterremo due elenchi: uno (Chart.nSeries) per memorizzare tutte le serie selezionate.
Codice di Esempio
Il seguente codice di esempio carica il file Excel di esempio.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Series Filter 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();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet and chart
const worksheet = workbook.worksheets.get(0);
const chart = worksheet.charts.get("Chart 1");
// Get filtered series list and visible series list
const nSeriesFiltered = chart.filteredNSeries;
const nSeries = chart.nSeries;
// Initial counts
const initialFilteredCount = nSeriesFiltered.count;
const initialVisibleCount = nSeries.count;
console.log("Filtered Series count: " + initialFilteredCount);
console.log("Visible Series count: " + initialVisibleCount);
// Process from the end to the beginning - mark series as filtered
nSeries.get(1).isFiltered = true;
nSeries.get(0).isFiltered = true;
// Counts after filtering
const afterFilteredCount = nSeriesFiltered.count;
const afterVisibleCount = nSeries.count;
console.log("Filtered Series count: " + afterFilteredCount);
console.log("Visible Series count: " + afterVisibleCount);
// Save the modified workbook and provide download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'seriesFiltered-out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
// Re-open the saved workbook from the in-memory output to validate counts
const reopenedWorkbook = new Workbook(outputData);
const reopenedWorksheet = reopenedWorkbook.worksheets.get(0);
const reopenedChart = reopenedWorksheet.charts.get("Chart 1");
const reopenedFiltered = reopenedChart.filteredNSeries;
const reopenedVisible = reopenedChart.nSeries;
const reopenedFilteredCount = reopenedFiltered.count;
const reopenedVisibleCount = reopenedVisible.count;
console.log("Filtered Series count: " + reopenedFilteredCount);
console.log("Visible Series count: " + reopenedVisibleCount);
// Display results to user
document.getElementById('result').innerHTML =
`<p style="color: green;">Initial - Filtered Series count: ${initialFilteredCount}, Visible Series count: ${initialVisibleCount}</p>` +
`<p style="color: green;">After Marking - Filtered Series count: ${afterFilteredCount}, Visible Series count: ${afterVisibleCount}</p>` +
`<p style="color: green;">After Reopen - Filtered Series count: ${reopenedFilteredCount}, Visible Series count: ${reopenedVisibleCount}</p>`;
});
</script>
</html>
2. Filtrare i dati e far cambiare il grafico
Filtrare i propri dati è un ottimo modo per gestire i filtri del grafico con molti dati. Quando filtri i dati, il grafico cambierà. Un problema che dovremo affrontare è assicurarsi che il grafico rimanga visibile a schermo. Quando si applicano filtri, vengono nastrate righe e occasionalmente il grafico sarà in quelle righe nascoste.
Passaggi per utilizzare i filtri dei dati per modificare il grafico in Excel
- Fare clic all’interno del proprio intervallo di dati.
- Fare clic sulla scheda Dati, e attivare i filtri cliccando su Filtri. La riga di intestazione avrà frecce a discesa.
- Creare un grafico andando alla scheda Inserisci e selezionando un grafico a colonne.
- Ora filtra i tuoi dati utilizzando le frecce a discesa nei dati. Non utilizzare i filtri del grafico.
Codice di Esempio
Il seguente esempio di codice mostra la stessa funzione usando Aspose.Cells.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Autofilter and Chart 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>
<a id="imageDownloadLink" style="display: none; margin-left: 10px;">Download Chart Image</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');
const imageDownloadLink = document.getElementById('imageDownloadLink');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
// Ensure Aspose.Cells is initialized
await AsposeCells.onReady();
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access the worksheet named "Sheet1"
const sheet = workbook.worksheets.get("Sheet1");
// Add data into detail cells
sheet.cells.get(0, 0).putValue("Fruits Name");
sheet.cells.get(0, 1).putValue("Fruits Price");
sheet.cells.get(1, 0).putValue("Apples");
sheet.cells.get(2, 0).putValue("Bananas");
sheet.cells.get(3, 0).putValue("Grapes");
sheet.cells.get(4, 0).putValue("Oranges");
sheet.cells.get(1, 1).putValue(5);
sheet.cells.get(2, 1).putValue(2);
sheet.cells.get(3, 1).putValue(1);
sheet.cells.get(4, 1).putValue(4);
// Add a chart to the worksheet
const chartIndex = sheet.charts.add(AsposeCells.ChartType.Column, 7, 7, 15, 15);
const chart = sheet.charts.get(chartIndex);
// Set data range (converted from setChartDataRange -> chartDataRange property)
chart.chartDataRange = "A1:B5";
// Set AutoFilter range (converted from setRange -> range property)
sheet.autoFilter.range = "A1:B5";
// Add filters for a filter column.
sheet.autoFilter.addFilter(0, "Bananas");
sheet.autoFilter.addFilter(0, "Oranges");
// Apply the filters
sheet.autoFilter.refresh();
// Export chart image (returns image data)
const imageData = chart.toImage("Autofilter.png");
// Save the modified workbook
const outputData = workbook.save(SaveFormat.Xlsx);
// Prepare Excel download link
const blob = new Blob([outputData]);
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'Autofilter.xlsx';
downloadLink.style.display = 'inline-block';
downloadLink.textContent = 'Download Excel File';
// Prepare image download link if image data exists
if (imageData) {
const imgBlob = new Blob([imageData], { type: 'image/png' });
imageDownloadLink.href = URL.createObjectURL(imgBlob);
imageDownloadLink.download = 'Autofilter.png';
imageDownloadLink.style.display = 'inline-block';
imageDownloadLink.textContent = 'Download Chart Image';
} else {
imageDownloadLink.style.display = 'none';
}
resultDiv.innerHTML = '<p style="color: green;">Operation completed successfully! Use the download links to get the modified files.</p>';
});
</script>
</html>
3. Filtra i dati utilizzando una Tabella e fai cambiare il grafico
Utilizzare una Tabella è simile al Metodo 2, utilizzando un intervallo, ma hai vantaggi con le tabelle rispetto agli intervalli. Quando cambia il tuo intervallo in una Tabella e aggiungi dati, il grafico si aggiorna automaticamente. Con un intervallo, dovrai modificare la fonte dati.
Formatta come tabella in Excel
Fare clic all’interno dei dati e utilizzare CTRL + T oppure utilizzare la scheda Home, Formatta come Tabella
Codice di Esempio
Il seguente esempio di codice carica il file di esempio Excel e mostra la stessa funzionalità usando Aspose.Cells.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Example Title</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>
<a id="downloadLinkBefore" style="display: none;">Download Before Image</a>
<a id="downloadLinkAfter" style="display: none;">Download After Image</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 from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first worksheet
const sheet = workbook.worksheets.get(0);
// Access the instance of the newly added chart
const chartIndex = sheet.charts.add(AsposeCells.ChartType.Column, 7, 7, 15, 15);
const chart = sheet.charts.get(chartIndex);
// Set data range (converted from setter to property)
chart.chartDataRange = { range: "A1:B7", isVertical: true };
// Convert the chart to image (before)
const beforeImageData = chart.toImage(SaveFormat.Png);
const beforeBlob = new Blob([beforeImageData]);
const beforeLink = document.getElementById('downloadLinkBefore');
beforeLink.href = URL.createObjectURL(beforeBlob);
beforeLink.download = 'TableFilters.before.png';
beforeLink.style.display = 'block';
beforeLink.textContent = 'Download Chart Before Image';
// Add a new List Object to the worksheet
const listObjectIndex = sheet.listObjects.add("A1", "B7", true);
const listObject = sheet.listObjects.get(listObjectIndex);
// Add default style to the table
listObject.tableStyleType = AsposeCells.TableStyleType.TableStyleMedium10;
// Show Total
listObject.showTotals = false;
// Add filters for a filter column.
listObject.autoFilter.addFilter(0, "James");
// Apply the filters
listObject.autoFilter.refresh();
// After adding new value the chart will change
listObject.putCellValue(7, 0, "Me");
listObject.putCellValue(7, 1, 1000);
// Check the changed images (after)
const afterImageData = chart.toImage(SaveFormat.Png);
const afterBlob = new Blob([afterImageData]);
const afterLink = document.getElementById('downloadLinkAfter');
afterLink.href = URL.createObjectURL(afterBlob);
afterLink.download = 'TableFilters.after.png';
afterLink.style.display = 'block';
afterLink.textContent = 'Download Chart After Image';
// Saving the 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 = 'TableFilter.out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Use the download links to get the result files.</p>';
});
</script>
</html>