topic Re: customize the formatting of the target excel file when save a data table as a excel file. in Discussions

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

zlsas — Tue, 28 Jun 2016 20:23:38 GMT

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

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

pmroz — Wed, 29 Jun 2016 13:29:33 GMT

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.

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

zlsas — Mon, 06 Nov 2017 15:06:44 GMT

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.

Open the *.xml file with notepad ++. It makes life much easier to identify each section of a XML file.
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.
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.

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

pmroz — Wed, 06 Jul 2016 18:26:47 GMT

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.