This project has moved and is read-only. For the latest updates, please go here.

Problem with adding series in already existing PieChart...

Jun 21, 2012 at 8:22 AM

I want to add data nodes to an existing pie chart template. For this I am using following code.

static void Main(string[] args)
        {
            String exlFile = @"Input\SpreadsheetTmpl.xlsx";
            FileInfo fn = new FileInfo(exlFile);

            using (ExcelPackage exl = new ExcelPackage(fn))
            {
                var datasheet = exl.Workbook.Worksheets.FirstOrDefault(sh => sh.Name == "SalesData");
                var table = datasheet.Tables.First();
                int datarows = table.Address.End.Row;

                var sh1 = exl.Workbook.Worksheets.FirstOrDefault(sh => sh.Name == "PieChart");
                if (sh1 != null)
                {
                    var chart = (ExcelChart)sh1.Drawings.FirstOrDefault(c => c.Name.Contains("Chart"));
                    if (chart != null)
                    {
                        
                        for (int colCount = 1; colCount < table.Columns.Count; colCount++)
                        {
                            String cellText = (colCount + 1).NumberToText();
                            String ySeriesRange = "SalesData!$" + cellText + "$2:$" + cellText + "$" + (datarows + 1);
                            String xSeriesRange = "SalesData!$A$2:$A$" + (datarows + 1);
                            var series = chart.Series.Add(ySeriesRange, xSeriesRange);
                            series.Header = table.Columns[colCount].Name;
                        }
                    }
                }
                exl.SaveAs(new FileInfo("SpreadsheetTmpl.xlsx"));
            }
        }

 

But at code line

var chart = (ExcelChart)sh1.Drawings.FirstOrDefault(c => c.Name.Contains("Chart"));
it's giving error as:

Index was out of range. Must be non-negative and less than the size of the collection.
And for any other type of charts (e.g. column chart, line chart) it's working file..
Can any one help me.. What's going wrong in above code...


Note: I have attached my sample input file..
Pranay

Jun 23, 2012 at 1:09 PM

I have assigned this issue to myself.  Will hope to have a look over the weekend.