Ways to automate and customize JSL results into PowerPoint

[#1]

Hello all ,

          I am trying to automate a customized Powerpoint Presentation@@ in JSL. While I have successfully automated the entire analysis - I am a little challenged as to how to automate this last step i.e. save/customize the figures and tables generated by JSL into the existing Power Point presentation I currently have. I know this question has been asked in different ways previously in the following posts :

1. Saving Analyses to PowerPoint
2. How to save graph automatically from JMP to powerpoint using jsl
3. Pick your brains...Script to export to PowerPoint
4. How to Save JMP Output from JMP 12 to your Organization’s PowerPoint Template
5. PowerPoint Export of Journal

        The above are some of the most relevant posts that I found helpful but I think (5) is the most relevant and it has not been answered . At the cost of being redundant - can some one provide the best way to handle this situation . To explain further - let us say I have a custom report - each slide has different types of graphs and text . Most of the figures/graphs are generated in my JSL script . However - what is the best way to have JSL export the graphs into the presentation in the customized format each slide requires ? The options I can think of are  :

1.       Save JMP datatable(s) as Excel sheets and then
   1. Manually generate the plots in Excel and copy paste into Powerpoint ( currently being done) - highly inefficient
   2. Write a VBA macro to combine the excel sheets and generate the graphs and insert into Powerpoint - am not proficient in VBA - dont want to pursue this route
   3. Write VBA code in JSL - again for the same reason above - dont want to pursue this route
2.       Save the figures in
   1. Reports - and then ? I know JSL can export the figures to PowerPoint in the template we want - but does this allow any formatting - if so kindly share a resource or a code snippet
   2. Journals - and then ? Should I manually copy paste the figures into Power Point ?

       I wish to use JSL to do this and keep the entire report within JSL rather than using Excel . Please kindly advice .

Best

Uday

[#2]

msharp​ , PMroz​ , Craige@JMP​  - Any Thoughts ?

[#3]

I haven't done much work to automate scripts to PowerPoint, but I've thought about it several times.  Here's the steps I would take:

1) Write a JSL script that generates the data, analyzes it, and puts into a report that can then be edited by a user.  (filter outliers, pin labels, ect.)

     Note: If this is built correctly, this is usually where I stop and leave it to the user to take snapshots and whatnot to create the PowerPoint.  Everyone likes their presentations different anyways, and the next steps don't always meet the ROI of my time.

2) Organize the report into Tabs or other JSL report structure that the end user can understand will turn into a separate PowerPoint page.

3) Add a button to the report, that will generate the PowerPoint after the user has made their edits in JMP.

4) Export the JSL tabs as Pictures.

5) Have JSL then run a PowerPoint VBA script that would then load in the pictures and generate the presentation.

There are lots of programs, not just JSL, that can generate .ppts.  My experience is they all fall short.  If you want the most customization to make the best report, it's best to just use VBA for PowerPoint.  It's a scripting language, and like other scripting languages, it does it's job well.  Maybe you aren't going be able to create an awesome statistical analysis add-in w/ VBA for PowerPoint (best leave that to JSL), but customize a .ppt?  Yeah, it'll do that great.

