topic Can JMP Support Excel Formatting through JSL in Discussions

Can JMP Support Excel Formatting through JSL

uday_guntupalli — Fri, 21 Dec 2018 16:29:06 GMT

All,
Going through several different posts in the community on the topic, I believe I know the answer to my question. However, I will still go ahead and summarize the issue and see if anyone else has gone down this path.

Problem Statement: Generate an Excel Workbook in a previously accepted and recognized template

Not for Debate : Why is Excel the chosen format of output

In going through the following posts :

Understandably a lot of focus is on how to import and export data between Excel and JMP. However, none of them really speak about preserving/customizing the format of the data that is exported from JMP and Excel. Whether we like it or not, Excel is still going to be around and there will be a continuous need for presenting our results via Excel (least of which is every user cannot have a JMP license).

Given that background, I have an Excel template and I would like to edit some data in the Excel template using JSL preferably. This is the last step of a really large application that I have in JMP. Ideally, all I need is for JMP to edit the data in the template and save the workbook. Unfortunately, as you can expect the minute I open the file in JMP, the formatting goes for a toss and leaves me with a garbled format which is not really presentable to the user.

So,
1. Is there a way using JSL not VBA to preserve the formatting in an Excel Template and edit the data in the file ?
2. Assuming the answer to my previous question is "No", I have gone down the path of using "XLConnect" package in R. However, that is also running into troubles. I don't expect the JMP community to address questions on why the R package is not yielding the results. Should anyone be interested in helping, the R code is provided below. Also attached is an example template that I am experimenting on

# Clear Console and screen 
cat("\014") # Clear Console 
rm(list = ls(all = TRUE)) # Clear Workspace
options(java.parameters = "- Xmx1024m")

# Load desired packages
suppressPackageStartupMessages(library(XLConnect))

# Define FilePaths 
FolderPath <- "C:\\Users\\Temp\\Desktop"
TemplateFilePath <- paste(FolderPath, "LoanTemplate.xlsx", sep = "\\")
OutputFilePath <- paste(FolderPath, "MyLoan.xlsx", sep = "\\")
OutputFilePath1 <- paste(FolderPath, "MyLoan1.xlsx", sep = "\\")
OutputFilePath2 <- paste(FolderPath, "MyLoan2.xlsx", sep = "\\")

# Read Data 
TemplateData <- readWorksheetFromFile(TemplateFilePath, sheet = 'Loan Schedule', header = T)
MyData <- TemplateData
MyData[[4]][2] <- 10000

# Set Style Action 
setStyleAction(wb, XLC$"STYLE_ACTION.NONE")

# Trial 1
writeWorksheet(OutputFilePath, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = TRUE)

# Trial 2 
writeWorksheetToFile(OutputFilePath1, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = FALSE) 

#Trial 3 
writeWorksheetToFile(OutputFilePath2, MyData, 'Loan Schedule')

Re: Can JMP Support Excel Formatting through JSL

ian_jmp — Mon, 24 Dec 2018 10:25:26 GMT

If you are running into problems with R, you might consider an alternative 'JMP only' route. Not sure how viable this would actually be.

Names Default To Here( 1 );
// .XLSX is a ZIP file
package = Open( "$DESKTOP/Loan Template.xlsx", zip );
// See what's in the package
dirlist = package << dir;
Print(dirlist);
// Get the XML
xml = package << read(dirlist[9]);
// Manipulate XML with JSL?
Print(xml);
// Update the package
package << Write(dirlist[9], xml );

Re: Can JMP Support Excel Formatting through JSL

gzmorgan0 — Tue, 25 Dec 2018 08:25:35 GMT

@uday_guntupalli your needs are not clear. If you have a template like the one you provided that has all the macro coding and formulas and you only need to change a few values, you can learn a few vbscript commands and use Run Program (see below).

Attached is a script that saves the JMP file Big Class as xslx and changes the format on one cell, to demonstrate how you might code this using JSL.

I am not an expert with XML schema, so Ian's appraoch might be what you need.

JSL to change the Principal, number of days, interest rate and extra payment.

Names Default to Here(1);

cmdList={"Dim xlApp",

"\[Set xlApp = CreateObject("Excel.Application")]\",
"xlApp.Visible = True",
"\[xlApp.Workbooks.Open ("C:\TEMP\Loan Template.xlsx")]\",
"'Change the values in cells e3-e5, e9", 
"\[xlApp.Range("E3").Select]\",
"\[xlApp.ActiveCell.Value = 10000]\",
"\[xlApp.Range("E4").Select]\",
"\[xlApp.ActiveCell.Value = .044]\",
"\[xlApp.Range("E5").Select]\",
"\[xlApp.ActiveCell.Value = 2]\",
"\[xlApp.Range("E9").Select]\",
"\[xlApp.ActiveCell.Value = 150]\",
"\[xlApp.ActiveWorkbook.SaveAs("C:\TEMP\Loan v2.xlsx")]\",
"xlApp.Quit"
	
};


vbsTxt = concat items(cmdList, "\!N"); //add new lines aka CR 

Save Text File( "c:\temp\jj2.vbs" , vbsTxt);


rp4 = RunProgram(Executable("wscript"),
	   Options("c:\temp\jj2.vbs"),
	   ReadFunction("text")
);

Caption( "JuMP has Control", Font( "Arial Black" ),  //u is added for spoken voice to say and not spelll JMP.
	Font Size( 16 ),
	Text Color( "red" ), Spoken(1)
);

wait(2);
Caption(Remove);

//look at the Excel file, the color coding is maintained for new payment schedule

Re: Can JMP Support Excel Formatting through JSL

uday_guntupalli — Tue, 25 Dec 2018 13:09:31 GMT

