Question

Problem using excel templates and fanning out data

  • 13 October 2015
  • 6 replies
  • 10 views
matself
Rank
Badge +8

Hi!

 

The excel writer doc says that:"The writer feature type option to Drop Existing Sheet/Named Range is irrelevant if specifying a template sheet here, since the existing sheet will always be replaced by the template sheet." [my underline].

 

This is not true. The template sheet is retained, and new sheets are created after it. Fanning out data on an expression like <fieldname>.xls and using <fieldname> as sheet name, and Sheet1 as template sheet produces an excel file with two sheets, Sheet1 and <fieldname>.

 

This is not as expected (or my misunderstanding).

 

I have tried numerous tweaks for truncate-overwrite etc etc to no avail.

 

Please advise.

 

Regards, Mats.E

6 replies

A
Badge
Hi

 

When fanout on a xlsx you need to create the the fanout settings under writer->advanced.

 

I've done it it works like a charm..

 

I too have a template xlsx file for the writer and then fanout on a field name created from the attributes

 

 

 

Try to see if u can solve it like above..

 

 

cheers

 

 

andre
matself
Rank
Badge +8
Thanks Andre, but that is just how I am doing it, and it does not work as predicted.

 

Update: Settnig 0 (zero) for sheet order puts the new sheet first, which is an improvement. It would actually work, if it weren't that the excel file is closed with focus on the second tab which is the empty template sheet.

 

 
pratap
Rank
Badge +2
Hi,

 

 

1. a. Does that mean even you have placed "Yes" in "Drop Existing Sheet/Named Range" still dropping of sheet is not happening?

 

    b. "The template sheet is retained, and new sheets are created after it" this will happen when your template has differnet sheet name.

 

 

2. Regarding Fanout, Please check both the setting whether you have placed as mentioned...

 

        a. Sheet name will be controlled by "Feature Type Name" in General Tab of Writer Properties

 

        b. File name will be controlled by writer->advanced as Mr.Andre stated

 

 

Do let me know your comments, if I understood your question wrongly
A
Badge
Hi Mats

 

If you in fact create unique xlsx files based on <fieldname>.xls, why do you need to have the sheets within named the same? The template you pointing to has got an existing sheet name. If you omitt the sheet name to be unique it should work just fine, that is set it to be same as template sheet name.

 

 

If you do need to have both the file name and sheet name identical then Pratap have given you some ideas to try.

 

 

Also, consider wether you actually need to use a template.

 

 

regards

 

 

andre

 

 
matself
Rank
Badge +8
Hi Pratap and Andre. Thanks for all hints.

 

I have done further experiments, and yes, if I do not specify the sheet name, the template sheet name is used, and only one sheet is created. In my case that sheet name became 'template', which seemed silly.

 

Second, if I want to fan out data in the same excel file, and thus make several named sheets, I see no point in keeping the empty template sheet. This is also how I understood the help file text that I quoted.

 

 

Conclusion: With the excel writer, It is not possible to use a template file/sheet AND fan out data on attribute names AND name the sheet with that attribute AND get only one dynamically named sheet in the excel file.

 

It is, however, possible to fan out data and name sheets as above, NOT using a template.

 

 

This cannot be understood from the documentation.

 

And I wanted to use a template to make a nicely formatted file, as well a machine readable one.

 

 

Case closed for my part. Thanks for your attention.

 

 
pratap
Rank
Badge +2
Yes, Excel is bit different from other formats since it has sheets. When you want to use the template then your sheet name also should match. If you want to consider only the headers from template then it is better to use attributes in user attributes tab in excel writer.

 

 

If you want 1 excel file and several sheets in it by using the contents of template then you should not use writer --> advanced parameters (Place Fanout Dataset: NO) but you should use only "Feature Type Name" in General Tab of Writer Properties. By this you will get 1 excel file with several sheets as per your template file. Please note even in this scenario sheet name should be matched in template.

 

 

If you want several excel files with template sheet then you should use writer --> advanced parameters (Place Fanout Dataset: Yes) as well as we have to create 1 more attribute before writer for Sheet Name with the value as per our template (E.g. SheetName="Template" in your case) and select the  "Feature Type Name" in General Tab of Writer Properties with this attribute (i.e. SheetName). So you will get several excel files same as per the template. Please note sheet name will be Template in all the excel files in this case.

 

 

Please do let me know if your comments

Reply


Community Stats

28,662
Posts
109,325
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.