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-p/1244  )

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. 

Service Report Generator_1.yxwz

SophiaF

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,

overwrite_excel_template.yxwz

results_test.xls