Control Order of Worksheets in Excel

Hi Jim,

 

 

You can control the order of worksheets specifying Sheet Order parameter for each writer feature type (Format Parameters), in FME 2014 (I confirmed with FME 2014 SP2).

 

 

Takashi

i think in 2013 u have to order top down the workflow leading to the worksheets.  Da primitve way.

 

 

Yeah, it's primitive, but effective in FME 2013. I've used that way last year ;)

Actually the Sheet Order parameter didn't work for me, even with FME 2014 SP2. Perhaps it was because I was writing to an XLS document, not an XLSX document. Writing to XLSX also failed, but for another reason, so I couldn't test it with XLSX.

 

 

I solved this problem by making workspace runners for each of the four worksheets in the XLS. Fortunately my application is also writing to a CSV file, so the first runner executes that, and then the next four runners read from the CSV at a specified begin / length of the CSV. I specified "Yes" for "Wait for Job to Complete". Each runner opens the XLS, writes to a particular worksheet, and then closes it. That allows me to control the order of the worksheets.

by order top down i meant actualy having sheet 1 connector above sheet 2 etc. Visiualy in your workbench...

 

I have tried this, but it does not work in this workspace. The script tests the value of a counter and if it is less than 65,001 it writes the record to page_1, if greater than 65,000 but less than 130,001 it writes to page_2, etc.

 

 

The problem is the fourth worksheet which typically has only about 3,000 rows. I would guess that FME writes that fourth worksheet before it writes the third worksheet, so the worksheets line up as page_1, page_2, page_4, page_3. 

 

 

I even used a feature holder so the worksheets would write in order, but that didn't have any effect. The only technique that worked is using workspace runners as above.

In my understanding, the order of worksheets creation is decided by the order of each first feature (record) to be written into each worksheet. If the order of the records was same as the counter, I think the worksheet would be created in appropriate order. I'm suspecting the order of records is confused.

 

How about sorting the records before testing?

I tried the two techniques that each of you suggested:

 

 

- Having multiple writers for the Excel and ordering them visually in the navigation pane

 

- Sorting the counter before writing to Excel

 

 

Between the two of these, it finally worked.  My Excel displays Page_1, Page_2, Page_3, Page_4.

 

 

Thanks guys.

It seems that the Page Order setting does not always work, even in FME 2015. I have a workspace with several SQLCreator transformers, routing the output of each to a separate sheet, and the Page Order setting seems to have no effect.

Sheet Order, of course.

Another thing that works is if you create output Excel spreadsheet before you run the process, then add the tabs in the order that you want in the speadsheet, and then in the Format Parameters tab of the Excel writer select No for Drop Existing Sheet and Yes for Truncate Existing Sheet. Doing this keeps the Excel tabs in the order that you want.

Thanks JimO!

 

You gave me hint I needed. I had already selected No for Drop Existing Sheet and Yes for Truncate Existing Sheet, but now I changed to specifying a template file and removed the Sheet Order parameter altogether, which did the trick.

 

 

Steinar