Hi,
I know the ExcelStyler is in beta but here goes the question anyway. Is it possible to style individual cells based on the attribute value?
I have tried different approaches that all color the entire row or entire colum as you see below.
Result if I filter through a tester and chose to style attribute "c".
Result if any of the attributes fail the tester, the entire row is colored.
This is what I need, to style individual cells based on their value
Thanks!
/Jacob
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Thanks for your quick reply!
I see what you mean but I'm not sure I can apply the solution to my problem.
I can't test for null or empty values as the entire column will get styled if just one value is null or empty.
And even if it had worked I would have to manually write in 30 test conditions and apply 30 ExcelStylers in the workflow.
What I think is needed is a conditional value in the styler itself maybe? The goal is to style cells with specific attribute values.
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
I think you can apply the technique with conditional value setting. This workspace example sets background color to cells having a value '0'.
fme2018-beta-excelstyler-example-2.fmwt (FME 2018.0 beta build 18248)
However, as you mentioned, it's too tedious to apply it to 30+ columns. Probably Python scripting would be a better approach if the number of columns is large.
In my observation, the cell style is defined as a string value, which is set to an attribute called "<field name>.formatting" with the ExcelStyler. The format of the style has not been documented yet, but it's easy to reverse-engineer by inspecting the resulting features output from the ExcelStyler. This workspace example contains a PythonCaller and creates the same result as the example above.
fme2018-beta-excelstyler-example-3.fmwt (FME 2018.0 beta build 18248)Hope this helps.
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
One last question is whether you can set "if v == null or missing" as value '0' might be in one of the fields.
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
You can use the fmeobjects.FMEFeature.getAttributeNullMissingAndType method.
See also this example: :Updated] fme2018-beta-excelstyler-example-4.fmwt (FME 2018.0 beta build 18248)
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
FME 2018: Allow to set style to individual cells with ExcelStyler
The ExcelStyler supports conditional values.
I've attached the example workspace form 2018. I you wanted a different set of conditions for, say, the Quality attribute, then you'd need a second ExcelStyler - as per my example workspace. Results would look something like:
2018 workspace: excelcellfillexample.fmwt
This second workspace illustrates how you can manually set all the styles and conditions in a single AttributeCreator: excelcellfillexample2.fmw
The ExcelStyler supports conditional values.
I've attached the example workspace form 2018. I you wanted a different set of conditions for, say, the Quality attribute, then you'd need a second ExcelStyler - as per my example workspace. Results would look something like:
2018 workspace: excelcellfillexample.fmwt
This second workspace illustrates how you can manually set all the styles and conditions in a single AttributeCreator: excelcellfillexample2.fmw
For example, how can you set the same background color to all cells whose values are null or missing, which lie scattered in multiple columns like this?
Maybe using 5 ExcelStylers is no problem. However, if you have 30+ columns with the same condition, how? It must be too tedious to put 30+ ExcelStylers with the same conditional value setting.
See also this idea: FME 2018: Allow to set style to individual cells with ExcelStyler
The ExcelStyler supports conditional values.
I've attached the example workspace form 2018. I you wanted a different set of conditions for, say, the Quality attribute, then you'd need a second ExcelStyler - as per my example workspace. Results would look something like:
2018 workspace: excelcellfillexample.fmwt
This second workspace illustrates how you can manually set all the styles and conditions in a single AttributeCreator: excelcellfillexample2.fmw
@takashi is absolutely right about the particular problem and I don't see how it's possible in the current version of the ExcelStyler. You would need a styler for every attribute instead of just styling cells with specific values based on certain conditional circumstances.
@takashi, your solution using Python is excellent and I've used it along with the AttributeValidator to color code different "error" messages, however the script is not working above FME Beta 18257.
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Hi @takashi
With my sparse Python insight I have tried getting your script to work in the newest version of FME 2018, however the script doesn't have an effect after version b18257. If anyone I thought you might be able to pinpoint what has changed?
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Hi @j2, I tested the latest example with FME 2018.0 build 18284 (official release) and it worked just fine. Try upgrading your FME 2018 to the latest version.
Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.
fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)
Thanks for the answer!
Hi @j2
I wonder if conditional formatting is the solution you are looking for.
When you use ExcelStyler to style a cell, you assign particular style to that cell, and despite the fact that the style depends on the cell value when you assign it, this dependency is not maintained once the data is output. I.e. if you change cell value, the style will not reflect the change - and the end user might end up with a spreadsheet with misleading coloring.
With conditional formatting, the dependency between the cell value and cell style will be maintained in Excel. If the data is edited by the end user, the styles will be adjusted accordingly.
Conditional formatting needs to be defined in the template file. In Excel, please see Home > Conditional Formatting > Manage Rules... to add new rules or alter existing rules. In FME, please specify the Template File for the Excel Writer.
Please check attached template for a simple demo.
Hi!
It seems that the solution using Python to style individual excel cells doesn't work in newer versions of the xls writer. The reason for this is that the formatting attribute isn't automatically written out but works if I manually write out the formatting attributes.
Since it's still not possible to do styling of individual excel cells using ExcelStyler (without using a transformer for each attribute), I was wondering if I have to slightly change to code in the script to acutally write out the styling attributes?
style2 = 'version<semicolon>1<semicolon>' \
+ 'background_color<semicolon>1<comma>1<comma>0.5'
for attr in attrNames:
v = feature.getAttribute('_concatenated')
isNull, isMissing, _ = feature.getAttributeNullMissingAndType(attr)
attrTest = "'" + attr + "'"
if isMissing and attrTest not in v:
feature.setAttribute('%s.formatting' % attr, style2)
Thanks!
/Jacob
@Takashi Iijima I have a similar issue, where I want to highlight individual cells in a single row and I don't want to use nested ExelStyler as I have got more than 30 columns. I went through your example of using Python caller, the fme script runs fine itself as I believe it is using an older version of FME Excel Writer, however, I am using FME 2020.2 and the script is not working as is. Please can you advise.
Thanks,
Astha
@Takashi Iijima I have a similar issue, where I want to highlight individual cells in a single row and I don't want to use nested ExelStyler as I have got more than 30 columns. I went through your example of using Python caller, the fme script runs fine itself as I believe it is using an older version of FME Excel Writer, however, I am using FME 2020.2 and the script is not working as is. Please can you advise.
Thanks,
Astha
Unfortunately, in later versions of the excel writer, you need to expose the .formatting attributes that are created in the python script for this to work.