deleteDataColumn adds strings in wrong places

[Mkranj]

Issue

Hi, this is a weird glitch that took some time to reproduce. Here is a sample excel file:
before.xlsx

Then I run this code to load the file, delete the 3rd column (C) and save to a new file:

library(openxlsx)

my_wb <- loadWorkbook("before.xlsx")

openxlsx::deleteDataColumn(my_wb, sheet =  1, col =  3)

saveWorkbook(my_wb, "example_after.xlsx", overwrite = T)

And the output looks like this:

You can see that in C2 there is the string "d" that should not be there. (The cell in the column named "d" should be an institution name, as before the column removal)

Extra context

It was hard to reproduce because when I changed the column names for my reprex, all of a sudden it worked without issue! The cell EXTRA originally had some other text. I changed it for privacy, which changed what string gets copied. Instead of "d", the cell C2 used to have content from A3 pasted into it. This changed after I manually edited the excel. Maybe it has something to do with the order in which cells were filled?

Possible cause

I'm not certain what causes this issue. My best guess would be something with pointers to shared strings, but I don't understand the topic well enough to see the problem. If I had to guess, using the "keep" object in the following line seems weird to me because it seems not to take into account the deleting of strings in the previous lines.

openxlsx/R/wrappers.R

Line 1929 in 69e00af

a$v <- a$v[keep]

Thank you for your hard work!

[JanMarvin]

Hi @Mkranj , thanks for the report. @DavZim if you have a moment, could you have a look? I think this issue stems from your PR here: #418.

Btw I didn't merge (and probably wouldn't have merged) the PR and I have very strong reasons not to add something like this to openxlsx2, see here. And I believe that the same is true for openxlsx as well. Chances are that you unknowingly introduce issues that are very hard to spot into your workbook. The only case where this would be safe to use, is in an all data workbook - and in such a case I would always try to write the entire workbook from scratch.