get a line break / new line in excel file with r xlsx

Question

I want to get a new line in my excel result such as i would get by going into the cell and pressing Alt+Enter

data + code

df <- data.frame(a="Funny\\nNot",b="rofl\nlol",c="hihi\rh3h3")

xlsx::write.xlsx(df,file = "df.xlsx")

Solution 1

Using the package xlsx:

Remove the escape '\' from column a:

df <- data.frame(a="Funny\nNot",b="rofl\nlol",c="hihi\rh3h3")

library(xlsx)

Create workbook and sheet objects:

wb <- createWorkbook() 
sheet <- createSheet(wb, sheetName="sheet1")

Add dataframe:

addDataFrame(df, sheet)

get row 2, column B from the wb/sheet:

rows <- getRows(sheet, rowIndex = 2)

cell_b_2 <- getCells(rows, colIndex = 2)[[1]]

Create a CellStyle that sets Wrap Text in Excel:

cs <- CellStyle(wb, alignment = Alignment(wrapText = TRUE))

Apply it to the cell:

setCellStyle(cell_b_2, cs) 

Save the workbook:

saveWorkbook(wb, 'df.xlsx')

The resulting workbook looks like this:

Solution 2

Thanks to @lukeA for name dropping.

When I use wrap text for cells containing "\n". I get the desired outcome.

Here is the code I used:

wb <- xlsx::loadWorkbook("df.xlsx")
sheets <- xlsx::getSheets(wb)
sheet  <- sheets[[1]]                  # get specific sheet
rows   <- xlsx::getRows(sheet)         # get rows
cells  <- xlsx::getCells(rows)         # get cells
values <- lapply(cells, xlsx::getCellValue) # extract the cell values


wrapStyle <- xlsx::CellStyle(wb) + xlsx::Alignment(horizontal=NULL, vertical=NULL, wrapText=T,
                              rotation=0, indent=0)

lapply(names(cells),function(c)if(values[[c]] %>% grepl("\\n",.))xlsx::setCellStyle(cells[[c]],wrapStyle))

xlsx::saveWorkbook(wb,"dfWrap.xlsx")