How to add a chart without adding the entire spreadsheet?

[lajolla]

I followed Aspose's sample code to add chart to a slide by adding the OLE Object.

This actually added the OLE Excel Object. Is there a way just to add the Chart without the entire spreadsheet? How do I do it if I create chart dynamically with Aspose.Cells (without reading an Excel file from my harddrive)? Thanks.

My code is like this:

//Instantiate a Presentation object that represents a PPT file

Presentation pres = new Presentation();

Slide slide = pres.AddEmptySlide();

//Reading excel chart from the excel file and save as an array of bytes

FileStream fstro = new FileStream("C:\\excel1.xls", FileMode.Open,FileAccess.Read);

byte[] b = new byte[fstro.Length];

fstro.Read(b, 0, (int)fstro.Length);

//Inserting the excel chart as new OleObjectFrame to a slide

OleObjectFrame oof = slide.Shapes.AddOleObjectFrame(0,0,pres.SlideSize.Width,

pres.SlideSize.Height,"Excel.Sheet.8", b);

pres.Write("C:\\modified.ppt");

[shakeel.faiz]

The worksheet is the part of the chart.

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

You can use Aspose.Cells.Worksheet.SaveToStream() method to get the chart/sheet as stream and use it in Aspose.Slides.Slide.Shapse.AddOleObjectFrame() method.

Please see the code example, which reads sample chart inside the ppt and update it with modified data.

http://www.aspose.com/Community/forums/72379/ShowThread.aspx#72379

[lajolla]

It might be easier if I just set up a template with an Excel Chart in my slide. I just need to open the slide retreive the OLE object and update the data points in the series.

The Aspose.Slides.Shapes class has an AddOleObjectFrame method. Is there a method for retrieving an Excel Chart (or OLE object) in a slide?

How can I retrieve an Excel Chart embedded in the slide?

Can I use “Designer Template” for the embedded Excel Chart? i.e., Can I put “Smart Data Markers” inside the Excel Chart which is embedded in the slide. If I could, then I can use Aspose.Cells to update the cells and the chart with just a few lines of code. That would be wonderful.

Thanks.

[shakeel.faiz]

Dear lajolla,

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

The quick and easy way is to use Slide.FindShape() method. Just set the Alternative text in MS-PowerPoint by right clicking the shape (chart) and select Format Object -- > Web Tab from the pop up context menu.

For example, if you had set it as mychart then, you will retrieve it like this

C#

OleObjectFrame oof=sld.FindShape(“mychart”) as OleObjectFrame;

VB.NET

Dim oof as OleObjectFrame

oof=Ctype(sld.FindShape(“mychart”), OleObjectFrame)

[lajolla]

Thanks very much. That was very helpful. Now I can retrieve an Excel worksheet embedded in a Powerpoint slide and write some value to a cell. I can save the byte stream back to my slide.ObjectData.

I open my Powerpoint slide and double click the “Object Changed” in the slide but saw format of the original chart got changed.

These are the issues I ran into:
1) Even though I just write some test data into some irrelvant cell, the chart size and format got changed. e.g., the legend got resized, partially covering the my bars of the chart. The whole chart got resized so I have to manually align and resize it to its original shape. The bar chart also lost the bold face format on the label (showing the value or height) for each bar. My intention was to update each data series so that the bar chart (height of the bars) can be automatically updated.

2) I was hoping I can use “Designer Worksheet” with “Smart Data Markers” in the embedded worksheet so that I can more easily update the data series. However I do not see an equivalent SaveToStream() method for the WorkbookDesigner class. I need to save the updated workbook in to stream or byte array so I can update OleObjectFrame.ObjectData and I prefer to do this all dynamically in memory with writing the workbook into a file.

Anyway I can keep the format of the original chart in the OLE object? Any way I can use “Designer Worksheet” in an Excel worksheet embedded in a Powerpoint slide?

This is the sample code I have:
Presentation pres = new Presentation(“PPSample.ppt”);
Slide slide = pres.Slides[1];
OleObjectFrame oof =slide.FindShape(“EmbeddedSampleWorkbook”) as OleObjectFrame;

if (oof != null) {
byte[] buf = oof.ObjectData;
Stream stream = new MemoryStream(buf);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(stream);
Worksheet ws = wb.Worksheets[“Data”];
if (ws != null) {
ws.Cells[“A8”].PutValue(“Test Data”); // test if I can write some value to a cell.
oof.ObjectData = wb.SaveToStream().ToArray();
}
else {
Console.WriteLine(“ERROR: failed to retrieve ‘Data’ worksheet”);
Console.ReadKey();
}
}
else {
Console.WriteLine(“ERROR: Cannot retrieve ‘EmbeddedSampleWorkbook’ OLE object”);
Console.ReadKey();
}

pres.Write(“chartTest.ppt”);

[shakeel.faiz]

You did not set the OLE size. Use SetOleSize method. For more information please see the post with id 71904 and replies to it.

http://www.aspose.com/Community/Forums/permalink/70370/71546/ShowThread.aspx <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

[lajolla]

Shakeel:

Many thanks. After playing with SetOleSize I can get the Chart to fit in the slide.

Any possiblity I can use Apose.Cells.WorkbookDesigner (i.e., Smart Data Marker) for injecting data into a worksheet embedded in a Powerpoint slide? The following code seems to be a possiblity but the problem I am facing is that I cannot save the WorkbookDesigner object into a stream so I can update the OleObjectFrame.ObjectData. If it is not possible to use WorkbookDesigner within a workbook embedded in a slide, can we have this feature added in a future release? I can see this can be a great feature for easily updating a chart or worksheet that many customers of yours might be interested in having.

I was hoping I can do something like this:

OleObjectFrame oof =slide.FindShape(“embeddedWorksheet”) as OleObjectFrame;

if (oof != null) {
byte[] buf = oof.ObjectData;
Stream stream = new MemoryStream(buf);
Aspose.Cells.Workbook designerWb = new Aspose.Cells.WorkbookDesigner();
designerWb .Open(stream);

if (ws != null) {
double[] dataArray = {.3513, .2845, .1875, .3941, .2376 };
designerWb .SetDataSource("dataArray ", dataArray );
// PROBLEM: WorkbookDesigner class does not have .SaveToStream method
oof.ObjectData = wb.SaveToStream().ToArray();
}

Then in the embedded worksheet, I can just have Smart Data Marker like: "&=$(“dataArray (noadd)”

&=$ASP_IRR(noadd)

[lajolla]

Shakeel:

I think I just found a way to save a WorkbookDesigner object into a stream. I just have to convert it to a regular Workbook first. Here is how I did it:

OleObjectFrame oof = slide.FindShape(“embeddedWorkbook”) as OleObjectFrame;
byte[] buf = oof.ObjectData;
MemoryStream stream = new MemoryStream(buf);
Aspose.Cells.WorkbookDesigner wbd = new Aspose.Cells.WorkbookDesigner();
wbd.Open(stream);
double[] dataArray = {.3513, .2845, .1875, .3941, .2376 };
wbd.SetDataSource("dataArray ", dataArray );
wbd.Process();
Workbook wb = wbd.Workbook;
wb.Worksheets.SetOleSize(1, 19, 1, 7);
oof.ObjectData = wb.SaveToStream().ToArray();

Again, thanks very much for your help.