OleObjectFrame.ObjectData --> Microsoft.Office.Interop.Graph.Chart

toddba · March 14, 2007, 8:39am

Forgive me if this has already been answered, but I've done a pretty comprehensive search through the forums and google and haven't been able to find an answer.

Has anyone been successful in converting the OleObjectFrame.ObjectData of an embedded MSGraph Chart into a useable Microsoft.Office.Interop.Graph.Chart object that can be manipulated?

Casting the ObjectData doesn't seem to work, and I haven't figured out any other way of recreating the binary object in memory.

We have a PPT file that is used as a base template for producing client-facing presentations. The file contains dozens of embedded chart objects. At runtime, we need to open these chart objects, manipulate the data, and save those changes back into the PPT. Our end users have requested that the charts must remain as editable objects -- so embedding images produced by Aspose.Charts won't do, and Excel seems a bit heavy for our needs.

We are an Enterprise Aspose.Total customer, so we have access to the full range of Apose products if there is some other solution.

Thanks,

Todd

shakeel.faiz · March 14, 2007, 8:54am

Dear Todd,

There are two ways to manipulate chart objects, either by using Aspose.Cells or by using VBA automation. You can also use any third party component that could manipulate chart objects.

toddba · March 14, 2007, 10:08am

Thanks. Our current process for generating these presentations involves the use of Office Automation (PowerPoint), which obviously isn’t a good server-side solution. Getting away from using Office Automation on our server is a very big reason why we purchased Aspose.Total in the first place.

You mentioned using Aspose.Cells to manipulate the Chart object. Are you saying that Aspose.Cells has the ability open a MS Graph object? I thought Aspose.Cells was a spreadsheet tool.

Are there any code samples you can point me to?

I’ve already been down this road:

And I can’t really find anything useful there. The example only illustrates how to save an embedded Excel worksheet to an XLS file.

Thanks much!

Todd

shakeel.faiz · March 14, 2007, 10:53am

Dear Todd,

Aspose.Cells is a non-graphical component that can be used to create/manipulate excel files. You can also use it to create/manipulate excel charts and then embed them inside the PowerPoint using Aspose.Slides. Please read about it here.

Also check demos for Aspose.Cells here.

toddba · March 17, 2007, 9:51am

I managed to get a little further. I was successful in manipulating the data of a MS Grpah object, however when I put it back into the presentation, the scale of the object is all goofed up. I haven't been able to figure out how to correct this programatically yet. Here is a code snipet:

// cast MSGraph shape as OleObjectFrame

OleObjectFrame oof = (OleObjectFrame)shape;

// check, if OleObjectFrame is not null then read the object data of

// OleObjectFrame as an array of bytes. Then write those bytes as temp file

if (oof != null)

{

// write the object out to disk so we can load it and manipulate it

FileStream fstr = new FileStream("D:\\graph.gra", FileMode.Create, FileAccess.Write);

byte[] buf = oof.ObjectData;

fstr.Write(buf, 0, buf.Length);

fstr.Flush();

fstr.Close();

// load the object from disk

Global global = new GlobalClass();

Microsoft.Office.Interop.Graph.Application application = global.Application;

application.FileImport("D:\\graph.gra", null, null, null, null);

// set some data

application.DataSheet.Cells[2,2] = 0.6859;

application.DataSheet.Cells[3,2] = 0.5;

application.DataSheet.Cells[4,2] = 0.6346;

application.DataSheet.Cells[2,3] = 0.662327;

application.DataSheet.Cells[3,3] = 0.884594;

application.DataSheet.Cells[4,3] = 0.629499;

application.DataSheet.Cells[2,4] = 0.70415;

application.DataSheet.Cells[3,4] = 0.875956;

application.DataSheet.Cells[4,4] = 0.676291;

// save the object back out to disk

application.SaveAs("D:\\graph-new.gra");

// load the new object into memory so we can stick it back into the powerpoint

FileStream fstr2 = new FileStream("D:\\graph-new.gra", FileMode.Open, FileAccess.Read);

byte[] buf2 = new byte[fstr2.Length];

fstr2.Read(buf2, 0, buf2.Length);

fstr2.Close();

// save the changed object back to the powerpoint

oof.ObjectData = buf2;

}

