accounting format?

[db369]

I'm not sure why I can't figure this out, but I'm having the hardest time getting accounting format to work. I assume the currency symbol is left out due to localization, but I'm not entirely sure how to add in a simple '$'. I'm using class, but I've also tried using wb_add_numfmt().

Can anyone share an example of how to do this? Thanks!

[JanMarvin]

Hi @db369 , the accounting format in Excel has no currency symbol. Its something like: #,##0.00 (thousand separator and two digits after the small number separator). You might want to look into the currency format that was restored for todays release of openxlsx2 1.3. This should provide you with a currency symbol. (I'll edit this in a min, once I switched PCs and provide an example.)

[db369]

Currency seems to work perfectly, but that was unexpected. In Excel, if I format as currency, the dollar sign is next to the value. Formatting as accounting, left-justifies the currency symbol and right-justifies the value, with respect to the decimal point. I'm still a little confused by this, but using currency solves my problem so I'm happy.

Thanks for getting back to so quickly!!

[JanMarvin]

I saw that as well, when I was restoring the currency format lately. It's even worse, for some currencies the currency symbol switches from left to right (like 1 € becomes € 1) and sometimes the minus sign switches from before the symbol to behind it (£ 1, but -£ 1, but $ 1 and $-1). But I'm not an accountant therefore, what do I know :)

Here's a more detailed example with currencies (I live in the EUR region, therefore the euro symbol). The openxlsx2 applied styles accounting and currency use builtin styles, but you can assign custom number formats as shown below. If I remember correctly, I picked the builtin name for accounting from the ECMA specification for OOXML, but don't quote me on that.

# example requires openxlsx2 (>= 1.3)
# prepare a data set
set.seed(123)
df <- data.frame(
  num = sample(100:150, size = 10, TRUE),
  act = sample(100:150, size = 10, TRUE),
  cur = sample(100:150, size = 10, TRUE)
)
class(df$cur) <- c(class(df$cur), "currency")
class(df$act) <- c(class(df$act), "accounting")

library(openxlsx2)

wb <- wb_workbook()$add_worksheet()$add_data(x = df)

# assign numformat for USD (fmt string copied from MS365)
wb$add_numfmt(
  dims = wb_dims(x = df, cols = "num"),
  numfmt = "_-[$$-409]* #,##0.00_ ;_-[$$-409]* \\-#,##0.00\\ ;_-[$$-409]* &quot;-&quot;??_ ;_-@_ "
)

if (interactive()) wb$open()