Can't connect to openxlsx generated file unless I open in Excel and save it first

[rowrowrowyourboat]

If I generate a file using openxlsx and then try to connect to it from Excel, Excel says that the file is not a valid Excel document. I'm connecting to it in Excel using these menu steps: Data > New Query > From File > From Excel workbook.

I can open that file in Excel just fine. If I open it in Excel and then save it from Excel, I can then connect to it

I'm using Microsoft Office Professional Plus 2016, Windows 11, R 4.2.1 and openxlsx 4.2.5

I'm attaching the file generated by openxlsx and that same file after it was opened and saved by Excel.

Here the code to reproduce it:

library(openxlsx)

print(sessionInfo())
# R version 4.2.1 (2022-06-23 ucrt)
# Platform: x86_64-w64-mingw32/x64 (64-bit)
# Running under: Windows 10 x64 (build 22000)
# 
# Matrix products: default
# 
# locale:
#   [1] LC_COLLATE=English_United States.utf8  LC_CTYPE=English_United States.utf8   
# [3] LC_MONETARY=English_United States.utf8 LC_NUMERIC=C                          
# [5] LC_TIME=English_United States.utf8    
# 
# attached base packages:
#   [1] stats     graphics  grDevices datasets  utils     methods   base     
# 
# other attached packages:
#   [1] openxlsx_4.2.5
# 
# loaded via a namespace (and not attached):
#   [1] compiler_4.2.1 tools_4.2.1    Rcpp_1.0.9     stringi_1.7.8  zip_2.2.1      renv_0.15.5 

write.xlsx(mtcars, 
           ".\\mtcars_astableF_withfilterF.xlsx",
           asTable = F, 
           withFilter = F)

# The file can be opened normally from Excel, but....
# When trying to connect using Data > New Query > From File > From Excel workbook
#    the following error message appears
# 
# Unable to connect
# We encountered an error while trying to connect.
# Details: "The input couldn't be recognized as a valid Excel document."
# Buttons: Retry/Edit/Cancel

write.xlsx(mtcars, 
           ".\\mtcars_astableF_withfilterF.xlsx",
           asTable = F, 
           withFilter = F)

mtcars_astableF_withfilterF_savedfromexcel.xlsx mtcars_astableF_withfilterF.xlsx

[rowrowrowyourboat]

I was comparing the two xlsx files I had attached and opening them up as zip files, I opened up the file /xl/sheet1.xml in both files.

I copied and replaced the text from the start up until (not including) the XML tag <dimension from the Excel saved file to the openxlsx generated file and saved it. Excel was able to read the file at that point so it seems like there must be some issue in that area.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.