Cell text with <br/> is replaced with NA

[dhicks]

# Works as expected
foo = data.frame(text = 'foo bar')
wb_workbook() |> 
    wb_add_worksheet() |> 
    wb_add_data_table(x = foo) |> 
    wb_to_df()
#      text
# 2 foo bar 

# Text is replaced with NA
bar = data.frame(text = 'foo <br/>bar')
wb_workbook() |> 
    wb_add_worksheet() |> 
    wb_add_data_table(x = bar) |> 
    wb_to_df()
#   text
# 2   NA

[dhicks]

Same thing happens with any closing HTML tag:

bar = data.frame(text = 'foo <em>bar</em>')
wb_workbook() |> 
    wb_add_worksheet() |> 
    wb_add_data_table(x = bar) |> 
    wb_to_df()

[JanMarvin]

Hi @dhicks, thanks for the report. Indeed a tricky one. The xml cell needs to be escaped like this:

"<is><t>foo &lt;em&gt;bar&lt;/em&gt;</t></is>"

But since we detect something like an xml tag inside, we assume that there the cell is already escaped.

openxlsx2:::txt_to_is('foo <em>bar</em>')
#> "<is><em>bar</em></is>"

Fixing this is a bit of a nuisance:

library(openxlsx2)

bar <- data.frame(
  x = xml_node_create(
    xml_name = "t",
    xml_children = openxlsx2:::replace_legal_chars("foo <em>bar</em>")
  )
)

wb <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data_table(x = bar)

wb$to_df()
#>                  x
#> 2 foo <em>bar</em>

if (interactive()) wb$open()

Same for the first example:

library(openxlsx2)

bar <- data.frame(
  x = xml_node_create(
    xml_name = "t",
    xml_children = openxlsx2:::replace_legal_chars("foo <br/> bar")
  )
)

wb <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data_table(x = bar)

wb$to_df()
#>               x
#> 2 foo <br/> bar

if (interactive()) wb$open()

[JanMarvin]

Interestingly my solution in is part of fmt_txt(). Therefore this works:

library(openxlsx2)
wb <- wb_workbook()$add_worksheet()$add_data(
  x = fmt_txt("a <br/> b")
)
wb$to_df()
#>           x
#> 2 a <br/> b