Free Support Forum - aspose.com

How to change the original workbook using Smart Marker

Aspose.Cells Product Family
#1

I am trying to update an excel chart imbedded in a Apose.Slide. I first get the OleObjectFrame from the slide and convert it to a WorkbookDesigner. It seems that WorkbookDesigner.process() does not change the original workbook or OleObjectFrame . My question is this: must I have the last highlight RED line in the example below in order to change the original OleObjectFrame?

Private Sub updateVintageYearPerformanceChart(ByVal oof As OleObjectFrame)

Dim ds As DataSet = getVintageYearPerformanceChartData()
Dim dtChartData As DataTable = ds.Tables(0)
dtChartData.TableName = "ChartData"

' Open chart with Aspose.Excel
Dim mstream As MemoryStream = New MemoryStream(oof.ObjectData)
Dim designer As Aspose.Cells.WorkbookDesigner = New Aspose.Cells.WorkbookDesigner()
designer.Open(mstream)
designer.SetDataSource(dtChartData)
designer.Process()
oof.ObjectData = designer.Workbook.SaveToStream().ToArray() ' is this what I need to do?

end sub

Thanks.

#2

Hi,

Well, WorkbookDesigner.Process processes all the smart markers resided in the templat file.

Does the last line work fine?

Any how we will check and get back to you for your query.

Thank you.

#3

Yes. It does work and update the original Excel workbook when the last line is added. My guess is that the lines

Dim mstream As MemoryStream = New MemoryStream(oof.ObjectData)
Dim designer As Aspose.Cells.WorkbookDesigner = New Aspose.Cells.WorkbookDesigner()

designer.Open(mstream)

only creates a copy of the object data so it does not update the original workbook without the last line

oof.ObjectData = designer.Workbook.SaveToStream().ToArray()

I was wondering if this is the proper way of updating a excel chart (extracted from Aspose.Slides object) or whether I was going a around about way.

#4

Hi,

Could you post your ppt file? We will check it soon.

#5

Here is the slide but it could be any slide with an excel chart imbedded in it. There is nothing special about the slide. Of course, the chart has the "smart markers" that can be used by Apose.Cells to update the chart.

#6

Hi,

We checked Workbook.SaveToStream method, it works fine. All smart marker has been replaced with data values. Please double click the slide and check the data of "Sheet1" in the PPT. You will see the correct data.

I think you want to change the image of OleObjectFrame in the PPT. You should call ChartToImage method and replace the old image with the created image after you process smart markers.

Presentation pres = new Presentation(@"F:\FileTemp\ForAspose.ppt");
Slide fstSlide = pres.GetSlideByPosition(1);
Slide slide = pres.Slides[0];
OleObjectFrame chartoof = (OleObjectFrame)slide.Shapes[3];

if (chartoof != null)
{
MemoryStream chart = new MemoryStream(chartoof.ObjectData);
Aspose.Cells.WorkbookDesigner wd = new Aspose.Cells.WorkbookDesigner();
wd.Open(chart);
DataTable dt = new DataTable();
dt.TableName = "ChartData";
dt.Columns.Add("Vintage_Year",typeof(int));

dt.Columns.Add("XIRR", typeof(int));
dt.Columns.Add("Lower_Quartile", typeof(int));
dt.Columns.Add("Midian_Quartile", typeof(int));
dt.Columns.Add("Upper_Quartile", typeof(int));
DataRow row = dt.NewRow();
row[0] = 2003;
row[1] = 0;
row[2] = 100;
row[3] = 200;
row[4] = 300;
dt.Rows.Add(row);
row = dt.NewRow();
row[0] = 2004;
row[1] = 0.5;
row[2] = 150;
row[3] = 250;
row[4] = 350;
dt.Rows.Add(row);
wd.SetDataSource(dt);

wd.Process();
//wd.Workbook.Worksheets[0].Charts[0].ToImage(@"F:\FileTemp\chart.emf", System.Drawing.Imaging.ImageFormat.Emf);
chartoof.ObjectData = wd.Workbook.SaveToStream().ToArray();
pres.Write(@"F:\FileTemp\dest.ppt");
}