在 R 中创建 xlsx 工作表 - 字体颜色不起作用 [英] Creating an xlsx sheet in R - font colour not working

问题描述

我正在从事一个项目，我需要在该项目中自动生成来自特定类型数据框的数据电子表格.我已经阅读了有关如何执行此操作的有用解释 here，基于这个解释，我在 R 中创建了一些代码.Studio(专业版 1.1.383)使用 xlsx 包(0.5.7 版).我的代码从现有数据框 TABLE 生成数据电子表格.这是我正在使用的代码，其中包含一个用于创建可重现示例的模拟数据框:

#在这段代码中，我们创建了一个名为TABLE的模拟数据框#在实践中我们有一个更大的数据框，这里没有复制数据表 <- data.frame(Var1 = c(1, 6, 3, 9, 10, 4, 3, 5, 2, 2),Var2 = c(18.1, 14.2, 12.0, 21.3, 33.4, 16.0, 15.4, 8.6, 6.7, 12.9),var3 = c(0, 1, 1, 0, 1, 1, 0, 0, 0, 0));#创建工作簿、工作表、列和样式WB <- createWorkbook(type = "xlsx");SHEET <- createSheet(WB, sheetName = "My Data");行 <- createRow(SHEET, rowIndex = 1:5);单元格 <- createCell(rows, colIndex = 1:(1+ncol(TABLE)));STYLE_TITLE <- CellStyle(WB) +字体(WB，名称 =Calibri"，heightInPoints = 16，颜色 = "黑色", isBold = TRUE, isItalic = FALSE);#从数据框TABLE中添加数据addDataFrame(TABLE, SHEET, startRow = 4L, startColumn = 2L,colnamesStyle = STYLE_COLNAMES, row.names = FALSE);#添加标题setCellValue(cells[[2, 2]], "这是我的电子表格标题");setCellStyle(cells[[2, 2]], STYLE_TITLE);#保存工作簿saveWorkbook(WB, "My Pretty Workbook.xlsx");

这段代码成功生成了我想要的电子表格，一切看起来都是我想要的，除了标题以白色字体出现，这意味着它们在白色背景上看不到电子表格.在我看来，这与我用 color = "black" 指定 Font 的代码部分相反.我也尝试过使用 color = "#000000" 并且得到相同的结果.

为什么我的标题和信息文本以白色字体出现?我该如何解决这个问题?

解决方案

如果你在控制台输入 STYLE_TITLE，你可以在输出中看到(查看 $font$refcode>)，表示颜色被设置为 FFFFFF，即白色.下面是我在控制台中看到的内容(我只显示了输出的相关部分).注意最后一行的 FFFFFF:

