Percentage format not working

[tkpmep]

[https://stackoverflow.com/questions/73016935/r-openxlsx-not-formatting-percentages-correctly]

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!

If it is any help, dataframes with integers get correctly written when I set numFmt to NULL, i.e. options("openxlsx.numFmt" = NULL)

[JanMarvin]

Hi @tkpmep , might be a regression. Could you please check the same with the development branch? Unfortunately at the moment the only solution would be to assign formats manually.

[tkpmep]

I'm happy to try - is there anything that I have to take care with when I install the development version? For example, remove the stable version, add some other packages etc. etc.

[JanMarvin]

Hi, no you should be fine just installing the development version with remotes like this: remotes::install_github("ycphs/openxlsx", ref="development") (typed on my smartphone therefore it might be slightly different)

[tkpmep]

Tried the development version. The problem is still a way from being fixed options("openxlsx.numFmt" = "0.0%") still gives me date formatted cells options("openxlsx.numFmt" = "PERCENTAGE") gives me decimals Nothing gives me percentages.

[JanMarvin]

Hm, well it was worth a try. You can assign custom styles:

s <- createStyle(numFmt = "0.0%")
addStyle(wb, ...)

Not sure what exactly broke it, but most likely it's around for quite some time. Might be some option doing something strange.

[tkpmep]

I actually tried this once, but it didn;t work at all (everything got formatted as a date). I wish I knew what version of openxlsx I was on when I was on R 4.05, because it then worked perfectly. It could be with base R, openxlsx, or one of the dependencies. for now, i'm going to stick with the development version and the use of "PERCENTAGE" and live with the output being formatted as a decimal, which is manageable, and much better than it being formatted as a date.

[JanMarvin]

It definitely works, I use it with the CRAN release frequently. You have to define the rows and cols you apply the style and maybe skip the colname cell. But use whatever suits you. Opening the XLSX file and setting the cellstyle manually brings the benefit that you see the numbers created at least once :)

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.