Exporting reports to MS Word

[#1]

Hi,

I need to export a report I create to MS Word in a very specific format.  I can export it as a PDF file which is very close to how I want it to look in MS Word, but I need to improve the look of it as much as I can, to minimize the amount of editing that it will need after having been exported. To illustrate what I'm trying to do, I enclose below a short script that creates a simple report containing some titles, a couple of tables and a footer, and then exports that report in three different formats, namely a PDF file, an MS Word document and an HTML file.

The PDF version of the report is created pretty much as I want it to appear in the MS Word version, with titles centered, different-sized fonts and the table styles ("heading separator" and "bordered") specified in the script.  The MS Word version has however lost the centering of the titles, the font size settings and the table styles - and I'd like to retain all of these features if at all possible.

Also in the MS Word version, every line is actually a box of text (you can see this by opening the document in Word, highlighting everything with <Control>-A and then adding borders to the selection).  This is of course how everything was exported in the first place and therefore isn't at all surprising; however I note that the HTML version doesn't consider the two titles and the footer to be boxes, but simply lines of text - and this is actually how I would like them to appear in the MS Word version.

My questions are therefore:

• Is it possible to retain (a) the centering and/or (b) the font size settings when exporting to MS Word?
• Is it possible to export simply lines of text (as opposed to text boxes) to MS Word?
• It is possible to retain the designated table style (e.g. "bordered" or "heading separator") when exporting to MS Word?

I’m guessing that some, if not all, of these are not going to be possible - but I know from past experience that there's often a way around such little problems if you just happen to know how to do it.  Can anyone help me with any of the above?

Many thanks – here’s the demo script I mentioned:

myTableBox1 = table box(

       string col box("Position", {"First", "Second", "Third", "Fourth", "Fifth"}),

       string col box("Description", {"Water", "Sulphuric Acid", "Oxygen", "Hydrogen Sulphide", "Sodium Chloride"}),

       string col box("Formula", {"H2O", "H2SO4", "O2", "H2S", "NaCl"}),

       << table style("heading separator")

);

myTableBox2 = table box(

       string col box("Town", {"Aberdeen", "Cardiff", "London", "Manchester"}),

       number col box("Population", [212125, 324800, 8174000, 512000]),

        << table style("bordered")

);

myTitle = text box("This is my Main Title", << justify text(center), << set font size(14), << font color({0, 0, 0.5}), << set width(200));

mySubtitle = text box("This is my Subtitle", << justify text(center), << set font size(9), << font color({0, 0, 1}), << set font style("italic"));

myBlankLine = text box(" ", << set font size(12));

myFootnote = text box("This is my footnote", << set font size(9), << justify text(center));

lub = line up box(ncol(1),

       myTitle,

       mySubtitle,

       myBlankLine,

       myTableBox1,

       myBlankLine,

       myTableBox2,

       myBlankLine,

       myFootnote

);

nw = new window("My Window", lub);

lub << save PDF("C:\Documents\My Report.pdf");

lub << save MS Word("C:\Documents\My Report.doc");

lub << save HTML("C:\Documents\My Report.html");

[#2]

There are two kinds of export in use here from JMP's perspective. HTML and DOC are exported as structured text with minimal styling information. PDF (and others like EPS and SVG) are exported as graphics, which preserve the styles and layout but not the structure.

Regarding the text being on separate lines or not, JMP is trying to put them on separate lines by emitting line separators between them, but HTML treats line separators are regular whitespace which explains the difference from DOC. If you're really looking for an extreme work-around, I did find one place in JMP that exports text without line separators or other adornments and that's when exporting a ComboBox.However, if you're going to such lengths and losing styling, you might as well just make one giant Text Box.

Nice clear example, btw.

[#3]

Hi Xan,

Thanks for those pointers; it looks as though I'm going to have to a bit cleverer to get the effects I want, as I don't think a giant Text Box is going to solve my problem unless I can incorporate a table within it - and I can't see a way to do that.  I'll carry on experimenting and post here again if I get anywhere - but in the meantime, could you elaborate on that comment about Combo Boxes please?  I've tried adding one to my Line Up Box, but it doesn't seem to be exported at all in either Word or HTML.

Many thanks

[#4 · Accepted solution]

If you're willing to do a bit of programming you can get the exact format with the following approach:

• Create a report in MS-Word that has the desired format.

• Save it as an XML file, using the format Word 2003 XML Document

• In JSL, create this XML file using the real data.  Save the text with a .XML extension

I've used this approach to produce multi-tabbed Excel spreadsheets.  It takes a bit of programming but it works.

[#5]

This sounds ingenious - I've almost no experience of writing XML files at the moment, but I'll definitely give it a go.

Many thanks!

[#6]

Hi,

I was made aware that there is already a topic about generating reports.

I need to generate a report (MS Mord or saved as PDF file) for multiple parameters (parameters quantity can vary from 1 to 500).

One page for one parameter. The navigation through the document is made with help of bookmarks.

The XML approach seams to be very interesting. I'm not firm with XML and just started with JSL.

Can you please present your approach with examples I can modify for my needs?

Perhaps using the documets I've attached?

The attached data set contains a script for multiple "Normal Quantile Plots".

I'd like to be able to generate a MS Word document with help of JSL according to a certain template, which can be substituted by an arbitrary one.

I've attached:

Dataset with a script for generating the multiple graphs (BTW: thanks to community for help)

PDF converted from MS Word "template" file

XML file converted from MS Word "template" (.zip file)

The XML file can be opened with MS Word.

@martindemel 

[#7]

I tried playing around with this a bit, and my recommendation is to simply export JMP output as a Word document.  It would be very difficult to generate "xml" code for graphs.

Take a look at this example program.  This approach might just be the ticket.  You'll need to learn what JSL display boxes are.  Hint: the scripting index is your best friend!

dt = open("$sample_data\Big Class.jmp");

nw = new window("Sample",
	panelbox("Actions",
		hlistbox(
			msword_button = buttonbox("Save to MS-Word", 
				sample_output << save msword("", Native);
			),
			close_button = buttonbox("Close this window",
				nw << close window;
			),
		),
	),
	sample_output = vlistbox(
		tb = table box(
			string col box("Name", (dt:name << get values)),
			number col box("Age", (dt:age << get values)),
			string col box("Name", (dt:sex << get values)),
		),
		page break box(),
		dist = dt << Distribution(
			Continuous Distribution( Column( :weight ) ),
			Nominal Distribution( Column( :age ) )
		),
	),
	msword_button << set icon("WinFileSave"),
	close_button  << set icon("Stop"),
	tb << set underline headings(1) << set column borders(1) 
		<< Set Shade Alternate Rows(1);
);

[#8]

Thanks for your proposal!!

It works.I just need to rename the file from filename.doc.jmp to .doc

Can you tell me how to adjust the page to landscape, please?

And perhaps I will need to scale the pages...

[#9]

Could you possibly share an example?  I am very interested in this approach.

Many thanks,

John

[#10]

Dodo wrote:

...in the meantime, could you elaborate on that comment about Combo Boxes please?  I've tried adding one to my Line Up Box, but it doesn't seem to be exported at all in either Word or HTML.

Looks like they're exported in JMP 11 but not in JMP 10. More reason to ignore it.