Percent formatting in openxlsx R package

Question

R3.2.3 / openxlsx 3.0.0

I have several data frames that I write to an Excel workbook using openxlsx. One of the data files contains values ​​such as 0.07. Via

createStyle(numFmt='PERCENTAGE') 

I get 7.00% as output in the book. So far, so good. But I want 7% as a way out. I tried several things like stack styles,

 createStyle(numFmt=c('PERCENTAGE','0')) createStyle(numFmt='PERCENTAGE 0') 

but they either lead to errors or give undesirable results. Any suggestion in the right direction would be very welcome. Upgrading to a newer version of openxlsx is not an option.

Answer 1

You can create a percent style without decimals using Excel format codes, and then apply the style to specific cells. For instance:

 library(openxlsx) # Fake data set.seed(2) dat = data.frame(v1=LETTERS[1:10], v2=runif(10), v3=letters[1:10], v4=runif(10)) # Create an Excel workbook object and add a worksheet wb = createWorkbook() sht = addWorksheet(wb, "Data") # Create a percent style pct = createStyle(numFmt="0%") # Add fake data to the worksheet we just created writeData(wb, sht, dat) # Add the percent style to the desired cells addStyle(wb, sht, style=pct, cols=c(2,4), rows=2:(nrow(dat)+1), gridExpand=TRUE) saveWorkbook(wb, "my_workbook.xlsx") 

The advantage of using the Excel format is that the underlying data in the spreadsheet will still contain numeric rather than text strings.

Here's what the worksheet looks like:

Answer 2

Continued answer @parL. For this approach to work, you need to remove createStyle(numFmt='PERCENTAGE 0') :

You can try to do something like this:

 i<-sapply(df, is.numeric) df[i]<-lapply(df[i], scales::percent) 

This makes all columns of a numeric value in the data frame in row format.