We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Creating Excel chart and embedding them as OLE Object inside a Presentation

Here is a complete code example that illustrates how to<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

1- Create an excel chart using Aspose.Cells.

2- Set the OLE size of the chart. using Aspose.Cells

3- Get the image of the chart with Aspose.Cells

4- Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides.

5- Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue

6- Write the output presentation on disk

I have attached the VS2005 solution/project and the output.ppt generated by the code.

Note: The attached output.ppt has been generated after setting Aspose.Cells license but in the attached solution, I have commented the code, so you will get a little different output.ppt. The difference is that when you will double click the chart in yours, it will show you Evaluation Sheet instead of chart sheet.

C#

-------------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.Text;

using System.Drawing;

using System.IO;

using Aspose.Slides;

using Aspose.Cells;

namespace ExcelChartAsOleObject

{

class ExcelChart

{

public static void Run()

{

//Uncomment it to avoid Evaluation Sheet

//Aspose.Cells.License lic = new Aspose.Cells.License();

//lic.SetLicense(@"D:\Shakeel Projects\Aspose\License\new2\Aspose.Total.lic");

//Step - 1: Create an excel chart using Aspose.Cells

//--------------------------------------------------

//Create a workbook

Workbook wb = new Workbook();

//Add an excel chart

int chartRows = 55;

int chartCols = 25;

int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);

//Step - 2: Set the OLE size of the chart. using Aspose.Cells

//-----------------------------------------------------------

wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols);

//Step - 3: Get the image of the chart with Aspose.Cells

//-----------------------------------------------------------

Bitmap imgChart = wb.Worksheets[chartSheetIndex].Charts[0].ToImage();

//Save the workbook to stream

MemoryStream wbStream = wb.SaveToStream();

//Step - 4 AND 5

//-----------------------------------------------------------

//Step - 4: Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides

//-----------------------------------------------------------

//Step - 5: Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue

//-----------------------------------------------------------

//Create a presentation

Presentation pres = new Presentation();

Slide sld = pres.GetSlideByPosition(1);

//Add the workbook on slide

AddExcelChartInPresentation(pres, sld, wbStream, imgChart);

//Step - 6: Write the output presentation on disk

//-----------------------------------------------------------

pres.Write("c:\\output.ppt");

}

static int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)

{

//Array of cell names

string[] cellsName = new string[]

{

"A1", "A2", "A3", "A4",

"B1", "B2", "B3", "B4",

"C1", "C2", "C3", "C4",

"D1", "D2", "D3", "D4",

"E1", "E2", "E3", "E4"

};

//Array of cell data

int[] cellsValue = new int[]

{

67,86,68,91,

44,64,89,48,

46,97,78,60,

43,29,69,26,

24,40,38,25

};

//Add a new worksheet to populate cells with data

int dataSheetIdx = wb.Worksheets.Add();

Worksheet dataSheet = wb.Worksheets[dataSheetIdx];

string sheetName = "DataSheet";

dataSheet.Name = sheetName;

//Populate DataSheet with data

for (int i = 0; i < cellsName.Length; i++)

{

string cellName = cellsName[i];

int cellValue = cellsValue[i];

dataSheet.Cells[cellName].PutValue(cellValue);

}

//Add a chart sheet

int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);

Worksheet chartSheet = wb.Worksheets[chartSheetIdx];

chartSheet.Name = "ChartSheet";

//Add a chart in ChartSheet with data series from DataSheet

int chartIdx = chartSheet.Charts.Add(ChartType.Column, 0, chartRows, 0, chartCols);

Chart chart = chartSheet.Charts[chartIdx];

chart.NSeries.Add(sheetName + "!A1:E1", false);

chart.NSeries.Add(sheetName + "!A2:E2", false);

chart.NSeries.Add(sheetName + "!A3:E3", false);

chart.NSeries.Add(sheetName + "!A4:E4", false);

//Set ChartSheet an active sheet

wb.Worksheets.ActiveSheetIndex = chartSheetIdx;

