Subscribe Bookmark RSS Feed

customize the formatting of the target excel file when save a data table as a excel file.

zlsas

New Contributor

Joined:

Jun 28, 2016

I would like to ask JMP community to help me out on a custom way to export JMP table to excel.

Part of my script need to export a JMP table to excel file.

Ideally, I would like to customize the formatting of the target excel file when exporting. (with desired column width, row height, color, etc.)

But so far, I find that JMP will only carry the cell color to the target excel file. And I’ve been looking for ways to pass a command or set some parameter when exporting, but not luck yet.

Any idea or insight will be appreciated!

Regards,

Zelong

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The approach I've used in the past is heavy on JSL programming but will produce nice-looking results.

1. Save the dataset as an Excel file

2. Format the Excel file exactly how you want it to look, e.g. colors, bolding, freeze rows etc.

3. Save the file as type XML Spreadsheet 2003 (*.xml)

4. This is the hard part: write a JSL program that "reverse-engineers" the xml file

I've used this approach to combine several result tables into a multi-tabbed Excel workbook.

3 REPLIES
Solution

The approach I've used in the past is heavy on JSL programming but will produce nice-looking results.

1. Save the dataset as an Excel file

2. Format the Excel file exactly how you want it to look, e.g. colors, bolding, freeze rows etc.

3. Save the file as type XML Spreadsheet 2003 (*.xml)

4. This is the hard part: write a JSL program that "reverse-engineers" the xml file

I've used this approach to combine several result tables into a multi-tabbed Excel workbook.

zlsas

New Contributor

Joined:

Jun 28, 2016

Thanks for the idea!! Exactly, that's  what I wanted.

The reverse-engineering part does take some time. Here are my approach and couple tips.

     1. Open the *.xml file with notepad ++. It makes life much easier to identify each section of a XML file.

     2. Replace the <Row>..</Row> selection with a "for" loop over the JMP data table.It's a good idea to start with a data table with only a couple of rows.

     3. Locate the debug log to see why if the Excel cannot open it. One possible cause could be the default Col/RowCount in the <Table>...</Table> section doesn't match the actual data we have. Seem it can simply be removed and excel can handle it fine. Not sure if that will be a problem if apply the script to a large data table.

pmroz

Super User

Joined:

Jun 23, 2011

Set the column and row counts to a large enough number to accomodate your data.  When I've done this the column count is fixed, and I set the rowcount to a large enough number to handle my largest dataset.