cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
HP_Lau
Level I

Excel Table Formatting

I have written jmp script to auto generate reports and save as .xlsx, but unable to perform the cell formatting such as merge cells, cell color, font type etc.

There was a discussion topic in this community mentioned about .xml method, however there is no further details.

Much appreciated if someone show me how to get it done. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Excel Table Formatting

For the import operation, if you have JMP 11 or newer then I would first open the file using the Excel Wizard so that you can avoid importing the top lines that contain the character data.  That way the columns won't get turned into Character columns.  Then you can take the Source script out of the resulting table and use that within your JSL script.

 

As far as saving goes, JMP will always save the column headers when saving as Excel.  There is no option to avoid that.  JMP doesn't really have a facility to use VBA or to drive other programs outside of JMP.  It does have an Automation interface to be driven by other programs like VBA in Excel.  You could write a VB program that drives both Excel and JMP and acts as a controller for the two of them, but that is getting more complicated.  In that scenario, you could have JMP open the file, and then use VB/VBA to retrieve the data and again us VB/VBA to populate the data in the Excel table.

Brian Corcoran

JMP Development

View solution in original post

5 REPLIES 5
pmroz
Super User

Re: Excel Table Formatting

To get exact formatting in an Excel spreadsheet from JMP here's one way to do it using xml output:

 

  • Create the desired Excel spreadsheet, with your merged columns, colors, etc. exactly in the format you need.
  • Save As a .XML file using the format "XML Spreadsheet 2003 (*.xml)
  • Now comes the hard part - reverse engineer this same xml file using JSL.  Substitute data from your JMP dataset into the xml file and you'll have nicely formatted output.

 

 

vince_faller
Super User (Alumni)

Re: Excel Table Formatting

You could run the JMP script using VBA, then write all the formatting stuff after in VBA.  And just to play devil's advocate, is there a reason you need to put it into excel?  JMP has a lot of formatting options (I really hate VBA).  

Vince Faller - Predictum
HP_Lau
Level I

Re: Excel Table Formatting

I don't like VBA either, but I have no choice as the report format is out of my control.



Could I use jsl command to call/activate VBA instruction instead? I want the main user interface to be JMP instead of Excel Macro.



The report has non-standard header (it is not a real header but multiple rows of information before the data table, and lesser columns than data table).

- It force me to convert all columns to "Character" due to the non-header rows on top.

- When save as Excel, the option for "Export Table Headers" become unavailable (which works for .txt case).

- Then jsl added a row name as "Column 1", "Column 2", ... on top of everything.


Re: Excel Table Formatting

For the import operation, if you have JMP 11 or newer then I would first open the file using the Excel Wizard so that you can avoid importing the top lines that contain the character data.  That way the columns won't get turned into Character columns.  Then you can take the Source script out of the resulting table and use that within your JSL script.

 

As far as saving goes, JMP will always save the column headers when saving as Excel.  There is no option to avoid that.  JMP doesn't really have a facility to use VBA or to drive other programs outside of JMP.  It does have an Automation interface to be driven by other programs like VBA in Excel.  You could write a VB program that drives both Excel and JMP and acts as a controller for the two of them, but that is getting more complicated.  In that scenario, you could have JMP open the file, and then use VB/VBA to retrieve the data and again us VB/VBA to populate the data in the Excel table.

Brian Corcoran

JMP Development

gangasani95
Level I

Re: Excel Table Formatting

Hi,

Even I was stuck with a similar issue. I changed the data type of the column to a character and I could save the formating in .xlsx file. Hope this helps.

-Sri