r - openxlsx 中的换行符

Question

关于 R 中 openxlsx 库的一个问题:

我正在寻找一种在 openxlsx 中向单元格值添加换行符的方法，以便该值在 Excel 中位于两行(或更多行)中。我的意思是通过键入一个值，按 Alt-Enter 并添加另一个值在 Excel 中获得的类似功能。

不，我不是在寻找使用数据框或具有适当列宽的自动换行的技巧，而是寻找其他方法。

例如，此解决方案候选不起作用:

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

一切仍然打印在一行上。

Answer 1

尝试这个:

似乎在 excel 中为如此简单的事情做了很多工作但我用 openxlsx 发现了这个你可以用一点力气做大多数事情，我真的很喜欢这个包。

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)

结果是: