Search code examples
rexcelopenxlsxopenxlsx2

Is there an R function to wrap text within a cell in an Excel output?

I'm writing a script to render an Excel output using openxlsx, and I'm having a hard time wrapping text within a cell.

I have a "Report Notes" sheet in my output with the usual disclaimers about the analysis in the file, which is populated by a text string written to a variable. I have to do it in a separate sheet because the required disclaimer text is too long to include in the header or the footer of my analysis sheets. The issue is that the text string will not wrap in the cell, and that creates a readability problem. I've tried all sorts of ways to force this to work with no success.

#### Example Data ####

library(tidyverse)
library(openxlsx)
library(openxlsx2)

reportnotes<- "THIS IS AN EXAMPLE TEXT STRING THAT I WANT TO WRITE TO AN EXCEL DOCUMENT. HOPEFULLY IT'S LONG ENOUGH TO MAKE MY POINT."

output<-createWorkbook()

addWorksheet(output, "Report Notes")

writeData(output, "Report Notes", reportnotes,
          startCol = 1,
          startRow = 1,
          colNames = TRUE,
          rowNames = FALSE,
          keepNA = TRUE)

pageSetup(output, "Report Notes", orientation = "landscape", scale = 100, fitToWidth = TRUE)

I attempted the following solutions:

#### Attempted Solutions ####

# Solution 1) Using str_wrap() and setColWidths() and setRowHeights() to create the wrap. 
# Resulted in an adjusted column and row height but no wrapped text:

reportnotes<-reportnotes%>%
   str_wrap(width = 80, indent = 0, whitespace_only = FALSE) 

setColWidths(output, "Report Notes", cols = 1, widths=70)

setRowHeights(output, "Report Notes", rows = 1, heights = 150)



# Solution 2) Defining a cell style. This solution produced an error:

wb_add_cell_style(output, sheet="Report Notes", dims = "A1", apply_border = TRUE, wrap_text=TRUE)

# Error: wb must be class wbWorkbook or R6

My console is RStudio 2023.06.1+524 "Mountain Hydrangea" Release (547dcf861cac0253a8abb52c135e44e02ba407a1, 2023-07-07) for windows, and my version of R is R version 4.3.0 (2023-04-21 ucrt) -- "Already Tomorrow". My access to updated versions is limited due to organization policies.

What can I do, given these restrictions, to force the text to wrap in the Excel output?

Solution

  • Not sure if openxlsx2 will cause conflicts, but with openxlsx you can create a style, then apply it to the cells of interest. Something like:

    wrapStyle <- createStyle(wrapText = TRUE)
addStyle(output, sheet = 1, wrapStyle, rows = 1, cols = 1)