[#4]

@msharp :

              If I understand this right and I am going with your approach(1) - looking at the sample code below :

NamesDefaultToHere(1);
dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
g1 = Expr(dt1 << Bivariate( Y( :height ), X( :weight ) ));
dt2 = Open("$SAMPLE_DATA/Bands Data.jmp");
g2 =  Expr(dt2 << Distribution( Nominal Distribution( Column( :Name( "Banding?" ) ) ) ));
win = NewWindow("Reports from Two Tables", HListBox(g1, g2));

- Courtesy :  Ian@JMP​

Every time - I dispatch a graph to a report - it creates a new report and then I need to use the references of all these reports to combine them into a horizontal collection or vertical collection of windows ?

[#5]

Many years ago my wonderful husband created a program p3icli, that enables loading pictures into PowerPoint.  The name p3icli, is an acronym for what it does, PowerPoint Picture Insert Command Line Interface.  The program is open source  and can be downloaded from the 1st link below

P3ICLI (PowerPoint Picture Insert Command Line Interface). I recommend using the binary distribution if you decide to download it.

I wrote the specific JSL example found at http://p3icli.sourceforge.net/jsl_example.html​ .  It includes:

• a link for the specially crafted PowerPoint template, with 3 different slides, that define the layout of the final presentation
• the example JSL script that
  • opens the JMP sample data Iris.jmp,
  • creates and saves JMP output from 3 platforms: Parallel Plot; Scatterplot Matrix (splom) and Oneway (4 plots, each response vs. Species)
  • writes the p3icli commands to create a title page and load the plots to the PPTX template,
  • runs p3icli using the JSL command Run Program()
• the text file created in JSL and run, is displayed in this link, and, finally
• a link to the resulting PowerPoint file.

p3icli commands are fairly easy,  for example

• insert slide # from template file_path
• title "you supply the text"
• pic# picture_path  {this command positions the picture on the page}
• cap# "you supply the text" {this command places text in a text box defined in the template}

I and many colleagues have used this to load 100's of graphs in seconds.  Embedding a picture in a PPTX shape can look distorted if the shape is not proportional to the picture size (pixel). p3icli has commands to resize, however, typically, I use one of two methods to ensure no distortion: (1) use a JSL command to size the FrameBox or size the window before saving it, or (2) use something like the previous response to craft a new window and save the window as a graph and use a template that has one shape per slide. 

This does take a little reading on the p3icli documentation on how to create a template. Here is a hint. Save the PPTX template. Open it.  On each shape, right click and select Format Shape.  Look at ALT TEXT.  Each shape has a tag: pic1, pic2 etc. Also each shape where you want to place text, the ALT TEXT will be cap1, cap2, etc.   So the gist is you define a layout, then you save graphs and write commands on how which graphs goes where.  Note you can have a template with logos and backup documentation.  The p3icli command copy source format on will keep the fancy logos and coloring etc.  Note if you have a template with 4 shapes and you only fill 2, the 2 unused shapes do not appear.

This approach enables a lot of flexibility, customization and has saved p3icli users thousands of hours of hand loading and editing PPTX presentations with commands that are much simpler than VBA.

By that way, there is GUI to p3icli, it is called p3i that can be found at the sourceforge.net website.

[#6]

I found this realy useful and created a bash wrapper script around it that I run from cygwin bash shell to auto load png's into a powerpoint with the correct aspect ratio:

file: png2ppt

#!/bin/bash
TCODE=`date +%s`
LIST=`ls -1rt *.png *.PNG`
if [[ $LIST != "" ]] ; then
# p3icli Syntax, Help: http://p3icli.sourceforge.net/p3icli_help.html
# http://p3icli.sourceforge.net/jsl_example.html
# http://p3icli.sourceforge.net/
WPATH=`cygpath -w $PWD`'\'
rm -f $TCODE.p3icli
# no cap1
echo 'open template C:\Programs\cygwin\usr\local\bin\portfolio.pot' >> $TCODE.p3icli
picX=674
picY=378 #388 #348
for F in $LIST ; do
if [[ -f $F ]] ; then
res=`pngcheck $F | cut -f2 -d'(' | cut -f1 -d','`
#res=`exiv2 $F | grep -F 'Image size :' | cut -f2 -d':'`
resX=`echo $res | cut -f1 -dx`
resY=`echo $res | cut -f2 -dx`
let aspect=$resX*10000/$resY

acutoff=17771 #9.33/5.25=1.77

if [[ $aspect -gt $acutoff ]] ; then
scale=`awk "BEGIN {printf \"%.6f\n\", $resX/$picX}"`
else
scale=`awk "BEGIN {printf \"%.6f\n\", $resY/$picY}"`
fi 
X=`awk "BEGIN {printf \"%.0f\n\", $resX/$scale}"`
Y=`awk "BEGIN {printf \"%.0f\n\", $resY/$scale}"`

TITLE=`echo $F | rev | cut -c5-99 | rev | tr '_' ' '`
echo "title "\"$TITLE\" >> $TCODE.p3icli
echo "pic1 $WPATH$F" >> $TCODE.p3icli
echo "pic1 size $X,$Y" >> $TCODE.p3icli
echo "sleep 155" >> $TCODE.p3icli
echo "duplicate template # create and move to next slide" >> $TCODE.p3icli
fi
done
echo "save as presentation $WPATH$TCODE.ppt" >> $TCODE.p3icli
echo "sleep 500" >> $TCODE.p3icli
echo "kill ppt save" >> $TCODE.p3icli
cmd /C p3icli -X -l $WPATH$TCODE".log" $WPATH$TCODE".p3icli"
rm -f $TCODE.p3icli
fi

[#7]

If i understood correctly, lets say i have 500 charts and want to put all these into ppt in some pre defined template. to acheive this i have to modify your code and repeat those insert and pic1.. commands so many times. if so it looks very inefficient. but could be wrong as well.

[#8]

I got a notification about this post, and since it's been three years I figured I mine as well update my response.  I have since built several tools that created PPTs, and I do follow steps 1-4.  For me the exception is step 5, where I instead use a Python library called pptx-ppt.  It looks like others have posted several other alternatives (apparently no one likes VBA).  The nice thing about using Python is that it's open source and in JMP 14 there are new Python Connection hooks, so you can run Python straight from JSL.  Example code below.

Names Default to Here(1);

//Created by msharp - 6/26/18
//Example script to generate a PPT presentation from a JSL report
//This takes advantage of python-pptx and the new Python hooks in JMP 14

//python-pptx installation:
//http://python-pptx.readthedocs.io/en/latest/user/install.html
//python-pptx quick start guide:
//http://python-pptx.readthedocs.io/en/latest/user/quickstart.html


dt = Open("$SAMPLE_DATA/Big Class.jmp");
biv = dt << Bivariate( Y( :height ), X( :weight ) );

//Save Report as Picture
dir = "C:\TEMP\";
file = "example.png";
biv << Save Picture(dir || file, "png");

//Clean Up
biv << Close Window();
close(dt, nosave);

//Create PPT w/ Python
Python Init();
plog = Python Execute({dir, file}, {img_path, left, top, height},
"\[
from pptx import Presentation
from pptx.util import Inches

img_path = dir + file

prs = Presentation()
blank_slide_layout = prs.slide_layouts[6]
slide = prs.slides.add_slide(blank_slide_layout)

left = top = Inches(1)
pic = slide.shapes.add_picture(img_path, left, top)

left = Inches(5)
height = Inches(5.5)
pic = slide.shapes.add_picture(img_path, left, top, height=height)

prs.save(dir + 'example.pptx')
]\"
);
Python Term();

//Open PPT
open(dir || "example.pptx");

[#9]

Hi, I ran the above code after istalling Python on windows. but somwhow this does not load anything but opens the same example.pptx. I see it create example.png in the directory.

Cpould you give some debugging of this issue?

[#10 · Accepted solution]

Dear Uday.

Actually going via Excel to create Powerpoint is a decent way - no matter where the data comes from. And in fact, there are countless manuals on the web available on how to do this. Unfortunately, most of them fall a little short as these manuals do something else than what is actually required. And then adapting this code will become a long way to go. In order to avoid such an issue there is a software called SlideFab available which allows it to flexibly create whole Powerpoint presentations from any Excel without a single line of VBA.

So, one approach would be to use the JMP export and create formulas and charts building on this data such that this raw data can be updated at any time without requiring to rewrite formulas. Then it's possible to use a Powerpoint presentation as a template or to let the software create it from scratch. Then the software allows it to define which data from Excel needs to go into which chart/table/shape in Powerpoint. And if there is some kind of repetition (e.g. create the same slide for multiple scenarios) then it can be defined as well. It's even possible to create data-driven titles, comments and even whole storylines, if the underlying logic is created through formulas within Excel. And then its just a single click to create the Powerpoint presentation automatically.

So this is the way which I would recommend ;-)

Cheers

Jens