XLconnect doesn't release file lock after writeWorksheet and saveWorkbook

[ErrantBard]

Since we updated R and Rstudio a couple of days ago we've noticed we have to close down R-studio to get R to release it's permission on all Excelfiles we write to. Earlier when we used the writeWorksheet-method and saveWorkbook-method R released permissions on the excel-file as well, which was great since we often do edits to the files and it's nice to have our RStudio-projects open at the same time.

We've tried this with:

R-version 3.6.3 as well as 4.0.2 and both RStudio version 1.2.5033 and 1.3.1056 and the problem with locked files remained with both versions.

If I do this:

library(XLConnect)

wb <- loadWorkbook("Cars.xlsx", create = TRUE)
createSheet(wb, "Data")
writeWorksheet(wb, cars, "Data")
saveWorkbook(wb)

The first time a open the file, I can save without closing the R-session. If I do that, open the Excel-file and save, and then re-run the above code my R-session hold the permission lock.

[ErrantBard]

xlcFreeMemory() does solve this problem. gc() in itself didn't, so I guess the garbage collection that is also done on the Java-side of things is what releases the last traces of the connection.

[vidarsh-shah]

I tried using
xlcFreeMemory()
gc()
rm(workbook_connection)

none of them works. May be R is holding the permission. It started happening after R 4.0.3 and xlconnect 1.0.1 update. other Excel packages seem to be working fine.

[spoltier]

haven't been able to reproduce (R 4.0.2, ubuntu, xlc latest state) - need to try on Windows as both Excel and R / rJava may handle files differently

[ErrantBard]

Yeah, I still suffer the same problem. For example, loading a workbook and reading it's sheet locks the file for editing which didn't happen in the good old days. New session info:

R version 4.1.0 (2021-05-18)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

  wb <- loadWorkbook("Fees.xlsx")
  dt.Fees <- setDT(readWorksheet(wb, "Data"))

The only thing that consistently helps is re-sourcing the entire enviroment.

[pasipasi123]

Pitching in with a comment. I'm building a Shiny app where users can download results in a preformatted xlsx-file, thus XLConnect. In Windows Shiny can't read the file because it's left open in R, but luckily the download works in Linux where I'm deploying my app.

[ErrantBard]

Yeah, seems to be a Windows-only problem.

[Carolcky]

I encountered the same issue. May I ask when this bug would be fixed?

[Ozeidi]

Same here on Windows 10. Any work around?

[davide-vitiello]

tried both use cases above mentioned , I could only reproduce this one

R version 4.1.0 (2021-05-18)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

  wb <- loadWorkbook("Fees.xlsx")
  dt.Fees <- setDT(readWorksheet(wb, "Data"))

while the very first case works without issue.

library(XLConnect)
#test1
wb <- loadWorkbook("./Cars.xlsx", create = TRUE)
createSheet(wb, "Data")
# open file with Ms Excel
writeWorksheet(wb, cars, "Data")
# make some change via MSE, save
saveWorkbook(wb)
# make some change via MSE, save

#pass

#2
wb <- loadWorkbook("Cars.xlsx")
# open MSE
# make some change via MSE, save => share violation (lock)
#fail

Eventually, it seems to come down to reopening an already existing file, since with the working use case 1 is different from 2 in no other way.
I can confirm running xlcFreeMemory() does not turn arround this issue.

env/platform:

> R.Version()
$platform
[1] "x86_64-w64-mingw32"
$arch
[1] "x86_64"
$os
[1] "mingw32"
$crt
[1] "ucrt"
$system
[1] "x86_64, mingw32"
$status
[1] ""
$major
[1] "4"
$minor
[1] "2.1"
$year
[1] "2022"
$month
[1] "06"
$day
[1] "23"
$`svn rev`
[1] "82513"
$language
[1] "R"
$version.string
[1] "R version 4.2.1 (2022-06-23 ucrt)"
$nickname
[1] "Funny-Looking Kid"

> Sys.info()
          sysname           release           version          
        "Windows"          "10 x64"     "build 19045" 
          machine            
         "x86-64"          

On windows the spec. error reads "Impossible to save changes to due to a sharing violation. Try to save a different file"