Hi,
I have created a simple workbench that merges two datasets together and performs statistical calculations. So far, I have been unable to set my Excel writer to have one sheet for the dataset and another sheet for the summary from the StatisticsCalculator.
I have already seen the "write data to different sheet" post, but have not had much luck. Would anybody be able to offer any advise to me, please?
Many thanks,
Jess
Best answer by chrisatsafe
Hi @_jj,
It might be worthwhile to check out the video demonstration on the Overview of Excel Writer Parameters article. The example from the "write data to different sheet" post uses a different technique called a Fanout - which is a great method for dividing output data based upon the value of an attribute.
In your case, you may want to use multiple Excel Writer Feature Types as suggested by @jpsalva as it will allow you to format the sheets independently. Each Writer feature type on the canvas will correspond to a new sheet in the output Excel Workbook. After adding an Excel Writer to the canvas, you can simply duplicate it by right-clicking on the Excel writer feature type and choosing duplicate or by selecting it and using the keyboard shortcut CTRL+D.
Note: if you are using a manual attribute definition you will have to manually enter attribute names but this will allow you to specify the attribute type, cell width, etc. If you are using an Automatic attribute definition, Workbench automatically defines the list of attributes depending on what it is connected to - you can learn more about this in the About Writer Feature Types: User Attributes documentation.
I have attached a sample workspace might also be helpful: Multiple_Excel_Sheets_Example.fmwt
Hope this helps.
Other useful resources:
+2Hi @_jj
Using the example workbook 'C:\\demo\\myworkbook' with 2 worksheets
This can be solved using two writers each pointing to one excel worksheet.
When you 'Add Writer' make sure the 'Sheet Definition' is set to 'Manual...'
Then you will prompted with the following window asking for your 'Sheet Name'
+2Hi @_jj,
It might be worthwhile to check out the video demonstration on the Overview of Excel Writer Parameters article. The example from the "write data to different sheet" post uses a different technique called a Fanout - which is a great method for dividing output data based upon the value of an attribute.
In your case, you may want to use multiple Excel Writer Feature Types as suggested by @jpsalva as it will allow you to format the sheets independently. Each Writer feature type on the canvas will correspond to a new sheet in the output Excel Workbook. After adding an Excel Writer to the canvas, you can simply duplicate it by right-clicking on the Excel writer feature type and choosing duplicate or by selecting it and using the keyboard shortcut CTRL+D.
Note: if you are using a manual attribute definition you will have to manually enter attribute names but this will allow you to specify the attribute type, cell width, etc. If you are using an Automatic attribute definition, Workbench automatically defines the list of attributes depending on what it is connected to - you can learn more about this in the About Writer Feature Types: User Attributes documentation.
I have attached a sample workspace might also be helpful: Multiple_Excel_Sheets_Example.fmwt
Hope this helps.
Other useful resources:
Hi @chrisatsafe and @jpsalva,
Thank you both so much for the swift response. I have now managed to write on multiple sheets perfectly. I have also noticed that I can right click and choose "Insert Writer Feature Type" to create the additional sheets too.
Thanks again!
I have also checked the "write data to different sheet" thread. However, I was not able to do it. I would recommend you to try and search for some tutorial videos on youtube. They might be more helpful. I have understood how to write on different sheets only after watching a couple of tutorials. But, everything that the guys wrote above is correct. If you follow all the steps, everything is going to be fine. I actually have a more difficult question, how to conect tableau to google sheets? I need it as soon as possible!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