shakeel.faiz · March 17, 2007, 10:54am

Dear Todd,

Please provide me these two files you are using in your code, to work on your problem.

graph.gra
graph-new.gra

shakeel.faiz · March 17, 2007, 3:35pm

Dear Todd,

I have also seen this problem myself, but it is not related to Aspose.Slides. The buffer is changed by Microsoft.Office library. So the only solution is you should learn the library and resize the chart’s dimensions as you desire them before saving the buffer to disk.

Here is code that I have written to see this problem

void AddMSGraphObject()
{
    Presentation srcPres = new Presentation("srcAddMsGraph.ppt");
    Slide fstSlide = srcPres.GetSlideByPosition(1);
    Shape srcShape = fstSlide.Shapes[0];
    OleObjectFrame msGrphObj = srcShape as OleObjectFrame;

    FileStream fout = new FileStream("d:\\msgraph.gra", FileMode.Create, FileAccess.Write);
    byte[] buf = msGrphObj.ObjectData;
    fout.Write(buf, 0, buf.Length);
    fout.Flush();
    fout.Close();

    // load the object from disk
    Microsoft.Office.Interop.Graph.Global global = new Microsoft.Office.Interop.Graph.GlobalClass();
    Microsoft.Office.Interop.Graph.Application application = global.Application;
    application.FileImport("d:\\msgraph.gra", null, null, null, null);

    // set some data
    application.DataSheet.Cells[2, 2] = 0.6859;
    application.DataSheet.Cells[3, 2] = 0.5;
    application.DataSheet.Cells[4, 2] = 0.6346;
    application.DataSheet.Cells[2, 3] = 0.662327;
    application.DataSheet.Cells[3, 3] = 0.884594;
    application.DataSheet.Cells[4, 3] = 0.629499;
    application.DataSheet.Cells[2, 4] = 0.70415;
    application.DataSheet.Cells[3, 4] = 0.875956;
    application.DataSheet.Cells[4, 4] = 0.676291;

    // save the object back out to disk
    application.SaveAs("d:\\newmsgraph.gra");
    FileStream fin = new FileStream("d:\\newmsgraph.gra", FileMode.Open, FileAccess.Read);
    byte[] buff2 = new byte[fin.Length];
    fin.Read(buff2, 0, buff2.Length);
    fin.Close();

    msGrphObj.ObjectData = buff2;

    srcPres.Write("outAddMSGraph.ppt");
}

toddba · March 17, 2007, 5:58pm

Attached are the two graph files -- the original, and the modified one. First thing you'll notice is the big size differerence. The original is 55KB, and the modified one is only 7KB. Nonetheless, all colors and other attributes seem to stay intact. The only thing that is lost is the scale of the chart.

If you open the modified presentation in PowerPoint, right click on Format Obeject... and then click on the Size tab, you'll notice that it has changed the scale of the embedded object. I have been unable to figure out how preserve the object's original size and scale.

Todd

shakeel.faiz · March 18, 2007, 11:56pm

Dear Todd,

Chart is being scaled down because of Microsoft provided library. You should find out what is causing it. It is not the problem of Aspose.Slides, actually it is problem of Microsoft library if any.

toddba · March 19, 2007, 12:00pm

I understand that it’s not Aspose.Slides screwing up the scaling. However, despite my best efforts, I haven’t been able to figure out how to preserve or reset the original scale.

I was hoping that one of the many people who read this forum might have seen this type of behavior before (or something similar), and could offer some assistance. Judging from the number of posts I’ve read here from people wanting to manipulate a MS Graph, you’d think that someone might have already solved this problem and just not posted about it yet.

