Using Template file when writing to multiple sheets in the same output excel

Hi ​@j.bramley I don't know the best way of doing it but one thing you could try is: Go to your Excel template file and create a bunch of copies of the template sheet. The writer may be working like "sheet1=sheet1fromtemplate, sheet2=sheet2fromtemplate, ..."

You could also try putting 1 in the order field. Change to insert not update.

Also, something doesn't quite look right. Are you using the featrewriter? If so click the parameters button and choose yes to overwrite file. And put your path to the Excel template file here.

https://support.safe.com/hc/en-us/articles/25407596375309-Using-a-Template-File-when-Writing-Excel-Data

Hope that helps.

And delete the sheet name you have. In the original. 

Thanks ​@crutledge, yes so I am using a feature writer and have changed this to insert with the template file all set up in the parameters. I have also added in duplicate template sheets, but have to name these differently to the template used (I could maybe try do this dynamically, however the number of sheets required each time will be different) but unfortunately it currently still only formats the first feature

My other option would be to output each feature as its own excel file to ensure the formatting comes through, however can’t then think of a best way to automate merging these files/sheets all in to one spreadsheet after

Hi ​@j.bramley I can't test until tomorrow but my next suggestion is exactly what you ended with. Combine all files into one....with formating...there is a setting Read Formatting that come into play I think.

Sorry that's not much help. I can test tomorrow.

​@crutledge thank you!! I feel that combining after too will be the way as it looks like its something that others have found with trying to write to multiple sheets using a template file where the sheet names/number need to be dynamic. Any advice on the read formatting and achieving this is greatly appreciated!!

​@crutledge perfect, that all works!! Thank you!

Renaming the sheets 1-4 worked perfectly, and I still managed to write out more than 4 sheets so it is not a case of one template sheet per output either, thank you!! Saves me requiring openpyxl on my FME server machine so this will deliver the output directly from FME

Good to hear! I will have to test and see if we can get away with only one template sheet in the template xlxs or if 1,2 or more sheets are needed to make this work. I think, but don’t know why, changing the name of the sheet to a number worked (2) rather than a name (Sheet1). It is definitely not intuitive and deserves further understanding/explanation.