I have a need to export results from R into MS Excel.
The package openxlsx works very nicely for my needs apart from one thing: I need to create a text box which would contain some text based on data in R.
Are there any R packages that will do this?
Thanks.
There's also a new Excel-writing package, writexl:
https://cran.r-project.org/package=writexl
What is special about your "text box"? I don't understand how it differs from any other data you want to write into Excel from R.
Well, most R-to-Excel packages I've tried do well at writing to cells within sheets. Due to specific works needs I also need to add information to a textbox which 'floats' on top of a sheet. It doesn't fill the contents of a cell or range of cells.
The example below shows data inserted into a cell and text and data into a text box. This is what I would like to achieve. writexl for example will write a dataframe to cells but I don't see an option for writing to a textbox.
Ha, yea, I also sometimes have to work with some of the less "tidy" excel features. Unfortunately, I don't know of a perfect solution for what you want (at least not for .xlsx files).
The xlsx package provides access to the Apache POI java API, which implements a lot of excel features. Not all of them are documented in the R package, but you can use rJava to call them directly.
Unfortunately, it hasn't been updated in a while, and one feature that is missing is that with a .xlsx file, you won't be able to preserve the existing text boxes (or images or graphs) if you want to add new ones. I believe that if the java files in the xlsxjars package were updated, you would be able to do it in .xlsx files as well. (Note that XLConnect also uses Apache POI, but I don't think it gives access to the full API like xlsx does)
For xls files, you can add text boxes (while keeping the old ones), and here's the code:
library(xlsx)
# If file already exists:
# wb <- loadWorkbook(file_path)
# Otherwise
wb <- createWorkbook(type = "xls")
new_sheet <- createSheet(wb)
all_sheets <- getSheets(wb)
sheet <- all_sheets[[1]] # First sheet
# For xlsx documents, the current version of xlsx only has
# `sheet$createDrawingPatriarch()` which will lose all
# existing text boxes and graphs in the document.
# If you're okay with that, you could replace the function below.
shapes <- sheet$getDrawingPatriarch()
# More about how to place the textbox here:
# https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDrawing.html#createAnchor-int-int-int-int-int-int-int-int-
new_text_box <- shapes$createTextbox(
shapes$createAnchor(
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(5),
as.integer(5)
)
)
# More about text formatting here: (oops there was a typo here)
# https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFTextBox.html
new_text_box$addNewTextParagraph("Test paragraph")
new_text_box$setFillColor(as.integer(255), as.integer(255), as.integer(255)) # Sets background to white
saveWorkbook(wb, "test.xlsx")
Excellent, thank you.
I'll give it a try and update with how I got on. Is it also possible to format text within a textbox, e.g. red font for some text, bold for other text?
Here is one way that you could add info based on R outputs. You can use ggplot and openxlsx and add the "text box" as an image to the workbook. Granted, this is a hacky solution, but it will get you what you need as long as the text box will not need to be edited once it is in the excel workbook. Here is the code:
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
ggplot(data = NULL, aes(x = 1:10, y = 1:10)) +
geom_text(aes(x = 5, y = 10), label = "This is a textbox") +
theme_minimal() +
theme(axis.text = element_blank(),
axis.title = element_blank(),
panel.grid = element_blank())
insertPlot(wb, sheet = 1, xy = c(4, 4), width = 2, height = 0.5)
openXL(wb)Yes, I believe so, but unfortunately I don't know much about this.
Sorry, there was a typo in the URL I posted about formatting, so it should have read this: (this is where I found the setFillColor method) https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFTextBox.html
Clicking around a bit got me to this page, which talks more about the text formatting:
https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRichTextString.html
I haven't used those functions though, so I can't offer too much help, sorry!
In response to tbradley:
Thanks for the suggestion, but ugh, too hacky!
I'm just getting the opportunity to try this now.
shapes$createTextbox seems to work without throwing an error.
However, when I try new_text_box$addNewTextParagraph("Test paragraph") then I get the error message:
Error in new_text_box$addNewTextParagraph :
no field, method or inner class called 'addNewTextParagraph'
Any ideas why this might be happening?
Oh sorry about that - I was flipping between .xls files and .xlsx files but didn't totally understand how they were different and so left in some of the code that only worked for a .xlsx file.
Apparently there's no "addNewTextPagraph" method for .xls files, so I set the text in a slightly different way.
library(xlsx)
# If file already exists:
# wb <- loadWorkbook(file_path)
# Otherwise
wb <- createWorkbook(type = "xls")
new_sheet <- createSheet(wb)
all_sheets <- getSheets(wb)
sheet <- all_sheets[[1]] # First sheet
# For xlsx documents, the current version of xlsx only has
# `sheet$createDrawingPatriarch()` which will lose all
# existing text boxes and graphs in the document.
# If you're okay with that, you could replace the function below.
shapes <- sheet$getDrawingPatriarch()
if (is.null(shapes)) shapes <- sheet$createDrawingPatriarch()
# More about how to place the textbox here:
# https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFDrawing.html#createAnchor-int-int-int-int-int-int-int-int-
new_text_box <- shapes$createTextbox(
shapes$createAnchor(
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(0),
as.integer(5),
as.integer(5)
)
)
# More about text formatting here: (oops there was a typo here)
# https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFTextBox.html
# Update: for .xls files this looks promising:
# https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFRichTextString.html
text <- rJava::.jnew(class = "org/apache/poi/hssf/usermodel/HSSFRichTextString", "Test paragraph")
new_text_box$setString(text)
new_text_box$setFillColor(as.integer(255), as.integer(255), as.integer(255)) # Sets background to white
saveWorkbook(wb, "test.xls")
Ah, got it working now, thanks very much!