Excel chart does not visualize correctly in PowerPoint?

[Joost Reuzel]

Hi,

I have an issue with trying to embed a Excel Charts in PowerPoint via the
Interop API's using C#. I need to get this to work for Office2003.

The process I'm following is that I create a PowerPoint from a template
using the interop API's. Then I insert a new slide for each chart, and on
these slides I add a Excel.Chart OLE Object. All is well so far. Then I go
into that workbook, change the data, and use the chartWizard to update the
chart on the first sheet. Finally I close the excel workbook.

The issue is that the chart does not show correctly in the generated
PowerPoint slides. The chart is adapted with the changed data, but all
actions on the chart itself, such as changing the title, the data range,
chart type, etc. are not shown.

It becomes stranger. When in PowerPoint I click the (incorrectly rendered)
generated chart and choose open or edit, the chart will immediatly refresh to
the correct settings. When I then close the chart and go back to powerpoint,
the chart is rendered correctly.

I've spend hours and hours to figure out what is going on, but somehow
nothing seems to work. I've tried to call:

- PowerPoint.Application.ActivePresenation.UpdateLinks() after all charts
are generated (is it a OLE issue?).
- Chart.Refresh();
- Workbook.RefreshAll();

Could someone give me a hint on what needs to be done? I'll post my code
that inserts the charts below.

Thanks in advance,
Joost Reuzel

//Add the Workbook
Excel.Workbook workbook =
(Excel.Workbook)slide.Shapes.AddOLEObject(basePoint.X, basePoint.Y,
chartSize.Width, chartSize.Height,
"Excel.Chart", "", MsoTriState.msoFalse, "", 0, "",
MsoTriState.msoFalse).OLEFormat.Object;

//update the worksheet with data
Excel.Worksheet datasheet =
(Excel.Worksheet)workbook.Worksheets["Sheet1"];
datasheet.Cells.ClearContents();

//set table contents
//<<SNIP code that fills data sheet with contents from our custom
dataTable object>>

//fetch chart
Excel.Chart chart = (Excel.Chart)workbook.Charts["Chart1"];
Excel.Range range = datasheet.get_Range(datasheet.Cells[1, 1],
datasheet.Cells[dataTable.RowCount + 1, dataTable.ColumnCount + 1]);
chart.ChartWizard(range, Excel.XlChartType.xlBarStacked, Type.Missing,
Excel.XlRowCol.xlRows, 1, 1, true, dataTable.ChartTitle, "", "", "");

//release objects
datasheet = null;
chart = null;
range = null;
workbook.Close(false, Type.Missing, Type.Missing);
workbook = null;

 

[Joost Reuzel]

Hi Steve,

I've looked for that method, but am unable to find it. Perhaps someone else
knows how this is done?

By the way, I've tried MSGraph first, but that didn't work for me either.
The code I used is below:

// create chartobject on slide
Microsoft.Office.Interop.Graph.Chart objChart =
(Graph.Chart)slide.Shapes.AddOLEObject(basePoint.X,
basePoint.Y, chartSize.Width, chartSize.Height, "MSGraph.Chart", "",
MsoTriState.msoFalse, "", 0, "", MsoTriState.msoFalse).OLEFormat.Object;

// setup the chart
objChart.Activate();
objChart.ChartType = Graph.XlChartType.xlColumnStacked;
objChart.HasTitle = true;
objChart.ChartTitle.Text = Title;
//objChart.AutoScaling = true;
objChart.HasLegend = true;

// set the font of the axislabels to something that fits on the
sheet

((Graph.Axis)objChart.Axes(Graph.XlAxisType.xlCategory,
Graph.XlAxisGroup.xlPrimary)).TickLabels.Font.Size = 12;

((Graph.Axis)objChart.Axes(Graph.XlAxisType.xlValue,
Graph.XlAxisGroup.xlPrimary)).TickLabels.Font.Size = 12;

// setup the underlying datasheet
Graph.DataSheet ds = objChart.Application.DataSheet;
ds.Font.Name = "Arial";
ds.Font.Size = 8;
ds.Cells.Delete(Missing.Value);

<<SNIP data table fill>>

// set labels on the rows, to fill the legend
Graph.Legend legend = objChart.Legend;
legend.Font.Name = "Arial";
legend.Font.Size = 10;

//update
objChart.Refresh();
objChart.Deselect();
objChart.Application.Update();
objChart.Application.Quit();

//dereference
legend = null;
ds = null;
objChart = null;

This sounds VERY much like a similar problem people run into when
automating
MSGraph. In MSGraph, there's an Application.Update method that causes the
changed data in the OLE server app to be written back to the object
embedded in
PPT.

Perhaps Excel has a similar method.

Reuzel
wrote:

Hi,

I have an issue with trying to embed a Excel Charts in PowerPoint via the
Interop API's using C#. I need to get this to work for Office2003.

The process I'm following is that I create a PowerPoint from a template
using the interop API's. Then I insert a new slide for each chart, and on
these slides I add a Excel.Chart OLE Object. All is well so far. Then I
go
into that workbook, change the data, and use the chartWizard to update
the
chart on the first sheet. Finally I close the excel workbook.

The issue is that the chart does not show correctly in the generated
PowerPoint slides. The chart is adapted with the changed data, but all
actions on the chart itself, such as changing the title, the data range,
chart type, etc. are not shown.

It becomes stranger. When in PowerPoint I click the (incorrectly
rendered)
generated chart and choose open or edit, the chart will immediatly
refresh to
the correct settings. When I then close the chart and go back to
powerpoint,
the chart is rendered correctly.

I've spend hours and hours to figure out what is going on, but somehow
nothing seems to work. I've tried to call:

- PowerPoint.Application.ActivePresenation.UpdateLinks() after all charts
are generated (is it a OLE issue?).
- Chart.Refresh();
- Workbook.RefreshAll();

Could someone give me a hint on what needs to be done? I'll post my code
that inserts the charts below.

Thanks in advance,
Joost Reuzel

//Add the Workbook
Excel.Workbook workbook =
(Excel.Workbook)slide.Shapes.AddOLEObject(basePoint.X,
basePoint.Y,
chartSize.Width, chartSize.Height,
"Excel.Chart", "", MsoTriState.msoFalse, "", 0, "",
MsoTriState.msoFalse).OLEFormat.Object;

//update the worksheet with data
Excel.Worksheet datasheet =
(Excel.Worksheet)workbook.Worksheets["Sheet1"];
datasheet.Cells.ClearContents();

//set table contents
//<<SNIP code that fills data sheet with contents from our custom
dataTable object>>

//fetch chart
Excel.Chart chart = (Excel.Chart)workbook.Charts["Chart1"];
Excel.Range range = datasheet.get_Range(datasheet.Cells[1, 1],
datasheet.Cells[dataTable.RowCount + 1, dataTable.ColumnCount + 1]);
chart.ChartWizard(range, Excel.XlChartType.xlBarStacked,
Type.Missing,
Excel.XlRowCol.xlRows, 1, 1, true, dataTable.ChartTitle, "", "", "");

//release objects
datasheet = null;
chart = null;
range = null;
workbook.Close(false, Type.Missing, Type.Missing);
workbook = null;

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================