OpenXML Excel filters: Exclude color option not working

[Former user]

Original issue 338 created by [e-mail] on 2013-05-10T09:40:51.000Z:

What steps will reproduce the problem?
1. Mark cells or colums in Excel file with red, yellow, blue color (text and/or background)
2. Use OpenXML filters and set those colors to be excluded
3. Create OmegaT project

What is the expected output? What do you see instead?
I would expect that words/phrases with those colors won't appear to be translated in OmegaT. But they are shown. (Using the exclusion option for whole colums however seems to work). Moreover, various numbers appear that are not included in the excel file (this might be another issue).

What version of the product are you using? On what operating system?
6.0.2.1 (Rainbow) and 2.6.3 update 1 (OmegaT)

Please provide any additional information below.
See screenshots and excel file attached. I think this is a very important options, since customers only want certain marked cells to be translated. This could probably be related to an older discussion of 2011: https://code.google.com/p/okapi/issues/detail?id=144

[Former user]

Comment 1. originally posted by @ysavourel on 2013-05-11T11:18:04.000Z:

[Former user]

Comment 2. originally posted by @ysavourel on 2013-05-11T11:19:36.000Z:

Related to issue comment 21.7

[Former user]

Comment 3. originally posted by @ysavourel on 2014-06-13T22:07:16.000Z:

Any chance to look at this at some point?
The issue comment 21.7 is also related i think.

[Former user]

Comment 4. originally posted by [e-mail] on 2014-06-26T18:46:40.000Z:

Would be great if somebody could take care of it. It's an oftenly used way to mark certain phrases to be translated and therefore would be a really usefull option

[Former user]

Comment 5. originally posted by @ysavourel on 2015-03-18T18:44:08.000Z:

[Former user]

Comment 6. originally posted by [e-mail] on 2015-03-18T18:53:07.000Z:

I you plan on fixing it, I would also suggest to change the option from "exclude colors" to "only include the following colors". In my personal experience people normally tend to mark those cells that need to be translated instead of marking those that do not need a translation.

[Former user]

Comment 7. originally posted by @ysavourel on 2015-03-18T19:19:29.000Z:

Thanks, that's good feedback.

I've seen it the other way (color = exclude) as well, so I'll have to figure out the best way to support both use cases.

[Former user]

Comment 8. originally posted by [e-mail] on 2015-03-18T19:25:58.000Z:

Could be an idea also to have an option for the text color for inclusion/exclusion, not only for the background color.

One more thing: Could you make it more clear on the frontend, which color you mean (text color / backgroud color)? A user also might ask himself, if the text color selection applies to all sheets (which would be best) or just to the columns that are marked in addition to the color selection.

My personal favourite would be to apply the color selection to all sheets, not only to the selected columns (current implementation is an AND operation, right? So color + column?).

[Former user]

Comment 9. originally posted by @ysavourel on 2015-03-18T19:32:47.000Z:

+1 for the checkbox (or maybe radio button)

I'm not even sure what the current behavior is supposed to be, as it doesn't seem to be working.

Applying the color-based exclusion makes sense to me and is consistent with what I've seen in other tools.

For foreground vs background color, I don't remember what other tools do. In your experience, is using foreground or background color the more common practice?

[Former user]

Comment 10. originally posted by [e-mail] on 2015-03-18T19:56:38.000Z:

In my experience, using a background color for explicit inclusion for translation is the most common way.

A radio botton mit include/exclude and another one with foreground/background color would be the most flexible, still understandable way.

It probably makes sense to also show the RGB color in the selection list (in brakets), e.g.
YELLOW (255/255/0).

Or maybe let the user specify a specific RGB value. Otherwise one can't be sure, what yellow (bright, pale etc.) the rule applies to.

[Chase Tingley]

I've pushed an initial implementation of a fix to my fork here. For now, this is implementing only the basic behavior of excluding cells with solid-color backgrounds of one or more specified colors.

A few notes:

• Things it does not currently support: theme-based colors, indexed colors (which are a legacy thing, and usually only found in old OpenXML docs), or font colors.
• The "Standard Colors" that Office 2011 provides for Excel spreadsheets do not match the RGB values for the equivalent color names in Rainbow. This means that even with this fix, the Rainbow UI still generates filterconfigs that mostly don't work, except for a couple of colors.
• I'm deferring some of the more advanced logic discussed in this thread (inclusion vs exclusion toggles, etc) for a separate enhancement. Baby steps.

[Chase Tingley]

More about RGB values. Here are the values that Rainbow currently maps to color names:

• FF0070C0 --> "blue"
• FF00B0F0 --> "light blue"
• FF00B050 --> "green"
• FF7030A0 --> "purple"
• FFFF0000 --> "red"
• FFFFFF00 --> "yellow"
• FFC00000 --> "dark red"
• FF92D050 --> "light green"
• FFFFC000 --> "orange"
• FF002060 --> "dark blue"

Here are the values of the "Standard Colors" that Excel expects:

• FF800000 --> dark red
• FFFF0000 --> red
• FFFF6600 --> orange
• FFFFFF00 --> yellow
• FFCCFFCC --> light green
• FF008000 --> green
• FF3366FF --> light blue
• FF0000FF --> blue
• FF000090 --> dark blue
• FF660066 --> purple

[Sebastian Ebert]

This issue seems to be related to https://bitbucket.org/okapiframework/okapi/issues/390/openxmlfilter-excel-exclude-columns-option which is fixed.

So part if this issue (338) is fixed, the rest is more like a feature request, right?

[Chase Tingley]

I've been sitting on this code, actually, so it's not fixed on the dev branch. I still haven't implemented the color alignment. I probably should.

[Chase Tingley]

Fix issue #338 - Parse fgColor fill data from styles XML

This implements color-based exclusions for cells with solid
backgrounds.  (Confusingly, OpenXML calls this "fgColor".)  It
doesn't handle theme-based colors of legacy (index-based) colors.
Color exclusions are still based on RGB values, but the values
mapped to the colors in the Rainbow UI are not correct for current
versions of OpenXML.

→ <<cset 2630415bd134>>

[Chase Tingley]

I dusted this off and made the UI changes, so now it is fixed.

We should split the additional options off into a separate ticket. Sebastian, if you have particular thoughts about how to organize, feel free to do the honors.