writexl package with ability to add highlighting and change font colors

[kbroman]

Suppose you want to encode data in an Excel file by highlighting cells in different colors, or changing font colors; it'd be great to have an R package that could create such. Asking for a friend. 😉

[karawoo]

Omg Karl 😂

[sarahsupp]

OMG I was just asked if an R package could do this [seriously] for a business. An employee wants to automate creating daily reports, but be able to output the summary into their existing excel files with color coded cells and font that sit on the server. It must be done this way, because these excel files are what everyone else uses to base the day's decisions on. This is not a drill.

[mschubert]

I've seen a lot of hate on this topic, but I was always missing a constructive point of view on why this could be helpful.

Yes, obviously, encoding information in a non-machine-readable way is bad. I'm not denying that.

The point is, there will be people who will not access this information encoded in the file programmatically but rather look at it in a tool like Excel.

Why do we consider providing an additional visual access to numerical data (e.g. values as colour scale) to be a bad thing? As long as all the information is available numbers, there is no reason why it should be viewed as inferior to make them human-readable as well.

[carlganz]

Working in public health, I have been dragged down this rabbit-hole more times than I would like to admit. Always found that openxlsx was more than sufficient.

[jennybc]

There are several packages that do this already! Such as

• openxlsx
• XLConnect
• xlsx

[karawoo]

@mschubert Yes, if the highlighting was truly redundant to data that is present in the table I wouldn't have such a problem with it. But in data I've received, this has never once been the case. Highlighting was always used instead of putting the values somewhere in the table, and often there was no indication whatsoever of what the colors represented. That said, I'm definitely guilty of oversimplifying to "highlighting = BAD" when I really mean "using highlighting as data without including the data in the table = BAD".

[jennybc]

@mschubert The big distinction is formatting as data (EVIL 👿) and conditional formatting that reinforces the data (fine 🙂).

[mschubert]

@karawoo @jennybc Totally agree.

[jennybc]

I suppose the worst of all is manual conditional formatting: in that case the formatting and the data can contradict each other 😬.

[batpigandme]

I'm not a big conditional formatter, myself, but you can do it with Huxtable

[jennybc]

I kind of like conditional formatting and the only reason I don't use it more often in R is because I perceive it to be a PITA to implement. I haven't yet delved into packages like Huxtable but I'm pretty keen on the idea.

[batpigandme]

@jennybc I'm not against them, just rarely use 'em (partially bc they can be hard to read). Huxtable has a bit of a a ways to go (currently you need to add the names of your columns as the first row of a data frame…and then you're formatting by index, and it gets a bit crazy), but proliferation of table-rendering package makes me think it's definitely in-demand.

[jennybc]

Yeah there should a nice grammar for mapping certain columns in your table (or functions thereof) to format attributes of other columns. The interface does seem hard to get right, but super important.

[kbroman]

I'm going to close this, because it was really just a Friday afternoon joke, and it shouldn't be cluttering the proper topics.