openxlsx currency style - Is there a rounding option?

Question

The createStyle function in the openxlsx package has an argument numFmt, which allows you to create an excel format to be applied to specific cells in a .xlsx file. You can round values by specifying numFmt = '0', and you can apply a currency format by specifying numFmt = "CURRENCY".

Is there a way to specify a rounded currency format? I've tried the following:

• Rounding the values in the data frame doesn't work, because the excel cells still display the cents e.g. $103.00.
• numfmt = 'CURRENCY0' does not work

If not, is there another package which allows you to specify the formats of excel cells, and allows rounding of currency-formatted cells?

Edit:

This gives me what I wanted (Currency format, with commas, no cents)

createStyle(numFmt="$0,0")

Guileless

Answer 1

You can set numFmt="$0" to get rounded currency values. For example:

library(openxlsx)

mtcars$hp = 100 * mtcars$hp

wb = createWorkbook()
sht = addWorksheet(wb, "test")
writeData(wb, sht, mtcars)

sty1 = createStyle(numFmt="$0")
sty2 = createStyle(numFmt="$0.00")
sty3 = createStyle(numFmt="$0,0.00")

addStyle(wb, sht, sty1, rows=2:(nrow(mtcars)+1), cols=1)
addStyle(wb, sht, sty2, rows=2:(nrow(mtcars)+1), cols=5)
addStyle(wb, sht, sty3, rows=2:(nrow(mtcars)+1), cols=4)

saveWorkbook(wb, "test.xlsx")

Here's what the resulting Excel file looks like:

Baedeker

Thanks. Two questions: (1) How did you know this? Is there an explanation of all available formats somewhere? (2) This removes the commas. How can I keep the commas and still round? – Guileless

I've added an example with a comma separator. How did I know this? I've never carefully studied the rules of Excel number formats, but over the years I've occasionally needed to create a custom format. As a result, I have a rough idea of how they work. The formats I created in my answer just seemed like the "natural" thing to do, and they turned out to work. If they hadn't worked, I would have Googled and figured out how to tweak them. – Baedeker

For an explanation of how to create your own custom formats, there are lots of web pages with examples. Google "Excel number formats" or "Excel custom number formats" (without the quotes) or something like that and you'll see a bunch of guides. – Baedeker

One additional note on custom number formats: I should have clarified that these formats are not special options limited to openxlsx. Rather, they are the standard Microsoft Office formatting codes, and openxlsx just allows you to use them within R to format data written to Excel files. – Baedeker

Answer 2

just incase you want to format in other currencies, you could try this

style = createStyle(numFmt="[$CURRENCY SYMBOL]#,##0")

E.g

style = createStyle(numFmt="[$UGX]#,##0")

Breland