return chartSheetIdx;

}

static void AddExcelChartInPresentation(Presentation pres, Slide sld, Stream wbStream, Bitmap imgChart)

{

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;

}

}

}

This is the JAVA version of the above code. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

I ran it on Aspose.Cells for JAVA 2.0.1.6 and Aspose.Slides for JAVA 1.9 to get the output.ppt which is attached.

Note: Java version of Aspose.Cells cannot create the image of the chart. Please see this post. So the code inserts Blue Hills.jpg to replace an OLE Object image.

JAVA

----------------------------------

import java.io.ByteArrayOutputStream;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.lang.reflect.Array;

import com.aspose.cells.*;

import com.aspose.slides.*;

public class ExcelChart {

public static void Run() {

try

{

//SetLicense();

//Create a workbook

Workbook wb = new Workbook();

//Add an excel chart

int chartRows = 55;

int chartCols = 25;

int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);

//Set chart ole size

wb.setOleSize(0, chartRows, 0, chartCols);

//Save the workbook to stream

ByteArrayOutputStream bout=new ByteArrayOutputStream();

wb.save(bout);

//Create a presentation

Presentation pres = new Presentation();

Slide sld = pres.getSlideByPosition(1);

//Add the workbook on slide

AddExcelChartInPresentation(pres, sld, bout.toByteArray(), "C:/Blue hills.jpg");

//Write the presentation on disk

pres.write(new FileOutputStream("c:\\output.ppt"));

}catch(Exception e){

e.printStackTrace();

}

}

static void SetLicense() throws Exception

{

com.aspose.cells.License lic=new com.aspose.cells.License();

lic.setLicense(new FileInputStream("D:/Shakeel Projects/Aspose/License/new2/Aspose.Total.Java.lic"));

}

static int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)

{

//Array of cell names

String[] cellsName = new String[]

{

"A1", "A2", "A3", "A4",

"B1", "B2", "B3", "B4",

"C1", "C2", "C3", "C4",

"D1", "D2", "D3", "D4",

"E1", "E2", "E3", "E4"

};

//Array of cell data

int[] cellsValue = new int[]

{

67,86,68,91,

44,64,89,48,

46,97,78,60,

43,29,69,26,

24,40,38,25

};

//Add a new worksheet to populate cells with data

//int dataSheetIdx = wb.getWorksheets().addSheet();

Worksheet dataSheet =wb.getWorksheets().addSheet();

String sheetName = "DataSheet";

dataSheet.setName(sheetName);

//Populate DataSheet with data

int size=Array.getLength(cellsName);

for (int i = 0; i < size; i++)

{

String cellName = cellsName[i];

int cellValue = cellsValue[i];

dataSheet.getCells().getCell(cellName).setValue(cellValue);

}

//Add a chart sheet

Worksheet chartSheet = wb.getWorksheets().addSheet(SheetType.CHART);

chartSheet.setName("ChartSheet");

int chartSheetIdx = chartSheet.getIndex();

//Add a chart in ChartSheet with data series from DataSheet

Chart chart = chartSheet.getCharts().addChart(ChartType.COLUMN_CLUSTERED, 0, chartRows, 0, chartCols);

chart.getNSeries().add(sheetName + "!A1:E1", false);

chart.getNSeries().add(sheetName + "!A2:E2", false);

chart.getNSeries().add(sheetName + "!A3:E3", false);

chart.getNSeries().add(sheetName + "!A4:E4", false);

//Set ChartSheet an active sheet

wb.getWorksheets().setActiveSheet(chartSheetIdx);

return chartSheetIdx;

}

static void AddExcelChartInPresentation(Presentation pres, Slide sld, byte[] wbArray, String imgChart) throws Exception

{

com.aspose.slides.Picture pic = new com.aspose.slides.Picture(pres, new FileInputStream(imgChart));

int picId = pres.getPictures().add(pic);

int slideWidth = (int)pres.getSlideSize().getX() - 1500;

int slideHeight = (int)pres.getSlideSize().getY();

int x = 1500 / 2;

OleObjectFrame oof = sld.getShapes().addOleObjectFrame(x, 0, slideWidth, slideHeight, "Excel.Sheet.8", wbArray);

oof.setPictureId(picId);

}

}

