在r中具有特定字符值的颜色单元将导出到xlsx [英] Color cells with specific character values in r to export to xlsx

问题描述

我正在尝试完成一些本不应该那么困难的事情，但是它逃脱了我的尝试.

I'm trying to accomplish something that shouldn't be that difficult, yet it escapes my tries.

所以我有一个看起来像这样的R数据框:

So I have an R Data Frame which looks something like this:

MeanTemperature(ºC) Longitude Latitude Height FinalConsiderations
      5                 91ºW    152ºS    548m     Slightly Cooler
      16               185ºE     53ºN    722m      Unchanged
      22                16ºW      2ºS    206m Significantly Warmer

数据帧是经过过滤的分析数据的结果.最终产品是Excel(xlsx)，其中最后一列是整体分析的结论.因此，这些步骤都已处理完了，但是这些表相当大，因此能够进行着色(例如在RED中表示显着变暖"的颜色)会很好.

The dataframe is a result of a filtered analyzed data. The end product is an Excel (xlsx) where the final column is the conclusion of the overall analysis. So, those steps are all taken care of, but these tables are rather large, so it would be nice to be able to color, for example, in RED where it says "Significantly Warmer".

我已经尝试过使用上述数据框中的工作簿

I've tried using the workbook from said dataframe

  wb <- loadWorkbook(file) #where file is the location path

在这里，我要收集那些单元格，其中的单元格显示为红色的明显变暖"，然后将工作簿导出到xlsx.

Here I want to collect those cells where it reads 'Significantly Warmer' in red, and afterwards export the workbook to xlsx.

 fo1 <- Fill(foregroundColor="blue")   # create fill object # 1
 cs1 <- CellStyle(wb, fill=fo1)        # create cell style # 1
 sheets <- getSheets(wb)               # get all sheets

但是现在我找不到在wb设置中创建函数的方法，其中在$ FinalConsiderations列=='Scoolly Cooler'，单元格前景被涂成红色，然后导出到xlsx.

But now I can't find a way of making a function in the wb setting where at the $FinalConsiderations column == 'Slightly Cooler', the cells foreground is colored red, afterwards exporting to xlsx.

推荐答案

所以我将写出解决方法，以防万一有类似情况的人得到帮助.

So I'll write how I solved it, in case it helps anyone with a similar situation.

我下载了openxlsx软件包.

library(openxlsx) #recall the library

wb <- createWorkbook() # create a workbook

addWorksheet(wb, "Sheet", gridLines = TRUE) #add a worksheet to the workbook

writeData(wb, "Sheet", df) # write my analysis into the worksheet of the workbook, 
 #where df is the name of my data frame

然后，按照createStyle函数创建样式(请参见文档).

Afterwards, I create the styles, following the createStyle function (see documentation).

就我而言，我必须在数据中查找特定字符

In my case, I had to look for specific characters in my data

 warm1Style <- createStyle(fontColour = "#000000", bgFill = "#FFFF00")
 # here search for the respective HEX color-code and assign a name to the style

 conditionalFormatting(wb, "Sheet", cols = 1:5,
                  rows = 1:50, rule = "Significantly Warmer", style = warm1Style,
                  type = "contains")
 # account the condition where "Significantly Warmer" is contained in a cell,
# then apply the respective style to it (in this case, warm1Style)

就是这样，就像对工作簿中的任何短语或字符一样.

Then that's it, like so it can be done to any phrase or characters in the workbook.

最后，将工作簿另存为xlsx:

Finally, save the workbook as an xlsx:

saveWorkbook(wb, file, overwrite = TRUE)

感谢您的回复

这篇关于在r中具有特定字符值的颜色单元将导出到xlsx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！