How to make series invisible in charting

Aspose.Cells Product Family
1

Hi,

In the excel charting, we can make series invisible by uncheck the legend entry in select data source dialog( please see Pic1), I have two questions related to this setting.

1. How I can make the series invisible in the aspose chart, I couldn’t find visible property for series object. So I try to achieve this using the following code.

chartSeries.Area.Formatting = Aspose.Cells.Charts.FormattingType.None
chartSeries.Border.IsVisible = False
'remove legend entry
chart.Legend.LegendEntries(seriesIndex).IsDeleted = True

The problem with this solution is that I can see the white gap left by the invisible series if the chart is column or bar type.

2. If I save the Aspose chart to the excel, how I can make this series unchecked in the select data source dialog.

Please let me know if you have any question.

Thanks,
Wei

2

Hi Wei,

Thanks for your posting and using Aspose.Cells.

We are afraid, hiding the series is not supported by Aspose.Cells. It is actually an Excel 2013 feature which is also not available in Excel 2010 and Excel 2007.

As a workaround, you can remove the Chart Series using the following code.

I have attached the source xlsx file and output xlsx file generated by the code for your reference.

In order to support this feature in our future versions, we have logged this issue in our database as a New Feature request. We will look into it and implement it if possible. Once, it is available or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-42808 - Support to Hide Chart Series

C#

Workbook workbook = new Workbook(“source.xlsx”);


Worksheet worksheet = workbook.Worksheets[0];


Chart chart = worksheet.Charts[0];


chart.NSeries.RemoveAt(1);


workbook.Save(“output.xlsx”);

3

Thanks for the information.

Do you know whether Aspose.cells will support this feature in the further release? And when will it be available if it does?

Thanks,
Wei

4

Sorry, Please ignore my previous update. I have found the answer from your post.

Thanks,
Wei

5

Hi Wei,

Thanks for your posting and using Aspose.Cells.

We have logged a New Feature request for this issue. We will evaluate it and see if it could be supported in our next versions. Please spare us some time. Once, there is some update/ETA for you, we will let you know asap.

6

Hi Guys,
I have exactly the same question.
Is there way to uncheck the checkbox (marked as red square) via Aspose.Cell for .Net? The purpose is to hide specific legend of the chart.
image.png (55.2 KB)

image.png (3.3 KB)

The chart.Legend.LegendEntries.IsDeleted seems to work but the series still appear like checked in the Data source. So it is not what we want. Legend is deleted but you still have the series and no way in excel to make it reappear again manually. (or I don’t know)
So the best would be to have a visible new property. (which would be the same behavior than in excel).

Any ETA?
Thanks,

Thanks for your help

7

@hormancey,

I think you may remove your unwanted series via SeriesCollection.RemoveAt() method.
e.g.,
Sample code:

Worksheet worksheet = workbook.Worksheets[0];
//Get the first chart
Chart chart = worksheet.Charts[0];
//Remove the second series
chart.NSeries.RemoveAt(1);
....

Hope, this helps a bit.

8

Thanks @amjad.sahi !
But it does not help.
RemoveAt removes totally the series so if we want to restore it again, we need to reference the data range again.
It is not what we want. We want to hide a serie. (like the check/uncheck feature in excel data source dialog). Like that we can display it again very easily if necessary.

Thanks for your help,

9

@hormancey,

We are sorry if removing data series won’t work for your scenario/case.

We have supported the feature in Aspose.Cells APIs. Please see the following sample code for your reference and try it with the attached file. The input Excel file has some data series visible and others invisible to demonstrate the task.
e.g.,
Sample code:

//Implement visible series invisible and invisible series visible using Aspose.Cells
string filePath = "e:\\test2\\";
Workbook workbook = new Workbook(filePath + "SeriesFiltered.xlsx");
ChartCollection charts = workbook.Worksheets[0].Charts;
Chart chart = charts["Chart 1"];
SeriesCollection nSeriesFiltered = chart.FilteredNSeries;//hidden series
SeriesCollection nSeries = chart.NSeries;//visible series
nSeries[1].IsFiltered = true;//Make second (visible) series invisible
nSeries[0].IsFiltered = true;//Make first (visible) series invisible

nSeriesFiltered[3].IsFiltered = false;//Make fourth (invisible) series visible


workbook.Save(filePath + "output12.xlsx");

Please find attached the input and output Excel files.
files1.zip (23.9 KB)

Also, see the document with example code for your reference.
https://docs.aspose.com/cells/net/how-to-set-series-invisible/

Hope, this helps a bit.

10

Hi @amjad.sahi ,
Sorry for the delay!
Thanks a lot. It is doing the job!!
Have a good wk!

11

@hormancey,

It is good to hear that the proposed code segment meets your requirements. Please don’t hesitate to reach out if you have any additional questions or feedback.

12

Hi @amjad.sahi ,
It is intended than when we set the IsFiltered as true the number of series decrease ?
For example, if we have 3 series and hide 2 of them, chart.NSeries.Count will be 1 and not 3.
And chart.NSeries[1] and chart.NSeries[2] (the series hidden) will raise an exception because they do not exist anymore.
How to restore a serie hidden via code ?
Thanks,
Hadrien

13

@hormancey,

Chart.FilteredNSeries will give you hidden series collection. So, you may use Boolean Series.IsFiltered attribute to set it to false to unhide specific series for your requirements. See the following sample code segment for your reference.
e.g.,
Sample code:

......
SeriesCollection nSeriesFiltered = chart.FilteredNSeries;//Get hidden series collection
nSeriesFiltered[1].IsFiltered = false;//Make second (invisible) series visible
nSeriesFiltered[2].IsFiltered = false;//Make third (invisible) series visible

Hope, this helps a bit.