Here is the VB.NET code equivalent to C# code in the first post. I have also attached .vb file.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports System.Drawing

Imports System.IO

Imports Aspose.Slides

Imports Aspose.Cells

Module ExcelChartAsOleObject

Public Class ExcelChart

Shared Sub Run()

'Uncomment it to avoid Evaluation Sheet

'Aspose.Cells.License lic = new Aspose.Cells.License();

'lic.SetLicense(@"D:\Shakeel Projects\Aspose\License\new2\Aspose.Total.lic");

'Step - 1: Create an excel chart using Aspose.Cells

'--------------------------------------------------

'Create a workbook

Dim wb As Workbook = New Workbook()

'Add an excel chart

Dim chartRows As Integer = 55

Dim chartCols As Integer = 25

Dim chartSheetIndex As Integer = AddExcelChartInWorkbook(wb, chartRows, chartCols)

'Step - 2: Set the OLE size of the chart. using Aspose.Cells

'-----------------------------------------------------------

wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols)

'Step - 3: Get the image of the chart with Aspose.Cells

'-----------------------------------------------------------

Dim imgChart As Bitmap = wb.Worksheets(chartSheetIndex).Charts(0).ToImage()

'Save the workbook to stream

Dim wbStream As MemoryStream = wb.SaveToStream()

'Step - 4 AND 5

'-----------------------------------------------------------

'Step - 4: Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides

'-----------------------------------------------------------

'Step - 5: Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue

'-----------------------------------------------------------

'Create a presentation

Dim pres As Presentation = New Presentation()

Dim sld As Slide = pres.GetSlideByPosition(1)

'Add the workbook on slide

AddExcelChartInPresentation(pres, sld, wbStream, imgChart)

'Step - 6: Write the output presentation on disk

'-----------------------------------------------------------

pres.Write("c:\test\output2.ppt")

End Sub

Shared Function AddExcelChartInWorkbook(ByVal wb As Workbook, ByVal chartRows As Integer, ByVal chartCols As Integer) As Integer

Dim cellsName As String() = { _

"A1", "A2", "A3", "A4", _

"B1", "B2", "B3", "B4", _

"C1", "C2", "C3", "C4", _

"D1", "D2", "D3", "D4", _

"E1", "E2", "E3", "E4" _

}

'Array of cell data

Dim cellsValue As Integer() = { _

67, 86, 68, 91, _

44, 64, 89, 48, _

46, 97, 78, 60, _

43, 29, 69, 26, _

24, 40, 38, 25 _

}

'Add a new worksheet to populate cells with data

Dim dataSheetIdx As Integer = wb.Worksheets.Add()

Dim dataSheet As Worksheet = wb.Worksheets(dataSheetIdx)

Dim sheetName As String = "DataSheet"

dataSheet.Name = sheetName

'Populate DataSheet with data

For i As Integer = 0 To cellsName.Length - 1

Dim cellName As String = cellsName(i)

Dim cellValue As Integer = cellsValue(i)

dataSheet.Cells(cellName).PutValue(cellValue)

Next

'Add a chart sheet

Dim chartSheetIdx As Integer = wb.Worksheets.Add(SheetType.Chart)

Dim chartSheet As Worksheet = wb.Worksheets(chartSheetIdx)

chartSheet.Name = "ChartSheet"

'Add a chart in ChartSheet with data series from DataSheet

Dim chartIdx As Integer = chartSheet.Charts.Add(ChartType.Column, 0, chartRows, 0, chartCols)

Dim _chart As Chart = chartSheet.Charts(chartIdx)

_chart.NSeries.Add(sheetName + "!A1:E1", False)

_chart.NSeries.Add(sheetName + "!A2:E2", False)

