Instead of making the Excel writer even more complex, we should move to an Excel styler transformer that allows for setting the output style for each record (feature/row/cell) and the whole sheet, separately from the writer and entirely dynamically, based on attributes.
This should be similar to transformers like PDFStyler, ChartGenerator, HTMLReportGenerator etc. and allow for settings like font, colors, alignment, formatting, cell protection, formulae, borders, conditional formatting, cell validation/dropdowns, filtering, inserting images, transposing, pivot tables, hyperlinks etc. There could be an Excel-style table in the transformer that allows to assign an attribute to each cell and make the above settings based on other attributes.
There are already a number of ideas around regarding Excel that could be united in this solution (filters, styles, fonts, template).
This is a great idea. I'd be interested in knowing from this Idea, what are the key things that are needed. I'd love to see comments listing what functionality is absolutely needed, ranked in order. That would help prioritize options for future development. Also, please do vote for any Excel Ideas you need or add another Idea if the specific requirement you need is not represented as an Idea already.
Note there is an article showing that it's possible, although non-trivial to do a transpose in FME:
https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html
May I suggest:
1. Record (row) formatting - fill colour / text style / border style etc
2. Conditional formatting for fields (columns) - eg change fill colour depending upon cell value
3. Ability to add a formula on a given ROW eg typically at the bottom of data columns. Useful for SUM or AVERAGE of data in that column for example (This suggestion could be #1 !!!)
4. Ability to freeze cells (eg freeze header row typically) or freeze at a cell reference eg D2
5. Ability to add data filter to a row of choice (typically the header row)
6. Ability to add Text eg a Title to a Row or at a Cell Reference
........................
Excel Styler Dialog could possibly be split into 3 sections:
1. Table head area - Titles, Text, Logos etc (all nice to have but not essential - maybe in version 2+)
2. Data area - Field names (column names) and tabular data
3. Table foot area - ability to add formulas eg to give SUM or AVERAGE etc of columns
.........................
Hope these ideas help
.........................
Cheers
Howard L'My wishes, prioritized:
I agree with Aaron but would put Conditional formatting for fields (columns) - eg change fill colour depending upon cell value as number 1.
2. Headings to have a different style to data
My priority:
1: Handling tables (more and more common vs named ranges, tables also has styles, could be nice template)
2: row/feature styling
3: header styling
4: cell stylingHi @ekkischeffler, I am very pleased to let you know that in our latest betas (18247 at the time of this post), FME has an ExcelStyler. Download and install the latest beta to give this new transformer a try!