Newline in openxlsx2

[JanMarvin]

Whenever I search for openxlsx on stackoverflow, this problem pops up: Newline in openxlsx. Well, let's answer the problem in an openxlsx2 way. All you need to know: The visual effect that tells the spreadsheet software to break the row in the cell for you is a cell style. If you omit this and simply write a\nb, the line will wrap in the formula editor, but not visually in the spreadsheet.

Background

Line breaks in open xml require the wrapText style element. It looks like this when written from MS365.

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
 <alignment wrapText="1" />
</xf>

When written from openxlsx2 it looks like this (we might align it in upcoming releases).

<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0">
 <alignment wrapText="1" />
</xf>

Code example

The style that creates line breaks is not applied by default. Therefore, each time a line break is desired, the "wrapText" cell style must be added manually. If we had to automate this, we'd have to search for "\n" in every string we add to a workbook.

Fortunately, it is quite easy to add the "wrapText" option to a cell style.

library(openxlsx2)
wb <- wb_workbook()$
  add_worksheet()$
  add_data(x = "I want this in two lines\nin one cell")$
  add_cell_style(wrapText = "1")