I would like to write several sheets into an excel file: on every single sheet there should be data and a chart based on one of the columns. I have tryed it using a template file which already has a chart on the template sheet. I am already using a template file and a template sheet.
On the output file i just get data on every single sheet which is perfect, but only get a chart on the first sheet. The chart is based in one of the columns.
Any clue on how to get an automatic chart per sheet?
Â
+10
First of all, congratulations on the 100,000th post. That's pretty cool.
Â
Â
As for the automatic charts, there's two things I would check: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.
+18
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?
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?
First of all, congratulations on the 100,000th post. That's pretty cool.
Â
Â
As for the automatic charts, there's two things I would check: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.
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.
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?
Should I send this as an issue for the excel writer in FME2019?
+18
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?
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.
+18
Should I send this as an issue for the excel writer in FME2019?
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
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.
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!!!!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
