R openxlsx not formatting percentages correctly

user_4462690 0
Viewed 53

I use openxlsx to write dataframes from an R script to Excel. I was on R 4.05 (and possibly and older version of openxlsx) and the following statement worked just fine:

options("openxlsx.numFmt" = "0.0%")
writeData(wb, "Sheet_1", df_tmp, startCol = 2, startRow = 16, rowNames = TRUE)

df_tmp (a dataframe) would be written to the spreadsheet with all the numbers nicely formatted as percentages with one digit after the decimal point. After upgrading to R 4.2.1 and updating all packages, the exact same code writes df_tmp to Excel as a date!

After some searching through the vignette and using Google, I tried changing the format to

options("openxlsx.numFmt" = "PERCENTAGE")

But this doesn't quite work either - it now formats the dataframe as a decimal! Is there something that I am missing or doing wrong with the options, and if so, what do I need to do to write the dataframe to Excel as a percentage.

If it is any help, dataframes with integers get correctly written when I set numFmt to NULL, i.e.

options("openxlsx.numFmt" = NULL)

Sincerely

Thomas Philips

openxlsx r excel
1 Answers

Did you try the following alternative (irrespective of the question if the behaviour you are stated in your post is a bug or not):

percent_style <- createStyle(numFmt = "0.0%")


addStyle(wb = wb,
         sheet = "Sheet_1",
         style = percent_style,
         rows = 16:???,
         cols = 2:???,
         gridExpand = TRUE,
         stack = TRUE)

writeData(wb, "Sheet_1", df_tmp, startCol = 2, startRow = 16, rowNames = TRUE)

See the placeholder ??? for the rows/cols argument in addStyle which you need to change.

user_27257730
Related