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.
@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);
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.