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:

 

[ChrisTX]

@RLad235  the instructions below work for me.  The configuration is a little cryptic, but I avoid write-contention errors.

Macro and sample workflow:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

Instructions to configure multiple copies of the macro above, to write multiple Sheets to one Excel file, in order:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a...

Chris

[RLad235]

Hey Chris,

Thanks for the solution, I've managed to now write to multiple sheets.

The only issue I'm having is trying to format the outputted excel table.
I'd usually do this using the "Reporting" > "Table" tool, however when trying to write to multiple sheets like this I cant see to make the output 'Pretty', at a minimum at least have the columns auto set to the largest width.
Even creating a workbook, over-writing and preserving the formatting doesn't seem to work

We're you able to find a work around to this, I saw you had similar issues above?

Thanks
Rav

[ChrisTX]

@RLad235 no I haven't tackled the Excel formatting issue  yet.  I have a script, written in another language, that creates then calls a Powershell script to format an Excel file, after the Excel file is created.

But I haven't had a chance to convert the code to Alteryx yet.

Chris

[ChrisTX]

It looks like the new Control Container could help with this one challenge:

• ability to export different formats/different schemas without the need for a batch macro

...but if we have a different Control Container for each Sheet we want to write to, we'll have to list the same output file multiple times

And I would still need my Powershell script to implement the numerous formatting options listed above.