Newline in openxlsx

Question

A question on openxlsx library in R:

I'm looking for a way in openxlsx to add a newline to a cell value so that the value is in two (or more lines) in Excel. I mean similar functionality that you get in Excel by typing a value, pressing Alt-Enter and adding another value.

And no, I am not looking for a trick to do it using data frames or word wrapping with appropriate column width but something else.

For example this solution candidate doesn't work:

openxlsx::write.xlsx(
    data.frame("I want this in two lines\nin one cell"),
    stringsAsFactors = FALSE,
    file = "foo.xlsx"
)

Everything is still printed on one line.

Answer 1

Try this:

Seems a lot of work for something so simple in excel but I've found this with openxlsx you can do most things with a bit of effort, I really like the package.

txt <- data.frame(t = c("I want this in two lines", "in one cell"), stringsAsFactors = FALSE)


wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", x = txt)
# to make sure your text shows as two lines
setRowHeights(wb, "Sheet 1", rows = 4, height = 30)

# this seems to force the line break to split the text over two lines
style1 <- createStyle(wrapText = TRUE)
addStyle(wb, sheet = 1, style1, rows = 4, cols = 1, gridExpand = TRUE)

#narrow width produces multiple lines as a consequence of text wrap
setColWidths(wb, sheet = 1, cols = 1, widths = 40)

#Here's the key bit; excel does not seem to like \n as a line break an online search suggested CHAR(10) as the alternative to `alt enter` keystroke for line break in an excel cell 
writeFormula(wb, 1, x = "A2&CHAR(10)&A3", startCol = 1, startRow = 4)

saveWorkbook(wb, file = "foo.xlsx", overwrite = TRUE)

Which results in:

Answer 2

I was just playing around with this problem and it looks like, (at least in some versions of excel), you can add a \n to specify where you want the text wrapping to occur. You still have to go through the whole process of creating a workbook, adding a style, saving the workbook, but, at least for me, it looks like it gave me a bit more control over where the line break occurs than like I would if I was just playing around with column widths to force a line break. For example:

Make the Dataframe

We'll use data from this library

library(nycflights13)

Then we'll make a small dataframe from a subset of flights

example_df <- as.data.frame(flights[1:3,c(1,7:8)])

Then we'll make some annoyingly long column names that we want openxlsx to wrap automatically based on column width

names(example_df)  <- rep("Here's a really really really unnecessarily long to the point of being painful column name",3)

Then we'll add text to the first column and put an \n where want the line break to occur

example_df[,1] <- paste0(example_df[,1], "\n",c("Total","Total","YTD"))

Here's what it looks like in R

> example_df
  Here's a really really really unnecessarily long to the point of being painful column name
1                                                                               2013\nTotal
2                                                                               2013\nTotal
3                                                                                 2013\nYTD
  Here's a really really really unnecessarily long to the point of being painful column name
1                                                                                       830
2                                                                                       850
3                                                                                       923
  Here's a really really really unnecessarily long to the point of being painful column name
1                                                                                       819
2                                                                                       830
3 

Format with openxlsx package

we'll use these packages

library(xlsx)
library(openxlsx)

Then we'll write our example dataframe to excel

xlsx::write.xlsx(example_df, file = "example_df.xlsx", row.names = FALSE)

Then we'll link the excel file to a workbook in R using the openxlsx package

n3 <- openxlsx::loadWorkbook(file = "example_df.xlsx")

Then we'll create a style that forces excel to text wrap

my_style <- openxlsx::createStyle(
  fontSize = 11, 
  halign = "center",
  wrapText = TRUE
)

Then we'll add the style

openxlsx::addStyle(n3, sheet = 1, my_style, rows = 1:400, cols = 1:400, gridExpand = TRUE)

Then we'll set the column widths so that text wrapping happens based on the specified width

openxlsx::setColWidths(n3, 1, cols = 1:400,  widths = 21)

Then we save the workbook

saveWorkbook(n3, "example_df.xlsx", overwrite = TRUE)

Result

That's what I ended up with. The first row wrapped based on column width and the first column wrapped where we put the \n. I did this on Microsoft® Excel® for Microsoft 365 MSO 32-bit and Windows 10. I hope this works for other ppl too, but knowing Microsoft, it probably won't ?