CODEWITHSUNDEEP
r
s7eqx9f74nc4
s7eqx9f74nc4

Reputation: 143

R > openxlsx::read.xlsx treats values and formulae differently

When using openxlsx::read.xlsx in R, I am getting different results, reading values in an Excel file vs. reading formulas.

If you save an xlsx workbook as 'test.xlsx' with the following formulas in cells A1:A3:

A
Col1
A & B
=A2

Then in R:

library(openxlsx)
library(tibble)
my_file <- openxlsx::read.xlsx('test.xlsx')
tibble::view(my_file)

Then you should see:

Col1
A & B
A &amp; B

Does anyone know how to change the &amp; back into an & (without modifying the original xlsx)?

Many thanks

Upvotes: 2

Views: 331

Answers (1)

Peter
Peter

Reputation: 12739

Using dplyr and stringr you can modify the "&" which is generated from the excel formula reading the file with read.xlsx

library(openxlsx)

library(dplyr)
library(stringr)

read.xlsx("test.xlsx") %>% 
  mutate(Col1 = str_replace(Col1, "&amp;", "&"))
#>    Col1
#> 1 A & B
#> 2 A & B

Alternatively you could avoid the issue altogether by using readxl to read the file.

library(readxl)

read_xlsx('test.xlsx')
#> # A tibble: 2 x 1
#>   Col1 
#>   <chr>
#> 1 A & B
#> 2 A & B

Created on 2021-07-08 by the reprex package (v2.0.0)

Upvotes: 2

Related Questions