I was especially hopeful that one of the Aspose engineers might be able to help all of us, since you guys are clearly the experts when it comes to dealing with quirky Microsoft Office products.

Todd

msfaiz:

Dear Todd,

Chart is being scaled down because of Microsoft provided library. You should find out what is causing it. It is not the problem of Aspose.Slides, actually it is problem of Microsoft library if any.

levinator · March 20, 2007, 12:30pm

I am having the same problem as well. It has been a long painful journey to this point and the poor scaling of the imported chart is the only thing holding us back from using Aspose in our development. If you cannot provide a solution for this, we will not be able to use your software and we will have to bite the bullet and install PowerPoint on the client’s server. If you can offer any more advice it would be greatly appreciated. Thanks.

Matt

shakeel.faiz · March 20, 2007, 1:03pm

Dear Todd and Matt,

You should check all the Graph objects with Width and Height properties. For example Application, Chart, ChartArea, DateSheet etc. I think one of the property should change scale parameter. The main task is just find out the right property.

I found out that if you just import the graph data file using application.FileImport and simply save it back using application.SaveAs, graph gets scale down. I don’t know why?

Please find out, if Aspose.Cells allows you to do the same thing for you and does it also pose the same problem as of Mircosoft Library?

toddba · March 22, 2007, 11:11am

Shakeel,

The thing that is screwing up the scale of the object is the call to ImportFile(). If there was a way to directly access and manipulate the object in memory without having to first save it to a file and then call ImportFile to load it, I'm certain it would solve the problem.

For example, if I were to use MS Office Automation to access the object, I would do something like this:

PowerPoint.OLEFormat fmt = shp.OLEFormat;
if(fmt.ProgID == "MSGraph.Chart.8")
{
Chart chart = (Chart)fmt.Object;
String str = chart.ChartTitle.Caption;
}

As you can see, it's really easy to cast the shape as the proper object (Chart) and access it directly in memory.

So while I was reading through the Aspose documentation, I noticed that Aspose.Words seems to support a comparible interface:

OleFormat Class

Provides access to the data of an OLE object or ActiveX control.

For a list of all members of this type, see OleFormat Members.

System.Object
Aspose.Words.Drawing.OleFormat

[Visual Basic]
Public Class OleFormat

