openxlsx format cells as number with comma and decimal places

Question

How can one create an excel file using openxlsx where the number formatting includes both comma thousand separators and 2 decimal places? I have tried the code below, but no luck on the formatting.

# Create Customer Dataset
cust <- data.table(Customer = c("Sue", "Ben", "Jason", "Cody"), Sales = 
c(5654.3456, 29384.4, 729, .4093))

# Start Workbook
wb <- createWorkbook()

# Set Sheet Name
sheet = "Customers Report"

# Initiate worksheet within workbook
addWorksheet(wb = wb, sheet = sheet)

# Add Formatting to Spreadsheet
addStyle(wb = wb, sheet = sheet, style = createStyle(numFmt = "NUMBER"), rows = 2:6, cols = 2)
addStyle(wb = wb, sheet = sheet, style = createStyle(numFmt = "COMMA"), rows = 2:6, cols = 2, stack = TRUE)

# Write Customer Dataset to Spreadsheet
writeData(wb = wb, sheet = sheet, x = cust, headerStyle = 
createStyle(textDecoration = "bold"))

# Write Workbook to File
saveWorkbook(wb = wb, file = "~/Desktop/Customer_Report.xlsx", overwrite = TRUE)

score 15 · Accepted Answer · answered Apr 12 '18 at 15:33

15

You can set the default formatting for the 2 decimal cases prior to adding the thousands format.

    wb      = createWorkbook()
    options("openxlsx.numFmt" = "0.00") # 2 decimal cases formating
    styleT <- createStyle(numFmt = "#,##0.00") # create thousands format
    addStyle(wb, sheetName, styleT,
             rows = 'yourrows',cols = 'yourcols',
             gridExpand = T, stack = T) # add thousands format to designated cols and rows

This will ensure that the thousands formatting happens on a value that already has only 2 decimal cases.

answered Apr 12 '18 at 15:33

Felipe Alvarenga

2,572
1
17
36

You beat me by couple of seconds, so I will not post my answer. Jason, take a look at the: https://cran.r-project.org/web/packages/openxlsx/vignettes/formatting.pdf – JeanVuda Apr 12 '18 at 15:36
Y'all both beat me to my answer! Geeze y'all are quick! Thank you for the responses! – Jason Apr 12 '18 at 15:45

score 6 · Answer 2 · answered Apr 12 '18 at 15:42

I found the answer as I was writing the question, but I figured I would go ahead and post it in the event that someone else has the same question down the road. I found this answer via github openxlsx issue #75. Please see the below code block:

# Add Formatting to Spreadsheet
addStyle(wb = wb, sheet = sheet, style = createStyle(numFmt = "#,##0.00"), rows = 2:6, cols = 2)

I have found that you use "0" when you want there to be a digit there no matter if it is 0 and that you use "#" to represent a placeholder for a potential digit. For example, if the number is .4093 as shown above, then it would be formatted to 0.41 and if the number is 29384.4 as shown above, then it would be formatted to 29,384.40.

openxlsx format cells as number with comma and decimal places

2 Answers2