I am trying to add a new workshet to an excel file with the openxlsx R package. In Denmark we use periods as thousands separators so ideally this is how I would like the output to show in the .xlsx file.

My code looks like this:


file <- file.path("S:", "test.xlsx")

df <- data.frame(A = c(1, 200, -3000, 40000, 500000, 6000000),
                 B = c(0.0101, 1.0245, 0.9876, 0.6512, 0.0101, 1.0245))

wb <- loadWorkbook(file)

sht <- addWorksheet(wb, sheetName = "newSheet")

head_st <- createStyle(textDecoration = "Bold")
number_st <- createStyle(numFmt = "#.##0")
pct_st <- createStyle(numFmt = "PERCENTAGE")

writeData(wb, sht, df, headerStyle = head_st)

addStyle(wb, sht, style = number_st, 
         cols=c(1), rows = 2:(nrow(df) + 1), gridExpand = TRUE)
addStyle(wb, sht, style = pct_st, 
         cols=c(2), rows = 2:(nrow(df) + 1), gridExpand = TRUE)

saveWorkbook(wb, file, overwrite = TRUE)

The excel output is shown below.

enter image description here

The percentages come out exactly how they are supposed to (with comma as decimal separator). However, the number format is not right!

I tried to solve it several ways, by going into the number formatting in excel and try to see how they define it:


Where I have also tried "#.##0;-#.##0" as well as the longer one in the below picture:


But that gives an error which I am not sure how to solve or if it helps with my issue.

> number_st <- createStyle(numFmt = "_-* #.##0_-;-* #.##0_-;_-* "-"??_-;_-@_-")
Error in "_-* #.##0_-;-* #.##0_-;_-* " - "??_-;_-@_-" : 
  non-numeric argument to binary operator 

Any ideas? :)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.