wb_add_numfmt: Modify number formatting in a cell region

In openxlsx2: Read, Write and Edit 'xlsx' Files

Modify number formatting in a cell region

Description

Add number formatting to a cell region. You can use a number format created by create_numfmt().

Usage

wb_add_numfmt(wb, sheet = current_sheet(), dims = "A1", numfmt)

Arguments

wb	A Workbook
sheet	the worksheet
dims	the cell range
numfmt	either an integer id for a builtin numeric font or a character string as described in the Details

Details

The list of number formats ID is located in the Details section of create_cell_style().

General Number Formatting

• "0": Displays numbers as integers without decimal places.

• "0.00": Displays numbers with two decimal places (e.g., 123.45).

• "#,##0": Displays thousands separators without decimals (e.g., ⁠1,000⁠).

• "#,##0.00": Displays thousands separators with two decimal places (e.g., ⁠1,000.00⁠).

Currency Formatting

• "$#,##0.00": Formats numbers as currency with two decimal places (e.g., ⁠$1,000.00⁠).

• "[$$-409]#,##0.00": Localized currency format in U.S. dollars.

• "¥#,##0": Custom currency format (e.g., for Japanese yen) without decimals.

• "£#,##0.00": GBP currency format with two decimal places.

Percentage Formatting

• "0%": Displays numbers as percentages with no decimal places (e.g., ⁠50%⁠).

• "0.00%": Displays numbers as percentages with two decimal places (e.g., ⁠50.00%⁠).

Scientific Formatting

• "0.00E+00": Scientific notation with two decimal places (e.g., 1.23E+03 for 1230).

Date and Time Formatting

• "yyyy-mm-dd": Year-month-day format (e.g., 2023-10-31).

• "dd/mm/yyyy": Day/month/year format (e.g., 31/10/2023).

• "mmm d, yyyy": Month abbreviation with day and year (e.g., ⁠Oct 31, 2023⁠).

• "h:mm AM/PM": Time with AM/PM format (e.g., ⁠1:30 PM⁠).

• "h:mm:ss": Time with seconds (e.g., 13:30:15 for ⁠1:30:15 PM⁠).

• "yyyy-mm-dd h:mm:ss": Full date and time format.

Fraction Formatting

• "# ?/?": Displays numbers as a fraction with a single digit denominator (e.g., 1/2).

• "# ??/??": Displays numbers as a fraction with a two-digit denominator (e.g., ⁠1 12/25⁠).

Custom Formatting

• ⁠"_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)⁠: Custom currency format with parentheses for negative values and dashes for zero values.

• "[Red]0.00;[Blue](0.00);0": Displays positive numbers in red, negatives in blue, and zeroes as plain.

• "@": Text placeholder format (e.g., for cells with mixed text and numeric values).

Formatting Symbols Reference

• 0: Digit placeholder, displays a digit or zero.

• ⁠#⁠: Digit placeholder, does not display extra zeroes.

• .: Decimal point.

• ⁠,⁠: Thousands separator.

• ⁠E+⁠, ⁠E-⁠: Scientific notation.

• ⁠_⁠ (underscore): Adds a space equal to the width of the next character.

• "text": Displays literal text within quotes.

• *: Repeat character to fill the cell width.

Value

The wbWorkbook object, invisibly.

See Also

Other styles: wb_add_border(), wb_add_cell_style(), wb_add_fill(), wb_add_font(), wb_add_named_style(), wb_cell_style

Examples

wb <- wb_workbook() %>% wb_add_worksheet("S1") %>% wb_add_data("S1", mtcars)
wb %>% wb_add_numfmt("S1", dims = "F1:F33", numfmt = "#.0")
# Chaining
wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
wb$add_numfmt("S1", "A1:A33", numfmt = 1)

openxlsx2 documentation built on April 3, 2025, 8:40 p.m.