openxlsx format style with using ' as 1000 separater

user_3270634 0
Viewed 35

I am trying to format the 1000s using writeData with a style that looks lik:

fmt_mil0 <- createStyle(numFmt = "#'##0", halign = "right")

However, this gives me the tick, but also in front of numbers that are less than 1000. Like '20. I tried using the back as well as the forward tick, but that didn't work.

library(openxlsx)

wb <- createWorkbook()
df <- c(1000, 20, -20, -0.1)
fmt_mil <- createStyle(numFmt = "#'##0", halign = "right")
fmt_mil1 <- createStyle(numFmt = "#`##0", halign = "right")
fmt_mil2 <- createStyle(numFmt = "#´##0", halign = "right")
addWorksheet(wb, "test")
writeData(wb, "test", df)
writeData(wb, "test", df, startCol = 2)
writeData(wb, "test", df, startCol = 3)
addStyle(wb, "test",style = fmt_mil,  rows = 1:4, cols =1, gridExpand = TRUE, stack = TRUE)
addStyle(wb, "test",style = fmt_mil1,  rows = 1:4, cols =2, gridExpand = TRUE, stack = TRUE)
addStyle(wb, "test",style = fmt_mil2,  rows = 1:4, cols =3, gridExpand = TRUE, stack = TRUE)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

Anyone an idea that could help?

Cheers

Renger

openxlsx r
1 Answers

Wow, this was simple: use #,##0 as format. On my Swiss machine this is automatically transformed in #'##0 (we have the tick as separater for 1000s).

user_32706340
Related