List of questions - regarding charts!

May 20, 2010 at 8:44 AM
Edited May 20, 2010 at 12:11 PM

Hi Jan

Also, sorry for the list of questions. Here are they

1. Is it possible to specify custom colors for an entire series of a chart like series.backcolor = color.red...

2. Is it possible to specify colors for individual datapoints

3. I declare a linechart as var lineChart = (worksheet.Drawings.AddChart("Chart1", eChartType.LineMarkers) as ExcelChart);

I am not able to set the datalabels visible.

Whereas I could do that for a barchart like

var barChart = (worksheet.Drawings.AddChart("Chart1", eChartType.BarClustered) as ExcelBarChart
barChart.DataLabel.ShowValue = true;

How do I access the datalabel for linecharts and columnclustered charts?

4. Is it possible to alter or choose the marker for line charts?

Best regards
Kit

 

Coordinator
May 20, 2010 at 2:34 PM

Hi again,

1. No

2. No

3. No, I really should add a separate ExcelLineChart class here, but there is non today. The ColumnClustered is an ExcelBarChart.

4. No, again I should add a a separate class here.

Alot of no here, but there are tons of properties on the different charts and I have simplified a little here. if its possible for you, use a template and set the properties you want.

If you want to struggle with the XML you can always use the ChartXML property to alter the the chart-Xml the way you want.

Jan

May 20, 2010 at 6:02 PM

Alright. thanks for the reply Jan. May I ask you whether you already have any of the above points in mind for next release?

Coordinator
May 21, 2010 at 12:53 PM

I added the ExcelLineChart-class to the source this morning so that will be included, you can check it out if you want to.

I only added a Datalabel property to it, so it's still some way to go.

Im not going to add any of the other points to the next release (I hope to do one in the coming weeks).

I'll put it on my TODO list for a future release

 

If you want to, have a look at the ExcelLinechart class and add the properties you need yourself (it would be great with some help :) ). 

It should not be that hard if you are familiar with Xml and XPath. Have a look at the chart-Xml files by extracting your formated xlsx file (just rename it *.zip).

 

May 24, 2010 at 8:44 PM

Hi Jan

Will check out the ExcelLinechart class first thing tomorrow morning. If I managed to include additional properties,  I will update you :)

I need your guidance on another thing. My scenario is that I have to create a chart in Excel and export it to powerpoint (both the chart and the data). The powerpoint export works perfect incase if I create the excel chart manually in Excel 2007. When I try to export the chart created using EPPlus, I face a strange situation

1. First of all the powerpoint slide appears blank. When I click on the supposed area of chart location I could see the outline box or shape of the chart box.

2. when I double click on the empty box, the corresponding datasheet opens and now I can see the chart picture in the powerpoint too. (Do you get it? At the first instance the chart is not seen. when I double click on the chartpart (guessing the location) the excel sheet opens and the chart pic is seen)

3. Intrestingly, If I open the excelsheet created by EPPlus in Excel2007 and save it without making anychanges, the export works perfectly.

4. I compared the excelsheet from EPPlus before(say EPPlus.xlsx) and after opening/saving via Excel2007(say copy.xlsx) and found that the cache details in chart.xml is missing in the EPPlus.xlsx. When I edited the chart.xml and copied in the cache data ( taken from the copy.xlsx) the export worked the way I expected.

5. The question is that Is there a simple / direct way to add the cache data to the chart.xml???? or should I have to edit (or rather struggle) ChartXML and include the cache from the code for each series and each datapoint????????????

cheers
Kit

Coordinator
May 25, 2010 at 6:41 PM

Hi Kit

There is no simple way to add the cache data for the chart. The main reason the chart cache is not updated(apart from that Excel do it for you) is because EPPlus relays on Excel to calculate all forumla values. So if a chart-serie has an underlaying formula , the formula values are calculated by Excel. There is no calculation engine in the component.

I guess you can update the chart.xml the way you descibe or if you can force powerpoint to recalculate the cache in some way.

Cheers Jan

May 25, 2010 at 7:24 PM
Edited May 25, 2010 at 7:37 PM

Hi Jan

Good, I will chk that out.

Meanwhile I have figured out how to set the "Vary colors by point". Heres the corresponding code. May be useful to somebodyelse

 XmlElement varyColors = barChart.ChartXml.CreateElement("c", "varyColors", http://schemas.openxmlformats.org/drawingml/2006/chart);
varyColors.SetAttribute("val", "1"); 
 
XmlNamespaceManager nsmgr;

nsmgr =
new XmlNamespaceManager(barChart.ChartXml.NameTable);

nsmgr.AddNamespace(
"c", http://schemas.openxmlformats.org/drawingml/2006/chart);
 
 
XmlNode barC = barChart.ChartXml.SelectSingleNode("//c:barChart", nsmgr);

barC.PrependChild(varyColors);

Kit