Formatting percentages in R-package openxlsx

Question

R3.2.3/openxlsx 3.0.0

I have several dataframes that I'm writing to an Excel-workbook with openxlsx. One of the dataframes contains values such as 0.07. With

createStyle(numFmt='PERCENTAGE') 

I get 7.00% as output in the workbook. So far so good. But I want 7% as output. I've tried several things, such as stacking styles,

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

createStyle(numFmt='PERCENTAGE 0')

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

Told

See the percent function from scales R package. Also check this post – Pyxidium

If you can, include a short but complete example of your code so far, with example data. Also, a potential "hacky" solution would be to create a second style which sets the number of decimal places to what you want and add that after you've specified it's percentage. I haven't checked that this works, and it would not be efficient, but you're interacting with excel through R so... – Molybdenous

Answer 1

You can create a percent style with no decimal places using Excel's formatting codes and then apply the style to specific cells. For example:

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")

An advantage of using Excel's percent format is that the underlying data in the spreadsheet will still be numeric, rather than, say, text strings.

Here's what the worksheet looks like:

Banjermasin

Answer 2

Extending from @parL's answer. 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 numeric value columns in the data frame to string format.

Comber