Transcript Creating PowerPoint presentations from within Brio.Enterprise

Creating PowerPoint
Presentations from within
Brio Intelligence™
Exploiting the Automation Interface
David Eastwood
Maddox Ford Ltd.
www.maddoxford.co.uk
Overview
• Work arose out of a need to create and
update complex PowerPoint® presentations
from Brio Intelligence™
• This presentation makes use of Brio
Intelligence’s JavaScript capabilities and the
Automation interface
• Key samples of code are shown and
described
• Although centred on controlling PowerPoint
presentations, the ideas are applicable to
other Office products
2
Software Versions
• The code featured has been developed under
Brio 6.2.3 (and earlier)
• It works with both Designer and Insight
• The software has been tested on Office 97,
Office 2000
3
Origins of the work
• Prompted by the needs of a Category
Management application
• End users worked with complex PowerPoint
presentations which used Brio queries as the
source of data
• There was a need to:
– update existing slides for different time periods and/or
customers
– provide a simple control panel to create and format the
slides as well as compose the data queries
– some graph types needed were not available in Brio –
Radar, Bubble
4
What do the applications look like?
5
The PowerPoint object model
Application
oPPT †
Presentation(s)
oPPTPres †
Slide(s)
oPPTSlide †
Shape(s)
OLEFormat
*
oPPTChart or oPPTTable †
*
The full object model is available from MSDN – see refs. at the end
†
Object names used by us within Brio
6
Object
Getting started
• Create the link to PowerPoint
oPPT = new JOOLEObject("PowerPoint.Application");
• Create a new presentation
oPPT.Visible = true;
oPPTPres = oPPT.Presentations.Add();
Alert("New presentation created:
\r\n"+ oPPTPres.Name)
• Open an existing presentation
sFile =
oPPTPres
tbFileLocation.Text; // name from text box
= oPPT.Presentations.Open(sFile);
Alert("Presentation Loaded: \r\n"+ oPPTPres.Name)
7
Slide manipulation (1)
• Add a slide to the end of the slide show
nSlideNums = oPPTPres.Slides.Count;
oPPTSlide =
oPPTPres.Slides.Add(ppLayoutTitleOnly,(nSlideNums+1)) //***
// const ppLayoutTitleOnly == 11
• Add a title (if the slide has one defined)
oPPTSlide.Shapes.Title.TextFrame.TextRange.Text = "Sample
title";
*** Note this syntax – reverse order to Office documentation
8
Slide manipulation (2)
• Add a footer
oPPTFooter = oPPTSlide.Shapes.AddTextbox(20,700,500,10,1);
//***
oPPTFooter.TextFrame.TextRange =
"Slide created on " + (new Date()).toUTCString();
• Format some text
oPPTFooter.TextFrame.TextRange.Font.Size = 8;
oPPTFooter.TextFrame.TextRange.Font.Italic = true;
*** Note this syntax – reverse order to Office documentation
9
Objects in slides
• Charts and tables are embedded objects on a
slide
• They form part of the Shapes collection
• The OLEFormat object contains the methods
and properties of embedded OLE objects
10
Graph creation (1)
• We chose to use Microsoft Graph –
you could use embedded Excel
graphs instead
• Equivalent to the Insert/Chart menu in
PowerPoint
11
Graph creation (2)
• Graph has an
attached
datasheet whose
layout determines
the look of the
graph
12
Create the graph object
oNew = oPPTSlide.Shapes.AddOLEObject(false,"",0,"",false,"",
"MSGraph.Chart",350,600,150,50);
// ****
//AddOLEObject arguments are:
//link,iconlabel,inconindex,iconfilename,displayasicon,filename,classname,
//height,width,top,left
oPPTChart =oNew.OLEFormat.Object;
// now set the chart type
oPPTChart.ChartType = xlColumnClustered ; // const xlColumnClustered == 54
oPPTChart.Application.PlotBy = xlColumns; // const xlColumns == 2
*** Note this syntax – reverse order to Office documentation
13
Moving data
• Data can be exported via the clipboard
• Exports from table sections can use the GetCell()
method but it’s slower
• We copied the data via the clipboard (and rearranged it within the DataSheet if necessary):
oPPTChart.Application.DataSheet.Columns.Clear;
ActiveDocument.Sections["Pivot"].Copy();
oPPTChart.Application.DataSheet.Range("00:00").Paste;
14
Formatting
• All PowerPoint format properties are available
to be read and altered
• As are the properties of embedded objects
such as graphs:
function AddDataLabels(chart){
chart.Application.Chart.ApplyDataLabels;
for (var i=1; i<=chart.SeriesCollection.Count; i++){
chart.SeriesCollection.Item(i).HasDataLabels = true;
chart.SeriesCollection.Item(i).DataLabels.Font.Size = 10;
}
}
15
Table creation
• We chose to create embedded spreadsheets.
• You can also use embedded Word Tables and
(in PowerPoint 2000) native PowerPoint
tables (members of the Shapes collection)
oNew = oPPTSlide.Shapes.AddOLEObject(false,"",0,"",false,
"","Excel.Sheet",350,600,150,50); //***
//AddOLEObject arguments are:
// link,iconlabel,iconindex,iconfilename,displayasicon,
// filename,classname,height,width,top,left
oPPTTable = oNew.OLEFormat.Object;
*** Note this syntax – reverse order to Office documentation
16
Table data
• Copy the data to the table
ActiveDocument.Sections["Pivot"].Copy();
oPPTTable.Sheets.Item(1).Paste;
17
Updating slides
• We need to identify the source of the data
already on the slide:
– did Brio create this slide?
– which Query created this slide?
– which Section was the source of the data?
– what Limits were used when the query ran?
• We can then reset the Brio query to match
the slide and allow the user to vary these
settings (e.g. time period)
18
Identifying slides and their source
• Slides have a SlideID property – a unique ID
independent of slide order (and not visible to the
PowerPoint user)
• Slides have Tags – this lets you create your own
properties for a slide
Slide.Tags.Add("Value", "TAG LABEL"); //***
// the name of the Tag Label should be in caps
// PowerPoint will return caps anyway!
*** Note this syntax – reverse order to Office documentation
19
Using Tags
• We use tags to
– Label the slide as being created by Brio
– Show the code version used to create the slide
– Name the query which created the slide
– Name limits and limit values
– etc.
20
Finding objects in a slide
• When updating a slide, you will need to find
the object from the Shapes collection
function FindOLE(oPPTSlide){
var nShapeCount = oPPTSlide.Shapes.Count
for (var n=1; n<=nShapeCount; n++){
var nShapeType= oPPTSlide.Shapes.Item(n).Type;
if (nShapeType == msoEmbeddedOLEObject){
// Const msoEmbeddedOLEObject == 7
break;
}
}
return oPPTSlide.Shapes.Item(n).OLEFormat.Object;
}
21
Live demo
• I will illustrate the code we have been
discussing via the demo application
• This demo code available from Maddox Ford
web site
www.maddoxford.co.uk
[email protected]
22
Coding considerations
• (Brio 6.2.3) When calling PowerPoint
methods with multiple arguments you need to
supply the arguments in the reverse order to
that shown in Microsoft documentation
Brio:
Slide.Tags.Add("Value", "TAG LABEL");
Microsoft: Slide.Tags.Add("TAG LABEL", "Value")
• When selecting Items in a collection, use the
.Item(n) method not [n]
23
Development aids (1)
• Use VB Script editor in
PowerPoint to confirm
syntax of commands
• You may need to load
additional Help files in
PowerPoint
• MSDN also has
reference information
and articles
24
‘On-Line Help’
• Find the object or
method
• Press F1
25
Development aids (2)
• External JavaScript
editor can be helpful
with large scripts
26
Development aids (3)
• Create a set of
constants to
match those used
by Office
• Try to follow good
JavaScript coding
conventions
27
Reference Material and
Acknowledgements
• MSDN
http://msdn.microsoft.com/library
http://msdn.microsoft.com/library/en-us/modcore/html/deovrObjectModelGuide.asp
• On-line Help in Office (VB Editors)
• Platypus JavaScript editor
http://www.c-point.com/pjedit.htm
• ‘JavaScript – The Definitive Guide’
David Flanagan, O’Reilly
• Maddox Ford Brio Development Standards
[email protected]
28