Three methods for filtering Chart Data
1. Filtering out series to render a chart
Steps to filter series from a chart in Excel
In Excel, we can filter out specific series from a chart, causing those filtered series not to be displayed in the chart. The original chart is shown in Figure 1. However, when we filter out Testseries2 and Testseries4,the chart will appear as shown in Figure 2.
In Aspose.Cells for Python via .NET, we can perform a similar operation. For a sample file like this, if we want to filter out Testseries2 and Testseries4,we can execute the following code. Additionally, we will maintain two lists: one (n_series) list to store all the selected series and another (filtered_n_series) to store the filtered series.
Please note that in the code, when we set chart.nSeries[0].is_filtered = TRUE;, the first series in n_series will be removed and placed in the appropriate position within filtered_n_series. Subsequently, the previous nSeries[1] will become the new first item in the list, and all the following series will shift forward by one position. This means that if we then run chart.nSeries[1].is_filtered = TRUE;, we are effectively removing the original third series. This can sometimes lead to confusion, so we recommend following the operation in the code, which deletes series from the end to the beginning.
Sample Code
The following sample code loads the sample Excel file.
from aspose.cells import Workbook | |
# Create an instance of existing Workbook | |
workbook = Workbook("seriesFiltered.xlsx") | |
# Get filtered series list | |
nSeriesFiltered = workbook.worksheets[0].charts.get("Chart 1").filtered_n_series | |
# Get selected series list | |
nSeries = workbook.worksheets[0].charts.get("Chart 1").n_series | |
# Should be 0 | |
print("Filtered Series count" + str(len(nSeriesFiltered))) | |
# Should be 6 | |
print("Visiable Series count" + str(len(nSeries))) | |
# Process from the end to the beginning | |
nSeries[1].is_filtered = True | |
nSeries[0].is_filtered = True | |
# Should be 2 | |
print("Filtered Series count" + str(len(nSeriesFiltered))) | |
# Should be 4 | |
print("Visiable Series count" + str(len(nSeries))) | |
workbook.save("seriesFiltered-out.xlsx") | |
workbook = Workbook("seriesFiltered-out.xlsx") | |
# Should be 2 | |
print("Filtered Series count" + str(len(nSeriesFiltered))) | |
# Should be 4 | |
print("Visiable Series count" + str(len(nSeries))) |
2. Filter the data and let the chart change
Filtering your data is a great way to handle chart filters with a lot of data. When you filter the data, the chart will change. One issue we’re going to have to address is making sure the chart stays on the screen. When you filter, you get hidden rows, and occasionally, the chart will be in those hidden rows.
Steps to use Data Filters to change the chart in Excel
- Click inside your data range.
- Click the Data tab, and turn on Filters by clicking Filters. Your header row will have drop down arrows.
- Create a chart by going to Insert tab and selecting a columns chart.
- Now filter your data using the drop down arrows in the data. Don’t use the Chart Filters.
Sample Code
The following sample code shows the same featrue using Aspsoe.Cells.
from aspose.cells import Workbook | |
from aspose.cells.charts import ChartType | |
# Create an instance of Workbook | |
workbook = Workbook() | |
# Get the First sheet. | |
sheet = workbook.worksheets.get("Sheet1") | |
# Add data into details cells. | |
sheet.cells.get(0, 0).put_value("Fruits Name") | |
sheet.cells.get(0, 1).put_value("Fruits Price") | |
sheet.cells.get(1, 0).put_value("Apples") | |
sheet.cells.get(2, 0).put_value("Bananas") | |
sheet.cells.get(3, 0).put_value("Grapes") | |
sheet.cells.get(4, 0).put_value("Oranges") | |
sheet.cells.get(1, 1).put_value(5) | |
sheet.cells.get(2, 1).put_value(2) | |
sheet.cells.get(3, 1).put_value(1) | |
sheet.cells.get(4, 1).put_value(4) | |
# Add a chart to the worksheet | |
chartIndex = sheet.charts.add(ChartType.COLUMN, 7, 7, 15, 15) | |
# Access the instance of the newly added chart | |
chart = sheet.charts[chartIndex] | |
# Set data range | |
chart.set_chart_data_range("A1:B5", True) | |
# Set AutoFilter range | |
sheet.auto_filter.range = "A1:B5" | |
# Add filters for a filter column. | |
sheet.auto_filter.add_filter(0, "Bananas") | |
sheet.auto_filter.add_filter(0, "Oranges") | |
# Apply the filters | |
sheet.auto_filter.refresh() | |
chart.to_image("Autofilter.png") | |
workbook.save("Autofilter.xlsx") |
3. Filter the data using a Table and let the chart change
Using a Table is similar to Method 2, using a range, but you have advantages with tables over ranges. When you change your range to a Table and add data, the chart automatically updates. With a range, you will have to change the data source.
Format as table in Excel
Click inside your data and use CTRL + T or use the Home tab, Format as Table
Sample Code
The following sample code loads the sample Excel file shows the same featrue using Aspsoe.Cells.
from aspose.cells import Workbook | |
from aspose.cells.charts import ChartType | |
from aspose.cells.tables import TableStyleType | |
# Create a workbook. | |
workbook = Workbook("TableFilters.xlsx") | |
# Access first worksheet | |
sheet = workbook.worksheets[0] | |
# Access the instance of the newly added chart | |
chartIndex = sheet.charts.add(ChartType.COLUMN, 7, 7, 15, 15) | |
chart = sheet.charts[chartIndex] | |
# Set data range | |
chart.set_chart_data_range("A1:B7", True) | |
# Convert the chart to image | |
chart.to_image("TableFilters.before.png") | |
# Add a new List Object to the worksheet | |
listObject = sheet.list_objects[sheet.list_objects.add("A1", "B7", True)] | |
# Add default style to the table | |
listObject.table_style_type = TableStyleType.TABLE_STYLE_MEDIUM10 | |
# Show Total | |
listObject.show_totals = False | |
# Add filters for a filter column. | |
listObject.auto_filter.add_filter(0, "James") | |
# Apply the filters | |
listObject.auto_filter.refresh() | |
# After adding new value the chart will change | |
listObject.put_cell_value(7, 0, "Me") | |
listObject.put_cell_value(7, 1, 1000) | |
# Check the changed images | |
chart.to_image("TableFilters.after.png") | |
# Saving the Excel file | |
workbook.save("TableFilter.out.xlsx") |