Output data to a formatted excel file with multiple sheets

[ckestler]

I have created an app which outputs 5 reports into one excel workbook (so five sheets). I would like to format each sheet so that the columns are pre-sized, headers are highlighted, and there the columns have a banded color. 

I've painstakingly followed and tried many suggestions from other questions, yet none have worked. Most of these attempts have been using a template. 

What I am currently trying, is to start with a template which holds some placeholder data. I highlighed the area which would be replaced, and clicked 'Define Name'. I did this for each sheet, then saved and closed the template. 

(full disclosure, I'm not exactly sure what the need for this 'Define Name' step is for...It just seemed to be a necesssary step on this site http://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-do-I-output-to-an-Excel-template-file/ta-...  )

Next, I went into my workflow, wrote to this file and the specified sheet, and put 'Delete Data and Append', so that the placeholder data will be deleted but the formatting will remain. 

ex:  where SandO is the sheet name

 

When I push play, my data get's written to the file, but each output becomes a new sheet with no formatting, named, for example 'SandO1'. It also takes FOREVER for the file to open.  

What gives?

I've also tried outputting to a specified range, as per another suggestion, but I get all sorts of errors..

Help!

I've attached my workflow. 

[SophiaF · Accepted solution]

Hi @ckestler,

Looks like the "Define Name" portion is the key here - it looks like from your workflow that you are ouputting to the sheet that you want in the workbook for each section; you will actually want to point to the Named Range instead. Here's my small example:

In the workflow/xls file attached, you'll see the Sheet Names are FirstSheet and SecondSheet, however my named ranges are Data1 and Data2, respectively. My output configuration shows the named ranges instead of the sheet names:

Make sure to select "Delete Data & Append" (looks like you had this in the screenshot, but for some reason in the workbook "Overwrite Data" is selected").

Here's the output:

A couple things to note:

     - The template file must be saved as a .xls (Excel97/2003) for the Delete Data & Append option to apply.

     - Fields that are Date/DateTime types remove the formatting (see image above); if you output as a String or number type it works fine.

Best,

[ckestler]

Thank you for your reply.

When I  set my output configuration to the named ranges, instead of the sheet name, it just creates a whole new tab, where the sheet name is my named range..and there is no formatting. Do I need to indicate the sheet name AND the named range somehow? 

I don't see in your workflow if/how you do this. 

[ckestler]

I have fixed it. Turns out it was a formatting issue for this particular version of excel. Thank you!

[ckestler]

 Once I publish my app to the Gallery, and run it, the formatting disappears. I believe, because it is writing to _externals...instead of the file I initially set. 

If there is a file the app uses every time, does it need to be in an _external file?

I put the template in the external folder, yet it still does not work. 

[SophiaF]

Could you package up your workflow and post again? I'm unable to see some of the tools and the data.

The first error looks like it's coming from the file being open somewhere, or perhaps the file is read only?

[ckestler]

I edited my question to something completely different (see above), but as a workaround to the previous question, I put a 'block until done' node between the R output and the data output. 

[ckestler]

The purpose of this whole thing..

I'm creating an app that queries 5 tables from a database. I want to output these to one excel workbook, with formatted columns. The app is going to be placed in the Analytics gallery, where anyone in my company can access it.

I tried to put the template in a common folder, one that everyone has access to. When running the app from the gallery, the output is not formatted. It doesn't seem to be able to reference the template file.

The template works when I am running the app in Alteryx, but not in the gallery. 

Sorry for all the questions, it just seems like I'm hitting one wall after another. .

[SophiaF]

Hi @ckestler,

I'm testing this out as we speak (with my fellow rockstar Criston) - stay tuned! Just for design purposes, is there a certain group # of users that will be using this? It seems that we might need to have the template file local on the machine - would that be possible?

[ckestler]

Thank you!

Right now, the template is located on a drive/folder that all users have access to. Would that work? Or would each user have to have the template, say, on their own desktop?