1

I am trying to write to an excel file that needs to be uploaded somewhere. The target software creates an excel file which has an XML map attached to it. I recreated the entire file structure in R using code, but any time I try to write to that excel file, i think R actually deletes the old file and creates a new one instead, because the XML map is gone the moment I start writing any data to it. Loading up the workbook also doesn't seem to bring in the xml map, only the workbook data and sheets.

Is there a way to write data to this existing file within R (or python) without losing the XML map? Now i need to generate a file and manually copy paste the data into the other excel file.

I've been trying with xlsx, readxl, xml2 packages.

TheJohn
  • 11
  • 1
  • 1
    Hello @TheJohn and welcome to SO! First, I recomend you to read this quiestion about how to make a great reproductible example: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example. Always try to post your code and the alternatives that you have tried. – Santiago Capobianco Dec 16 '22 at 16:13

1 Answers1

0

In the past Ive deal with a similar problem. To my knowledge, almost all the R packages that interact with excel replace the entire file with a new one. Except the openxlsx package. You can replace specific sheets, and range of cells, whitout touching the rest (data, styling , etc..). One last comment is that I dont know much about XLM maps, but maybe you are lucky.

Here is the vignette:

https://cran.r-project.org/web/packages/openxlsx/vignettes/Introduction.html

Hope it helps

Santiago Capobianco
  • 866
  • 9
  • 19
  • I have tried openxlsx, which seems to have the same behaviour. I load the workbook, change data, then write to a sheet as you recommended and it still seemed to remove the metamap. The moment i call saveWorkbook(wb, "file.xlsx", overwrite = TRUE), if i overwrite it removes the map, if i don't it will throw errors about the file already containing data (since i'm appending to an existing sheet in most cases). I'm afraid i'm simply trying to do something that can't be done the way i want to. Thanks for the response though. It is probably not possible is also an answer. – TheJohn Dec 20 '22 at 12:12
  • @TheJohn here you can find a working example with [`openxlsx2`](https://github.com/JanMarvin/openxlsx2/discussions/484) – Jan Marvin Dec 20 '22 at 18:49