topic Excel Table Formatting in Discussions

Excel Table Formatting

HP_Lau — Wed, 14 Dec 2016 07:33:10 GMT

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.

Re: Excel Table Formatting

pmroz — Wed, 14 Dec 2016 15:01:35 GMT

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.

Re: Excel Table Formatting

vince_faller — Wed, 14 Dec 2016 18:30:26 GMT

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

Re: Excel Table Formatting

HP_Lau — Thu, 15 Dec 2016 02:02:32 GMT

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

briancorcoran — Thu, 15 Dec 2016 13:39:58 GMT

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

Re: Excel Table Formatting

gangasani95 — Mon, 20 Aug 2018 19:15:12 GMT

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