formatRow Colors don't appear to work

[JamoCA]

When using formatRow(), the color documentation doesn't provide much information. I've tested multiple named colors and have only found "GREEN" to work (when using v2.7.0 with ACF 2016 & Java 11.)

While reviewing some other reported issues, I saw that RGB triplet values worked. (This should be added to the documentation.)

spreadsheet.formatRow( workbook, {bold="true", fgcolor="GREY"}, 1);  /* invalid color*/
spreadsheet.formatRow( workbook, {bold="true", fgcolor="192,192,192"}, 1); /* works */

Recommendation: Add a function to retrieve and display all possible "named" colors.

[cfsimplicity]

Hi James. Which other named colours have you tested and found not to work? I've just checked AQUA, BLACK and BLUE and they all worked on ACF2016 with Java 11, e.g.

spreadsheet = new luceeSpreadsheet.Spreadsheet();
workbook = spreadsheet.newXlsx();
data = [ "a", "b" ];
spreadsheet.addRow( workbook, data );
spreadsheet.formatRow( workbook, {bold="true", fgcolor="BLUE"}, 1 );
spreadsheet.download( workbook, "test" );

GREY is not a valid pre-defined colour in POI, you need to choose from:

GREY_25_PERCENT
GREY_40_PERCENT
GREY_50_PERCENT
GREY_80_PERCENT

There's a link to the full set of pre-set colours on the Formatting Options wiki page under color, where it also mentions that you can use RBG triplets with XLSX.

That page is linked from the formatRow() wiki page, and all of the other functions which allow formatting options to be passed. It's also on the main wiki home page.

I like your suggestion of a function to return the full set of pre-set colour names. Will look into that.

[cfsimplicity]

I've added getPresetColorNames() the develop branch. Could you please test? Thanks.

[JamoCA]

Thanks for the quick response! I tested the developer branch and the array of colors is awesome and will make it easier for developers to identify available colors without having to return to the online documentation (since it's always possible that these options may change.)

---

When I viewed the online documentation in the wiki yesterday, it looked like this:

fgcolor (string): See color for possible values

I didn't notice the link above under color option. As a result, I viewed the source code, saw that java.awt.Color was used, dumped it to get names and also searched online to find this list. I've always used a grey background color, so I immediately gravitated to that and lightGrey, grey and darkGrey didn't work.

Before posting, I saw this comment from 2016 regarding using RGB and it worked. (I prefer this method anyway and will probably generate my own color palette function.)

OPINION/DIFFERENT ISSUE: Regarding passing an invalid color as an option, what do you think about using a predefined fallback color versus throwing an error? IMHO, this is purely cosmetic and probably shouldn't be a showstopper... but you'd probably need to be aware of the full context so that white text on a white background isn't being outputted. (ie, maybe force a named color if RGB triplet is used and output format is XLS.)

[cfsimplicity]

Thanks for the feedback. I've tried to make the link to the predefined colours more obvious on the formatting options page.

As for the fallback colour... my gut reaction is to let the user (developer) handle the exception (which does tell you what you've done wrong) as they wish. Otherwise you could be left scratching your head as to why the colour you specified is ending up as something else. And as you say there are dangers in forcing an inappropriate colour, and complexities in trying to mitigate that.

It's a good thought, but I think I prefer to leave things as they are.

[cfsimplicity]

PS: I've amended the last commit in develop to improve the exception message if you use an invalid colour, so that it prompts you to use the new getPresetColorNames() method.