@gzmorgan0,
Using VBA in the simplified example does seem reasonable, however in my actual problem statement, I would need to modify multiple values and I would prefer to do it without VBA. I was able to get the R code to work and I am trying to see how to work with @ian_jmp suggestion as well. However, thank you for providing me with one more option. Merry Christmas.

Re: Can JMP Support Excel Formatting through JSL

gzmorgan0 — Wed, 26 Dec 2018 23:53:42 GMT

This reply is not so much for @uday_guntupalli but for the blog in case this discussion has followers who would like to be able to save a JMP table in Excel with a more pleasant format where the column headers contain filters/slicers. The solution provided below only works on the simple case of saving one JMP table in a .xlsx file. The VBScript code would need additional code to find all sheets and create a for-loop wrapper to run on each sheet. I am not good at VBS and enlisted syntax help from my better half. If there is interest, post a comment and we'll extend the VBSCript.

Here is how it works:

save the attached file tblFmt.txt to a path where you store programs that you use many times, rename it to have the extension .vbs
open the JSL file CreateExcelTable_Style.jsl and change the path names for .vbs and the saved Excel file, if needed

Items to note regarding the vbs code:

It applies the built-in Excel TableStyleMedium6 to the table on the first sheet which produces dark blue headers, alternating light blue and white rows, and column filters (slicers).
The header font is changed to Arial Black, size 12, bold
The table is AutoFit to the EntireColumn. This might not be useful in all cases.

This VBS code can be called in any JSL program by modifying the path names in this JSL snippet

dt << Save("c:/temp/Biggie Class.xlsx");

//command line syntax: wscript path\tblFmt.vbs 'path to excel workbook'

rp4 = RunProgram(Executable("wscript"),
	   Options({"c:\temp\tblFmt.vbs", "\!"c:\temp\biggie class.xlsx\!""}),
	   ReadFunction("text")
);

Note to JMP Developers ( @ian_jmp ) : I tried making Book.xltx my default Excel template. Either I was not successful, or JMP does not use it. If successful, my vbs code could have specified a custom table style. A Wish List item, might be an extension of Create Excel Workbook() to have optional arguments to specify the .xltx template file, and the TableStyle name that would apply a custom style to be applied. I realize that gets into Excel versions, the issue of 64 vs. 32 bit MicrosoftOffice, etc. This is not high on my list, but there seems to be frequent Excel table requests.

Re: Can JMP Support Excel Formatting through JSL

gzmorgan0 — Thu, 27 Dec 2018 09:31:52 GMT

Here is the multi-sheet version, for the previous post. Note JMP 12 only allows saving a table to a single sheet Excel workbook. JMP 13 enabled the function Create Excel Workbook() but it required a list of JMP table names, not the table references. JMP 14 allows either a list of opened table names or table references.

Here is how it works:

save the attached file multi_tblFmt.txt to a path where you store programs that you use many times, rename it to have the extension .vbs
If you are using JMP 12 or later with wscript (a PC not sure about a MAC), open the JSL file CreateExcelTable_Style.jsl and change the path names for the .vbs file and the saved Excel file (save_path), if needed.

It is interesting to note that the Excel template that JMP uses for:

JMP v12 and JMP v13, TableStyleMedium6 is an Aqua color, and TableStyleMedium2 is Blue.
JMP v14 the TableStyleMedium6 is Blue.

Edit the .vbs file with Notepad to specify the style you prefer. ( In Excel, Home >Format>Table ).

Final Note: The .vbs file contains an "IF" block of code that produces an interactive message. If running this script non-interactively, this if block should be removed or commented out. See the .vbs file comment.

Re: Can JMP Support Excel Formatting through JSL

uday_guntupalli — Fri, 28 Dec 2018 22:17:24 GMT

@ian_jmp,
In the example you have shared, could you kindly elaborate on how one could edit the values in XML using JSL ?

Re: Can JMP Support Excel Formatting through JSL

gzmorgan0 — Sat, 05 Jan 2019 12:29:48 GMT

@uday_guntupalli there is an alternative to editing XML to edit Excel and save formatting and functions: use JMP functions to treat Excel as a database. I have only done this with sheets that have database layout, that is, columns are fields and rows are records.

I am not an SQL expert, but you can find documentation on updating many database records with a table. I will provide a simple example.

Below is a picture of a simple Excel file with two sheets and with formatting. The file is called suppliers.xlsx and the sheet to be updated is named suppliers. The book JSL Companion, 2nd Edition, documents three JMP ODBC methods: Open Database(), New SQL Query() and one the authors named a 3-step method.

This method can be used to execute any valid SQL statement. Below is the script to change one record using the SQL UPDATE syntax.

dbc = create database connection("DSN=Excel Files; DBQ=c:\temp\suppliers.xlsx");

Execute sql(dbc, "SELECT * FROM `suppliers$` WHERE  supplier_name = 'Google' ", "READIT"); //example extraction

Execute sql(dbc, "UPDATE `suppliers$`
   SET supplier_id = 150,
    supplier_name = 'Apple',
    city = 'Cupertino'
   WHERE supplier_name = 'Google'");
   
close database connection(dbc);

Before the SQL scriptAfter the SQL Update - red box drawn

Note that the formatting is unchanged. I cannot attest to the performance of a database UPDATE with another table, but system updates are typically pretty fast. You can do a web search for the proper SQL syntax. Note if you are interested in this method and have 32bit Microsoft Office, it takes a couple steps to complete the onetime setup of the Machine File connection (my machine connection to Excel is named Excel Files ).

This is not the forum to discuss all of that. We (authors) wrote a supplementary document if you request it.

From your last post, it seems you have an R method to do waht you want, so I was not going to reply, but decided for closure/alternatives to add another method into the mix.