STYLE_TITLE <- STYLE_TITLE <- CellStyle(WB) +字体(WB，名称 =Calibri"，heightInPoints = 16，颜色 = "黑色", isBold = TRUE, isItalic = FALSE)STYLE_TITLE

<块引用>

$font$ref[1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\n \n 

我不知道为什么会发生这种情况或如何让 Font 函数将black"或#000000"转换为 000000 的正确颜色代码.但是，我确实(奇怪地)发现其他颜色编码可以正常工作.例如，您可以使用颜色 "#010101"，它几乎是黑色:

STYLE_TITLE <- STYLE_TITLE <- CellStyle(WB) +字体(WB，名称 =Calibri"，heightInPoints = 16，color = "#010101", isBold = TRUE, isItalic = FALSE)STYLE_TITLE

<块引用>

$font$ref[1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\n<main:name val=\"Calibri\"/>\n <main:sz val=\"16.0\"/>\n <main:colorrgb=\"010101\"/>\n <main:b val=\"true\"/>\n</xml-fragment>}"

此颜色名称和其他颜色名称以及 rgb 颜色规范(例如，红色"、紫色"、#F372B6")也在 Font 输出对象中正确编码，并呈现颜色正如在电子表格输出中所预期的那样.

我知道您想要 100% 的黑色，但如果您能满足 99.6% 的黑色，这个解决方法应该可以完成工作.

I am working on a project where I need to automate the production of spreadsheets of data from a particular type of data-frame. I have read a useful explanation of how to do this here, and based on this explanation, I have created some code in R.Studio (professional version 1.1.383) using the xlsx package (version 0.5.7). My code generates a spreadsheet of data from an existing data frame TABLE. Here is the code I am using, with a mock data frame used to create a reproducible example:

#In this code we create a mock data frame called TABLE
#In practice we have a larger data frame with data that is not reproduced here

TABLE <- data.frame(Var1 = c(1, 6, 3, 9, 10, 4, 3, 5, 2, 2), 
                    Var2 = c(18.1, 14.2, 12.0, 21.3, 33.4, 16.0, 15.4, 8.6, 6.7, 12.9),
                    Var3 = c(0, 1, 1, 0, 1, 1, 0, 0, 0, 0));

#Create workbook, sheet, columns and styles
WB             <- createWorkbook(type = "xlsx");
SHEET          <- createSheet(WB, sheetName = "My Data");

rows           <- createRow(SHEET, rowIndex = 1:5);
cells          <- createCell(rows, colIndex = 1:(1+ncol(TABLE)));

STYLE_TITLE    <- CellStyle(WB) + 
                  Font(WB, name = "Calibri", heightInPoints = 16, 
                       color = "black", isBold = TRUE, isItalic = FALSE);

#Add data from data frame TABLE
addDataFrame(TABLE, SHEET, startRow = 4L, startColumn = 2L, 
             colnamesStyle = STYLE_COLNAMES, row.names = FALSE);

#Add title
setCellValue(cells[[2, 2]], "This is my spreadsheet title"); 
setCellStyle(cells[[2, 2]], STYLE_TITLE);

#Save workbook
saveWorkbook(WB, "My Pretty Workbook.xlsx");

This code successfully generates the spreadsheet I want, and everything looks how I want it, except that the title comes up in white font, which means they can't be seen on the white background of the spreadsheet. This seems to me to be contrary to the part of my code where I specify the Font with color = "black". I have also tried using color = "#000000" and this gets the same result.

Why is my title and information text coming up in white font? How do I fix this?

解决方案

If you type STYLE_TITLE in the console, you can see in the output (look under $font$ref), that the color is being set to FFFFFF, which is white. Below is what I see in the console (I've shown only the relevant portion of the output). Note the FFFFFF on the last line:

STYLE_TITLE    <- STYLE_TITLE    <- CellStyle(WB) + 
  Font(WB, name = "Calibri", heightInPoints = 16, 
       color = "black", isBold = TRUE, isItalic = FALSE)

STYLE_TITLE

$font
$ref
[1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org
/spreadsheetml/2006/main\">\n  <main:name val=\"Calibri\"/>\n  <main:sz val=\"16.0
\"/>\n  <main:color rgb=\"FFFFFF\"/>\n  <main:b val=\"true\"/>\n</xml-fragment>}"

I don't know why this is occurring or how to get the Font function to convert "black" or "#000000" into the proper color code of 000000. However, I did find (strangely) that other color encodings work properly. For example, you can use the color "#010101", which is almost black:

STYLE_TITLE    <- STYLE_TITLE    <- CellStyle(WB) + 
  Font(WB, name = "Calibri", heightInPoints = 16, 
       color = "#010101", isBold = TRUE, isItalic = FALSE)

STYLE_TITLE

$font
$ref
[1] "Java-Object{<xml-fragment xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\n
<main:name val=\"Calibri\"/>\n  <main:sz val=\"16.0\"/>\n  <main:color
rgb=\"010101\"/>\n  <main:b val=\"true\"/>\n</xml-fragment>}"

This and other color names and rgb color specifications (e.g., "red", "purple", "#F372B6") are also encoded properly in the Font output object, and the colors are rendered as expected in the spreadsheet output.

I know you wanted 100% black, but if you can settle for 99.6% black, this workaround should get the job done.

这篇关于在 R 中创建 xlsx 工作表 - 字体颜色不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！