Hi all,
I have read that the Excel writer supports colors for font and background.
What I can not find is how.
I have this scenario:
Based on the value of an attribute ("Yes" or "No") I want to color the font (or background) black (if "Yes") and red (if "No").
I would use the Conditional Values in the AttributeCreator, but can not find what to set for the color.
Is this possible, and if Yes how.
Any help is appreciated.
Erik Jan
You cannot do this in 2016, I don't know whether the enhancement mentioned here made it into 2017
https://knowledge.safe.com/questions/30191/insert-color-in-attribute-names-in-xls.html
I wish this would be an option:
Use a @Value(my_color) to set the font color of a cell:
Tried this in FME 2016.1 and 2017.0.
But not (yet) available.
I wish this would be an option:
Use a @Value(my_color) to set the font color of a cell:
Tried this in FME 2016.1 and 2017.0.
But not (yet) available.
https://knowledge.safe.com/idea/42340/styling-cells-in-excel-based-on-an-attribute-value.html
As a workaround it is not possible to have a conditional format set in a template file to do the styling?
I've done it by setting a custom number format, using a color name between straight brackets, e.g. [red] to get red text.
You could create the variables etc. and construct a batch file which calls a vb script and save it using a featurewriter.
The vb script sets your colours, font format etc.
SystemCaller would then be used to start the bat.
I've done it by setting a custom number format, using a color name between straight brackets, e.g. [red] to get red text.
I've done it by setting a custom number format, using a color name between straight brackets, e.g. [red] to get red text.
But again: this only allows hard-coded values (like red]) and will not take it from an attribute.
That will not get an error in FME, but does not allow you to open the xlsx file in Excel without errors.
But again: this only allows hard-coded values (like red]) and will not take it from an attribute.
That will not get an error in FME, but does not allow you to open the xlsx file in Excel without errors.
@erik_jan
@redgeographics
Only the palette is limited.
Not excel. You can extract and place celcolor. Using VisualBasicfoApplications
Here are some parts form some VB scripts I made (with the help of the for a and Microsoft helpfile on VBA of course)
Extract cellcolor:
cellcolor from cell in r,g,b
Function CelRGB2(MyRef As Range) As Variant
CelCol = MyRef.Interior.Color
CelRGB2 = (CelCol Mod 256) & ", " & ((CelCol \\ 256) Mod 256) & ", " & (CelCol \\ 65536)
End Function
------------------------
Place celcolor based on R,G,B:
Sub SerRGB3()
CelCol = ActiveCell.Offset(0, -1).Value
R = (CelCol Mod 256)
G = ((CelCol \\ 256) Mod 256)
B = (CelCol \\ 65536)
colmn = Split(ActiveCell.Address, "$")(1)
MyRef = "A" & ActiveCell.Row & ":" & colmn & ActiveCell.Row
Range(FormulaRange).Interior.Color = RGB(R, G, B)
End Sub
------------------
Thank you all for your answers.
I still believe it would be a huge improvement if the color could be set per attribute and in doing so would allow a color setting per cell.
me too i would love that!
In FME 2018 (currently beta) looks like this problem is solved.
I tested it and looks very promising. Via an new transformer it's possible to style one of more cel's. But you can also do this with an attributecreator or an Pythoncaller. So styling in Excel will be much more versatible.
In FME 2018 (currently beta) looks like this problem is solved.
I tested it and looks very promising. Via an new transformer it's possible to style one of more cel's. But you can also do this with an attributecreator or an Pythoncaller. So styling in Excel will be much more versatible.
I noticed the same during the FME 2018 webinar.
Looks good and I will try it, when I get another project that requires this.
Erik