XML scheme is not complete

[jliebert007]

Expected Behavior

Import an XLSX file, created via openxlsx, into Power BI

Actual Behavior

When I try to import the XLSX file into Power BI, I get an error: "The input couldn't be recognized as a valid Excel document." To correct the error, I have to open the XLSX, hit "save," and then Power BI can read it without an error.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

1. Create any kind of XLSX file (the contents don't matter AFAIK). Example: df <- (data.frame(matrix(rnorm(20), nrow=10))) style <- createStyle(border = "TopBottomLeftRight", fgFill = "yellow", textDecoration = "bold") write.xlsx(df, "df.xlsx", sheetName = "df", rowNames = FALSE, colnames = TRUE, borders = "all", keepNA = FALSE, colWidths = "auto", headerStyle = style)

Import the created file into Power BI, and Power BI will generate the error message

sessionInfo()

R version 3.6.2 (2019-12-12) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 18362)

Matrix products: default

locale: [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 [4] LC_NUMERIC=C LC_TIME=English_United States.1252

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] openxlsx_4.1.4.9000

loaded via a namespace (and not attached): [1] compiler_3.6.2 tools_3.6.2 Rcpp_1.0.3 stringi_1.4.5 zip_2.0.4

• Version of openxlsx: ‘4.1.4.9000’

• Version of R: R version 3.6.2 (2019-12-12)

[ycphs]

Hi,

The version of openxlsx is an old one. Could you try to use ether the CRAN-Release or the current development version?

If the error occurs again, please let me know.

[jliebert007]

Thank you for looking into this.

I am currently using version 4.1.5 of openxlsx and version 4.0.2 of R. However, I am still getting the same error in Power BI when I try to read an XLSX file created by openxlsx. To fix the error, I need to open the XSLX file, save it, and then reload it into PBI.

[ycphs]

Can you provide the two files (one from openxlsx and the other saved by excel)? Then I could start analyzing the root.

[jliebert007]

I have attached both versions.

_ERROR = version generated by OPENXLSX that Power BI cannot read _NO ERROR = version that I opened, saved, closed, and then successfully read into Power BI

[jliebert007]

Hello, I'm wondering if you may have ever had a chance to take a look at this. Thank you!

[irb75]

It is also possible to recreate this in Excel:

1. Create df.xlsx file with openxlsx df <- (data.frame(matrix(rnorm(20), nrow=10))) write.xlsx(df, "C:/df.xlsx")
2. Open Excel, open a new workbook and try to import data (Data -> Get Data -> From File -> From Workbook -> select .xlsx file and "import")
3. You will see the error "Unable to connect. We encountered an error while trying to connect. Details: The Input couldn't be recognized as a valid Excel Document"
4. Open, save, close df.xlsx
5. It is then possible to import data from df.xlsx into a workbook successfully. df-orig.xlsx df-open-save.xlsx

[ycphs]

I will try to take a closer look in the near future. I hope to provide more insight to this issue.

[archedraft]

Any chance you may have time to look into this? I am also running into this on version 4.2.5

[JanMarvin]

Hi @archedraft , if you want to, you could have a look at openxlsx2 which is currently in active development. I don't have access to power-bi therefore unfortunately I cannot really help you.

[Acord4]

@jliebert007 Did you ever find a solution to this issue? I am running across the same problem a few years later

[majazaloznik]

i can confirm that the issue still exists with openxlsx 4.2.5.2, but that there are no such issues with openxls2 as @JanMarvin suggested above, so i for one will be migrating to that package