R3.2.3/openxlsx3.0.0如何设置无小数位的百分比格式？

I get it—stuck on an older version of openxlsx and needing that clean 7% instead of 7.00% can be frustrating. The fix here is to bypass the package's pre-defined PERCENTAGE alias and use Excel's native custom number format code directly.

Here's how to make it work: #

Instead of using numFmt='PERCENTAGE', pass the raw Excel format string '0%' to createStyle(). This tells Excel to display the percentage without any decimal places, exactly what you need.

Full working code example:

library(openxlsx)

# Create your sample dataframe with the 0.07 value
df <- data.frame(
  Metric = c("Conversion Rate", "Retention Rate", "Growth Rate"),
  Value = c(0.07, 0.152, 0.4)
)

# Initialize workbook and add worksheet
wb <- createWorkbook()
addWorksheet(wb, "Percent Data")

# Define the custom percentage style
no_decimal_percent <- createStyle(numFmt = "0%")

# Write data to the worksheet
writeData(wb, "Percent Data", df, rowNames = FALSE)

# Apply the style to the Value column (adjust rows/cols to match your data)
addStyle(
  wb,
  sheet = "Percent Data",
  style = no_decimal_percent,
  rows = 2:(nrow(df) + 1),  # Skip header row
  cols = 2,
  gridExpand = TRUE  # Ensure style applies to all cells in the range
)

# Save the final workbook
saveWorkbook(wb, "clean_percentages.xlsx", overwrite = TRUE)

Why your previous attempts didn't work: #

• createStyle(numFmt=c('PERCENTAGE','0')): This passes a vector, which the old openxlsx version doesn't accept—it expects a single string value for numFmt.
• createStyle(numFmt='PERCENTAGE 0'): The PERCENTAGE alias maps directly to Excel's 0.00% format under the hood. Adding 0 to the alias doesn't modify it; the package doesn't recognize this combined string as a valid format.

By using the raw Excel format code 0%, you're directly telling Excel how to display the number, which bypasses the package's pre-set aliases and works reliably in older versions.

内容的提问来源于stack exchange，提问作者Jack Random