How can i write data to excel and build a chart from one column into several sheets?

First of all, congratulations on the 100,000th post. That's pretty cool.

1. In your template file, are the charts set up to read from the specific column in each sheet?

2. Are you using named ranges in your writer to define where in the sheets you want the data to be written? If not, I'm wondering if the chart is getting overwritten when it writes to each sheet. Although, that wouldn't explain why chart still shows up on the first sheet.

Hi @tono

I suspect the behaviour you are seeing may be related to a known issue with FME2019 and template files with the Excel writer. It appears the Excel writer is not copying the template sheet if the output sheet name does not have the same name as the template sheet.

Does the sheet with chart in your output file have the same name as the template sheet?

I would like to produce several sheets in the excel file. And their names are set dynamically (based on an attribute value) so the answer is, no. :( Any workaround?

Thanks! As long as i use one excel file with just one excel ark (and the excel ark has the same name as the template) it works like a clock. I get the excel file with the chart, but when i try it dynamically...it fails.

Should I send this as an issue for the excel writer in FME2019?

I wasn't able to find a workaround using FME. But if you are not against using macros in an Excel file, it is possible to set up a macro which creates a chart for each sheet. The macro-enabled Excel file containing the macro should be set as the template file in the Excel writer.

I have attached a template workspace that demonstrates this approach. Please note the output Excel file is required to have the same extension as the template file. So if you are using a macro-enabled Excel file (.xlsm), the output Excel file will need to be *.xlsm.

excelchartfromtemplate.fmwt

If you wish to create a support case for this issue, please feel free to do so at www.safe.com/support

We are aware that this is an issue in FME 2019 and are tracking this bug internally.

Edit: this issue is internally tracked as FMEENGINE-60964

As a workarond:

A) I wrote an excel file per table and chart instead of writing one file and several sheets. It works fine with the template (but as you say, the sheet you write to must have the same name as the templates sheet)

B) I wrote all the data in one single sheet and then made a template with a sheet that has a pivot table and a chart associated, so you can filter and show only the data you want in the pivot table and the histogram chart (had to group the data using the data analysis tool in excel)

Both work fine, now is time for the client to decide :)

Good to hear you are chasing the bug!!!!