This may be a common question regarding Aspose.Slides. I would like to add an editable chart to PowerPoint in which I am able to change the styles of the chart using the styling options that are available in PowerPoint. I do not want to embed an Excel chart as an OLE Object in the PowerPoint slide because I will not be able to change the styles of the chart using PowerPoint. Please let me know if this can be done in Aspose Slides.
This message was posted using Aspose.Live 2 Forum
Dear Wil,
Thanks for considering Aspose.Slides.
I regret to inform you that editable charts/graphs are not supported in Aspose.Slides. The only possible way available is to add the charts as Ole Objects from excel sheets using Aspose.Cell.
We are sorry for your inconvenience,
Aspose Team,
I was working with a PowerPoint presentation where I used Aspose to embed an Excel chart as an OLE Object, and found out that I can manually change the chart styles using PowerPoint. To do so, I had to:
1. Right click chart and select Worksheet Object > Edit
2. Click on Excel chart that is embedded in PowerPoint. Notice that "Design" option appears in the toolbar
3. Select "Design" and “chart styles” options will appear
4. Select a style and PowerPoint will apply the style to the chart
My question is, "Is there a way to do this programmitically in Aspose"? Why wouldn't there be?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />One more important item to note: the "chart styles" options and steps are not specific to PowerPoint. The exact same thing can be done in Excel (no PowerPoint required). I am using Office 2007
Dear Wil,
I regret to share that Aspose.Slides for.NET does not support editing chart styles in embedded Ole Object frame. There is nothing much one can do with Ole Frame inside presentation using Aspose.Slides. Regarding chart styles query, I feel this is perhaps more related to our other product Aspose.Cell and I will request them to share some more detailed techincal insight about the mentioned issue with you.
Thanks and Regards,
Hi,
Well, Aspose.Cells is a library (that does not support interface or events), it can manipulate existing charts (see the topic: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/manipulating-designer-charts.html , http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-charts-data.html , http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-charts-appearance.html) and set formatting (Background/Foreground, line/border colors) to data series or data points etc. But again it does not support events, so how could you perform your four steps while your chart is embedded in the power point as an ole object. When you simply create or manipulate chart using Aspose.Cells API and then add it to power point slide as an ole object using Aspose.Slides API, now how could we get that chart to perform your design options.
If you want to directly set formatting (apply color to the data series/data points, etc.) and save the file with the updation, you can do it using Aspose.Cells API, but once the chart is created and embedded as an ole object in power point, I think we don’t have control over it.
Thanks for your understanding!
Yes, I assumed that this would be the case. But what about this scenario (this is for Aspose.Cells only). For Microsoft Excel only (no PowerPoint and Aspose.Slides is not involved in the questions below), I can manually do the following:
1. Based on sample data, create a column chart by going to Insert > Column > 2-D Column > select "Cluserted Column"
2. After chart appears in Excel, the "Design" option is available in the toolbar. I select "Design" and I can then select various options for "Chart Styles"
3. I apply a chart style and the style of my column chart changes.
My question is, "How can you do this programmatically in Aspose.Cells"?
If this cannot be done, can Apose.Cells do the following:
1. In the column chart, select one data point
2. Right click on the data point, and the option "Format Data Point" appears
3. Under "Format Data Point" select the option "3-D format"
4. Programmatically change the following:
A. Bevel (Top/Bottom, Width, Height)
B. Surface (Material, Lighting, Angle)
This is all native to MS Excel 2007.
Hi,
wilburw:
If this cannot be done, can Apose.Cells do the following:
1. In the column chart, select one data point
2. Right click on the data point, and the option "Format Data Point" appears
3. Under "Format Data Point" select the option "3-D format"
4. Programmatically change the following:
A. Bevel (Top/Bottom, Width, Height)
B. Surface (Material, Lighting, Angle)
Yes, you may implement the 3D effects/formats for charts programmatically using Aspose.Cells for .NET APIs, attached is the template file. I have performed some 3D formatting on the template chart for your requirements, see the sample code below for your reference:
string inFn = "e:\\test\\src.xlsx";
Workbook book = new Workbook(inFn);
Chart chart = book.Worksheets[0].Charts[0];
Series ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.RelaxedInset;
bevel.Height = 10;
bevel.Width = 10;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.Balanced;
fmt3d.LightingAngle = 70;
book.Save("e:\\test\\myoutput.xlsx");
Thank you.
Thanks for your response. So based on your response should I assume that a "template" style cannot be applied by Aspose.Cells?
Lastly, I tried your example and it works great, but when I apply the same thing to my code which embeds an Excel chart into PowerPoint, the styles are not changing. What could I be missing?
Aspose.Cells.Charts.Chart crt = wb.Worksheets[chartSheetIdx].Charts[0];
Aspose.Cells.Charts.ASeries ser = crt.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
Hi,
Kindly post your input and output Excel files generated by Aspose.Cells here, we will check your issue soon.
Thank you.
I do not have an input file. Attached is my output file. I am trying to add 3D formatting to this chart.
Hi,
Which version you are using? I tried with the attached latest version v5.1.3.3, it works fine. Here is my sample code (I used your file as template file to get source data for the chart) and attached is the generated file.
Sample code:
Workbook wb = new Workbook(“e:\test\myoutputtest.xlsx”);
Worksheet sheet = wb.Worksheets.Add(“MyChart”);
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart crt = charts[0];
crt.NSeries.Add(“DataSheet!B1:B10”, true);
crt.NSeries.CategoryData = “DataSheet!A1:A10”;
Aspose.Cells.Charts.Series ser = crt.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
wb.Save(“e:\test\myoutputChrt.xlsx”);
Please try the attached version, if you still find the issue, kindly do post your complete sample code (similar to mine) with generated file here, we will check your issue soon.
Note: I have moved this thread to Aspose.Cells forum now for our ease.
Thank you.
I am using slides version 4.1.1.0 and cells version 5.0.0.0. Below is my sample code:
string sFilePath = "c:\\test\\myoutput.ppt";
Presentation pres = new Presentation();
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets.Add("DataSheet");
Worksheet sheet = book.Worksheets.Add("MyChart");
dataSheet.Cells["B1"].PutValue(1);
dataSheet.Cells["B2"].PutValue(2);
dataSheet.Cells["B3"].PutValue(3);
dataSheet.Cells["A1"].PutValue("A");
dataSheet.Cells["A2"].PutValue("B");
dataSheet.Cells["A3"].PutValue("C");
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart chart = book.Worksheets[2].Charts[0];
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ValueAxis.MajorGridLines.Color = Color.Silver;
chart.ValueAxis.AxisLine.Color = Color.Silver;
chart.CategoryAxis.AxisLine.Color = Color.Silver;
chart.ShowLegend = false;
chart.NSeries.Add("DataSheet!B1:B3", true);
chart.NSeries.CategoryData = "DataSheet!A1:A3";
Aspose.Cells.Charts.ASeries ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
for (int x = 0; x < chart.NSeries[0].Points.Count; x++)
{
chart.NSeries[0].Points[x].Area.BackgroundColor = Color.Blue;
chart.NSeries[0].Points[x].Area.ForegroundColor = Color.Blue;
chart.NSeries[0].Points[x].Border.Color = Color.Blue;
}
book.Worksheets.SetOleSize(0, 3, 0, 1);
//Step - 3: Get the image of the chart with Aspose.Cells
//-----------------------------------------------------------
Bitmap imgChart = book.Worksheets[2].Charts[0].ToImage();
//Save the workbook to stream
MemoryStream wbStream = book.SaveToStream();
Slide sld = pres.GetSlideByPosition(1);
//Add the workbook on slide
AddExcelChartInPresentation(pres, sld, wbStream, imgChart);
// End response to avoid unneeded html after xls
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Buffer = true;
response.ContentType = "application/vnd.ms-powerpoint";
response.AppendHeader("Content-Disposition", "attachment; filename=myoutput.ppt");
pres.Write(response.OutputStream);
response.Flush();
System.IO.File.Delete(sFilePath);
response.End();
static void AddExcelChartInPresentation(Presentation pres, Slide sld, Stream wbStream, Bitmap imgChart)
{
Aspose.Slides.License lic = new Aspose.Slides.License();
lic.SetLicense(HttpContext.Current.Server.MapPath("/Aspose.Total.lic"));
Aspose.Slides.Picture pic = new Aspose.Slides.Picture(pres, imgChart);
int picId = pres.Pictures.Add(pic);
int slideWidth = pres.SlideSize.Width;
int slideHeight = pres.SlideSize.Height;
int x = 0;
byte[] chartOleData = new byte[wbStream.Length];
wbStream.Position = 0;
wbStream.Read(chartOleData, 0, chartOleData.Length);
OleObjectFrame oof = sld.Shapes.AddOleObjectFrame(x, 0, slideWidth, slideHeight, "Excel.Sheet.8", chartOleData);
oof.PictureId = picId;
}
Hi,
1) We have found an issue regarding the 3D Formattings when customizing the series/points colors and saving the charts in xlsx file format. I have logged this issue into our issue tracking system with an id: CELLSNET-20705. We will figure it out soon.
2) I am afraid, in Chart to Image feature, we don’t support to render 3D effects/formattings at the moment, I have logged this feature into our issue tracking system with an id: CELLSNET-20706. We may look int it in future versions.
For your case for the time being, please skip to customize the colors for series/data points, it would work fine for first issue, here is the complete updated sample code for your reference:
Sample code:
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets.Add(“DataSheet”);
Worksheet sheet = book.Worksheets.Add(“MyChart”);
dataSheet.Cells[“B1”].PutValue(1);
dataSheet.Cells[“B2”].PutValue(2);
dataSheet.Cells[“B3”].PutValue(3);
dataSheet.Cells[“A1”].PutValue(“A”);
dataSheet.Cells[“A2”].PutValue(“B”);
dataSheet.Cells[“A3”].PutValue(“C”);
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart chart = book.Worksheets[2].Charts[0];
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ValueAxis.MajorGridLines.Color = Color.Silver;
chart.ValueAxis.AxisLine.Color = Color.Silver;
chart.CategoryAxis.AxisLine.Color = Color.Silver;
chart.ShowLegend = false;
chart.NSeries.Add(“DataSheet!B1:B3”, true);
chart.NSeries.CategoryData = “DataSheet!A1:A3”;
Aspose.Cells.Charts.Series ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
book.Worksheets.SetOleSize(0, 3, 0, 1);
Bitmap imgChart = book.Worksheets[2].Charts[0].ToImage();
//Save the workbook to stream
MemoryStream wbStream = new MemoryStream();
book.Save(wbStream, SaveFormat.Xlsx);
wbStream.Seek(0, SeekOrigin.Begin);
PresentationEx pres = new PresentationEx();
SlideEx sld = pres.Slides[0];
ImageEx pic = pres.Images.AddImage(imgChart);
int x = 0;
byte[] chartOleData = new byte[wbStream.Length];
wbStream.Position = 0;
wbStream.Read(chartOleData, 0, chartOleData.Length);
OleObjectFrameEx oof = null;
oof = sld.Shapes.AddOleObjectFrame(x, 0, pres.SlideSize.Size.Width, pres.SlideSize.Size.Height, “Excel.Sheet.12”, chartOleData);
oof.Image = pres.Images.AddImage((System.Drawing.Image)imgChart);
pres.Write(“e:\test\output.pptx”);
Thank you.
We would like to build a new feature that will utilize this capability. All of the requirements, including the ability to specify data point colors, will be necessary to allow “presentation ready” reports. It seems that the ability to do these things should already be available in Aspose Cells and Slides, and not an enhancement request. With that being said, is there a way to notify me when this becomes available?
Hi,
We will surely notify you here in this thread when we figure your issue(s) out.
Thank you.
Hi Wil,
If you want to set the 3D chart color, following code will help you.
Code snippet:
string outfn = path + "chart_out.xlsx";
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets.Add("DataSheet");
Worksheet sheet = book.Worksheets.Add("MyChart");
dataSheet.Cells["B1"].PutValue(1);
dataSheet.Cells["B2"].PutValue(2);
dataSheet.Cells["B3"].PutValue(3);
dataSheet.Cells["A1"].PutValue("A");
dataSheet.Cells["A2"].PutValue("B");
dataSheet.Cells["A3"].PutValue("C");
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart chart = book.Worksheets[2].Charts[0];
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ValueAxis.MajorGridLines.Color = Color.Silver;
chart.ValueAxis.AxisLine.Color = Color.Silver;
chart.CategoryAxis.AxisLine.Color = Color.Silver;
chart.ShowLegend = false;
chart.NSeries.Add("DataSheet!B1:B3", true);
chart.NSeries.CategoryData = "DataSheet!A1:A3";
Aspose.Cells.Charts.Series ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
//for (int x = 0; x < chart.NSeries[0].Points.Count; x++)
//{
// chart.NSeries[0].Points[x].Area.BackgroundColor = Color.Blue;
// chart.NSeries[0].Points[x].Area.ForegroundColor = Color.Blue;
// chart.NSeries[0].Points[x].Border.Color = Color.Blue;
//}
spPr.Area.BackgroundColor = Color.Blue;
spPr.Area.ForegroundColor = Color.Blue;
spPr.Line.Color = Color.Blue;
book.Save(outfn, SaveFormat.Xlsx);
Thanks,
Hi,
There is a slight error in the sample, please see the updated sample code (see the bold lines) for your requirement, it works fine for your need.
Code snippet:
string outfn = path + “chart_out.xlsx”;
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets.Add(“DataSheet”);
Worksheet sheet = book.Worksheets.Add(“MyChart”);
dataSheet.Cells[“B1”].PutValue(1);
dataSheet.Cells[“B2”].PutValue(2);
dataSheet.Cells[“B3”].PutValue(3);
dataSheet.Cells[“A1”].PutValue(“A”);
dataSheet.Cells[“A2”].PutValue(“B”);
dataSheet.Cells[“A3”].PutValue(“C”);
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart chart = book.Worksheets[2].Charts[0];
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ValueAxis.MajorGridLines.Color = Color.Silver;
chart.ValueAxis.AxisLine.Color = Color.Silver;
chart.CategoryAxis.AxisLine.Color = Color.Silver;
chart.ShowLegend = false;
chart.NSeries.Add(“DataSheet!B1:B3”, true);
chart.NSeries.CategoryData = “DataSheet!A1:A3”;
Aspose.Cells.Charts.Series ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
//for (int x = 0; x < chart.NSeries[0].Points.Count; x++)
//{
// chart.NSeries[0].Points[x].Area.BackgroundColor = Color.Blue;
// chart.NSeries[0].Points[x].Area.ForegroundColor = Color.Blue;
// chart.NSeries[0].Points[x].Border.Color = Color.Blue;
//}
ser.Area.BackgroundColor = Color.Blue;
ser.Area.ForegroundColor = Color.Blue;
ser.Line.Color = Color.Blue;
book.Save(outfn, SaveFormat.Xlsx);
Thank you.
Hi,
Also, currently, customizing data point colors (for the series) won’t copy the 3D effects. So, you need to use Series.Area.BackgroundColor/ForegroundColor attributes (as suggested in my updated code segment).
We will soon figure this issue out soon.
Thank you.
Thank you for getting back to me. I appreciate the extra effort in finding a resolution to this matter. However, my issues are the following:
1. 3D effects are not supported when embedding an Excel chart in PowerPoint. This is a very serious limitation because 3D effects add a lot of visual appeal for "presentation ready" reports. It sounds as though this will not be part of the product anytime soon.
2. Customizing datapoint colors as well as 3D effects, which will be resolved sometime soon.
I really need both to work for me to continue further, so for now I will have to settle with not having 3D effects.
Hi,
1) Well, as I told you earlier, only chart to image feature does not support Excel 2007 3D effects at the moment (we will support it later), but, Excel chart with 3D effects are rendered absolutely fine when embedding it into PowerPoint slide using Aspose.Cells and Aspose.Slides products, here, I will paste my complete code that works fine.
Sample code:
Workbook book = new Workbook();
Worksheet dataSheet = book.Worksheets.Add(“DataSheet”);
Worksheet sheet = book.Worksheets.Add(“MyChart”);
dataSheet.Cells[“B1”].PutValue(1);
dataSheet.Cells[“B2”].PutValue(2);
dataSheet.Cells[“B3”].PutValue(3);
dataSheet.Cells[“A1”].PutValue(“A”);
dataSheet.Cells[“A2”].PutValue(“B”);
dataSheet.Cells[“A3”].PutValue(“C”);
ChartCollection charts = sheet.Charts;
int chartSheetIdx = charts.Add(ChartType.Column, 5, 0, 25, 15);
Aspose.Cells.Charts.Chart chart = book.Worksheets[2].Charts[0];
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.White;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ValueAxis.MajorGridLines.Color = Color.Silver;
chart.ValueAxis.AxisLine.Color = Color.Silver;
chart.CategoryAxis.AxisLine.Color = Color.Silver;
chart.ShowLegend = false;
chart.NSeries.Add(“DataSheet!B1:B3”, true);
chart.NSeries.CategoryData = “DataSheet!A1:A3”;
Aspose.Cells.Charts.Series ser = chart.NSeries[0];
ShapeProperties spPr = ser.ShapeProperties;
Format3D fmt3d = spPr.Format3D;
Bevel bevel = fmt3d.TopBevel;
bevel.Type = BevelPresetType.Circle;
bevel.Height = 2;
bevel.Width = 5;
fmt3d.SurfaceMaterialType = PresetMaterialType.WarmMatte;
fmt3d.SurfaceLightingType = LightRigType.ThreePoint;
fmt3d.LightingAngle = 20;
ser.Area.BackgroundColor = Color.Blue;
ser.Area.ForegroundColor = Color.Blue;
ser.Line.Color = Color.Blue;
book.Worksheets.SetOleSize(0, 3, 0, 1);
Bitmap imgChart = book.Worksheets[2].Charts[0].ToImage();
//Save the workbook to stream
MemoryStream wbStream = new MemoryStream();
book.Save(wbStream, SaveFormat.Xlsx);
wbStream.Seek(0, SeekOrigin.Begin);
PresentationEx pres = new PresentationEx();
SlideEx sld = pres.Slides[0];
ImageEx pic = pres.Images.AddImage(imgChart);
int x = 0;
byte[] chartOleData = new byte[wbStream.Length];
wbStream.Position = 0;
wbStream.Read(chartOleData, 0, chartOleData.Length);
OleObjectFrameEx oof = null;
oof = sld.Shapes.AddOleObjectFrame(x, 0, pres.SlideSize.Size.Width,
pres.SlideSize.Size.Height, “Excel.Sheet.12”, chartOleData);
oof.Image = pres.Images.AddImage((System.Drawing.Image)imgChart);
pres.Write(“e:\test\output.pptx”);
Please try my above code, it will work fine except for the image issue (that does not render 3D effects), but when you double click on the chart image, it will navigate to the MS Excel chart that has 3d effects according to the code.
2) We will fix this issue soon.
Thank you.