Solved

Style Excel cell values

jatoxa
Rank
Badge +2

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
icon

Best answer by takashi 20 January 2018, 11:23

View original

17 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.

fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)

 

jatoxa
Rank
Badge +2

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,

 

 

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.

 

 

 

 

 

takashi
Rank
Userlevel 2
Badge +17

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.

jatoxa
Rank
Badge +2

Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.

fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)

 

Thank you so much for these examples! The Python solution solves it completely but is not something I would've stumbled upon easily. It is a tremendous help!

 

 

One last question is whether you can set "if v == null or missing" as value '0' might be in one of the fields.

 

takashi
Rank
Userlevel 2
Badge +17

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)

 

jatoxa
Rank
Badge +2

Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.

fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)

 

This was perfect, I can't thank you enough!

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.

fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)

 

I have posted an Idea regarding enhancement of ExcelStyler.

 

FME 2018: Allow to set style to individual cells with ExcelStyler

 

 

M
Badge +2

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
Rank
Userlevel 2
Badge +17

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

Hi @MarkAtSafe, thanks for posting an example on conditional value setting in the ExcelStyler. However, I'm afraid that the point of @j2's question is not here. In my understanding, the requirement is to apply the same condition on individual cell value to many (e.g. 30+) columns with just a single ExcelStyler, ideally.

 

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?

 

I think you need to use five ExcelStylers to do that.

 

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
jatoxa
Rank
Badge +2

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

Hi @MarkAtSafe,

 

@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.

 

 

jatoxa
Rank
Badge +2

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?

takashi
Rank
Userlevel 2
Badge +17

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.

 

jatoxa
Rank
Badge +2

Hi @j2, it's an interesting challenge. Perhaps this workspace demonstrates fundamental technique.

fme2018-beta-excelstyler-example.fmw (FME 2018 beta build 18244)

 

You are absolutely right, it worked all along. The problem arise when I dynamically create Excel sheets and the script doesn't work. If I write to one specific sheet it works as before.

 

Thanks for the answer!

 

 

L
Badge

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.

jatoxa
Rank
Badge +2

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
A

@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

ebygomm
Rank
Userlevel 1
Badge +10

@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.

Reply


Community Stats

28,668
Posts
109,359
Replies
36,536
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.