Removing a single series in a chart

[Mark Oudesluys]

Hi,

I am opening and attaching worksheets into a workbook to create a custom report. All my excel templates have charts setup on them, waiting for the data to be entered. The series are setup already, so all I need to do is fill the required data and remove any series which don't have any data in them.

But I can't seem to find a command which will remove a specific series. I don't want to remove all the series, only the last x, where x is the number of series minus the number of data sets in the report.

If I just try to remove the workbook rows which feed the chart series I get the following error:
****************************************************************
System.IndexOutOfRangeException - Index was outside the bounds of the array.

StackTrace:
at Syncfusion.XlsIO.Implementation.Charts.ChartSerieImpl.GetRange(ChartAIRecord chartAi)
at Syncfusion.XlsIO.Implementation.Charts.ChartSerieImpl.UpdateRange(ChartAIRecord chartAI, Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.Charts.ChartSerieImpl.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.Charts.ChartSeriesCollection.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.Charts.ChartImpl.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.Shapes.ChartShapeImpl.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.Collections.ShapesCollection.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.WorksheetBaseImpl.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.UpdateFormula(Int32 iCurIndex, Int32 iSourceIndex, Rectangle sourceRect, Int32 iDestIndex, Rectangle destRect)
at Syncfusion.XlsIO.Implementation.WorkbookImpl.UpdateFormula(Int32 iSourceIndex, Rectangle rectSource, Int32 iDestIndex, Rectangle rectDest)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.MoveRange(IRange destination, IRange source, ExcelCopyRangeOptions options, Boolean bUpdateRowRecords, IOperation beforeMove)
at Syncfusion.XlsIO.Implementation.WorksheetImpl.DeleteRow(Int32 index, Int32 count)
at GMRPortfolioAnalysis.Common.ReportGen.ProcessQuartiles(IWorksheet wsPage, String sQuartileType, String sDate, DataRow[] drPorts, DataRow drUniv) in E:\Visual Studio 2008\Projects\GMRPortfolioAnalysis\GMRPortfolioAnalysis.Common\ReportGen.cs:line 376
at GMRPortfolioAnalysis.Common.ReportGen.PopulateReports(DataRow drProd, DateTime asatDate, Boolean frenchRpt, Boolean finalReport) in E:\Visual Studio 2008\Projects\GMRPortfolioAnalysis\GMRPortfolioAnalysis.Common\ReportGen.cs:line 249
****************************************************************

It looks to me that it doesn't like that I am trying to remove rows from the workbook which feed the data for some of the series.

Is there a way of removing the series before I delete the workbook rows? Will that resolve this error? Is there another approach to this?

Thanks,
Mark

[Mark Oudesluys]

Hi,

Further to my previous post, I figured out how to remove individual series, using the following code:
for (int i = 19; i >= (11 + rowCnt); i--)
{
IChartSerie delSeries = wsPage.Charts[0].Series[i];
wsPage.Charts[0].Series.Remove(delSeries);
}
But when I try to delete the rows that used to feed these series, I still get the same error as before.

Do I need to do a recalc or a refresh of some kind on the chart so it knows they have been deleted before I try to delete the feeder rows?

Any help would be appreciated.

Thanks,
Mark

[Sridhar]

Hi Mark,

Thank you very much for the update.

We have created the sample at our side for removing the series from the chart.
Plese try the attached sample at your side nad let us know if these helps you.

And we found the usage issue at your side that you are using the Chart Collection index of the worksheet for removing the series.
But we remove the series for the single chart object not for an chart collection.

Use the following code.

//The first worksheet object in the worksheets collection is accessed.

IWorksheet sheet = workbook.Worksheets[1];

//Getting the chart colleciton
ICharts charts = workbook.Charts;
//Getting the first chart object
IChart chart = charts[0];

//Getting the chart series collection
IChartSeries Series = chart.Series;

//Get the 3rd sereis from the collection
IChartSerie serie3 = Series[2];

//Removing the 3rd series
chart.Series.Remove(serie3);


Note:
The removal of data rows for the particular data series does not included in the other series of the chart. Then you can remove the data rows. If you remove the data source which included to the other chart series then it will affect other series drawing posiong, references to the chart.


Please let us know if you require any further clarifications.

Thanks,
Sridhar.S




ChartTemplate_8e0c85b1.zip