This project has moved. For the latest updates, please go here.

Chart Source Data

Oct 30, 2011 at 7:54 PM
Edited Oct 31, 2011 at 10:30 AM

Hi,

Firstly let me say great work on the library, it's really cool.

I'm trying to figure out if Ep-plus can be used to achieve the following - my gut is telling me no but hopefully I'm wrong. 

I have an Excel template which has a chart in it. I'm using Epplus to insert the data for the chart into a particular tab. I need to ideally change the source data address for the chart to update it to the new range for the data i.e. A1:B16 (if there were 16 data rows of data on this particular export).

The number of data rows will be unpredictable as it's the result of what filter criteria are passed to a query that gets the data.

Many Thanks

- Paul

Oct 31, 2011 at 3:58 PM
Edited Oct 31, 2011 at 3:59 PM

For a pie chart, the solution was to pass the new range into the ExcelChart.Series[0].Series field the new range as a string "Sheet1!$A$1:$A$6".

However, to update the category labels you also need to pass in the new range for these into the XSeries field i.e. ExcelChart.Series[0].XSeries = "Sheet1!$B$1:$B$6".

There is a bug in the source code however that if you pass a string into the XSeries property it malforms the XML. This bug is alluded to in this post:

http://epplus.codeplex.com/workitem/13128

The above fix would have to be applied to fix the problem.

 

Coordinator
Nov 3, 2011 at 4:50 PM

Ok, thanks, I'll have a look at it.

Jan

May 18, 2012 at 9:02 AM
Edited May 18, 2012 at 9:10 AM
JanKallman wrote:

Ok, thanks, I'll have a look at it.

Jan


Hi,

I was also trying somthing of same kind.

I have a spreadsheet with 2 sheets of data table, 4-5 sheets of different types of chart template sheet

I have faced 2 problem.

1. When simpling following code:

using (ExcelPackage exl = new ExcelPackage(file))
           {
               try
               {
                   exl.SaveAs(outfile);
               }
               catch (Exception ex)
               { 
               }
           }

It's giving me error as "{"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"}"

Is there somthing wrong with above code.

 2. I removed some of the Chart sheets then tried same thing now there is no error. But next step is I need to get the chart part from those chart sheets and add reng data from other sheets.For this Can any one let me know how to get the ChartDrawing part from a sheet.

  using (ExcelPackage exl = new ExcelPackage(file))
           {
               try
               {
                   var sheet = exl.Workbook.Worksheets.FirstOrDefault(sh => sh.Name == "ColumnChart");
                   var chart = sheet.Drawings.  //Stuck here on how to get chart part from sheet.
                   exl.SaveAs(outfile);
               }
               catch (Exception ex)
               { 
               }
           }