[C#]

public class OleFormat

Remarks

Use the OleFormat property to access the data of an OLE object. You do not create instances of the OleFormat class directly.

Requirements

Namespace: Aspose.Words.Drawing

Assembly: Aspose.Words (in Aspose.Words.dll)

void AddExcelChart()
{
    //Source presentation contains Excel Chart object,
    //Which was inserted inside the slide using Insert-->Object
    Presentation srcPres = new Presentation("srcExcelChart.ppt");
    Slide fstSlide = srcPres.GetSlideByPosition(1);

    //The alternative text of the source is set as oleobject
    //to get its reference quickly
    Shape srcShape = fstSlide.FindShape("oleobject");
    OleObjectFrame msExcelObj = srcShape as OleObjectFrame;

    //Create a workbook
    Workbook wb = new Workbook();

    //Open the Excel Chart inside the workbook
    MemoryStream ms = new MemoryStream(msExcelObj.ObjectData);
    wb.Open(ms);

    //Change the February bars
    wb.Worksheets[1].Cells[2, 1].PutValue(25);
    wb.Worksheets[1].Cells[2, 2].PutValue(25);
    wb.Worksheets[1].Cells[2, 3].PutValue(25);

    //Now save back the modified workbook to memory stream
    MemoryStream ms2 = wb.SaveToStream();

    //Read the new memory stream into new buffer
    byte[] newBuf = new byte[ms2.Length];
    ms2.Position = 0;
    ms2.Read(newBuf, 0, newBuf.Length);

    //Add a new slide with new oleobjectframe and set its objectdata with new buffer
    Slide sld = srcPres.AddEmptySlide();
    sld.Shapes.AddOleObjectFrame(msExcelObj.X, msExcelObj.Y, msExcelObj.Width,
    msExcelObj.Height, msExcelObj.ObjectClassName, newBuf);

    //Write the presentation on disk
    srcPres.Write("outExcelChart.ppt");

    //Now look at the second slide, doubl click it, then click chart tab at
    //the bottom with three other tabs and see the modified chart
}

levinator · March 27, 2007, 10:45am

Shakeel,
Thanks a lot for finding a work around for this issue. I am now having some issues implementing your solution. At first, when I was using an older version of aspose cells, I ran your code and when I opened the output PowerPoint presentation and double clicked on the “object changed” image, it told me server application not found. I then upgraded to the newest version of cells and now when I try running the code it always gives me a null reference exception on the AddOleObjectFrame line. Do you have any ideas as to why this would be happening? Thanks again.

Matt

levinator · March 27, 2007, 11:15am

I just downloaded the 2nd to last version of slides, 2.6.10.0, and I no longer get the null reference exception. The last hot fix said you fixed items dealing with oleObjects so perhaps something was broken in the process.

Matt

shakeel.faiz · March 27, 2007, 11:39am

Dear Matt,

Thank you for your quick and precise information. This problem will be fixed in new release as early as possible.

shakeel.faiz · March 28, 2007, 12:14pm

Dear Matt,

The above problem has been fixed in the latest release Aspose.Slides 2.6.12.0.

toddba · March 30, 2007, 9:57am

msfaiz:

Dear Todd and Matt,

You should check all the Graph objects with Width and Height properties. For example Application, Chart, ChartArea, DateSheet etc. I think one of the property should change scale parameter. The main task is just find out the right property.

I found out that if you just import the graph data file using application.FileImport and simply save it back using application.SaveAs, graph gets scale down. I don’t know why?

Please find out, if Aspose.Cells allows you to do the same thing for you and does it also pose the same problem as of Mircosoft Library?

The problem seems to exist with Aspose.Cells as well. I converted one of our charts to be an Excel Chart instead of a MS Graph Chart. Then I used the following code to load the chart into memory, adjust the data points, and then put it back into the PowerPoint. When the new PowerPoint is opened, the scale of the object is once again hosed up. So it appears to be a problem with Aspose, not the MS Components.

OleObjectFrame oof = (OleObjectFrame)shape;

// check, if OleObjectFrame is not null then read the
// object data of OleObjectFrame as an array of bytes.
if (oof != null)
{
    // create a workbook
    Workbook wb = new Workbook();

    // open the Excel Chart inside the workbook
    MemoryStream msIn = new MemoryStream(oof.ObjectData);
    wb.Open(msIn);

    // set some data
    wb.Worksheets[1].Cells[1, 1].PutValue(0.6859);
    wb.Worksheets[1].Cells[2, 1].PutValue(0.5);
    wb.Worksheets[1].Cells[3, 1].PutValue(0.6346);
    wb.Worksheets[1].Cells[1, 2].PutValue(0.662327);
    wb.Worksheets[1].Cells[2, 2].PutValue(0.884594);
    wb.Worksheets[1].Cells[3, 2].PutValue(0.629499);
    wb.Worksheets[1].Cells[1, 3].PutValue(0.70415);
    wb.Worksheets[1].Cells[2, 3].PutValue(0.875956);
    wb.Worksheets[1].Cells[3, 3].PutValue(0.676291);

    // now save back the modified workbook to memory stream
    MemoryStream msOut = wb.SaveToStream();
    oof.ObjectData = msOut.ToArray();
}

OleObjectFrame.ObjectData --&gt; Microsoft.Office.Interop.Graph.Chart

OleFormat Class

Remarks

Requirements

See Also

OleObjectFrame.ObjectData --> Microsoft.Office.Interop.Graph.Chart