wb_load() and path

[olivroy]

Should wb_load() add the path to the wbWorkbook object.

wb <- wb_load(file = my_file.xlsx")
wb$path()
#> character(0)

This could allow things like this to be done.

wb <- wb_load(file = my_file.xlsx")
wb$remove_worksheet(1)
wb$save()
#> Error in if (file.exists(file) & !overwrite) { : 
#>   argument is of length zero
wb$save(overwrite = TRUE)
#> Error in if (file.exists(file) & !overwrite) { : 
#>  argument is of length zero

In any cases, could this error be improved?

I don't know if I'm playing outside the rule, but there are currently no safeguards.. :)

Edit: also, what can metadata be? is there a way to edit metadata, like title, subject, keywords, comment etc.

No docs here https://janmarvin.github.io/openxlsx2/dev/reference/wbWorkbook.html#public-fields

[JanMarvin]

Hi @olivroy , thanks for the idea. Have added it to #742

wb$metadata contains cell/value metadata imported on load from xl/metadata.xml. I've seen it used in MS365 files to hide array formulas (not sure if hiding is the intention, the Ecma office open xml part 1 file with the openxml documentation is 5k pages and I didn't read all 😅 ). So the following formula is shown as a plain formula to MS365 users, while other spreadsheet software will show this as array formula. We provide the functionality in wb_add_formula():

library(openxlsx2)
wb <- wb_workbook()$add_worksheet()$add_data(x = sample(c(-1, 0, 1), 10, TRUE))

wb$add_formula(x = "SUM(ABS(A1:A10))", dims = "B2", cm = TRUE)
#> Warning in write_data2(wb = wb, sheet = sheet, data = x, name = name, colNames
#> = colNames, : modifications with cm formulas are experimental. use at own risk

if (interactive()) wb$open()

I'm of the impression that the user is an adult. If the user wants to do something stupid, the user will suffer the consequences. I have already done many stupid things and will do many stupid things, but such is life. R allows you to do many potentially bad things like overwriting, replacing, or unlinking files ... loading, modifying and replacing in a single pipe belongs to the same realm. But ... I do not recommend doing this 😉 (After all there is always something that might break along the way and openxlsx2 is not bullet proof and even with spreadsheet software things can go sideways and making backup files is always a good idea.

[olivroy]

Thanks!

Stupid question, why does this fail? why is wb2$subject NULL

library(openxlsx2)
wb1 <- wb_workbook(title = "thingy", subject = "x")
wb1$add_worksheet()
wb1$save("tmp.xlsx")
wb1$open()
#> Warning in xl_open.default(x$clone()$save(temp_xlsx(macros = has_macros))$path,
#> : will not open file when not interactive
wb1$subject
#> [1] "x"
wb2 <- wb_load("tmp.xlsx")
wb2$subject
#> NULL
wb2$title
#> NULL

^{Created on 2023-08-17 with reprex v2.0.2}

Also, with path, why does wb$open() opens a copy (tmp-file.xlsx)
I know that viewing a file, then remodify it in the code caused issue in openxlsx..

Could openxlsx2 open files in read-only mode to avoid messing with the wbWorkbook object still in use? https://learn.microsoft.com/en-us/office/open-xml/how-to-open-a-spreadsheet-document-for-read-only-access

I am very noob, just random thoughts that occur to me

[JanMarvin]

• that is because nobody cared to implement splicing the xml node into different sections:

library(openxlsx2)
tmp <- temp_xlsx()

wb_workbook(title = "thingy", subject = "x")$add_worksheet()$save(tmp)

wb2 <- wb_load(tmp)

wb2$core %>% as_xml()
#> <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
#>  <dc:creator>janmarvingarbuszus</dc:creator>
#>  <cp:lastModifiedBy>janmarvingarbuszus</cp:lastModifiedBy>
#>  <dcterms:created xsi:type="dcterms:W3CDTF">2023-08-17T22:17:06Z</dcterms:created>
#>  <dc:title>thingy</dc:title>
#>  <dc:subject>x</dc:subject>
#> </cp:coreProperties>

# title
xml_value(wb2$core, "cp:coreProperties", "dc:title")
#> [1] "thingy"

# subject
xml_value(wb2$core, "cp:coreProperties", "dc:subject")
#> [1] "x"

• it opens a temp copy, because we do not set a path at workbook creation and if we wb_load()$open() a workbook, we'd have to overwrite the workbook and this defeats many testing purposes. You could use something like this. All other options depend on the file system support and the spreadsheet software used to open the file.

library(openxlsx2)

wb <- wb_workbook()
wb$add_worksheet("S1")
wb$protect(
  protect = TRUE,
  password = "Password",
  lock_structure = TRUE,
  type = 2L,
  file_sharing = TRUE,
  username = "Test",
  read_only_recommended = TRUE
)