How do I add fill colors and borders to my Excel data output? Render seems remove it

I read this can be done using the table and render tools and it seems to work, but my issue is that it keeps overwriting my other sheets from my "data output" tool.

My current flow looks like this:

Data Input = Workbook1.xlsx

Workbook1.xlsx

Sheet1WB1

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet1WB2

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet2WB2

Block until done > Data Output (Results.xlsx)

This results in the following Output

Results.xlsx

Sheet1WB1

Sheet1WB2

Sheet2WB2

a single Excel workbook with multiple sheets (desired result).

What I am trying to add:

I would like to add formatting to "Sheet1WB2", but when I use the table and render tools the entire output file (Results.xlsx) gets overwritten and only shows Sheet1WB2 with the formatting.

Workflow

Input

Designer Cloud

Error Message

Help

Accepted answers

All comments

gautiergodard

Hi @alteryxisconfusing would you be able to share the workflow or sample inputs/outputs?

The table + render tool combinations should the appropriate way to output formatted cells with fill color etc so I am thinking you may have one of the tools not configured correctly.

Thank you!

alteryxisconfusing

Hi, I attached the input files (Workbook1 and Workbook2) and I made a desired version of the Results.xlsx.

This is what I am trying to achieve.

Hi @alteryxisconfusing

You could try something like this! You'll just need to enter a new file path for the input tools and in formula tool 24.

Hope this helps.

Please like, and mark this thread as resolved if this provides an answer to your question.

Blend.yxmd

InputAllSheets.yxmc

binu_acs

@alteryxisconfusing one way of doing this

Render_1011.yxzp

gautiergodard

@alteryxisconfusing please accept this solution as complete if it worked for your issue

alteryxisconfusing

Thank you for putting this together, it's very helpful!

Couple of questions:

1. I changed the formula name of "tab" to something else and noticed it created a sheet1 in the output with a summary of the sheets. Is this because of a setting in the table tool? I think it's the group by that will help eliminate the sheet1. Just want to understand how this logic works.

2. Is it possible to arrange the sheet order? I noticed when I run the tool with more inputs it randomly places the sheets in the output.

3. Is it possible to output to .xlsx with an Excel filter on the headers? That way someone could get the workbook and easily filter the data.

gautiergodard

Hey @alteryxisconfusing

The group by functionality in the table tool creates separate tables for each value present in the column you are grouping by. In addition, in the output of the table tools, the group by functionality creates a new column with the value you are grouping by. In the example I'd provided, the group by was used only to get the sheet name value to be attached to the tables, so that it can be referenced in the later layout tool to create the different tabs in the output.

2. Is it possible to arrange the sheet order? I noticed when I run the tool with more inputs it randomly places the sheets in the output.

Sheet order is sorted alphabetically - you can add a 1. ,2.,3.....etc in front of each sheet name and it'll output sequentially in the order you specify. .

3. Is it possible to output to .xlsx with an Excel filter on the headers? That way someone could get the workbook and easily filter the data.

Short answer is no, however users can definitely add a filter on their end once the output has been created. There may be some ways to do what you're asking by using run command scripts but I dont think the effort will be worth the benefit here.

Hope this helps please don't forget to mark the solution as complete if your questions have been answered!!

alteryxisconfusing

Ahh so the basis of separating the sheets is based on the "Tab" group aspect in the final render.

Is it possible to add a crosstab/pivot output with this? I tried doing it, but I keep getting an error in the output saying "we found a problem with some content". This only appears when I try adding the crosstab > table, but I keep the group of Tab in place.

Otherwise:

- This solution seems to work for what I need, I even added join statements.

- I took your suggestion on sheet order and that worked, thank you! Only downside is I had to put static formulas in for the numbers since I don't know how to have the formula automatically cycle the number inputs.

- Okay, no filter is not a big deal, but I definitely would like to adjust one of the input files to look like a crosstab/pivot table in the output.

gautiergodard

Hello @alteryxisconfusing

Would you be able to share the workflow you are using and a sample of what you are looking to crosstab?

Glad the rest of solutions worked!

alteryxisconfusing

Hi,

Here's the input of the raw data I"m using and the desired output.

I basically changed the input file of workbook1 in the workflow you put together and then I added a "cross tab" tool before the original "table" tool that ultimately leads to the original "union" tool. I'm having trouble with the output since it keeps throwing an error and the format corrupts.

PivotInputData1 - Copy.xlsx

PivotInputData1 - Desired Pivot Table - Copy.xlsx

gautiergodard

@alteryxisconfusing answer was provided above, reattaching here!

pivottest.yxmd

alteryxisconfusing

Whoops! Didn't see the page 2 on the comments ha

thank you so much for the help so far! Will give this a shot right now.

Will the pivot test still have the logic to group by "Tab"? I still want it to go in as an outputted Excel sheet.

gautiergodard

The approach can be leveraged. You can always create a new "Tab" field using a formula tool in order to create a tab based on values which are not present in your data.

Please mark all posts on this thread containing solution as accepted in order to help others answer similar issues in the future!

alteryxisconfusing

Hm so I cut down the size of the text in my raw data and it seems to work if it's only a small set of text.

I'm wondering if perhaps I need to increase the "field size" to be larger than 2048?

alteryxisconfusing

Hm tried increasing the field size and no luck....my max character length is only 187 and max rows are 2186 (input file).

Looks great if I cut it to a size of 30 rows (input file)

gautiergodard

@alteryxisconfusing Could you share the full workflow? Suspect there may be inconsistencies with how the reporting tools were formatted.

alteryxisconfusing

Seems like it was the "paper size" in the render tool that's leading to the issue. I put a custom size of 40x20 and that removed the issue, but now the columns in the output are too large. Is there a way to adjust the sizes of all the columns or do I have to adjust them column by column in the table tools?

gautiergodard

@alteryxisconfusing you should be able to adjust the width of the output at the bottom of the render tool configurations.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703