Enhance options for Excel output

[ChrisTX]

Please improve the Excel XLSX output options in the Output tool, or create a new Excel Output tool, 
or enhance the Render tool to include an Excel output option, with no focus on margins, paper size, or paper orientation

The problem with the current Basic Table and Render tools are they are geared towards reporting, with a focus on page size and margins.
Many of us use Excel as simply a general output method, with no consideration for fitting the output on a printed page.

The new tool or Render enhancement would handle different formats/different schemas without the need for a batch macro, and would include the options below.

The only current option to export different schemas to different Sheets in one Excel file, without regard to paper formatting, is to use a batch macro and include the CReW macro Wait a Second, to allow Excel to properly shut down before a new Sheet is created, to avoid file-write-contention issues.

Including the Wait a Second macro increased the completion time for one of my workflows by 50%, as shown in the screehshots below.

I have a Powershell script that includes many of the formatting options below, but it would be a great help if a native Output or Reporting tool included these options:

Allow options below for specific selected Sheet names, or for All Sheets

AllColumns_MaxWidth: Maximum width for ALL columns in the spreadsheet. Default value = 50. This value can be changed for specific columns by using option Column_SetWidth.

Column_SetWidth: Set selected columns to an exact width. For the selected columns, this value will override the value in AllColumns_MaxWidth.

Column_Centered: Set selected columns to have text centered horizontally.

Column_WrapText: Set selected columns to Wrap text.

AllCells_WrapText: Checkbox: wrap text in every cell in the entire worksheet. Default value = False.

AllRows_AutoFit: Checkbox: to set the height for every row to autofit. Default value False.

Header_Format: checkbox for Bold, specify header cells background color, Border size: 1pt, 2pt, 3pt, and border color, Enable_Data_Filter: checkbox

Header_freeze_top_row: checkbox, or specify A2:B2 to freeze panes

Sheet_overflow: checkbox: if the number of Sheet rows exceeds Excel limit, automatically create the next sheet with "(2)" appended

Column_format_Currency: Set selected columns to Currency: currency format, with comma separators, and negative numbers colored red.

Column_format_TwoDecimals: Set selected columns to Two decimals: two decimals, with comma separators, and negative numbers colored red.
Note: If the same field name is used in Column_Currency and Column_TwoDecimals, the field will be formatted with two decimals, and not formatted as currency.

Column_format_ShortDate: Set selected columns to Short Date: the Excel default for Short Date is "MM/DD/YYYY".

File_suggest_read_only: checkbox: Set flag to display this message when a user opens the Excel file: "The author would like you to open 'Analytic List.xlsx' as read-only unless you need to make changes. Open as read-only?
vb code: xlWB.ReadOnlyRecommended = True

File_name_include_date_time: checkboxes to add file name Prefix or Suffix with creation Date and/or Time

========

Examples:

My only current option: use a batch macro, plus a Wait a Second macro, to write different formats/schemas to multiple Sheets in one Excel file:

 

Using the Wait a Second macro, to allow Excel to shut down before writing a new Sheet, to avoid write-contention issues, results in a workflow that runs 50% longer:

 

[CristonS]

hi @ChrisTX thanks for your idea! Also, you make a good point about exporting different schemas to different sheets in one excel file.

[patrick_digan]

@ChrisTX A lot of great ideas in your post.

[AlexSTeryx]

Hi @ChrisTX and Alteryx developer,

maybe - I don’t know whether it is technical possible - to create first for each output a sort of „virtual“ sheet, storing them in a „cache“ and put them together in one excel file in a final step?

just an idea.

best

alex

[ChrisTX]

Update: How to avoid using the Wait a Sec macro to write out different formats to different Sheets in the same Excel file

Option 1: best option: Use Parallel Block Until Done AND use a macro to write to Excel:

• Seems to work consistently, without write contention errors

I was able to finally achieve my goal (not using Wait a Sec) by using Parallel Block Until Done and another macro found here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Unable-to-Create-Backup-Block-Until-Do...

original macro from: @patrick_digan, original macro name: Excel Output ordering.yxmc

Option 2: mentioned in the original Idea above: slower (uses the CReW macro Wait a Sec)

• For one workflow that writes out 38 separate Sheets, this option caused a 50% increase in run time

Other attempts that don't always work:

• I tried using the CReW macro Parallel Block Until Done, but I still get write contention errors when I’m writing to a network drive. Details below.
  • I get one or more write contention errors, like: Unable to create backup of C:\Users\...\My Output File.xlsx to C:\Users\...\My Output File.xlsx.bak: Access is denied. (5)
  • If I write to a folder on my laptop, I generally don’t get write contention errors
  • The errors seem to depend on the location of the output file (on a network drive / slower write = write contention errors)

I have eloquently presented the value of this Idea to @NicoleJohnson and I have confidence her team will eventually deliver this functionality within Alteryx:

Easily write out many sheets to one Excel file, in a specific order, with different layouts on each sheet, without using a macro…..because:

• It is a very common need, even for beginners
• Needing to use a CReW macro and a batch macro to write out different formats will prevent many users from developing a solution that works
• For such a common need, this “should” be easy
• The basic formatting options above for Excel sheets are not easy without a Powershell script

[ChrisTX]

This idea is up to 7 votes, with a status of Accepting Votes.

Do you have a few colleagues who would be willing to click Like, to get to 10 votes?

We use Excel for most output, so enhanced functionality would really help.

Here's the link to forward to a few people...

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...

/end shameless plug

[ChrisTX]

Another user who needs better Excel output options:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Render-Tool-Excel-Column-Width-Formatt...

Render Tool - Excel - Column Width Formatting

I have source data with about 75 columns that I want to format and output as an Excel file.
The Render Tool seems to require setting a Page Size, but that's irrelevant for an Excel file...I'm not trying to print it.

Since this Idea now has 12 votes, is there any chance the Alteryx team will consider providing some new functionality, soon?

[ChrisTX]

My comments under this post Re: Clear the output file before running a macro a... - Alteryx Community

show the not-so-easy steps to write out multiple Excel Sheets, in order, with different formats, to one Excel file while avoiding write contention errors.

This solution is too difficult for most beginners.

For a task like this, that is so common for so many users, using a simple tool like Alteryx, the solution shouldn't be this hard.

Still hoping @NicoleJohnson and her team will deliver an impressive new solution.

[ChrisTX]

After @NicoleJ delivers the new Control Container functionality, I hope her team can help with a solid solution to write different layouts to multiple sheets in one Excel file without using a macro, and without write-contention errors.

[RLad235]

I need this, having the same issue
Cant output to several sheets even using BUDs due to separate inputs.
Adding wait timers or throttles does not need like a efficient way and slows down the whole flow