_chart.NSeries.Add(sheetName + "!A3:E3", False)

_chart.NSeries.Add(sheetName + "!A4:E4", False)

'Set ChartSheet an active sheet

wb.Worksheets.ActiveSheetIndex = chartSheetIdx

AddExcelChartInWorkbook = chartSheetIdx

End Function

Shared Sub AddExcelChartInPresentation(ByVal pres As Presentation, ByVal sld As Slide, ByVal wbStream As Stream, ByVal imgChart As Bitmap)

Dim pic As Aspose.Slides.Picture = New Aspose.Slides.Picture(pres, imgChart)

Dim picId As Integer = pres.Pictures.Add(pic)

Dim slideWidth As Integer = pres.SlideSize.Width

Dim slideHeight As Integer = pres.SlideSize.Height

Dim x As Integer = 0

Dim chartOleData(0 To wbStream.Length) As Byte

wbStream.Position = 0

wbStream.Read(chartOleData, 0, chartOleData.Length)

Dim oof As OleObjectFrame = sld.Shapes.AddOleObjectFrame(x, 0, slideWidth, slideHeight, "Excel.Sheet.8", chartOleData)

oof.PictureId = picId

End Sub

End Class

End Module

Will you be adding a functionality to create an chart image in JAVA?

Hello,

We don’t have plans to add charting to our Aspose.Slides for Java library. To create image of a chart you should use another charting library. The ideal way is use Aspose.Cells to create Excel OLE object and image but I’m not sure if creating images already available in the Aspose.Cells for Java. You can contact Aspose.Cells team in their forum.

Hi,

I am having a issue with Chart resizing in a slide. Please see the attached zip file. It has a java class and a powerpoint template. The java class suppose to create a chart on slide 1 same as slide 2. Could you please help me with it. I asked the questions to aspose.cells team first. Their reply was, the code is correct, reach out to Slide's team. Your quick response will be appreciated.

Thanks,

Vimal

Hi,

Any udates for me? We are not able to move forward without fixing the issue?

thanks,

Vimal

Hello Vimal,

We are investigating this issue but there is no solution yet. We will let you know as soon as problem will be fixed or some workaround will be found.

Thanks for update. Please keep me posted. This is part of an important initivative, so quick resolution will be much appreciated.

Hi,

Any updates? I thought resizing the OLEObject is existing functionality. Our requirement should have been achieved using existing apis. Is there any other issue here?

Thanks,

Vimal

Hi,

Regarding Aspose.Slides for .NET, there seems no issue with embedding the excel chart as ole object except resizing and the one discussed here.

Hi,

We are using Aspose.Slides for JAVA. Have you executed the code I attached earlier? The issue is, there are two charts on a slide. The "Object Changed" image shows up appropriately as per the specified dimension and location. But once user click on edit, the OleObject resizes. I have attached the PowerPoint. Slide 1 in the PowerPoint is the desired result we are looking for. Slide 2 is generated using Aspose.Slides. If you right click and edit the image, you will see the the issue I mentioned. Let me know if you have more questions. The quick help will be appreciated.

Thanks,

Vimal

Guys, any updates? any suggestion here?

Hello,

Sabir already answered the question and provided the link to the thread with similar issue. Since Aspose.Slides for Java is Java port of Aspose.Slides for .NET they have similar problems.

We know about this problem and working on it. As soon as the fix will be ready we will notify you.

Hi Alexey,

Thanks for the reply. Could you tell us the time estimates, will it be days, weeks or months? This will help us to define our strategy.

Thanks,

Vimal

Hello Vimal,

The next Aspose.Slides version will be released at the end of this month. In case this particular problem will be fixed earlier then immediate hot fix also will be published.

Perfect, let us know as soon as new hot fix is available.

Thanks,

Vimal

Hi Alexey,

Is there any chance the hot fix will be available this week?

Thanks,

Vimal

Hi Vimal,

This issue is on priority with issued id 11051 and you will be informed without any delay as soon as it is resolved.

Have you released the newer version?