How can I bold part of a text string using openxlsx?

Question

I want to bold part of a text string. For example, is there a way to only bold 'text' in 'this is my text string' using openxlsx R package:

wb <- openxlsx::createWorkbook()

openxlsx::addWorksheet(wb, 'Contents')

bold_text <- openxlsx::createStyle(textDecoration = "bold")
openxlsx::addStyle(wb, sheet = 'Contents', bold_text, rows = 1, cols = 1, gridExpand = TRUE, stack = TRUE)

writeData(wb, sheet = 'Contents', x = 'this is my text string', startRow = 1, startCol = 1)

openxlsx::saveWorkbook(wb, file =  'D:testing.xlsx', overwrite = TRUE)

The package openxlsx2 has a vignette on styling. In the middle of it it shows how to "style strings independently of the cell containing the string". — Michael Dewar
– Michael Dewar, Commented Jan 24 at 0:21

lailaps · Accepted Answer · 2025-01-24 10:26:14Z

4

+50

Clonky way in openxlsx

Sure, you can replace text with its 𝐁𝐎𝐋𝐃 Unicode equivalent which we then combine with normal text using paste0. You can do the same for all sorts of font styles.

setwd(dirname(rstudioapi::getSourceEditorContext()$path)) # set the current script's location as working directory
#install.packages("openxlsx")
library(openxlsx)

file <- "BoldText.xlsx"
sheet <- 'Contents'

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(wb, sheet)

b <- function(text) {
  # Define a mapping of characters to their bold Unicode equivalents
  bold_map <- list(
    A = "𝐀", B = "𝐁", C = "𝐂", D = "𝐃", E = "𝐄", F = "𝐅", G = "𝐆", H = "𝐇", I = "𝐈", J = "𝐉", K = "𝐊", L = "𝐋", M = "𝐌", N = "𝐍", O = "𝐎", P = "𝐏", Q = "𝐐", R = "𝐑", S = "𝐒", T = "𝐓", U = "𝐔", V = "𝐕", W = "𝐖", X = "𝐗", Y = "𝐘", Z = "𝐙",
    a = "𝐚", b = "𝐛", c = "𝐜", d = "𝐝", e = "𝐞", f = "𝐟", g = "𝐠", h = "𝐡", i = "𝐢", j = "𝐣", k = "𝐤", l = "𝐥", m = "𝐦", n = "𝐧", o = "𝐨", p = "𝐩", q = "𝐪", r = "𝐫", s = "𝐬", t = "𝐭", u = "𝐮", v = "𝐯", w = "𝐰", x = "𝐱", y = "𝐲", z = "𝐳",
    `0` = "𝟎", `1` = "𝟏", `2` = "𝟐", `3` = "𝟑", `4` = "𝟒", `5` = "𝟓", `6` = "𝟔",`7` = "𝟕", `8` = "𝟖", `9` = "𝟗"
  )
  paste(sapply(strsplit(text, NULL)[[1]], function(char) { ifelse(char %in% names(bold_map), bold_map[[char]], char) }), collapse = "")
}

writeData(wb, sheet = sheet, x = paste0('this is my', b(" test "),'String'), startCol = 1, startRow = 1)

saveWorkbook(wb, file = file, overwrite = TRUE)

If you are fine with using openxlsx2

Then as user @Micheal Dewar pointed out, it can be done much simpler:

### Using openxlsx2
library(openxlsx2)

# Create formatted text
txt <- paste( fmt_txt("This is my "), fmt_txt("Test", bold = TRUE, size = 11),fmt_txt(" String."))

# Create workbook and add data
wb <- wb_workbook()$add_worksheet()$add_data(x = txt)

wb$save("BoldText_openxlsx2.xlsx")

edited Jan 24 at 10:26

answered Jan 23 at 15:57

lailaps

11.3k1 gold badge5 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SamR Jan 24 at 9:11

I'm not a huge fan of this. Firstly it looks weird - your bold font has serifs even though your standard font doesn't. Secondly it's no longer searchable as you're using different characters. For example grep("A", "𝐀") will not find a match.

SamR Jan 24 at 10:29

Even though it uses a different package I think the second method that you've added now is a much better approach

Collectives™ on Stack Overflow

How can I bold part of a text string using openxlsx?

1 Answer 1

Clonky way in openxlsx

If you are fine with using openxlsx2

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Clonky way in openxlsx

If you are fine with using openxlsx2

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related